1. 概念梳理

根据加锁的范围，MySQL里面的锁大致可以分为：全局锁、表级锁、行锁三类。

1.1. 全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（增删改）、数据定义语句（包括建表、修改表结构等）、更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。

以前使用FTWRL备份，不好的点是：

1. 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
2. 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

FTWRL不好，为什么有时还需要呢？因为MyISAM引擎不支持事务，无法做到细粒度的锁，这时只能使用FTWRL命令。

1.2. 表级锁

MySQL里面表级别的锁有两种：表锁、元数据锁（metadata lock，MDL)。

1.2.1. 表锁

表锁的语法是 lock tables xxx read/write ，与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

举例, 如果在某个线程A中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

1.2.2. MDL-(metadata lock)

场景：如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上。

为解决这种问题，MySQL在5.5版本中引入了MDL。MDL不需要显式使用，在访问一个表的时候会被自动加上，其作用是保证读写的正确性。

当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

1.3. 行锁

行锁就是针对数据表中行记录的锁。

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

下面讨论 InnoDB 的行锁。

1.3.1. 两阶段锁

两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

样例（id为主键）：

事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。事务A持有的两个记录的行锁，都是在commit的时候才释放的。

所以在使用事务时，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

1.3.2 事务

1.3.2.1. 事务基本要素（ACID）

• 原子性（Atomicity）：事务开始到结束，中间所有操作为一个整体的原子操作，不可分割，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账10元，A减少和B增加数值一样，不多不少。
• 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如账户A正在取钱减少，再次过程结束前，其他账户不能向A汇入金额。
• 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

1.3.2.2. 事务读现象问题

• 丢失更新 （事务ACID保证不会发⽣）
• 脏读：读到了其他事务未提交的修改数据，这些数据可能回滚
• 不可重复读：A事务第一次读取数据，B事务修改了该数据并提交，A再读取拿到B修改后数据，两次数据不一样
• 幻读：A事务第一次查到符合条件的m条数据，B事务新增或删除了符合条件的n条数据，A再查询获得m±n条数据

不可重复读与幻读的区别是不可重复读是针对修改，幻读是针对插入和删除。

1.3.2.3. 事务隔离级别

• 读未提交（Read-Uncommitted，RU）：可读取未提交的事务的操作数据。（很少用到）
• 读已提交（Read-Committed，RC）：一个事务只能看见已经提交事务所做的改变，存在不可重复读和幻读。（其他数据库默认，非MySQL）（常用）
• 可重复读（Repeatable-Read，RR）：同一事务的多个实例在并发读取数据时，会看到同样的数据行。对读取到的记录加锁（⾏锁），存在幻读现象，InnoDB通过MVCC解决了该问题。（MySQL默认级别）（常用）
• 可串行化（Serializable）：从MVCC并发控制退化为基于锁的并发控制。所有的读操作都为当前读，读加读锁（S锁），写加写锁（X锁）。Serializable隔离级别下，读写冲突，并发性能急剧下降（基本不会用）

1.3.3. InnoDB 锁类型

1. Shared Locks and Exclusive Locks - 共享锁（S锁）和排他锁（X锁）

   • 共享锁（S Lock）：允许事务读取一行数据，多个事务可以拿到一把S锁（即读读并行），例如 select * from xx where a=1；
   • 排他锁（X Lock）：允许事务删除或更新一行数据，多个事务有且只有一个事务可以拿到X锁（即写写/写读互斥），例如 select * from xx where a=1 for update；

   S 和 S 兼容， X 和 S 互斥， X 和 X 互斥

2. Intention Locks - 意向锁
   InnoDB 为了支持多粒度锁（multiple granularity locking），引入意向锁（一种表锁），它允许行级锁与表级锁共存。

   • 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁；
   • 意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁；

   事务在请求S锁和X锁前，需要先获得对应的IS、IX锁，表明“某个事务正在某一行上持有了锁，或者准备去持有锁”。

   举个例子，事务1在表1上加了S锁后，事务2想要更改某行记录，需要添加IX锁，由于不兼容，所以需要等待S锁释放；如果事务1在表1上加了IS锁，事务2添加的IX锁与IS锁兼容，就可以操作，这就实现了更细粒度的加锁

3. Record Locks - 记录锁（locks rec but not gap）
   记录锁是的单个行记录上的锁，锁在索引记录上。会阻塞其他事务对其插入、更新、删除；

4. Gap Locks - 间隙锁
   间隙锁锁定记录的一个间隔范围，但不包含记录本身。间隙锁都是左开右闭原则。

   例子： 数据库已有id为2,6两条记录，已有三个间隙锁(-∞,2] (2,6] (6,+∞]。事务A现在想要在(4,10)之间更新数据的时候（id=5的数据），会加上间隙锁select * from t where id>4 and id<10 for update，锁住(2,6] (6,+∞]； 如果不加间隙锁，事务B有可能会在(4,10)之间插入一条数据，这个时候事务A再去更新，发现在(4,10)这个区间内多出了一条“幻影”数据。

   间隙锁就是防止其他事务在间隔中插入数据，以导致“不可重复读”。

