mysql innodb间隙锁示例

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;

* 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

autocommit=0;

Query OK, 0 rows affected (0.00 sec)

tx_isolation='REPEATABLE-READ';

from xdual where v=8;

Query OK, 1 row affected (0.01 sec)

#sess2

into xdual values(11,now(),7);

Query OK, 1 row affected (0.00 sec)

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

分析：

#sess1: delete from xdual where v=8;

这个sql锁定的范围是

(7,18)。此时，#sess2如果想插入一笔v=8的数据，肯定被blocking，但是插入一笔v=7的数据，就要看插入记录的位置是否在这个区间(7,18)以内。

from xdual where v=7;

| 16 | 2012-04-18 18:24:14 |    7 |

| 26 |

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也会被阻塞

into xdual(x,v) values(now(),9);

(BLOCKING)

ERROR 1205 (HY000): Lock

wait timeout exceeded; try restarting transaction

xdual(id,x,v) values(20,now(),18);

<a href="mailto:root@localhost">root@localhost</a>

: test 10:10:50&gt; insert into xdual(id,x,v) values(20,now(),18);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting

transaction