前面分析过MySQL的锁分类方式，MyISAM仅支持表锁，不支持行锁，所以不需要做过多分析。这篇文章就针对InnoDB分析一下加锁的案例。
创建一个表student，带有字段id，name，age，address，其中id是主键，name是唯一索引，age是普通索引，address没有索引。
初始化数据
mysql> select * from student;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 1 | gary | 30 | suzhou |
| 2 | kerry | 30 | nantong |
+----+-------+-----+---------+
2 rows in set (0.00 sec)
非索引项的锁升级表锁
在session 1里面查询address = 'nantong'的记录，并加上排他锁。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where address = 'nantong' for update;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 2 | kerry | 30 | nantong |
+----+-------+-----+---------+
1 row in set (0.00 sec)
session 2里面去更新id = 1的age字段。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update student set age = 10 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到的行为是整个表都被锁住了。
结论:
|-- 非索引条件加锁会变成表锁。
间隙锁和Next-Key lock
除了行锁和表锁之外，InnoDB引入间隙锁，为了解决RR隔离级别下的不可重复读带来的幻读问题。
当使用范围条件查询而不是等值条件查询的时候，InnoDB就会给符合条件的范围索引加锁，在条件范围内不存在的记录就叫做“间隙”。
这里已经排除了非索引项的可能性，那么下面就需要讨论几个方面问题：
普通索引项是否会加间隙锁？
范围查询是否会加间隙锁？
唯一索引和主键索引是否会加间隙锁？
不存在的检索条件是否会加间隙锁？
再开始之前准备的数据
mysql> select * from student;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 1 | gary | 1 | suzhou |
| 2 | kerry | 5 | nantong |
| 5 | gary1 | 10 | sz |
| 6 | test | 15 | nt |
| 7 | test1 | 20 | sz |
+----+-------+-----+---------+
5 rows in set (0.00 sec)
Next-Key lock会在age字段分间隙 (-&,1], (1,5], (5,10], (10,15], (15,20], (20,+&)
普通索引项
- 通过普通索引，查询一个不存在的索引值，比如12，落在(10,15]的范围内，因为12不存在所以不会加行锁，那么间隙锁的范围就是(10,15)。
session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where age = 12 for update;
Empty set (0.00 sec)
session 2
mysql> update student set address = 'teest' where id = 5; --age=10
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set address = 'teest' where id = 6; --age=15
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into student(name,age,address) values ('test2', 11, 'sz');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into student(name,age,address) values ('test2', 14, 'sz');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到age为10和15的行并没有锁，可以更新成功，而age为11和14的值是insert失败的。
结论：
|-- RR级别下，普通索引未命中，会加gap lock。
|-- RC级别，不存在Gap lock，所以不会加锁
- 通过普通索引，查询一个存在的索引值，比如15，落在(10,15]的范围内，这个时候就是锁定的(10, 20)。
mysql> update student set address = 'teest' where id = 5; -- age=10
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update student set address = 'teest' where id = 6; -- age=15
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update student set address = 'teest' where id = 7; -- age=20
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into student(name,age,address) values ('test2', 11, 'sz');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into student(name,age,address) values ('test2', 19, 'sz');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到age为10和20的行是没有锁的，更新成功，而10和20之间都不能做更新和插入。
结论：
|-- RR级别下，普通索引命中，普通索引会加next-key lock，聚簇索引加X锁
|-- RC级别，对普通索引和聚簇索引都加X锁
唯一索引项
通过唯一索引，查询一个不存在的索引值，与普通索引类似。
结论：
|-- RR级别下，唯一索引未命中，只会在唯一索引加gap lock
|-- RC级别，不存在Gap lock，所以不会加锁通过唯一索引，查询存在的索引值，这里不会再加gap lock了，都会退化成record lock。
结论：
|-- RR级别，对唯一索引和聚簇索引都加X锁
|-- RC级别，对唯一索引和聚簇索引都加X锁
聚簇索引
- 查询未命中
|-- RR级别下，聚簇索引加gap lock
|-- RC级别，不加锁 - 查询命中
|-- RR级别下，聚簇索引加X锁
|-- RC级别，聚簇索引加X锁
REFER:一个线上SQL死锁异常分析：深入了解事务和锁
