mysql鎖表就是為了更好的處理超大負載時的一個優化東西,下面我來給各位同學介紹一下mysql鎖表機制與mysql鎖表碰到的一些問題分析.

為了給高并發情況下的mysql進行更好的優化,有必要了解一下mysql查詢更新時的鎖表機制.

一、概述

MySQL有三種鎖的級別：頁級、表級、行級。

MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；BDB存儲引擎采用的是頁面鎖（page-levellocking）,但也支持表級鎖；InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是采用行級鎖。

MySQL這3種鎖的特性可大致歸納如下:

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高,并發度最低。

行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高。

頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

二、MyISAM表鎖

MyISAM存儲引擎只支持表鎖,是現在用得最多的存儲引擎.

1、查詢表級鎖爭用情況

可以通過檢查table_locks_waited和table_locks_immediate狀態變量來分析系統上的表鎖定爭奪,代碼如下:

1. mysql> show status like ‘table%’; 
2. +———————–+———-+ 
3. | Variable_name | Value | 
4. +———————–+———-+ 
5. | Table_locks_immediate | 76939364 | 
6. | Table_locks_waited | 305089 | 
7. +———————–+———-+ 

2 rows in set (0.00 sec)Table_locks_waited的值比較高，說明存在著較嚴重的表級鎖爭用情況。

2、MySQL表級鎖的鎖模式

MySQL的表級鎖有兩種模式:表共享讀鎖(Table Read Lock)和表獨占寫鎖(Table WriteLock),MyISAM在執行查詢語句(SELECT)前,會自動給涉及的所有表加讀鎖，在執行更新操作(UPDATE、DELETE、INSERT等)前,會自動給涉及的表加寫鎖.

所以對MyISAM表進行操作,會有以下情況:

a、對MyISAM表的讀操作(加讀鎖),不會阻塞其他進程對同一表的讀請求,但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執行其它進程的寫操作。

b、對MyISAM表的寫操作(加寫鎖),會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放后，才會執行其它進程的讀寫操作。

下面通過例子來進行驗證以上觀點,數據表gz_phone里有二百多萬數據,字段id,phone,ua,day。現在同時用多個客戶端同時對該表進行操作分析.

a、當我用客戶端1進行一個比較長時間的讀操作時,分別用客戶端2進行讀和寫操作.

client1,代碼如下:

1. mysql>select count(*) from gz_phone group by ua; 
2. 75508 rows in set (3 min 15.87 sec) client2: 
3. select id,phone from gz_phone limit 1000,10; 
4. +——+——-+ 
5. | id | phone | 
6. +——+——-+ 
7. | 1001 | 2222 | 
8. | 1002 | 2222 | 
9. | 1003 | 2222 | 
10. | 1004 | 2222 | 
11. | 1005 | 2222 | 
12. | 1006 | 2222 | 
13. | 1007 | 2222 | 
14. | 1008 | 2222 | 
15. | 1009 | 2222 | 
16. | 1010 | 2222 | 
17. +——+——-+ 
18. 10 rows in set (0.01 sec) 
19. mysql> update gz_phone set phone=’11111111111′ where id=1001; 
20. Query OK, 0 rows affected (2 min 57.88 sec) 
21. Rows matched: 1 Changed: 0 Warnings: 0 

說明當數據表有一個讀鎖時,其它進程的查詢操作可以馬上執行,但更新操作需等待讀鎖釋放后才會執行.

b、當用客戶端1進行一個較長時間的更新操作時,用客戶端2,3分別進行讀寫操作.

client1,代碼如下:

1. mysql> update gz_phone set phone=’11111111111′; 
2. Query OK, 1671823 rows affected (3 min 4.03 sec) 
3. Rows matched: 2212070 Changed: 1671823 Warnings: 0 client2: 
4. mysql> select id,phone,ua,day from gz_phone limit 10; 
5. +—-+——-+——————-+————+ 
6. | id | phone | ua | day | 
7. +—-+——-+——————-+————+ 
8. | 1 | 2222 | SonyEricssonK310c | 2007-12-19 | 
9. | 2 | 2222 | SonyEricssonK750c | 2007-12-19 | 
10. | 3 | 2222 | MAUI WAP Browser | 2007-12-19 | 
11. | 4 | 2222 | Nokia3108 | 2007-12-19 | 
12. | 5 | 2222 | LENOVO-I750 | 2007-12-19 | 
13. | 6 | 2222 | BIRD_D636 | 2007-12-19 | 
14. | 7 | 2222 | SonyEricssonS500c | 2007-12-19 | 
15. | 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 | 
16. | 9 | 2222 | NokiaN73-1 | 2007-12-19 | 
17. | 10 | 2222 | Nokia2610 | 2007-12-19 | 
18. +—-+——-+——————-+————+ 
19. 10 rows in set (2 min 58.56 sec) client3: 
20. mysql> update gz_phone set phone=’55555′ where id=1; 
21. Query OK, 1 row affected (3 min 50.16 sec) 
22. Rows matched: 1 Changed: 1 Warnings: 0 

