MySQL锁篇

一、一条update语句

　　https://zhuanlan.zhihu.com/p/146968292

二、MySQL锁介绍

　　锁粒度分类：全局锁、表锁和行锁。MySQL Server支持全局锁和表锁，行锁是存储引擎实现的。

　　锁功能分类：共享锁、排他锁。加了共享锁的记录，其他事务可以继续加共享锁，但不允许加排他锁 select ... lock in share mode。加了排他锁的记录，不允许加共享锁和排他锁 select ... for update。

　　锁实现分类：悲观锁、乐观锁

三、表级锁

　　1. 读/写锁

#加读锁
lock table table_name read;
#加写锁
lock table table_name write;
#释放锁
unlock tables;

　　读锁是共享锁，写锁是排他锁

　　2. 元数据锁

　　事务开始后，执行第一条select语句时，对表结构加锁，在当前事务没有提交之前，除了本事务外，其他事务不允许修改表结构。

　　3. 意向锁

　　对表中的行数据添加锁时，会额外对整张表添加意向锁。根据原有加锁的类型分别添加意向共享锁和意向排他锁。

　　当有其他事务想要锁表时，只需要判断整张表上是否有意向锁即可知道表内数据是否已被锁定。

　　意向锁之间互不影响加锁。当意向锁和普通锁比较时，当成普通锁。

　　4. 自增锁

　　当列设置了AUTO_INCREMENT后，插入记录会自动添加，插入完成后自动释放，不需要等到事务提交

四、行级锁（InnoDB）

　　InnoDB加行锁是加在索引列上的，如果查询没有用到索引，那么会对全部数据在主键索引上加锁。InnoDB每个页有一个锁对象，对象内部有位图，对某行记录加锁则在位图对应位置标记。

　　假设一个表有3百万数据页，每页100条记录。对全表进行更新

　　如果对行加锁，则每行产生一个锁对象，假设大小为10字节，则花费3G内存管理锁。

　　如果对页加锁，每页的锁信息占用30字节，总花费90M的内存。

　　InnoDB行锁按照锁定范围分成：记录锁、间隙锁、临键锁、插入意向锁

　　4.1 记录锁

　　锁住一条记录的索引，可以是共享/排他锁。锁是加在索引上的，并且必须是主键索引或者唯一索引，否则会转化成临键锁。

　　如果一条sql没有用到索引，那么会将所有记录的索引都加上锁，相当于锁表。记录锁会阻碍当前读。

#加共享锁
select ... lock in share mode;
#加排他锁
select ... for update;

　　4.2 间隙锁

　　锁索引区间，不包括端点。间隙锁阻碍其他事务对区间的插入/修改操作，但不会阻碍另一个事务对锁区间的查询。包括lock in share mode和 for update。跟记录锁是不同的

　　4.3 临键锁(Next-key Locks)

　　锁间隙和索引节点，左开右闭。解决幻读问题。比如索引值有3、5、8、10。对应的next-key lock区间为 (最小值,3]、(3,5]、(5,8]、(8,10]、 (10,最大值]。相当于记录锁和间隙锁的结合。

　　4.4 测试（本地测试环境 MySQL5.7.26版本RR隔离级别）：

　　如果唯一索引是组合索引，除非查询条件用到了组合索引的所有列，否则，依然用间隙锁锁定，因为无法确定具体的记录，属于范围查询。

　　1. 唯一索引，等值查询，命中记录。譬如select a from test where a = 5 for update；锁住记录5，即对5加了记录锁，阻塞5的插入和删除。测试结果一致

　　2. 唯一索引，等值查询，没有命中。譬如select a from test where a = 4 for update；锁住了(3,5)的区间。测试结果一致

　　4. 唯一索引，范围查询。譬如select a from test where a between 5 and 8 for update；锁住了(3,10]的区间。测试结果一致，包括删除和插入的测试

　　3. 非唯一索引，等值查询，命中记录。譬如select a from test where a = 5 for update；锁住了(3,8)区间，即对5加了临键锁锁住(3,5]，对7加间隙锁锁住(5,8)。

　　　　在测试中，阻塞了[3,8)的插入，锁定了记录5的删除。

　　4. 非唯一索引，等值查询，没有命中。譬如select a from test where a = 4 for update；锁住了(3,5)区间，即对5加了间隙锁锁住(3,5)。

　　　　在测试中，阻塞了[3,5)的插入，未锁定删除。

　　5. 非唯一索引，范围查询。譬如select a from test where a between 5 and 8 for update；锁住了(3,10]区间，即对5、8、10加了临键锁锁住(3,5]、(5,8]、(8,10]。

　　　　在测试中，阻塞了[3,10)的插入，锁定了记录5，8，10的删除。

　　　　示例数据和索引如下：

mysql> select * from test;
+------+
| a    |
+------+
|    3 |
|    5 |
|    8 |
|   10 |
+------+
4 rows in set (0.00 sec)

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | idx_a    |            1 | a           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

