• 锁是计算机协调多个进程或线程并发访问某一资源的机制。
• 官方文档： InnoDB Locking

• MySQL的锁机制相对于别的数据库来说，相对简单。不同的存储引擎支持不同的锁机制。

- 表级锁：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
- 页级锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；并发度一般

- 意向锁（Intention Locks）: lock mode IX
- 记录锁（Record Locks）: lock_mode X locks rec but not gap
- Gap锁（Gap Locks）: lock_mode X locks gap before rec
- Next-Key Locks: lock_mode X
- 插入意向锁（Insert Intention Locks）: lock_mode X locks gap before rec insert intention

• 可以通过show status开查看innodb的锁竞争情况

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | VALUE |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 34843 |
| Innodb_row_lock_time_avg      | 5807  |
| Innodb_row_lock_time_max      | 21657 |
| Innodb_row_lock_waits         | 6     |
+-------------------------------+-------+
5 ROWS IN SET (0.00 sec)

• 如果发现锁竞争情况严重，如Innodbrowlockwaits和Innodbrowlocktimeavg的值比较高，可以通过查询informationschema库中相关的表来查看锁情况。
• 或者设置InnoDBMonitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

SELECT * FROM information_schema.innodb_locks;

SELECT * FROM information_schema.innodb_lock_waits;

• 官网文档 https://dev.mysql.com/doc/refman/8.0/en/innodb-monitors.html
• 通过设置InnoDB Monitors观察锁冲突情况(参考博客InnoDB Monitor(innodb监视器))
• 5.6

mysql> CREATE TABLE innodb_monitor(a INT) engine=innodb;
Query OK, 0 ROWS affected (0.18 sec)

• 5.7

SET GLOBAL innodb_status_output_locks=ON;

mysql> BEGIN;
sQuery OK, 0 ROWS affected (2.05 sec)
 
mysql> SELECT * FROM test_table FOR UPDATE;
+----+-----+
| id | val |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
+----+-----+
3 ROWS IN SET (4.78 sec)

• 然后就可以通过下面的语句来进行查看

mysql> SHOW engine innodb STATUS\G
*************************** 1. ROW ***************************
  TYPE: InnoDB
  Name: 
STATUS: 
=====================================
2022-04-15 23:21:30 0x7f021c8ae700 INNODB MONITOR OUTPUT
=====================================
......
......
......
------------
TRANSACTIONS
------------
---TRANSACTION 549605684, ACTIVE 10 sec
2 LOCK struct(s), heap SIZE 1136, 4 ROW LOCK(s)
MySQL thread id 3592040, OS thread handle 139647045527296, query id 1729758289 10.10.183.226 root starting
SHOW engine innodb STATUS
TABLE LOCK TABLE `okami`.`test_table` trx id 549605684 LOCK mode IX
RECORD LOCKS SPACE id 5466 page no 3 n bits 72 INDEX PRIMARY OF TABLE `okami`.`test_table` trx id 549605684 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
Record LOCK, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; ASC     ;;
 1: len 6; hex 000020c13de4; ASC     = ;;
 2: len 7; hex ab000001520110; ASC     R  ;;
 3: len 4; hex 80000001; ASC     ;;
 
Record LOCK, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; ASC     ;;
 1: len 6; hex 000020c14054; ASC     @T;;
 2: len 7; hex d80000100d0110; ASC        ;;
 3: len 4; hex 80000001; ASC     ;;
 
Record LOCK, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000003; ASC     ;;
 1: len 6; hex 000020c18b3a; ASC      :;;
 2: len 7; hex b7000002f40110; ASC        ;;
 3: len 4; hex 80000001; ASC     ;;
 
......

• TRANSACTION 549605684, ACTIVE 10 sec
  • ACTIVE 10 sec: 表示事务活动时间
• 2 lock struct(s), heap size 1136, 4 row lock(s)
  • 2 lock struct(s): 表示该事务的锁链表的长度为2。每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等
  • heap size 1136: 事务分配的锁堆内存大小
  • 4 row lock(s): 表示当前事务持有的行锁个数为4
• RECORD LOCKS space id 5466 page no 3 n bits 72 index PRIMARY of table okami.testtable trx id 549605684 lockmode X
  • RECORD LOCKS: 表示记录锁
  • space id 5466: space id为5466
  • page no 3: page no 为 3
  • n bits 72: 表示这个记录锁结构上留有72个bit位（该page上的记录数+64）
  • lock_mode X: 表示该记录锁为排他锁

