Innodb鎖機制：Next-Key Lock 淺談 · 數據庫學習摘記

來自數據庫使用鎖是為了支持更好的并發，提供數據的完整性和一致性。InnoDB是一個支持行鎖的存儲引擎，鎖的類型有：共享鎖（S）、排他鎖（X）、意向共享（IS）、意向排他（IX）。為了提供更好的并發，InnoDB提供了非鎖定讀：不需要等待訪問行上的鎖釋放，讀取行的一個快照。該方法是通過InnoDB的一個特性：MVCC來實現的。 **InnoDB有三種行鎖的算法：** 1，Record Lock：單個行記錄上的鎖。 2，Gap Lock：間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。 3，Next-Key Lock：1+2，鎖定一個范圍，并且鎖定記錄本身。對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。 **測試一：默認RR隔離級別** ![復制代碼](http://common.cnblogs.com/images/copycode.gif) root@localhost : test 10:56:10>create table t(a int,key idx_a(a))engine =innodb; Query OK, 0 rows affected (0.20 sec) root@localhost : test 10:56:13>insert into t values(1),(3),(5),(8),(11); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 root@localhost : test 10:56:15>select * from t; +------+ | a | +------+ | 1 | | 3 | | 5 | | 8 | | 11 | +------+ 5 rows in set (0.00 sec) **section A:** root@localhost : test 10:56:27>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost : test 10:56:29>select * from t where a = 8 for update; +------+ | a | +------+ | 8 | +------+ 1 row in set (0.00 sec) **section B:** root@localhost : test 10:54:50>begin; Query OK, 0 rows affected (0.00 sec) root@localhost : test 10:56:51>select * from t; +------+ | a | +------+ | 1 | | 3 | | 5 | | 8 | | 11 | +------+ 5 rows in set (0.00 sec) root@localhost : test 10:56:54>insert into t values(2); Query OK, 1 row affected (0.00 sec) root@localhost : test 10:57:01>insert into t values(4); Query OK, 1 row affected (0.00 sec) ++++++++++ root@localhost : test 10:57:04>insert into t values(6); root@localhost : test 10:57:11>insert into t values(7); root@localhost : test 10:57:15>insert into t values(9); root@localhost : test 10:57:33>insert into t values(10); ++++++++++ **上面全被鎖住，阻塞住了** root@localhost : test 10:57:39>insert into t values(12); Query OK, 1 row affected (0.00 sec) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **問題：** **為什么section B上面的插入語句會出現鎖等待的情況**？InnoDB是行鎖，在section A里面鎖住了a=8的行，其他應該不受影響。why？ **分析：** 因為InnoDB對于行的查詢都是采用了Next-Key Lock的算法，鎖定的不是單個值，而是一個范圍（GAP）。上面索引值有1，3，5，8，11，其記錄的GAP的區間如下：是一個**左開右閉**的空間（原因是默認主鍵的有序自增的特性，結合后面的例子說明）（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）特別需要注意的是，InnoDB存儲引擎還會對輔助索引下一個鍵值加上gap lock。如上面分析，那就可以解釋了。 ![復制代碼](http://common.cnblogs.com/images/copycode.gif) root@localhost : test 10:56:29>select * from t where a = 8 for update; +------+ | a | +------+ | 8 | +------+ 1 row in set (0.00 sec) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 該SQL語句鎖定的范圍是（5,8]，下個下個鍵值范圍是（8,11]，所以插入5~11之間的值的時候都會被鎖定，要求等待。即：插入5，6，7，8，9，10 會被鎖住。插入非這個范圍內的值都正常。 **################################### 2016-07-21 更新?** 因為例子里沒有主鍵，所以要用隱藏的ROWID來代替，數據根據Rowid進行排序。而Rowid是有一定順序的（自增），所以其中11可以被寫入，5不能被寫入，不清楚的可以再看一個有主鍵的例子： ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 會話1： 01:43:07>create table t(id int,name varchar(10),key idx_id(id),primary key(name))engine =innodb; Query OK, 0 rows affected (0.02 sec) 01:43:11>insert into t values(1,'a'),(3,'c'),(5,'e'),(8,'g'),(11,'j'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 01:44:03>select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec) 01:44:58>select * from t; +------+------+ | id | name | +------+------+ | 1 | a | | 3 | c | | 5 | e | | 8 | g | | 11 | j | +------+------+ 5 rows in set (0.00 sec) 01:45:07>start transaction; 01:45:09>delete from t where id=8; Query OK, 1 row affected (0.01 sec) 會話2： 01:50:38>select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec) 01:50:48>start transaction; 01:50:51>select * from t; +------+------+ | id | name | +------+------+ | 1 | a | | 3 | c | | 5 | e | | 8 | g | | 11 | j | +------+------+ 5 rows in set (0.01 sec) 01:51:35>insert into t(id,name) values(6,'f'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 01:53:32>insert into t(id,name) values(5,'e1'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 01:53:41>insert into t(id,name) values(7,'h'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 01:54:43>insert into t(id,name) values(8,'gg'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 01:55:10>insert into t(id,name) values(9,'k'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 01:55:23>insert into t(id,name) values(10,'p'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 01:55:33>insert into t(id,name) values(11,'iz'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted #########上面看到 id：5，6，7，8，9，10，11都被鎖了。 #########下面看到 id：5，11 還是可以插入的 01:54:33>insert into t(id,name) values(5,'cz'); Query OK, 1 row affected (0.01 sec) 01:55:59>insert into t(id,name) values(11,'ja'); Query OK, 1 row affected (0.01 sec) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **分析：**因為會話1已經對id=8的記錄加了一個X鎖，由于是RR隔離級別，INNODB要防止幻讀需要加GAP鎖：即id=5（8的左邊），id=11（8的右邊）之間需要加間隙鎖（GAP）。這樣[5,e]和[8,g]，[8,g]和[11,j]之間的數據都要被鎖。上面測試已經驗證了這一點，根據索引的有序性，數據按照主鍵（name）排序，后面寫入的[5,cz]（[5,e]的左邊）和[11,ja]（[11,j]的右邊）不屬于上面的范圍從而可以寫入。另外一種情況，把name主鍵去掉會是怎么樣的情況？有興趣的同學可以測試一下。 **##################################################** **繼續：**插入超時失敗后，會怎么樣？超時時間的參數：innodb_lock_wait_timeout ，默認是50秒。超時是否回滾參數：innodb_rollback_on_timeout 默認是OFF。 ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **section A:** root@localhost : test 04:48:51>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:48:53>select * from t where a = 8 for update; +------+ | a | +------+ | 8 | +------+ 1 row in set (0.01 sec) **section B:** root@localhost : test 04:49:04>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:49:07>insert into t values(12); Query OK, 1 row affected (0.00 sec) root@localhost : test 04:49:13>insert into t values(10); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost : test 04:50:06>select * from t; +------+ | a | +------+ | 1 | | 3 | | 5 | | 8 | | 11 | | 12 | +------+ 6 rows in set (0.00 sec) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 經過測試，不會回滾超時引發的異常，當參數innodb_rollback_on_timeout 設置成ON時，則可以回滾，會把插進去的12回滾掉。默認情況下，InnoDB存儲引擎不會回滾超時引發的異常，除死鎖外。既然InnoDB有三種算法，那Record Lock什么時候用？還是用上面的列子，把輔助索引改成唯一屬性的索引。 **測試二：** ![復制代碼](http://common.cnblogs.com/images/copycode.gif) root@localhost : test 04:58:49>create table t(a int primary key)engine =innodb; Query OK, 0 rows affected (0.19 sec) root@localhost : test 04:59:02>insert into t values(1),(3),(5),(8),(11); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 root@localhost : test 04:59:10>select * from t; +----+ | a | +----+ | 1 | | 3 | | 5 | | 8 | | 11 | +----+ 5 rows in set (0.00 sec) **section A:** root@localhost : test 04:59:30>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:59:33>select * from t where a = 8 for update; +---+ | a | +---+ | 8 | +---+ 1 row in set (0.00 sec) **section B:** root@localhost : test 04:58:41>start transaction; Query OK, 0 rows affected (0.00 sec) root@localhost : test 04:59:45>insert into t values(6); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:00:05>insert into t values(7); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:00:08>insert into t values(9); Query OK, 1 row affected (0.00 sec) root@localhost : test 05:00:10>insert into t values(10); Query OK, 1 row affected (0.00 sec) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **問題：** 為什么section B上面的插入語句可以正常，和測試一不一樣？ **分析：** 因為InnoDB對于行的查詢都是采用了Next-Key Lock的算法，鎖定的不是單個值，而是一個范圍，按照這個方法是會和第一次測試結果一樣。但是，當查詢的索引含有唯一屬性的時候，Next-Key Lock 會進行優化，將其降級為Record Lock，即僅鎖住索引本身，不是范圍。注意：通過主鍵或則唯一索引來鎖定不存在的值，也會產生GAP鎖定。即：? ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 會話1： 04:22:38>show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) 04:22:49>start transaction; 04:23:16>select * from t where id = 15 for update; Empty set (0.00 sec) 會話2： 04:26:10>insert into t(id,name) values(10,'k'); Query OK, 1 row affected (0.01 sec) 04:26:26>insert into t(id,name) values(12,'k'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 04:29:32>insert into t(id,name) values(16,'kxx'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted 04:29:39>insert into t(id,name) values(160,'kxx'); ^CCtrl-C -- sending "KILL QUERY 9851" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted? ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 如何讓測試一不阻塞？可以顯式的關閉Gap Lock： 1：把事務隔離級別改成：Read Committed，提交讀、不可重復讀。SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 2：修改參數：innodb_locks_unsafe_for_binlog 設置為1。 **總結：** 本文只對 Next-Key Lock 做了一些說明測試，關于鎖還有很多其他方面的知識，可以查閱相關資料進行學習。寫完之后的幾天剛好牛人寫了一篇詳細的文章：[http://hedengcheng.com/?p=771](http://hedengcheng.com/?p=771)