【鎖】MySQL間隙鎖
前段時間系統老是出現insert死鎖，很是糾結。經過排查發現是間隙鎖！間隙鎖是innodb中行鎖的一種， 但是這種鎖鎖住的卻不止一行資料，他鎖住的是多行，是一個資料範圍。間隙鎖的主要作用是為了防止出現幻讀，但是它會把鎖定範圍擴大，有時候也會給我們帶來麻煩，我們就遇到了。 在資料庫引數中， 控制間隙鎖的引數是：innodb_locks_unsafe_for_binlog， 這個引數預設值是OFF， 也就是啟用間隙鎖， 他是一個bool值， 當值為true時表示disable間隙鎖。那為了防止間隙鎖是不是直接將innodb_locaks_unsafe_for_binlog設定為true就可以了呢？ 不一定！而且這個引數會影響到主從複製及災難恢復， 這個方法還尚待商量。
間隙鎖的出現主要集中在同一個事務中先delete 後 insert的情況下， 當我們透過一個引數去刪除一條記錄的時候， 如果引數在資料庫中存在， 那麼這個時候產生的是普通行鎖， 鎖住這個記錄， 然後刪除， 然後釋放鎖。如果這條記錄不存在，問題就來了， 資料庫會掃描索引，發現這個記錄不存在， 這個時候的delete語句獲取到的就是一個間隙鎖，然後資料庫會向左掃描掃到第一個比給定引數小的值， 向右掃描掃描到第一個比給定引數大的值， 然後以此為界，構建一個區間， 鎖住整個區間內的資料， 一個特別容易出現死鎖的間隙鎖誕生了。
舉個例子：
表task_queue
Id taskId
1 2
3 9
10 20
40 41
開啟一個會話： session 1
sql> set autocommit=0;
##
取消自動提交
sql> delete from task_queue where taskId = 20;
sql> insert into task_queue values(20, 20);
在開啟一個會話： session 2
sql> set autocommit=0;
##
取消自動提交
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(30, 25);
在沒有併發，或是極少併發的情況下， 這樣會可能會正常執行，在Mysql中， 事務最終都是穿行執行， 但是在高併發的情況下， 執行的順序就極有可能發生改變， 變成下面這個樣子：
sql> delete from task_queue where taskId = 20;
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(20, 20);
sql> insert into task_queue values(30, 25);
這個時候最後一條語句：insert into task_queue values(30, 25); 執行時就會爆出死鎖錯誤。因為刪除taskId = 20這條記錄的時候，20 -- 41 都被鎖住了， 他們都取得了這一個資料段的共享鎖， 所以在獲取這個資料段的排它鎖時出現死鎖。
這種問題的解決辦法：前面說了， 透過修改innodb_locaks_unsafe_for_binlog引數來取消間隙鎖從而達到避免這種情況的死鎖的方式尚待商量， 那就只有修改程式碼邏輯， 存在才刪除，儘量不去刪除不存在的記錄。
20.3.5 間隙鎖（Next-Key鎖）
《深入淺出——資料庫開發、最佳化與管理維護》從資料庫的基礎、開發、最佳化、管理4方面對MySQL進行了詳細的介紹，其中每一部分都獨立成篇，每一篇又包括多個章節。本書面向實用，內容覆蓋廣泛，講解由淺入深，適合於各個層次的讀者。本文介紹了InnoDB鎖。
20.3.5 間隙鎖（Next-Key鎖）
當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。
舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
|
是一個範圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的“間隙”加鎖。
InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對於上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大於100的任何記錄，那麼本事務如果再次執行上述語句，就會發生幻讀；另外一方面，是為了滿足其恢復和複製的需要。有關其恢復和複製對鎖機制的影響，以及不同隔離級別下InnoDB使用間隙鎖的情況，在後續的章節中會做進一步介紹。
很顯然，在使用範圍條件檢索並鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍內鍵值的併發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是併發插入比較多的應用，我們要儘量最佳化業務邏輯，儘量使用相等條件來訪問更新資料，避免使用範圍條件。
還要特別說明的是，InnoDB除了透過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！
在如表20-13所示的例子中，假如emp表中只有101條記錄，其empid的值分別是1,2,......,100,101。
表20-13 InnoDB儲存引擎的間隙鎖阻塞例子
tbody>
|
session_1
|
session_2
|
|
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
|
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
|
|
當前session對不存在的記錄加for update的鎖：
mysql> select * from emp where empid = 102 for update;
Empty set (0.00 sec)
|
|
|
|
這時，如果其他session插入empid為201的記錄（注意：這條記錄並不存在），也會出現鎖等待：
mysql>insert into emp(empid,...) values(201,...);
阻塞等待
|
|
Session_1 執行rollback：
mysql> rollback;
Query OK, 0 rows affected (13.04 sec)
|
|
|
|
由於其他session_1回退後釋放了Next-Key鎖，當前session可以獲得鎖併成功插入記錄：
mysql>insert into emp(empid,...) values(201,...);
Query OK, 1 row affected (13.35 sec)
|
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 14.5.4, “Phantom Rows”).
Transaction data for a next-key lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc
About Me
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2143009/，如需轉載，請註明出處，否則將追究法律責任。