• InnoDB实现了以下两种类型的行锁
  • 共享锁(S): 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 排他锁(X): 允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁和排他锁
• 另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁
  • 意向共享锁(IS): 事务打算给数据行加锁行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX): 事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

• 意向锁是InnoDB自动加的，不需要用户干预。对于update、delete、insert语句，InnoDB会自动给设计数据集添加排他锁(X).对于普通的select语句，InnoDB不会加任何锁。
• 事务可以通过SQL显示的给记录集添加共享锁或排他锁
  • 排他锁(X): select * from table_name where ... for update
  • 共享锁(S): select * from table_name where ... lock in share mode

• InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为以下3中情形。
  • Record lock: 对索引项加锁
  • Gap lock: 对索引项之间的间隙、第一条记录前的间隙或最后一条记录后的间隙加锁
  • Next-key-lock: 前两者的组合，对记录及其前面的间隙加锁
• InnoDB这种行锁死线特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

间隙锁锁定一个范围，但不包含记录本身。间隙锁封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
Gap Locks 的作用是为了阻止多个事务将记录插入到同一范围内，避免幻读问题的产生。

• 我们在事务A执行 SELECT * FROM ql_tx_test.ql_tx WHERE ver_ > 5 AND ver_ < 9 FOR UPDATE;
  • ver_ 字段是普通索引：【此时产生间隙锁】，（5,9）这个范围会被锁定，当我们在 事务B 中插入 ver_ = 7 的记录是，执行会被阻塞，事务A再次执行同样查询时返回相同的记录，幻读问题就可以避免。当我们在事务B中插入 ver_ = 11 的记录时，因为11不在锁定的范围，执行新增操作成功。
  • ver_ 字段是唯一索引：【此时产生间隙锁】，（5,9）这个范围会被锁定。
  • ver_ 字段没有索引：整张表全部记录都会被加锁。

间隙锁唯一的目的是防止其他事务插入到统一范围。间隙锁可以共存，一个事务获取的间隙锁并不阻止另一个事务获取同一间隙的间隙锁。

• 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(Gap).InnoDB也会对这个间隙加锁，这种锁机制就是Next-Key Lock
• 在使用范围条件检索并锁定记录时，InnoDB这种锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用等值条件来访问更新数据，避免使用范围条件
• InnoDb除了通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁

Next-Key Locks 是Record Lock 和Record之前的间隙的间隙锁的一种结合。

• 我们执行一条SQL语句：SELECT * FROM user WHERE id > 7 AND id < 11 FOR UPDATE，锁住的不是9这单个值，而是对(5,9]、(9,12] 这2个区间加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免幻读。
• 所以触发临键锁与触发间隙锁的区别就在于：查询条件范围的端点是否在索引上。不在，用临键锁。在，用间隙锁

---

插入意向锁是一种特殊的间隙锁（所以有的地方把它简写成 II GAP），这个锁表示插入的意向，只有在 INSERT 的时候才会有这个锁。
注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混淆了。
插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。
插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，那么间隙锁是如何防止幻读的呢？正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

• MySQL死锁场景【官网案例】
• MySQL死锁场景【insert on duplicate 导致的死锁】
• MySQL的死锁场景【不存在就新增】
• MySQL死锁场景（删除&插入 不存在的记录）
• MySQL死锁场景【insert插入】
• MySQL中死锁的场景【带索引的表一边删除一遍插入】
• MySQL死锁场景【加锁顺序不同导致的死锁】

• 尽量避免大事务。小事务发生锁冲突的概率更小
• 尽量使用较低的隔离级别
• 精心设计索引，尽量使用索引访问数据，使加锁更精确。从而减少锁冲突的机会
• 尽量使用等值条件检索数据，在这样可以尽可能的避免Next-Key锁对并发插入的影响
• 对于一些特定的事务，可以选择使用表锁来提高处理速度并减少发生死锁的概率
• Gap 锁往往是程序中导致死锁的真凶，由于默认情况下 MySQL 的隔离级别是 RR，所以如果能确定幻读和不可重复读对应用的影响不大，可以考虑将隔离级别改成 RC，可以避免 Gap 锁导致的死锁
• 设置锁等待超时参数：innodb_lock_wait_timeout，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

• MySQL中的锁类型以及死锁分析
• 数据库中常见的死锁原因与解决方案
• MySQL死锁产生原因和解决方法
• 常见 SQL 语句的加锁分析