說明當數據表有一個寫鎖時,其它進程的讀寫操作都需等待讀鎖釋放后才會執行.

3、并發插入

原則上數據表有一個讀鎖時,其它進程無法對此表進行更新操作,但在一定條件下,MyISAM表也支持查詢和插入操作的并發進行.

MyISAM存儲引擎有一個系統變量concurrent_insert,專門用以控制其并發插入的行為,其值分別可以為0、1或2.

a、當concurrent_insert設置為0時,不允許并發插入.

b、當concurrent_insert設置為1時,如果MyISAM表中沒有空洞,即表的中間沒有被刪除的行,MyISAM允許在一個進程讀表的同時,另一個進程從表尾插入記錄,這也是MySQL的默認設置.

c、當concurrent_insert設置為2時,無論MyISAM表中有沒有空洞，,都允許在表尾并發插入記錄.

4、MyISAM的鎖調度

由于MySQL認為寫請求一般比讀請求要重要,所以如果有讀寫請求同時進行的話,MYSQL將會優先執行寫操作,這樣MyISAM表在進行大量的更新操作時,特別是更新的字段中存在索引的情況下,會造成查詢操作很難獲得讀鎖,從而導致查詢阻塞.

我們可以通過一些設置來調節MyISAM的調度行為:

a、通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。

b、通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先級降低。

c、通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級。

上面3種方法都是要么更新優先,要么查詢優先的方法,這里要說明的就是,不要盲目的給mysql設置為讀優先,因為一些需要長時間運行的查詢操作,也會使寫進程“餓死”,只有根據你的實際情況,來決定設置哪種操作優先,這些方法還是沒有從根本上同時解決查詢和更新的問題.

在一個有大數據量高并發表的mysql里,我們還可采用另一種策略來進行優化,那就是通過mysql主從（讀寫）分離來實現負載均衡,這樣可避免優先哪一種操作從而可能導致另一種操作的堵塞,下面將用一個篇幅來說明mysql的讀寫分離技術.

殺掉被鎖的表,代碼如下:

SHOW PROCESSLIST查看數據庫中表的狀態,是否被鎖.

1. kill id //殺掉被鎖的表 
2.  
3. =================================================== 
4. set autocommit=0; 
5. select * from t1  where uid='xxxx' for update    //在有索引（例如uid）的情況下是行鎖，否則是表鎖 
6. insert into t1 values(1,'xxxxx'); 
7. commit; 
8. ===================================================== 
9. lock tables t1 write|read; 
10. insert into t1 values(2,'xxxxx'); //只有insert 
11. unlock tables; 

下面描述了一些方法來避免或減少表鎖定造成的競爭:

· 試圖使 SELECT 語句運行得更快,可能必須創建一些摘要(summary)表做到這點。

· 用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下，在先前情形的第2個SELECT語句將在UPDATE語句前執行，而不需要等候第1個 SELECT 完成.

· 可以使用SET_UPDATES=1語句指定具體連接中的所有更新應使用低優先級.

· 可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級.

· 可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級.

· 為max_write_lock_count系統變量指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成后臨時提高所有等待一個表的SELECT 語句的優先級,這樣允許在一定數量的WRITE鎖定后給出READ鎖定.

· 如果你有關于INSERT結合SELECT的問題,切換到使用新的MyISAM表,因為它們支持并發的SELECT和INSERT.

· 如果你對同一個表混合插入和刪除,INSERT DELAYED將會有很大的幫助.

· 如果你對同一個表混合使用 SELECT 和DELETE 語句出現問題,DELETE 的LIMIT 選項可以有所幫助.

· 對 SELECT 語句使用SQL_BUFFER_RESULT可以幫助使表鎖定時間變短。

· 可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列。在這種情況下，寫鎖定和讀鎖定將具有相同的優先級，對一些應用程序會有幫助。

這里是一些MySQL中表鎖定相關的技巧:

· 如果不混合更新與需要在同一個表中檢查許多行的選擇,可以進行并行操作.

· 可以使用 LOCK TABLES 來提高速度,因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多,將表中的內容切分為幾個表也可以有所幫助.

· 如果在MySQL中表鎖定時遇到速度問題,可以將表轉換為 InnoDB 或BDB 表來提高性能.