　　　　事务A的操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where a between 5 and 8 for update;
+------+
| a    |
+------+
|    5 |
|    8 |
+------+
2 rows in set (0.00 sec)

　　　　事务B插入和删除操作：

mysql> insert into test values(2);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values(3);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(10);
Query OK, 1 row affected (0.01 sec)
mysql> delete from test where a = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from test where a = 3;
Query OK, 1 row affected (0.00 sec)

　　原博客地址：https://blog.csdn.net/qingsong3333/article/details/78024931。比较奇怪的是非唯一索引锁定区间是左闭右开？还请大神解惑。

　　4.5 RR隔离级别下加锁总结

唯一索引：
1. 等值查询
  1. 命中记录，加记录锁，然后在主键上也加记录锁
  2. 没有命中，加间隙锁
2. 范围查询
  1. 没有命中，加临键锁
  2. 命中记录，包含where条件的区间加临键锁，然后在主键上也加记录锁
非唯一索引：
1. 等值查询
  1. 命中记录，加间隙锁和临键锁，然后在主键上也加记录锁
  2. 没有命中，加间隙锁
2. 范围查询
  1. 没有命中，加临键锁
  2. 命中记录，包含where条件的区间加临键锁，然后在主键上也加记录锁
没用到索引：
1. 全表主键上加临键锁，如果设置了innodb_locks_unsafe_for_binlog参数，那么MySQL Server层会优化，释放查询条件不包含的锁

　　4.6 RC隔离级别下加锁总结

唯一索引：
1. 等值查询
  1. 命中记录，加记录锁，然后在主键上也加记录锁
  2. 没有命中，不加锁
2. 范围查询
  1. 没有命中，不加锁
  2. 命中记录，对命中的记录的索引加记录锁，然后在主键上也加记录锁
非唯一索引：
1. 等值查询
  1. 命中记录，加记录锁，然后在主键上也加记录锁
  2. 没有命中，不加锁
2. 范围查询
  1. 没有命中，不加锁
  2. 命中记录，对命中的记录的索引加记录锁，然后在主键上也加记录锁
未用到索引：
1. 在全表主键上加记录锁，返回给MySQL Server层，MySQL Server层做优化，释放查询条件不包含的锁

　　RC隔离级别下只使用记录锁。

　　4.7 插入意向锁　　

　　跟表级的意向锁是不同的概念。在索引区间插入数据，会在这个区间产生插入意向锁。插入完成后，会持有插入记录的记录锁。设计的目的是考虑避免在相同索引区间插入时需要加区间锁。

　　如果包含唯一索引，会阻塞其他事务相同索引值的插入和删除。如果是非唯一索引，那么只会阻塞删除。

　　一个有趣的现象：

　　事务A插入数据，获取了插入的记录锁。

begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(6);
Query OK, 1 row affected (0.03 sec)

　　事务B同样的操作，同样获得了记录锁，当然这是两条记录，锁是不一样的。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(6);
Query OK, 1 row affected (0.00 sec)

　　事务A继续执行删除操作，因为有两条数据，事务A会尝试获取事务B持有的记录锁，此时事务A阻塞了。

mysql> delete from test where a = 6;

　　如果此时事务B也执行删除操作，同样的，会等待事务A释放记录锁。

#事务B
mysql> delete from test where a = 6;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

#事务A
mysql> delete from test where a = 6;
Query OK, 1 row affected (4.18 sec)

　　产生了死锁。MySQL内部处理，事务B回滚释放了记录锁，事务A执行成功，事务A删除的记录只有自己插入的那一条。

　　另外一个产生死锁的例子：

　　数据和索引如下：

mysql> select * from t;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    3 |    5 |
| 2 |    4 |    6 |
| 3 |    5 |    7 |
+---+------+------+
3 rows in set (0.00 sec)

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | a           | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | b        |            1 | b           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | c        |            1 | c           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.07 sec)

　　事务A如下查询，除了在辅助索引上加锁之外，还需要到主键索引加锁，加锁顺序为1，2，3。因为每次只处理一条数据，并交由MySQL Service层缓存到结果集，并不是一次加全部锁。

mysql> select * from t where b > 2 for update;

　　事务B如下查询，主键的加锁顺序为3，2，1。

mysql> select * from test where c < 8 for update;

　　因为相反的加锁顺序，此时就会产生死锁。

五、死锁解决方法

　　1. 超时回滚。默认的情况下，InnoDB不会回滚超时引发的错误异常（1205错误码）。通过innodb_rollback_on_timeout设置，默认为OFF即只回滚加锁超时的SQL，设置为ON之后，会回滚整个事务。

　　2. 等待图检测。每个事务在请求锁并发生等待时，都会判断等待图中是否存在回路，如果有回路，InnoDB会选择undo log最小的事务进行回滚。

六、避免死锁

　　1. 一致的加锁顺序

　　2. 保持事务的轻量

　　3. 优化事务执行顺序

　　4. 尽快提交事务释放锁

人生就像蒲公英，看似自由，其实身不由己。