MySQL鎖機制

標籤：http   io   ar   使用   for   sp   strong   資料   div   

一、概況
MySQL的鎖機制比較簡單，其最顯著的特點是不同的儲存引擎支援不同的鎖機制。比如，MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；BDB儲存引擎採用的是頁面鎖（page-level locking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下。
·表級鎖：開銷小，加鎖快；不會出現死結；鎖定粒度大，發生鎖衝突的機率最高,並發度最低。
·行級鎖：開銷大，加鎖慢；會出現死結；鎖定粒度最小，發生鎖衝突的機率最低,並發度也最高。
·頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死結；鎖定粒度界於表鎖和行鎖之間，並發度一般。
從上述特點可見，表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用，如Web應用；而行級鎖則更適合於有大量按索引條件並發更新少量不同資料，同時又有並發查詢的應用，如一些線上交易處理（OLTP）系統。
二、MyISAM表鎖
MyISAM儲存引擎只支援表鎖，是現在用得最多的儲存引擎。MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要使用者幹預。

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表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。
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的讀寫分離技術。
三、InnoDB行鎖

InnoDB實現了以下兩種類型的行鎖。 

? 共用鎖定（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的獨佔鎖定。

? 獨佔鎖定（X)：允許獲得獨佔鎖定的事務更新資料，阻止其他事務取得相同資料集的共用讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意圖鎖定（Intention Locks），這兩種意圖鎖定都是表鎖。

? 意圖共用鎖（IS）：事務打算給資料行加行共用鎖定，事務在給一個資料行加共用鎖定前必須先取得該表的IS鎖。

? 意向獨佔鎖定（IX）：事務打算給資料行加行獨佔鎖定，事務在給一個資料行加獨佔鎖定前必須先取得該表的IX鎖。

上述鎖模式的相容情況具體如下表所示。

InnoDB行鎖模式相容性列表

 

 

請求鎖模式      是否相容   當前鎖模式	X	IX	S	IS
X	衝突	衝突	衝突	衝突
IX	衝突	相容	衝突	相容
S	衝突	衝突	相容	相容
IS	衝突	相容	相容	相容

 

 

如果一個事務請求的鎖模式與當前的鎖相容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不相容，該事務就要等待鎖釋放。

意圖鎖定是InnoDB自動加的，不需使用者幹預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加獨佔鎖定（X)；對於普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集加共用鎖定或獨佔鎖定。

·共用鎖定（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

·獨佔鎖定（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE獲得共用鎖定，主要用在需要資料依存關係時來確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死結，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT... FOR UPDATE方式獲得獨佔鎖定。 

轉載請註明地址: http://www.phpddt.com/db/mysql-locking.html 尊重他人勞動成果就是尊重自己!

MySQL鎖機制