5. Next-Key Locks - 临键锁=Gap Lock + Record Lock
   临建锁是记录锁与间隙锁的组合。即：既包含索引记录，又包含索引区间，主要是为了解决幻读。

6. Insert Intention Locks - 插入意向锁
   插入意向锁是间隙锁的一种，专门针对insert的。即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此；

   例子：在可重复读隔离级别下，对PK id为10-20的数据进行操作：
   事务A在10-20的记录中插入了一行： insert into t value(11, xx)
   事务B在10-20的记录中插入了一行： insert into t value(12, xx)
   由于两条插入的记录不冲突，所以会使用插入意向锁，且事务B不会阻塞。

7. Auto-inc Locks - 自增锁
   自增锁是一种特殊的表级别锁，专门针对事务插入AUTO-INCREMENT类型的列。 即一个事务正在往表中插入记录时，其他事务的插入必须等待，以便第1个事务插入的行得到的主键值是连续的。

8. Predicate Locks for Spatial Index - 空间索引的谓词锁（忽略）

兼容矩阵（✔-兼容，✖-互斥）：

1.3.4. MVCC

MVCC(Mutil-Version Concurrency Control，多版本并发控制)，是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

InnoDB引擎中就是只在已提交读(RC)和可重复读(RR)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。这就使得别的事务可以修改某行记录，每次修改都会在版本链中记录，SELECT时可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

1.3.4.1. ROW记录格式

InnoDB内部为每一行添加了两个隐藏列：DB_TRX_ID和DB_ROLL_PTR（MySQL另外还有一个隐藏列DB_ROW_ID，这是在InnoDB表没有主键的时候用此作为主键）

ROW记录格式.png

• DB_TRX_ID：长度为6字节，该行记录最后一次更改的事务ID
• DB_ROLL_PTR：长度为7字节，回滚指针，指向写入回滚段的Undo log记录

1.3.4.2. ROW和Undo关系

ROW和Undo关系.png

1.3.4.3. ReadView判断

读已提交（RC）和可重复读（RR）的区别就在于它们生成ReadView的策略不同。

事务版本ID是递增的

ReadView原理主要是其中有个列表来存储系统中当前活跃着的读写事务（事务begin，还未commit）。通过这个列表和某行记录的版本链比较，判断出该记录的哪个版本对当前事务可见。计较判断遵循从尾部向前查找。

举个例子（以ID=1的记录为例）：

ReadView查询示例.png

①在事务trx20时候修改成name=A，并已提交；事务trx30修改成name=B，还未提交；
②来了一个事务trx35 select这个记录，查询前先生成一个ReadView，此时里面有[30]（注意如果还有其他事务修改ID1，ReadView列表就有两个值，以此类推）；
③比较判断，发现trx30在ReadView，对本事务不可见，根据DB_ROLL_PTR查询上一个事务，找到trx20，发现trx20<ReadView中的30，可见，select的结果是name=A；
RC与RR区别
④如果trx30提交，再起一个事务trx40，修改name=C，并未提交；
⑤trx35 再次select查询：RC，此时会重新生成一个ReadView，里面是[40]，可查到name=B；RR，此时的ReadView是第一次查询是生成的，查到的name=A；

读已提交隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用这个ReadView。

2. 死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

2.1. 发生死锁的条件

• 2个或2个以上事务（线程）
• 不同方向
• 相同锁资源

2.2. 解决死锁的方法

• 超时等待：设置innodb_lock_wait_timeout(InnoDB默认50s)当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。（缺点：如果回滚的事务更新了很多行，占用了较多的undo log，那么在回滚的时候花费的时间比另外一个正常执行的事务花费的时间可能还要多，就不太合适；还有等待时间设置大了服务等待过长，设小了会误伤正常的锁等待事务）

• 等待图（wait-for graph），死锁碰撞检测：是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息，通过这两个部分信息构造出一张图，在每个事务请求锁并发生等待时都会判断是否存在回路，如果在图中检测到回路，就表明有死锁产生，这时候InnoDB存储引擎会选择回滚undo量最小的事务。将参数innodb_deadlock_detect=on表示开启这个逻辑。（缺点是当并发很高时，死锁检测会消耗大量CPU资源，导致CPU利用率很高，但是每秒却执行事务数很少）

一般大多采用第二种死锁碰撞检测。对于这种由热点行更新导致的性能问题怎么解决呢？

• 关闭死锁检测：暴力解决办法，前提是你能确保这个业务一定不会出现死锁。但这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。不推荐。

• 并发控制：如果并发很低的情况下，死锁检测的成本很低。一般在服务器业务端限制对同一表、同一行或范围并发操作。

3. 案例分析

