InnoDB引擎的行锁
InnoDB引擎特点简述
- 支持事务
- 支持外键
- 必须要有主键，聚焦索引
- 不支持全文检索
- 使用行锁
行锁特点
支持事务，加锁开销大，加锁慢，会出现死锁，锁的粒度小，并发下等待锁的概率较低，所以支持高并发。
手动事务语法
手动测试行锁需要首先关闭自动提交，每个会话都需要关闭自动提交
mysql> SHOW VARIABLES LIKE 'autocommit'; #查看 mysql> SET autocommit = 0;
中间执行SQL
最后执行完了需要进行提交
mysql> COMMIT;
最佳实践
| 步骤 | Session1 | Session2 |
| 1 | 可以查询innodb_lock表数据 | 可以查询innodb_lock表数据 |
| 2 | 可以增删改innodb_lock表数据 | 可以查询旧数据，增删改innodb_lock表数据阻塞 |
| 3 | commit | commit同时阻塞的增删改成功 |
| 4 | 可以增删改innodb_lock表数据 | 增删改innodb_lock表与Session1不同行数据也阻塞 |
| 5 | commit | commit同时阻塞的增删改成功 |
| 6 | 没能获取session2更新的数据 | commit |
| 7 | 可以查询innodb_lock表更新后数据 |
这里有几个问题：
- 这两个session，谁先进行的增删改，谁就先拿到锁。但是在查询的时候，如果session1进行了一次SELECT操作，而session2提交之后，session1再一次的SELECT是无法获取session2更新的值的，必须session1进行提交后才可以更新数据。
- 在第3步，session2会将session1更新的数据覆盖掉
- 在第4步，session2修改的与session1不同的行也会阻塞，在这里其实session1的锁是表锁，只有用上索引之后才能变为行锁。
针对给表加索引，让表锁变行锁的实践
表名：innodb_lock
| 步骤 | Session1 | Session2 |
| 1 | CREATE INDEX idx_id ON innodb_lock(id); | |
| 2 | COMMIT; | |
| 3 | UPDATE innodb_lock SET color = 'pink' WHERE id = 1; | UPDATE innodb_lock SET color = 'yellow' WHERE id = 2; |
| 4 | COMMIT; | COMMIT; |
在这里两个session可以同时更新数据，实现行锁。我们可以看出这个索引落在WHERE语句参数的ID上，假如没有WHERE 条件并且没有索引是否也可成功？
| 步骤 | Session1 | Session2 |
| 1 | DROP INDEX idx_id ON innodb_lock; | |
| 2 | COMMIT; | |
| 3 | UPDATE innodb_lock SET color = 'red' WHERE id = 1; | UPDATE innodb_lock SET color = 'black'; |
| 4 | COMMIT; | COMMIT; |
在第3行session2发生阻塞，因为没有索引则会发生阻塞。
手动锁定记录
这种锁方式也是MySQL实现悲观锁的方式
mysql> BEGIN; mysql> SELECT ... FOR UPDATE;
在中间进行数据更新
mysql> COMMIT;
最后进行提交。
最佳实践
1、还是innodb_lock这张表和数据，自动提交开启的情况和没有索引的实践手动锁定记录。
| 步骤 | Session1 | Session2 |
| 1 | BEGIN; | |
| 2 | SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE; | UPDATE innodb_lock SET color = 'orange' WHERE id = 1; #阻塞 |
| 3 | UPDATE innodb_lock SET color = 'pink' WHERE id = 1; | |
| 4 | COMMIT; | #阻塞释放，执行更新，数据被覆盖为orange |
2、上一个测试两个session修改的是同一条数据，如果修改不一样的数据，是否还会发生阻塞
| 步骤 | Session1 | Session2 |
| 1 | BEGIN; | |
| 2 | SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE; | UPDATE innodb_lock SET color = 'yellow' WHERE id = 2; #阻塞 |
| 3 | UPDATE innodb_lock SET color = 'red' WHERE id = 1; | |
| 4 | COMMIT; | #阻塞释放，执行更新 |
3、这里可以看到session1对数据表进行了锁表，那我们给id加上索引是否就不会阻塞
| 步骤 | Session1 | Session2 |
| 1 | CREATE INDEX idx_id ON innodb_lock(id); | |
| 2 | BEGIN; | |
| 3 | SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE; | UPDATE innodb_lock SET color = 'blue' WHERE id = 2; #未阻塞 |
| 4 | UPDATE innodb_lock SET color = 'pink' WHERE id = 1; | |
| 5 | COMMIT; |
4、session2未发生阻塞，那么假如锁定的行与更新的行不是一个行那session2能更新哪行呢
| 步骤 | Session1 | Session2 |
| 1 | BEGIN; | |
| 2 | SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE; | |
| 3 | UPDATE innodb_lock SET color = 'yellow' WHERE id = 2; | UPDATE innodb_lock SET color = 'red' WHERE id = 1; #阻塞 |
| 4 | UPDATE innodb_lock SET color = 'orange' WHERE id = 2; #阻塞 | |
| 5 | UPDATE innodb_lock SET color = 'grey' WHERE id = 3; #未阻塞 | |
| 6 | COMMIT; |
5、我们发现id为1和2的两行都被锁住了，如果这样的话是不是说明只要是锁定或更新的行都会被锁
| 步骤 | Session1 | Session2 |
| 1 | INSERT INTO innodb_lock VALUES(4, 'red'),(5, 'orange'); | |
| 1 | BEGIN; | |
| 2 | SELECT * FROM innodb_lock WHERE id < 3 FOR UPDATE; | |
| 3 | UPDATE innodb_lock SET color = 'black' WHERE id > 3; | UPDATE innodb_lock SET color = 'black' WHERE id = 1; #阻塞 |
| 4 | UPDATE innodb_lock SET color = 'black' WHERE id = 2; #阻塞 | |
| 5 | UPDATE innodb_lock SET color = 'black' WHERE id = 3; #阻塞 | |
| 6 | UPDATE innodb_lock SET color = 'black' WHERE id = 4; #阻塞 | |
| 7 | UPDATE innodb_lock SET color = 'black' WHERE id = 5; #阻塞 | |
| 8 | COMMIT; |
6、不会吧，全部阻塞了，id为3的数据明明没在锁住和更新的数据里，这是为什么呢？我们再试一组数据。
| 步骤 | Session1 | Session2 |
| 1 | INSERT INTO innodb_lock VALUES(6, 'purple'),(7, 'white'); | |
| 2 | BEGIN; | |
| 3 | SELECT * FROM innodb_lock WHERE id < 3 FOR UPDATE; | |
| 4 | UPDATE innodb_lock SET color = 'black' WHERE id > 5; | UPDATE innodb_lock SET color = 'black' WHERE id = 2; #阻塞 |
| 5 | UPDATE innodb_lock SET color = 'black' WHERE id = 3; #阻塞 | |
| 6 | UPDATE innodb_lock SET color = 'black' WHERE id = 4; #未阻塞 | |
| 7 | UPDATE innodb_lock SET color = 'black' WHERE id = 5; #未阻塞 | |
| 8 | UPDATE innodb_lock SET color = 'black' WHERE id = 6; #阻塞 | |
| 9 | INSERT INTO innodb_lock VALUES(8, 'test'); #阻塞 | |
| 10 | COMMIT; |
我再添加两条数据，我知道的颜色单词就这些了，多一个也想不起来了。
我嘞个去，结果让人匪夷所思，小于号是后妈养的吗？为什么3就阻塞而5就不阻塞。
好吧，算你厉害，大家还是记住这个结果吧。
这里这种范围的锁定就是传说中的间隙锁，他有一种特点就是只要在范围之内的数据全部被锁住，不管当前是否存在。
7、如果有两张innodb的表，两个session锁住各一张表，然后再去更新对方的那张表会怎样呢？
| 步骤 | Session1 | Session2 |
| 1 | BEGIN; | BEGIN; |
| 2 | SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE; | SELECT * FROM innodb_test WHERE id = 1 FOR UPDATE; |
| 3 | UPDATE innodb_test SET color = 'pink' WHERE id = 1; #阻塞 | UPDATE innodb_lock SET color = 'pink' WHERE id = 1; #阻塞 |
| 4 | COMMIT; |
在第3行都发生了阻塞，而在Session2的第3行出现以下错误
这里出现了传说中的死锁，就是两个session吃着自己碗里的还看着对方碗里的，双方还都是倔脾气，就在这里杠上了，谁也不服谁。
查看行锁的信息
- Innodb_row_lock_current_waits：当前正在等待的数量
- Innodb_row_lock_time：从启动到现在锁定的总时长，单位ms
- Innodb_row_lock_time_avg：锁等待的平均时长，单位ms
- Innodb_row_lock_time_max：等待锁时间最长的一个时间，单位ms
- Innodb_row_lock_waits：总共的等待次数
本文索引关键字：
间隙锁：http://www.cnblogs.com/huanStephen/p/8076172.html#c_lock
悲观锁：http://www.cnblogs.com/huanStephen/p/8076172.html#p_lock
死锁：http://www.cnblogs.com/huanStephen/p/8076172.html#d_lock
欢迎大家索引！
