系列文档参考 MYSQL系列-整体架构介绍
本文介绍MYSQL中各种锁的实现，以及一些遇到问题的分析和解决方法。
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
锁类型
根据加锁范围：MySQL里面的锁可以分为：全局锁、表级锁、行级锁
全局锁
对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景
全库逻辑备份
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数--single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
一致性读是好，但是前提是引擎要支持这个隔离级别。因此推荐使用INNODB引擎
延伸：如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
表锁
MySQL里面表级锁有两种，一种是表锁，一种是元数据锁(meta data lock,MDL)
表锁
表锁的语法是:lock tables ... read/write
用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。
mysql> lock tables test read;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
元数据锁（MDL）
不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。
表锁之间关系
上面已经讲到表锁有排他锁（X）和共享锁（S），接下来还有意向锁：
-
共享意向锁（intention shared lock，以下简称 IS）：表示事务持有表中行的共享锁或者打算获取行的共享锁。
-
共享排他锁（intention exclusive lock，以下简称 IX）：表示事务持有表中行的排他锁或者打算获取行的排他锁。
IS 和 IX 只是为了表达出一种意图，它们除了全表请求之外，不会阻塞任何操作。它们的主要目的只是为了表示持有一个行锁，或者打算获取行锁。
意向锁的使用规则如下：
- 事务在获取表中的共享行锁时，需要先获取表中的 IS 锁或者等级更高的锁。
- 事务在获取表中的排他行锁时，需要先获取表中的 IX 锁。
因此，只有获取表中行锁时，才需要申请意向锁。如果执行DDL命令，直接申请表级X锁
表级别下的X锁、S锁、IS 锁和 IX 锁的兼容性如下：
| X | S | IS | IX | |
|---|---|---|---|---|
| IS | 冲突 | 兼容 | 兼容 | 兼容 |
| IX | 冲突 | 冲突 | 兼容 | 兼容 |
| X | 冲突 | 冲突 | 冲突 | 冲突 |
| S | 冲突 | 兼容 | 兼容 | 冲突 |
延伸：MYSQL5.6之后支持的online ddl说明
MySQL 5.6 引入了在线 DDL 功能，可以在不停止或阻塞对数据库的访问的情况下进行结构修改。在线 DDL 是基于 InnoDB 存储引擎实现的，可以通过以下命令执行在线 DDL 操作：
ALTER TABLE table_name [options];
其中，table_name 是要修改的表名，options 是修改选项，可以是以下选项之一或多个：
ADD COLUMN：添加一个新的列。DROP COLUMN：删除一个列。MODIFY COLUMN：修改一个列的数据类型、长度或属性。RENAME COLUMN：重命名一个列。ADD INDEX：添加一个新的索引。DROP INDEX：删除一个索引。RENAME INDEX：重命名一个索引。
需要注意的是，执行 ALTER TABLE 命令时，MySQL 可能会对表进行锁定和重建，这可能会导致一些性能问题和延迟。因此，在进行在线 DDL 操作时，需要进行充分的测试和评估，并根据实际情况进行调整和优化。
MySQL 5.6 之后，InnoDB 存储引擎引入了一些新的在线 DDL 功能，包括：
ALGORITHM=INPLACE：使用原地算法进行表结构修改，不需要创建新的表，可以大大减少表锁定的时间和影响。ALGORITHM=COPY：使用复制算法进行表结构修改，需要创建一个新的表，将数据从旧表复制到新表，并在完成后重命名表，这可能会对性能产生影响。LOCK=NONE：使用非阻塞锁定进行表结构修改，可以避免表锁定和对应用程序的影响，但是可能会增加一些额外的成本和风险。
需要注意的是，不是所有的 ALTER TABLE 操作都支持在线 DDL，一些特殊的操作，如改变表的存储引擎、改变表名等，可能需要停止或阻塞对数据库的访问。因此，在进行在线 DDL 操作时，需要仔细阅读 MySQL 文档，并根据实际情况进行调整和优化。
行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。
InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。
两阶段锁
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放， 而是要等到事务结束时才释放。
建议：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
下面介绍几种行锁，以下表举例
mysql> CREATE TABLE user (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(255) NOT NULL,
-> age INT,
-> INDEX age_index(age)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO user (id, name, age) VALUES
-> (1, 'Alice', 25),
-> (2, 'Bob', 30),
-> (3, 'Bob', 35),
-> (4, 'David', 40),
-> (5, 'Eve', 45),
-> (6, 'Frank', 50),
-> (7, 'Grace', 55),
-> (8, 'Henry', 60),
-> (9, 'Ivy', 65),
-> (10, 'John', 70);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
Record Locks（记录锁）
行锁，对一行记录进行加锁 是对索引记录的锁定，每一个表必定会有一个主键索引，而该主键索引中的非叶子节点中的记录就是使用该记录锁进行锁定
//session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 10 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 10 | John | 70 |
+----+------+------+
1 row in set (0.00 sec)
//session 2 (bolck)
mysql> select * from user where id = 10 for update;
//查询结果
mysql> SELECT * FROM information_schema.INNODB_LOCKS;
+--------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 9517:72:3:11 | 9517 | X | RECORD | `toby`.`user` | PRIMARY | 72 | 3 | 11 | 10 |
| 9516:72:3:11 | 9516 | X | RECORD | `toby`.`user` | PRIMARY | 72 | 3 | 11 | 10 |
+--------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 9517 | 9517:72:3:11 | 9516 | 9516:72:3:11 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
如果使用普通索引锁定，则将会将普通索引锁定，然后再锁定主键索引 比如执行SQL select * from user where name = 'Bob' for update;
Gap（间隙锁）
间隙锁（简称为 Gap）是对索引记录之间的间隙的锁定，或者是对第一条索引记录之前的间隙和对最后一条记录之后的间隙的锁。间隙锁是防止幻读的主要手段之一 比如这个SQL select * from user where id >9 for update; 会记录锁 ID=10 和间隙锁 (10, +∞)
间隙锁并不是在任何情况下都会使用，它在以下情况并不会使用：
- 隔离级别为 RC、RU。
- 使用唯一索引进行等值比较获取一条索引记录。这是因为唯一索引进行等值比较只能获取一条记录，不会出现多条记录的情况，那么也就不会出现多次读取出现不一致的情况。
间隙锁的主要目的是阻止事务往间隙中插入记录，并且间隙锁之间是可以共存的，多个事务可以同时获取得到相同间隙的锁。共享间隙锁和排他间隙锁之间并没有区别，它们是完全一样的东西。
Next-Key Lock
Next-Key Locks：由Gap Locks和Record Locks组成，前开后闭
在默认的 REPEATABLE READ 隔离级别下，InnoDB 在查找和扫描索引时，都会使用 Next-Key 锁，以此来防止幻读的发生
Insert Intention Locks（II Gap锁 插入意向锁）
插入意向锁（简称为 II Gap）是一种特殊的间隙锁，只有在插入记录的时候才会使用，这个锁表示插入的意向。它与上面说到的表级意向锁是完全不同的，插入意向锁是属于行级锁，并且互相之间是兼容的，互不冲突，所以多个事务可以同时获取到相同间隙的 II Gap 锁。
插入意向锁只会和间隙锁和 Next-Key 锁冲突。因为间隙锁的主要作用是防止幻读的发生，而在插入操作执行前需要获取到插入意向锁，而插入意向锁和间隙锁之间是冲突的，可以阻塞插入操作，所以间隙锁可以防止幻读的发生。
四种行锁总结
InnoDB 的四种行锁的兼容性，如下表所示：
| Record | Gap | Next-Key | II Gap | |
|---|---|---|---|---|
| Record | 冲突 | 兼容 | 冲突 | 兼容 |
| Gap | 兼容 | 兼容 | 兼容 | 冲突 |
| Next-Key | 冲突 | 兼容 | 冲突 | 冲突 |
| II Gap | 兼容 | 兼容 | 兼容 | 兼容 |
note： 第一列表示已经持有的锁，第一行表示要获取的锁。
从表中可以得出结论：
- 插入意向锁不影响其他事务获取其他的锁。
- 插入意向锁会受到 Gap 锁和 Next-Key 锁的影响。一个事务想要获取指定间隙的插入意向锁，那么该间隙中的 Gap 锁和 Next-Key 锁必须没有被其他事务持有，否则，将会被阻塞。
加锁原则和验证
加锁原则
- 加锁基本单位next-key lock，next-key lock = 间隙锁 + 行锁，前开后闭
- 查询过程中访问到的对象都要加锁
- 索引等值查询，给唯一索引加锁时，next-key lock会退化为行锁
- 索引等值查询，向右遍历时且最后一个值不满足查询条件，next-key lock会退化为间隙锁
- 索引上的范围查询会访问到不满足条件的第一个值为止
验证
创建表和数据如下
mysql> CREATE TABLE t1 (
-> id INT PRIMARY KEY,
-> b INT,
-> c INT,
-> INDEX b_index(b)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> INSERT INTO t1 (id, b, c) VALUES
-> (1, 1, 1),
-> (5, 5, 5),
-> (10, 10, 10),
-> (15, 15, 15),
-> (30, 30, 30);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
产生6个区间(-oo,1)(1,5)(5,10)(10,15)(15,30)(30,+supermum),对应的next-key lock:(-oo,1](1,5](5,10](10,15](15,30](30,+supermum]
加锁规则
唯一索引等值查询
记录存在等值查询
| session1 | session2 |
|---|---|
| begin | |
| select * from t1 where id = 5 for update;//ok | |
| begin | |
| insert into t1 values(8, 8, 8);//ok | |
| update t set c = c + 1 where id = 5;//block | |
| rollback | |
| rollback |
1条，加锁加next-key lock (0,5]
第3条，唯一索引，退化为行锁 id=5
记录不存在等值查询
| session1 | session2 |
|---|---|
| begin | |
| select * from t1 where id = 8 for update;//ok | |
| begin | |
| update t set c = c + 1 where id = 10//ok | |
| insert into t1 values(9, 9, 9);;//block | |
| rollback | |
| rollback |
1条规则，加锁加next-key lock(5, 10]
第4条规则，0不满足查询条件(id = 8)，退化为间隙锁(5,10)
普通索引等值查询
记录存在的等值查询
| session1 | session2 |
|---|---|
| begin | |
| select * from t1 where b = 5 for update;//ok | |
| begin | |
| insert into t1 values(4, 4, 4);//block | |
| insert into t1 values(9, 9, 9);//block | |
| update t1 set c = c + 1 where b = 5;//block | |
| update t1 set c = c + 1 where b = 10;//ok | |
| rollback | |
| rollback |
第1条规则，加锁加next-key lock(0, 5]，由于c索引是非唯一索引，会继续往后查找，找到10这条记录，不满足，返回，第2条规则查找过程中访问到的对象会被加锁，因此加锁范围包括(5,10]，根据第4条规则，加锁范围为(5,10)，最终加锁范围(0,5]、(5,10)
记录不存在的等值查询
| session1 | session2 |
|---|---|
| begin | |
| select * from t1 where b = 8 for update;//ok | |
| begin | |
| update t set c = c + 1 where b = 10//ok | |
| insert into t1 values(9, 9, 9);;//block | |
| rollback | |
| rollback |
第一条规则，加锁加next-key lock(5, 10]，第4条规则10不满足查询条件(c = 7)，退化为间隙锁(5,10)
唯一索引范围查询加锁
| session1 | session2 |
|---|---|
| begin | |
| select * from t1 where id >= 10 and id <= 15 for update;//ok | |
| begin | |
| insert into t1 values(9, 9, 9);//ok | |
| insert into t1 values(16, 16, 16);//block | |
| insert into t1 values(12, 12, 12);//block | |
| update t1 set c = c + 1 where id = 12;//ok | |
| update t1 set c = c + 1 where id = 15;//block | |
| update t1 set c = c + 1 where id = 20;//ok | |
| rollback | |
| rollback |
第1条规则，加锁加next-key lock(5,10],(10,15],第3条规则，(5,10]退化为行锁 id=10
根据第5条规则，(15,20]区间范围也会被加锁.根据第4条规则，退还为(15,20)
注意期间更新不存在的行不会锁住
非唯一索引范围查询加锁
| session1 | session2 |
|---|---|
| begin | |
| select * from t1 where b >= 10 and b <= 15 for update;//ok | |
| begin | |
| insert into t1 values(9, 9, 9);//block | |
| insert into t1 values(16, 16, 16);//block | |
| insert into t1 values(12, 12, 12);//block | |
| update t1 set c = c + 1 where id = 12;//ok | |
| update t1 set c = c + 1 where id = 15;//block | |
| update t1 set c = c + 1 where id = 20;//ok | |
| rollback | |
| rollback |
第1条规则，加锁加next-key lock(5,10],(10,15]
根据第5条规则，(15,20]区间范围也会被加锁.根据第4条规则，退还为(15,20)
注意期间更新不存在的行不会锁住
死锁与死锁检测
并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。
解决方案：
1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
mysql> show variables like '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。
如何解决热点行更新导致的性能问题？
1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。
死锁案例
案例1：根据字段值查询（有索引），如果不存在，则插入；否则更新
| session1 | session2 |
|---|---|
| begin | begin |
| select * from t1 where id=17 for update;//ok | |
| select * from t1 where id=18 for update; | |
| insert into t1 values(17, 17, 17);//block | |
| insert into t1 values(18, 18, 18); //ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | |
| rollback | |
| rollback |
当对存在的行进行锁的时候(主键)，mysql就只有行锁。当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）
解决方法：insert into t1(xx,xx) on duplicate key update xx ='XX';用mysql特有的语法来解决此问题
案例2：两个session分别通过一个sql持有一把锁，然后互相访问对方加锁的数据产生死锁
| session1 | session2 |
|---|---|
| begin | begin |
| select * from t1 where id=5 for update;//ok | |
| delete from t1 where id = 15;//ok | |
| update t1 set c=c+1 where id=15;//ok ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | |
| delete from t1 where id = 5; | |
| rollback | |
| rollback |
session2执行完delete from t1 where id = 5后，session发现deadlock
MYSQL死锁相关配置（5.7）
- innodb_deadlock_detect：该参数控制InnoDB引擎是否开启自动检测死锁，默认为开启。如果关闭该参数，虽然可以提高性能，但会影响数据库的稳定性。
- innodb_lock_wait_timeout：该参数控制InnoDB引擎在等待获取锁的过程中的超时时间，默认值为50秒。如果在等待该时间后仍未获取到锁，则会返回错误信息。
- innodb_locks_unsafe_for_binlog：该参数控制InnoDB引擎是否将锁信息写入binlog文件，默认为关闭。如果开启该参数，可以提高性能，但会影响主从同步的正确性。
- innodb_print_all_deadlocks：该参数控制InnoDB引擎是否在错误日志中打印所有检测到的死锁信息，默认为关闭。如果开启该参数，可以方便地查看死锁的详细信息，但会影响错误日志的大小。
- innodb_thread_concurrency：该参数控制InnoDB引擎的并发线程数，默认值为0，表示自动计算。如果设置该参数为一个正整数，可以提高并发性能，但需要根据硬件配置和负载情况进行合理的调整。
innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。
学习参考
1.MySQL的解“锁”之路（一）—— 这些锁你都认识吗？
2.带你了解record lock、gap lock、next-key lock
3.(八)MySQL锁机制：高并发场景下该如何保证数据读写的安全性？
