正文
InnoDB 存储引擎的锁机制
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
测试环境隔离级别：REPEATABLE-READ
行级别的 - Share and Exclusive Locks- 共享锁 S：允许持有S锁的事务对行进行读操作
- 排他锁 X: 允许持有X锁的事务对行进行update或delete操作
表级别的意向锁 - Intention Lock
InnoDB支持多粒度的锁定，允许行锁和表锁共存。通过意向锁来实现。
比如，SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock.
- 意向共享锁（IS）：在对数据行获取S锁之前，必须先获取IS锁或更强级别的锁
- 意向排他锁（IX）：在对数据行获取X锁之前，必须先获取IX锁
不同类型锁之间的兼容总结
X | IX | S | IS | |
|---|---|---|---|---|
X | Conflict | Conflict | Conflict | Conflict |
IX | Conflict | Compatible | Conflict | Compatible |
S | Conflict | Conflict | Compatible | Compatible |
IS | Conflict | Compatible | Compatible | Compatible |
A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks.
A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannotbe granted because it would cause deadlock, an error occurs.
因此，除了对整个表的请求（比如，LOCK TABLES ... WRITE）外，意向锁不会阻塞其他的事务。持有意向锁表示持有者正在锁定数据行，或者即将锁定数据行。
deadlock：
A deadlock can occur when the transactions lock rows in multiple tables (through statements such as UPDATE or SELECT ... FOR UPDATE), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue.
索引记录上的锁 - Record Lock
比如，SELECT c1 FOR UPDATE FROM t WHERE c1 = 10;避免其他的事务在t.c1=10的位置进行insert、update和delete操作
record lock始终锁定索引记录，即使一个表没有进行索引定义，对于这种情况，InnoDB创建一个隐藏的聚集索引并使用该索引记录锁定
Cap Lock
比如，SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20;会在t.c1在10到20之间的索引记录上加锁，防止其他的事务在t.c1列上插入10到20之间的值
测试：t2.id上无索引
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 5 |
| 8 |
| 11 |
+------+
4 rows in set (0.00 sec)
S1 S2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
mysql> update t2 set id=6 where id=5; Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t2 values(5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(8);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
S1锁住整张表
t2.id上加上索引的验证
mysql> alter table t2 add index id_idx(id);
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
mysql> update t2 set id=6 where id=5; Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into t2 values(8);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values(1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
S1会在[1,5] [5,8)区间加上间隙锁
Next-Key Locks
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 uses next-key locks for searches and index scans, which prevents phantom rows .
幻影读的解释：http://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html
验证如上：S1会在id=5的行加上X锁，为防止幻读，又在5的左边[1,5) 和5的又边（5，8）加上了gap锁
Insert Intention Locks
是gap锁的一种。多个事务如果不是在相同的索引范围内插入，则无需等待彼此
测试：
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 5 |
| 8 |
| 11 |
+------+
会话S1 会话S2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2(id) values(7); mysql> begin;
Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2(id) values(6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(id) values(7);
Query OK, 1 row affected (0.00 sec)
两个会话在插入的行上获取排他锁前，分别在id为5~8的行记录范围内持有intention locks，但不会阻塞对方，因为行之间没有冲突。
再举一个会话在要插入的行记录上获取排他锁之前获取insert intention lock但被阻塞的例子：
S1在id>5的行持有排它锁。此排它锁包含5~11之间的gap锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id>5 for update;
+------+
| id |
+------+
| 8 |
| 11 |
+------+
S2上在S1查询出的行范围内插入行记录。会持有insert intention lock，但是会等待排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
参考链接：
http://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html
http://www.hollischuang.com/archives/923
