MySQL 锁

两段锁

• 数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）
• 加锁阶段：在该阶段可以进行加锁操作，在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁），加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
• 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

事务	加锁/解锁处理
begin;
insert into test …	加insert对应的锁
update test set …	加update对应的锁
delete from test…	加delete对应的锁
commit;	事务提交时，同时释放insert,update,delete对应的锁

• 这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

锁的分类

• 按照锁的使用方式可分为：共享锁，排它锁，意向共享锁，意向排他锁。
  • 共享锁/读锁（S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁，（其他事务可以读但不能写该数据集）
  • 排他锁/写锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁，（其他事务不能读和写该数据集）
  • 意向共享锁（IS)：通知数据库接下来需要施加什么锁并对表加锁，如果需要对记录A加共享锁，那么此时innodb会先找到这张表，对该表加意向共享锁之后，再对记录A添加共享锁。
  • 意向排他锁（IX)：通知数据库接下来需要施加什么锁并对表加锁，如果需要对记录A加排他锁，那么此时innodb会先找到这张表，对该表加意向排他锁之后，再对记录A添加排他锁。
• 按照锁的粒度可分为：行锁，页锁（间隙锁），表锁。
  • 表级锁：开锁小，加锁快，不会出现死锁，锁的粒度大，发生锁冲突的概率最高，并发量最低。
  • 行级锁：开销大，加锁慢，会出现死锁，锁的粒度小，容易发生冲突的概率小，并发度最高。
    • 行锁直接加在索引记录上面，无索引项时演变成表锁（因为如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤）
  • 间隙锁(Gap Lock)：锁定索引记录间隙，确保索引记录的间隙不变，在无索引的情况下是锁全表，间隙锁是针对事务隔离级别为可重复读或以上级别的。
  • Next-Key Lock：行锁和间隙锁组合起来就是 Next-Key Lock，在 RR 隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读问题产生。
• 意向共享锁和意向排它锁是数据库主动加的，不需要我们手动处理。
• 对于UPDATE,DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)，对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
  • 共享锁（S):SELECT * FROM table_name WHERE…LOCK IN SHARE MODE
  • 排他锁（X):SELECT * FROM table_name WHERE…FOR UPDATE

意向锁（ Intention lock )

• InnoDB 为了支持多粒度的加锁，允许行锁和表锁同时存在。为了支持在不同粒度上的加锁操作，InnoDB 支持了额外的一种锁方式，称之为意向锁（ Intention Lock )。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。意向锁分为两种：
  • 意向共享锁（ IS )：事务有意向对表中的某些行加共享锁。
  • 意向排他锁（ IX )：事务有意向对表中的某些行加排他锁。
• 由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求。表级意向锁与行级锁的兼容性如下所示：

插入意向锁（ Insert Intention lock )

• 插入意向锁是在插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插入方式的信号，即多个事务在相同的索引间隙插入时如果不是插入间隙中相同的位置就不需要互相等待。假设某列有索引值2，6，只要两个事务插入位置不同（如事务 A 插入3，事务 B 插入4)，那么就可以同时插入。

锁模式兼容矩阵

• 横向是已持有锁，纵向是正在请求的锁：

死锁

什么是死锁

• 死锁是并发系统中常见的问题，同样也会出现在数据库MySQL的并发读写请求场景中。当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现“死锁”。常见的报错信息为 Deadlock found when trying to get lock...。
• 举例来说 A 事务持有 X1 锁 ，申请 X2 锁，B事务持有 X2 锁，申请 X1 锁。A 和 B 事务持有锁并且申请对方持有的锁进入循环等待，就造成了死锁。
• 从死锁的定义来看，MySQL 出现死锁的几个要素为：
  • 两个或者两个以上事务。
  • 每个事务都已经持有锁并且申请新的锁。
  • 锁资源同时只能被同一个事务持有或者不兼容。
  • 事务之间因为持有锁和申请锁导致彼此循环等待。

阅读死锁日志

• 在进行具体案例分析之前，咱们先了解下如何去读懂死锁日志，尽可能地使用死锁日志里面的信息来帮助我们来解决死锁问题。后面测试用例的数据库场景如下：MySQL 5.7 事务隔离级别为 RR，表结构和数据如下：

• 测试用例如下：

• 通过执行show engine innodb status可以查看到近一次死锁的日志。

（1）TRANSACTION

• TRANSACTION 2199, ACTIVE 26 sec starting index read：事务号为2199，活跃26秒，starting index read 表示事务状态为根据索引读取数据。常见的其他状态有：

• mysql tables in use 1 locked 1 ：说明当前的事务使用一个表，locked 1 表示表上有一个表锁，对于 DML 语句为 LOCK_IX
• LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)：
  • LOCK WAIT 表示正在等待锁，2 lock struct(s) 表示 trx->trx_locks 锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及自增锁等。本用例中 2 locks 表示 IX 锁和lock_mode X (Next-key lock)
  • 1 row lock(s) 表示当前事务持有的行记录锁/ gap 锁的个数。
• MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating
  • MySQL thread id 37 表示执行该事务的线程 ID 为 37 （即 show processlist; 展示的 ID )
• delete from student where stuno=5 表示事务1正在执行的 sql，但是 show engine innodb status 是查看不到完整的 sql 的，通常显示当前正在等待锁的 sql。

（1）WAITING FOR THIS LOCK TO BE GRANTED

• RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw.student trx id 2322 lock_mode X waiting：RECORD LOCKS 表示记录锁， 此条内容表示事务 1 正在等待表 student 上的 idx_stuno 的 X 锁，本案例中其实是 Next-Key Lock 。

（2）HOLDS THE LOCK(S):

• RECORD LOCKS space id 8 page no 5 n bits 72 index idx_stuno of table cw.student trx id 2198 lock_mode X
  • 显示事务 2 的 insert into student(stuno,score) values(2,10）持有了 a=5 的 Lock mode X
  • 不过我们从日志里面看不到事务2执行的 delete from student where stuno=5，这点也是造成 DBA 仅仅根据日志难以分析死锁的问题的根本原因。

（2）WAITING FOR THIS LOCK TO BE GRANTED:

• RECORD LOCKS space id 7 page no 5 n bits 72 index idx_stuno of table cw.student trx id 2321 lock_mode X locks gap before rec insert intention waiting
  • 表示事务 2 的 insert 语句正在等待插入意向锁 lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )

经典案例分析

事务并发 insert 键冲突

• 表结构和数据如下所示：

• 测试用例如下：

• 事务 T2 insert into t7(id,a) values (26,10）语句 insert 成功，持有 a=10 的 排他行锁（ Xlocks rec but no gap )
• 事务 T1 insert into t7(id,a) values (30,10)，因为T2的条 insert 已经插入 a=10 的记录，事务 T1 insert a=10 则发生键冲突，需要申请对冲突的索引加上S Next-key Lock（即 lock mode S waiting ）这是一个间隙锁会申请锁住（,10],(10,20]之间的 gap 区域。
• 事务 T2 insert into t7(id,a) values (40，9）该语句插入的 a=9 的值在事务 T1 申请的 gap 锁4-10之间， 故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放，在日志中显示 lock_mode X locks gap before rec insert intention waiting 。

先 update 再 insert 的并发死锁问题

• 表结构如下，无数据：

• 测试用例如下：

• 可以看到两个事务 update 不存在的记录，先后获得间隙锁（ gap 锁），gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。

如何尽可能避免死锁

1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
4. 以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0），那么就会锁定所查找到的记录。
6. 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。