3.1. 案例一

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-02-26 18:04:40 0x7fcfdbcfe700
*** (1) TRANSACTION:
TRANSACTION 12570689, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7405652, OS thread handle 140522483459840, query id 144925248 10.20.30.31 root updating
DELETE FROM t WHERE a='a1' AND b='b1' AND c='c1' AND d='d1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 81 page no 4970 n bits 136 index PRIMARY of table `db_name`.`t` trx id 12570689 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12570690, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 0
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 7403815, OS thread handle 140530722793216, query id 144925250 10.20.30.32 root updating
DELETE FROM t WHERE a= 'a2' AND b='b2' AND c='c1' AND d='d1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 81 page no 4970 n bits 136 index PRIMARY of table `db_name`.`t` trx id 12570690 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 81 page no 17 n bits 384 index idx_cd of table `db_name`.`t` trx id 12570690 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------ 

检测到死锁，记录两个事务，事务1和事务2，最终事务1回滚

• 事务1

事务ID=12570689，活跃了0s
使用了1个表，持有1个锁
有2个行锁
线程ID=7405652，IP=10.20.30.31，用户=root，更新操作（增删改都是updating）
当前事务的SQL操作: DELETE FROM t WHERE a='a1' AND b='b1' AND c='c1' AND d='d1'
上面SQL等待的锁信息：行锁，加在表db_name.t的PRIMARY（主键）索引上，索引模式X locks rec but not gap（记录排他锁）

• 事务2

事务ID=12570690，活跃了0s
使用了1个表，持有1个锁
有2个行锁
线程ID=7403815，IP=10.20.30.32，用户=root，更新操作（增删改都是updating）
当前事务的SQL操作: DELETE FROM t WHERE a= 'a2' AND b='b2' AND c='c1' AND d='d1'
已持有的锁西信息：行锁，加在表db_name.t的PRIMARY索引上，索引模式X locks rec but not gap（记录排他锁）
上面SQL等待的锁信息：
行锁，加在表db_name.t的idx_cd索引上，索引模式X locks rec but not gap（记录排他锁）

• 表索引结构

PRIMARY KEY (id),
UNIQUE KEY uniq_uk (a,c) USING BTREE,
KEY idx_ab (a,b) USING BTREE,
KEY idx_cd (c,d) USING BTREE

很明显，时因为删除操作，要把X locks rec but not gap加到idx_cd索引上，两个操作涉及到idx_cd索引的同一行，所以出现死锁。索引设计不合理，优化索引。

3.1. 案例二

TRANSACTION 9012724, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 43 row lock(s), undo log entries 21
MySQL thread id 12643211, OS thread handle 140223917704960, query id 137518665 10.20.28.40 root update
INSERT INTO DAM_DS_RDB_TABLE_FIELD (pid, ds_id, field_name, field_type, is_null, default_value, expand, comment, `position`, create_time, ID)
VALUES (117309, 1001123, 'ID', 'bigint(20)', '0', NULL, 'auto_increment', 'ID', 0, '2020-06-16 02:01:10.451', 9331810),
    (117309, 1001123, 'CONTRACT_NO', 'varchar(64)', '1', NULL, '', '额度协议号', 2, '2020-06-16 02:01:10.451', 9331812)
RECORD LOCKS space id 146 page no 16 n bits 416 index uniq_uk of table `db_name`.`dam_ds_rdb_table_field` trx id 9012724 lock mode S waiting

TRANSACTION 9012726, ACTIVE 0 sec inserting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 44 row lock(s), undo log entries 24
MySQL thread id 12643272, OS thread handle 140223928846080, query id 137518674 10.20.28.41 root update
INSERT INTO DAM_DS_RDB_TABLE_FIELD (pid, ds_id, field_name, field_type, is_null, default_value, expand, comment, `position`, create_time, ID)
VALUES (117310, 1001020, 'ID', 'bigint(20)', '0', NULL, '', '主键', 0, '2020-06-16 02:01:10.472', 9331790),
    (117310, 1001020, 'AUTH_ID', 'varchar(32)', '1' NULL, '', '商户协议号', 2, '2020-06-16 02:01:10.472', 9331872)
RECORD LOCKS space id 146 page no 16 n bits 416 index uniq_uk of table `db_name`.`dam_ds_rdb_table_field` trx id 9012726 lock_mode X locks rec but not gap
RECORD LOCKS space id 146 page no 16 n bits 424 index uniq_uk of table `damassetspre_007`.`dam_ds_rdb_table_field` trx id 9012726 lock_mode X locks gap before rec insert intention waiting

事务1批量插入，id in (9331810, 9331812)，等待 uniq_uk 的S共享锁。
事务2批量插入，id in (9331790, 9331872)，已持有 uniq_uk 的记录排他锁，等待插入意向范围锁X locks gap before rec insert intention。

根据业务代码，是多线批量插入，id范围冲突。解决办法，可接受情况下，改成单线程操作。

参考

https://blog.csdn.net/weixin_41850404/article/details/84653909

https://blog.csdn.net/noaman_wgs/article/details/82529908