innodb的记录锁有三种类型：
sql
间隙锁主要是防止幻象读，用在Repeated-Read（简称RR）隔离级别下。在Read-Commited（简称RC）下，通常没有间隙锁（有外键状况下例外，此处不考虑）。间隙锁还用于statement based replicationide
间隙锁有些反作用，若是要关闭，一是将会话隔离级别改到RC下，或者开启 innodb_locks_unsafe_for_binlog（默认是OFF）。测试
间隙锁只会出如今辅助索引上，惟一索引和主键索引是没有间隙锁。间隙锁（不管是S仍是X）只会阻塞insert操做。
rest
下面演示一种由于间隙锁而出现等待的情形。索引
准备脚本ci
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;it
root@localhost : 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)io
测试场景innodb
#sess1class
root@localhost : test 09:45:40> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 09:46:14> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 09:46:22> delete from xdual where v=8;
Query OK, 1 row affected (0.01 sec)
root@localhost : test 09:46:50>
#sess2
root@localhost : test 09:40:20> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 09:46:30> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@localhost : test 09:46:33> insert into xdual values(11,now(),7);
Query OK, 1 row affected (0.00 sec)
root@localhost : 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
root@localhost : test 09:49:20>
分析：
#sess1: delete from xdual where v=8;
这个sql锁定的范围是 (7,18)。此时，#sess2若是想插入一笔v=8的数据，确定被blocking，可是插入一笔v=7的数据，就要看插入记录的位置是否在这个区间(7,18)之内。
root@localhost : 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也会被阻塞
root@localhost : test 10:06:40> insert into xdual(x,v) values(now(),9);
(BLOCKING)
root@localhost : test 10:06:40> insert into xdual(x,v) values(now(),9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
root@localhost : test 10:10:50> insert into xdual(id,x,v) values(20,now(),18);
(BLOCKING)
root@localhost : test 10:10:50> insert into xdual(id,x,v) values(20,now(),18);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionroot@localhost : test 10:14:35>