mysql innodb間隙鎖示例

innodb的記錄鎖介紹原文見 http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

innodb的記錄鎖有三種型別：

• 記錄鎖：是加在索引記錄上的。
• 間隙鎖：對索引記錄間的範圍加鎖，或者加在最後一個索引記錄的前面或者後面
• Next-key鎖：記錄鎖和間隙鎖的組合，間隙鎖鎖定記錄鎖之前的範圍

間隙鎖主要是防止幻象讀，用在Repeated-Read（簡稱RR）隔離級別下。在Read-Commited（簡稱RC）下，一般沒有間隙鎖（有外來鍵情況下例外，此處不考慮）。間隙鎖還用於statement based replication

間隙鎖有些副作用，如果要關閉，一是將會話隔離級別改到RC下，或者開啟 innodb_locks_unsafe_for_binlog（預設是OFF）。

間隙鎖只會出現在輔助索引上，唯一索引和主鍵索引是沒有間隙鎖。間隙鎖（無論是S還是X）只會阻塞insert操作。

下面演示一種因為間隙鎖而出現等待的情形。

---

準備指令碼

CREATE TABLE `xdual` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_x` (`x`),
  KEY `idx_v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;

: test 09:39:47> select * from xdual;
+----+---------------------+------+
| id | x                   | v    |
+----+---------------------+------+
|  2 | 2012-04-19 20:25:40 |    1 |
|  4 | 2012-04-18 00:53:58 |    3 |
|  6 | 2012-04-18 00:54:00 |    5 |
|  8 | 2012-04-18 18:23:16 |    7 |
| 10 | 2012-04-18 00:54:03 |    2 |
| 12 | 2012-04-18 02:26:13 |    4 |
| 14 | 2012-04-18 00:54:06 |    6 |
| 15 | 2012-04-18 02:26:13 |    4 |
| 16 | 2012-04-18 18:24:14 |    7 |
| 18 | 2012-04-18 00:54:10 |    8 |
| 22 | 2012-04-18 15:12:08 |   18 |
| 26 | 2012-04-18 18:23:16 |    7 |
| 34 | 2012-04-18 02:30:09 |    4 |
+----+---------------------+------+
13 rows in set (0.03 sec)

---

測試場景

#sess1

: test 09:45:40> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

: test 09:46:14> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

: test 09:46:22> delete from xdual where v=8;
Query OK, 1 row affected (0.01 sec)

: test 09:46:50>

#sess2

: test 09:40:20> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

: test 09:46:30> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

: test 09:46:33> insert into xdual values(11,now(),7);
Query OK, 1 row affected (0.00 sec)

: test 09:47:08> insert into xdual values(31,now(),7);
（BLOCKING）

此時用innotop檢視鎖分佈

_________________________________________ InnoDB Locks __________________________________________
ID        Type    Waiting  Wait   Active  Mode  DB    Table  Index    Ins Intent  Special       
24066093  RECORD        1  01:11   01:22  X     test  xdual  idx_v             1  gap before rec
24066093  TABLE         0  01:11   01:22  IX    test  xdual                    0                
24066093  RECORD        1  01:11   01:22  X     test  xdual  idx_v             1  gap before rec
24066090  TABLE         0  00:00   01:40  IX    test  xdual                    0                
24066090  RECORD        0  00:00   01:40  X     test  xdual  idx_v             0                
24066090  RECORD        0  00:00   01:40  X     test  xdual  PRIMARY           0  rec but not gap
24066090  RECORD        0  00:00   01:40  X     test  xdual  idx_v             0  gap before rec
Press any key to continue

很快會話2就timeout

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
: test 09:49:20>

---

分析：

#sess1: delete from xdual where v=8;
這個sql鎖定的範圍是 (7,18)。此時，#sess2如果想插入一筆v=8的資料，肯定被blocking，但是插入一筆v=7的資料，就要看插入記錄的位置是否在這個區間(7,18)以內。
: test 10:06:35> select * from xdual where v=7;
+----+---------------------+------+
| id | x                   | v    |
+----+---------------------+------+
|  8 | 2012-04-18 18:23:16 |    7 |
| 16 | 2012-04-18 18:24:14 |    7 |
| 26 | 2012-04-18 18:23:16 |    7 |
+----+---------------------+------+
3 rows in set (0.00 sec)

insert into xdual values(11,now(),7); 要插入的位置在 id=16和id=26之間，不在上面那個區間內，所以不被blocking
insert into xdual values(31,now(),7); 這個就在被鎖定的區間內，所以被阻塞。
同理，#sess2 下面的sql也會被阻塞

: test 10:06:40> insert into xdual(x,v) values(now(),9);
(BLOCKING)

: test 10:06:40> insert into xdual(x,v) values(now(),9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
: test 10:10:50> insert into xdual(id,x,v) values(20,now(),18);
(BLOCKING)

: test 10:10:50> insert into xdual(id,x,v) values(20,now(),18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
: test 10:14:35>

---