mysql 鎖機制

一、概述

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

MyISAM和MEMORY儲存引擎採用的是表級鎖table-level locking）；BDB儲存引擎採用的是頁面鎖page-level locking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。

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

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

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

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

二、MyISAM表鎖

MyISAM儲存引擎只支援表鎖，是現在用得最多的儲存引擎。

1、查詢表級鎖爭用情況

可以通過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪：

mysql> show status like ‘table%’;

+———————–+———-+

| Variable_name | Value |

+———————–+———-+

| Table_locks_immediate | 76939364 |

| Table_locks_waited | 305089 |

+———————–+———-+

2 rows in set (0.00 sec)

Table_locks_waited的值比較高，說明存在著較嚴重的表級鎖爭用情況。

2、MySQL表級鎖的鎖模式

MySQL的表級鎖有兩種模式：表共用讀鎖Table Read Lock）和表獨佔寫鎖Table Write Lock）。MyISAM在執行查詢語句SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖。

所以對MyISAM表進行操作，會有以下情況：

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

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

下面通過例子來進行驗證以上觀點。資料表gz_phone裡有二百多萬資料，欄位id,phone,ua,day。現在同時用多個用戶端同時對該表進行操作分析。

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

client1:

mysql>select count(*) from gz_phone group by ua;

75508 rows in set (3 min 15.87 sec)

client2:

select id,phone from gz_phone limit 1000,10;

+——+——-+

| id | phone |

+——+——-+

| 1001 | 2222 |

| 1002 | 2222 |

| 1003 | 2222 |

| 1004 | 2222 |

| 1005 | 2222 |

| 1006 | 2222 |

| 1007 | 2222 |

| 1008 | 2222 |

| 1009 | 2222 |

| 1010 | 2222 |

+——+——-+

10 rows in set (0.01 sec)

mysql> update gz_phone set phone=’11111111111′ where id=1001;

Query OK, 0 rows affected (2 min 57.88 sec)

Rows matched: 1 Changed: 0 Warnings: 0

說明當資料表有一個讀鎖時，其它進程的查詢操作可以馬上執行，但更新操作需等待讀鎖釋放後才會執行。

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

client1:

mysql> update gz_phone set phone=’11111111111′;

Query OK, 1671823 rows affected (3 min 4.03 sec)

Rows matched: 2212070 Changed: 1671823 Warnings: 0

client2:

mysql> select id,phone,ua,day from gz_phone limit 10;

+—-+——-+——————-+————+

| id | phone | ua | day |

+—-+——-+——————-+————+

| 1 | 2222 | SonyEricssonK310c | 2007-12-19 |

| 2 | 2222 | SonyEricssonK750c | 2007-12-19 |

| 3 | 2222 | MAUI WAP Browser | 2007-12-19 |

| 4 | 2222 | Nokia3108 | 2007-12-19 |

| 5 | 2222 | LENOVO-I750 | 2007-12-19 |

| 6 | 2222 | BIRD_D636 | 2007-12-19 |

| 7 | 2222 | SonyEricssonS500c | 2007-12-19 |

| 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 |

| 9 | 2222 | NokiaN73-1 | 2007-12-19 |

| 10 | 2222 | Nokia2610 | 2007-12-19 |

+—-+——-+——————-+————+

10 rows in set (2 min 58.56 sec)

client3:

mysql> update gz_phone set phone=’55555′ where id=1;

Query OK, 1 row affected (3 min 50.16 sec)

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設定為讀優先，因為一些需要長時間啟動並執行查詢操作，也會使寫進程“餓死”。只有根據你的實際情況，來決定設定哪種操作優先。這些方法還是沒有從根本上同時解決查詢和更新的問題。