MySQL系列（10）— 事务隔离性之锁
InnoDB 中的锁
上篇文章末尾说到 MVCC 其实是 快照读，对普通的 SELECT 查询可以保证事务的隔离性，但 当前读 还是能读到别的事务已提交的修改。除此之外，多个事务并发更新同一条数据，还需要保证并发写的隔离性，避免脏写的问题。这些情况下就要用到锁的机制了，锁机制就是为了支持对共享资源的并发访问，保证数据的完整性和一致性。
锁的类型
MySQL 有多种存储引擎，MyISAM、MEMORY、MERGE 这些存储引擎只支持表级锁，而且这些引擎不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话。
InnoDB 存储引擎既支持表级锁，也支持行级锁。行级锁就是针对行记录加锁，行级锁粒度更细，并发性能比表级锁更高。
InnoDB 有如下两种类型的行级锁：
- 共享锁（S Lock）：简称
S锁，后面就用SLock来表示。SLock 允许事务读一行数据。 - 排它锁（X Lock）：简称
X锁（XLock），后面就用XLock来表示。XLock 允许事务删除或更新一行数据。
如果一个事务T1获取了一行记录的 SLock，接着另一个事务T2可以立即获取到这行记录的 SLock，因为读取并没有改变这行记录。如果另一个事务T3想要获取 XLock，就会被阻塞，必须等待 T1、T2 都释放了 SLock 才能获取到 XLock。
如果事务T1一开始就获取了 XLock，那其它事务就无法获取 SLock 和 XLock 了，必须等待T1释放了 XLock 后才能获得锁。
它们的兼容性如下表所示：
一致性非锁定读
前面说过基于 MVCC 的读取是快照读，也可以称为一致性读或一致性非锁定读，所谓的非锁定读就是指读操作不会对表中的记录做任何加锁操作，其他事务可以对表中的记录做修改。
因为基于 MVCC 的读取是读的undo版本链上的快照版本，所以其它事务可以对同样的记录加 SLock 或 XLock，一致性非锁定读不会去等待行上锁的释放，避免了频繁的加锁操作，大大提高了读操作的性能。
可想而知，非锁定读机制可以极大地提高数据库的并发性。InnoDB 默认就是一致性非锁定读的读取方式，即读取不会占用和等待表上的锁。但需要注意的是，InnoDB 只在 READ COMMITTED、REPEATABLE READ 这两个隔离级别下采用一致性非锁定读，也就是基于 MVCC 的读取。
一致性锁定读
前面说 InnoDB 默认是一致性非锁定读，但有些场景下，我们可能想要显示的对读取操作加锁来保证数据逻辑的一致性，这种就是一致性锁定读。也可以称为当前读，因为通过加锁操作来保证读取的是最新的数据，获得锁之后，别的事务就不能更新加锁的记录了。
InnoDB 中的当前读：
SELECT ... LOCK IN SHARE MODE：获取到SLock，其它事务可以获取到 SLock，但不能获取 XLock。SELECT ... FOR UPDATE：获取到XLock，可能要对数据做更新，其它事务会阻塞等待。本质上和 UPDATE 语句的语意是一致的。UPDATE：更新数据都是先读后写的，而这个读，只能读当前的值，就是当前读。
需要注意的是，LOCK IN SHARE MODE或FOR UPDATE必须在一个事务中使用，事务结束后，锁就自动释放了。
比如在查询账户余额来更新的时候，查询时先对记录显示加 SLock，而不是默认的快照读，这时其它的事务就只能读取这条记录，而无法更新。但这可能会导致死锁，比如事务T1先获取了 SLock，事务T2也获取了同一条记录的 SLock，然后事务T1要更新这条记录，就会一直阻塞住，因为更新要获取记录的 XLock，XLock 和 SLock 是不兼容的。
BEGIN;
SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE;
UPDATE account SET balance=100 WHERE id = 1;
如果一开始查询就加 XLock，这样别的事务就无法再加 SLock 或者 XLock 了，这样就能保证只有一个事务更新记录。
BEGIN;
SELECT * FROM account WHERE id = 1 FOR UPDATE;
UPDATE account SET balance=100 WHERE id = 1;
一般来说，通过SQL加锁来实现一致性是不太好的方式，这样会导致将复杂的业务锁机制隐藏到数据库层面去，在业务代码层面就非常不好维护。一般在分布式系统的场景中，更推荐基于 redis、zookeeper 的分布式锁来实现复杂业务下的锁机制。
表级锁
InnoDB 支持对表加锁，表级锁也分为共享锁（SLock）和排他锁（XLock）。
在执行增删改查的SQL语句时，InnoDB 并不会为这个表添加表级别的 SLock 或者 XLock。对表执行DDL操作时（如 ALTER TABLE、DROP TABLE），也不会用到 InnoDB 表级锁，而是用的 MySQL Server 层面提供的一种元数据锁（Metadata Lock，简称MDL）来实现的。
不过我们可以通过如下语句手动获取表级别的 SLock 和 XLock：
LOCK TABLES t READ：InnoDB 会对表加表级别的 SLock。LOCK TABLES t WRITE：InnoDB 会对表加表级别的 XLock。
表级锁和行级锁是互斥的，它们的兼容性如下表所示。
那如何实现表级别的锁和行级别的锁互斥呢？例如一个事务中在更新某些记录，对这些记录加了行级 XLock，另一个并发事务要用 LOCK TABLES 对这个表加 SLOck 或者 XLock，这时肯定就会阻塞。
InnoDB 提供了另一种表级锁，称为意向锁，也分为共享锁和排它锁：
意向共享锁：简称IS锁，后面就称ISLock。当事务准备在某条记录上加 SLock 时，需要先在表级别加一个 ISLock。意向排他锁：简称IX锁，后面就称IXLock。当事务准备在某条记录上加 XLock 时，需要先在表级别加一个 IXLock。
ISLock和IXLock仅仅为了在之后加表级别的SLock和XLock时可以快速判断表中的记录是否被加锁，所以 ISLock 和 IXLock 是兼容的，IXLock 和 IXLock 也是兼容的，ISLock、IXLock和表级别的 SLock、XLock 是有一定互斥性的。表级锁和意向锁的兼容性如下表所示。
所以在一个事物中更新记录加行级XLock时，首先会在表上加IXLock，如果此时表已经加了 XLock 或 SLock，就会阻塞。如果没有加表级锁，IXLock 就会加锁成功，此时另外的事务想要加表级锁就会阻塞。
表级锁的粒度比较粗，一般我们也不会用到 LOCK TABLES 来手动加表级锁，所以 InnoDB 的表级锁和意向锁是比较鸡肋的。
锁结构
无论是表级锁还是行级锁，锁其实就是内存中的一个数据结构，对一条记录加锁的本质，其实就是在内存中创建一个锁结构与之关联。
锁的内存结构
行锁结构
首先看一下行锁的结构，主要包含的一些信息如下图所示：
① 锁所在的事务信息：记录事务ID（trx_id）等信息。
② 索引信息：行锁是通过索引实现的，索引信息就是记录加锁的记录是属于哪个索引的。
③ 行锁信息：
- 表空间ID：记录所在表空间ID。
- 页号：记录所在页号。
- n_bits：行锁末尾放了一堆比特位，n_bits 表示使用了多少个比特位。
④ type_mode：这是一个32位的数，存储了四个部分的信息。
-
lock_mode：锁的模式，占用低4位。可选的值有：- LOCK_IS：意向共享锁
- LOCK_IX：意向排它锁
- LOCK_S：共享锁
- LOCK_X：排它锁
- LOCK_AUTO_INC：主键自增锁
-
lock_type：锁的类型，占用第5～8位。可选的值有：- LOCK_TABLE：表级锁
- LOCK_REC：行级锁
-
is_waiting：锁的状态，占用第9位。可选的值有：- 1：当这个比特位为1时，表示当前事务未获取到锁，处在等待状态
- 0：当这个比特位为0时，表示当前事务获取锁成功
-
rec_lock_type：行锁的具体类型，使用其余的位来表示。只有在lock_type的值为LOCK_REC时，也就是只有在该锁为行级锁时，才会被细分为更多的类型。可选的值有：- LOCK_ORDINARY：Next-Key Lock
- LOCK_GAP：Gap Lock
- LOCK_REC_NOT_GAP：Record Lock
- LOCK_INSERT_INTENTION：插入意向锁
⑤ 比特位：一个页中有很多记录，末尾的这一堆比特位就是用来表示哪条记录被加锁了。页中的每条记录的记录头中都有一个 heap_no 属性表示记录的排序号，每个比特位就映射着一个 heap_no。
表锁结构
相比行锁结构，表锁没有末尾的比特位，以及只存储了表锁相关的一些表信息。
查看锁信息
在 information_schema 数据库下，我们可以通过 INNODB_TRX 查询系统当前正在运行的事务信息，还可以通过 INNODB_LOCKS 查询事务持有的锁信息。
INNODB_LOCKS 表中有如下字段：
- lock_id：锁的ID
- lock_trx_id：事务ID
- lock_mode：锁的模式，主要的类型有：共享锁（
S）、排它锁（X）、意向共享锁（IS）、意向排它锁（IX）。 - lock_type：锁的类型，
RECORD代表行级锁，TABLE代表表级锁。 - lock_table：要加锁的表
- lock_index：锁住的索引
- lock_space：表空间ID
- lock_page：事务锁定页的数量。若是表锁，则该值为NULL
- lock_rec：事务锁定行的数量，若是表锁，则该值为NULL
- lock_data：事务锁定记录的主键值，若是表锁，则该值为NULL
加锁方式
InnoDB 在任何隔离级别下都不会发生脏写的问题，多个并发事务对同一条记录做修改时，只能排队一个一个修改，这个排队就是通过锁来实现的。在对某条记录做 UPDATE、DELETE 操作时，都会先获取这条记录的 XLock 后再操作，获取不到就会阻塞等待。下面就来看下 InnoDB 是如何通过锁避免脏写的问题的。
后面还是以 account 这张表为例，来做一些测试：注意 card 列是唯一索引，name 列是普通索引。
CREATE TABLE `account` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`card` varchar(60) NOT NULL COMMENT '卡号',
`name` varchar(60) DEFAULT NULL COMMENT '姓名',
`balance` int(11) NOT NULL DEFAULT '0' COMMENT '余额',
PRIMARY KEY (`id`),
UNIQUE KEY `account_u1` (`card`) USING BTREE,
KEY `account_n1` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户表';
例如有两个事务T1、T2按如下顺序更新 id=1 这条记录：
事务T1更新 id=1 这条记录时，首先会看内存中有没有与这条记录关联的锁结构，没有的话就会生成一个锁结构与之关联，并且 is_waiting=false，表示获取锁成功（加锁成功），然后事务T1就可以执行更新操作了。
接着事务T2查询id=1这行数据，此时是基于MVCC的快照读，不会对记录加任何锁。
接着事务T2更新id=1这行数据，这时就会发现已经有一个锁与这条记录关联了，然后事务T2也生成一个锁结构与这条记录关联，但是 is_waiting=true，表示获取锁失败（加锁失败），需要等待。
这时查看 INNODB_LOCKS 表，可以看到有两把锁，我们可以得到如下信息：
lock_trx_id：对应的事务ID分别是 560677、560676。lock_mode：锁的模式是排它锁（XLock）。lock_type：RECORD 表示加的是行锁。lock_index：是针对主键索引加的锁。
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 |
+------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 560677:13971:3:2 | 560677 | X | RECORD | `test`.`account` | PRIMARY | 13971 | 3 | 2 | 1 |
| 560676:13971:3:2 | 560676 | X | RECORD | `test`.`account` | PRIMARY | 13971 | 3 | 2 | 1 |
+------------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
接着可以通过 INNODB_TRX 表查询当前事务信息，可以看到，560676 这个事务的运行状态为 RUNNING， 560677 这个事务的运行状态为 LOCK WAIT，也就是等待锁。从这也可以知道，T1事务的事务ID=560676，T2事务的事务ID=560677。
mysql> SELECT * FROM information_schema.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 560677 | LOCK WAIT | 2021-05-31 17:41:03 | 560677:13971:3:2 | 2021-05-31 17:41:03 | 2 | 4 | UPDATE account SET balance=200 WHERE id = 1 | starting index read | 1 | 1 | 2 | 1136 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 |
| 560676 | RUNNING | 2021-05-31 17:40:58 | NULL | NULL | 3 | 3 | NULL | NULL | 0 | 1 | 2 | 1136 | 1 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
之后事务T1提交事务，就会把该事务生成的锁结构释放掉，然后看还有没有别的事务在等待获取锁，发现事务T2还在等待获取锁，所以把事务T2对应的锁结构的 is_waiting 属性设置为 false，然后把该事务对应的线程唤醒，让它继续执行，此时事务T2就获取到锁了。
行级锁
参考：极客时间 《丁奇-MySQL实战45讲》
行锁类型
InnoDB 既实现了行锁，也实现了表锁。行锁是通过索引实现的，当SQL命中索引时，就会锁住条件内的索引节点。如果没有命中索引，那么锁的就是整个索引树，其实就是升级为表锁了。
InnoDB 有3种行级锁的算法实现，锁结构中rec_lock_type属性就表示行锁类型。
-
Record Lock：记录锁，专门对索引项加锁，就是锁单条记录。 -
Gap Lock：间隙锁，对索引项之间的间隙加锁，但不包含记录本身，锁住的是一个区间。 -
Next-Key Lock：临键锁，Gap Lock + Record Lock 的组合，对索引项之间的间隙加锁，包含记录本身，是一个左开右闭的区间。
Record Lock 总是会锁住索引记录，如果在建表的时候没有设置任何一个索引，那么就会使用隐式的主键(row_id)来进行锁定。
Gap Lock 和 Next-key Lock 锁住的是一段区间，即这个区间被锁住后，不允许插入新的值，所以间隙锁主要用于解决幻读的问题。
还有一种锁是 插入意向锁（Insert Intention），插入意向锁类似于 Gap Lock，不过插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。插入意向锁的主要目的是，如果别的事务在这个间隙加了 Gap Lock，那么要在这个间隙插入数据的这个事务就要生成一个插入意向锁的结构，然后等待。所以插入意向锁和 Gap Lock 是不兼容的。
关于间隙锁，例如一个索引有 10、15、20 这三个值：
-
那么该索引可能被 Next-Key Lock 锁住的区间为：
(-∞, 10] (10, 15] (15, 20] (20, +Supremum] -
能被 Gap Lock 锁住的区间为：
(-∞, 10) (10, 15) (15, 20) (20, +Supremum)
注意在 (20, +∞) 区间实际上的区间是 (20, +Supremum] 这个区间。前面介绍页的结构时说过，InnoDB 每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的记录，Supremum 记录是比改页中何主键值都要大的记录。(20, +Supremum] 这里的 Supremum 就是 20 这条记录所在页的最大记录。
加锁规则
什么时候加 Record Lock，什么时候又加 Gap Lock 或者 Next-Key Lock，总结起来加锁规则有如下几条。
-
1、加锁时会根据索引从左往右查找，加锁的基本单位是
Next-Key Lock，就是说在对某条记录加锁时，默认用 Next-Key Lock 去锁住一个左开右闭的区间。 -
2、查找过程中只有访问到的对象才会加锁，比如查询只用到了辅助索引，就不会对聚簇索引加锁。
-
3、唯一索引上的等值查询，给唯一索引加锁的时候，
Next-Key Lock退化为Record Lock。 -
4、普通索引上的等值查询，会一直向右遍历，最后一个值不满足等值条件的时候，
Next-Key Lock退化为Gap Lock。（就是说某个普通索引上可能有多个相同的值，因此就会有多个 Next-Key Lock 锁住的左开右闭的区间，但最后一个区间会退化为 Gap Lock）。 -
5、唯一索引上的范围查询会访问到不满足条件的第一个值为止（包含这条记录）。
注意只有在 REPEATABLE READ 或以上隔离级别下的特定操作才会取得 Gap Lock 或 Next-key Lock，在 SELECT 、UPDATE 和 DELETE 时，除了基于唯一索引的查询之外，其他索引查询时都会获取 Gap Lock 或 Next-key Lock，即锁住其扫描的范围。
我们往 account 表中初始化几条数据，下面来做些测试验证上面的规则。
TRUNCATE TABLE account;
INSERT INTO account ( id, card, NAME, balance )
VALUES
( 1, 'A', 'A', 0 ),
( 5, 'D', 'D', 5 ),
( 10, 'H', 'H', 10 ),
( 15, 'M', 'M', 15 ),
( 20, 'R', 'R', 20 )
下面是这张表的初始数据，为了测试，后续我所有的测试SQL都会默认使用 ROLLBACK 回滚掉，也就是每次测试执行SQL前的初始数据都是一样的。
mysql> SELECT * FROM account;
+----+------+------+---------+
| id | card | name | balance |
+----+------+------+---------+
| 1 | A | A | 0 |
| 5 | D | D | 5 |
| 10 | H | H | 10 |
| 15 | M | M | 15 |
| 20 | R | R | 20 |
+----+------+------+---------+
这几条数据在主键索引上就会产生如下几个间隙：
(-∞, 1]
(1, 5]
(5, 10]
(10, 15]
(15, 20]
(20, +Supremum]
name 这个普通索引上会产生如下几个间隙：
(-∞, A]
(A, D]
(D, H]
(H, M]
(M, R]
(R, +Supremum]
唯一索引等值查询 - 行锁
1、两个事务按如下顺序执行SQL语句：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT * FROM account WHERE id=1 LOCK IN SHARE MODE; | |
| UPDATE account SET balance=200 WHERE id=1;(blocked) | |
| COMMIT; | |
| ROLLBACK; |
两个事务都是使用的主键ID来查询数据，T2事务执行时会阻塞住。
用加锁规则分析下：
- 根据规则1，加 Next-Key Lock 锁住
(-∞, 1]这个区间。 - 根据规则3，由于是唯一索引等值查询，且记录存在，因此 Next-Key Lock 退化为 Record Lock，因此只锁住
id=1这条记录。
因为T1锁住了 id=1 这条记录，T2 同样也是更新 id=1 这条记录，因此需要等待。
查看 INNODB_LOCKS 表，其中 lock_type 都为 RECORD，表示加的是行级锁；lock_index 列显示锁住的是聚簇索引（主键），lock_data 显示锁定的主键为 1。
mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-----------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data |
+-----------------+-----------+-----------+------------------+------------+-----------+
| 566301 | X | RECORD | `test`.`account` | PRIMARY | 1 |
| 282768778791576 | S | RECORD | `test`.`account` | PRIMARY | 1 |
+-----------------+-----------+-----------+------------------+------------+-----------+
两个事务的 locke_mode 是不同的，一个是 X（XLock），一个是 S（SLock）。从这可以看出，Record Lock 有 SLock 和 XLock 之分，互斥规则和前面说的是一样的。
2、接着按如下的顺序执行：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT * FROM account WHERE card='A' LOCK IN SHARE MODE; | |
| UPDATE account SET balance=200 WHERE card='A'(blocked) |
这次T1、T2是根据唯一索引列 card 来查询，且 card=A 存在，所以在唯一索引card上对card=A这条记录加 Record Lock。从 INNODB_LOCKS 查看 lock_index、lock_data 可以得到证实。
mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-----------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data |
+-----------------+-----------+-----------+------------------+------------+-----------+
| 566334 | X | RECORD | `test`.`account` | account_u1 | 'A' |
| 282768778791576 | S | RECORD | `test`.`account` | account_u1 | 'A' |
+-----------------+-----------+-----------+------------------+------------+-----------+
3、接着按如下顺序执行：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT * FROM account WHERE card='A'LOCK IN SHARE MODE; | |
| UPDATE account SET balance=200 WHERE id=1;（blocked） |
T1 事务根据 card 列查询，T2 事务根据 id 列查询，推测 T1 事务应该是锁住唯一索引 card=A，T2 锁住聚簇索引 id=1。但查看 INNODB_LOCKS，会发现它们都是锁住的聚簇索引 id=1 这条记录。其实也很好理解，T2 要锁住 id=1 这条记录，T1 是想锁定读 card=A 这条记录，T1 需要回表查询聚簇索引上的数据，因此就直接锁住聚簇索引 id=1 这条记录了，阻塞 T2 事务更新这条记录。
mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-----------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data |
+-----------------+-----------+-----------+------------------+------------+-----------+
| 566336 | X | RECORD | `test`.`account` | PRIMARY | 1 |
| 282768778791576 | S | RECORD | `test`.`account` | PRIMARY | 1 |
+-----------------+-----------+-----------+------------------+------------+-----------+
4、接着按如下顺序执行：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT id FROM account WHERE card = 'A'LOCK IN SHARE MODE; | |
| UPDATE account SET balance=balance+200 WHERE id=1;（Affected rows: 1） |
T1 事务根据 card 查询 id 字段，不需要回表，所以它只需要锁住唯一索引 card=A 即可，T2 事务根据 id 列查询更新，这次会发现 T2 事务可以更新成功，不会阻塞住了。根据加锁规则的第2条可知，只有访问到的对象才会加锁，因此 T1 事务不会对聚簇索引 id=1 加锁，因此T2可以加锁成功。
5、最后按照如下顺序执行：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT id FROM account WHERE card = 'A'FOR UPDATE; | |
| UPDATE account SET balance=200 WHERE id=1;（blocked） |
与上一次的执行相比，区别在于 T1 事务的锁定读是用的 FOR UPDATE，这次 T2 事务会阻塞住。查看 INNODB_LOCKS 会发现两个事务都是对聚簇索引 id=1 加 Record Lock(XLock)。
mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data |
+-------------+-----------+-----------+------------------+------------+-----------+
| 566341 | X | RECORD | `test`.`account` | PRIMARY | 1 |
| 566339 | X | RECORD | `test`.`account` | PRIMARY | 1 |
+-------------+-----------+-----------+------------------+------------+-----------+
这可以验证锁定读语句 LOCK IN SHARE MODE 和 FOR UPDATE 在语义上的不同，FOR UPDATE 表示想要更新这条记录，LOCK IN SHARE MODE 可能只是想读取这条记录，防止别的事务更新。因此 FOR UPDATE 的语义和 UPDATE 是类似的，认为你接下来要去更新数据，因此会顺便给聚簇索引上满足条件的行加上行锁，防止其它事务并发更新。
唯一索引等值查询 - 间隙锁
在上一小节中根据唯一索引查询的记录在数据库中都是存在的，所以都是加的行锁（Record Lock），那如果记录不存在又会加什么锁呢？
按如下顺序执行：
| T1 | T2 | T3 |
|---|---|---|
| BEGIN; | BEGIN; | BEGIN; |
| SELECT * FROM account WHERE id=3LOCK IN SHARE MODE;（Query 0） | ||
| INSERT INTO account VALUES (2, 'B', 'B', 0);（blocked） | ||
| UPDATE account SET balance=balance+100 WHERE id = 5;（Affected rows: 1） |
T1 事务查询 id=3 这条记录不存在，接着事务 T2 要插入 id=2 这条记录，被阻塞住了，接着 T3 更新了 id=5 这条记录，可以更新成功。
用加锁规则分析下 T1 事务的加锁：
- 根据规则1，加锁
Next-Key Lock，由于id=3这条记录不存在，所以锁住的是(1, 5]这个区间。 - 根据规则4，这是一个唯一索引等值查询（
id=3），所以id=5这条记录不满足条件，Next-Key Lock退化为Gap Lock，因此锁住的范围是(1, 5)。
事务 T1 锁住了 (1, 5) 这个间隙，因此 T2 事务想在这个间隙插入一个 id=2 的记录会被阻塞住，而 T3 事务就可以成功更新 id=5 这条记录。
执行 T1、T2，查看 INNODB_LOCKS 表，lock_mode 显示加的是 Gap Lock，很明显 T1 事务加的是 S Gap Lock，T2 事务加的是 X Gap Lock，说明 Gap Lock 也分 S、X 类型。lock_index 显示锁住的是主键索引，lock_data 表示在 id=5 这条记录上加的 Gap Lock。
mysql> SELECT lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data FROM information_schema.INNODB_LOCKS;
+-------------+-----------+-----------+------------------+------------+-----------+
| lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data |
+-------------+-----------+-----------+------------------+------------+-----------+
| 566789 | X,GAP | RECORD | `test`.`account` | PRIMARY | 5 |
| 566788 | S,GAP | RECORD | `test`.`account` | PRIMARY | 5 |
+-------------+-----------+-----------+------------------+------------+-----------+
接着按如下顺序执行
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT * FROM account WHERE id=3LOCK IN SHARE MODE;（Query 0） | |
| SELECT * FROM account WHERE id=4 LOCK IN SHARE MODE;（Query 0） |
在上一步中，T3 事务是对 id=5 这条记录加 Record Lock，可以执行成功。这次执行事务 T2 同样是对 (1,5) 这个区间加 Gap Lock，可以执行成功。
从这可以说明一个问题：虽然 Gap Lock 有 S、X 之分，但是它们起到的作用都是相同的。如果对一条记录加了 Gap Lock，并不会限制其他事务对继续这条记录加 Record Lock 或者 Gap Lock。
Gap Lock 只是用于保护这个间隙，防止插入新的记录，Gap Lock 其实仅仅是为了防止插入幻影记录而提出的。
普通索引等值查询
按如下顺序执行SQL
| T1 | T2 | T3 | T4 |
|---|---|---|---|
| BEGIN; | BEGIN; | BEGIN; | BEGIN; |
| SELECT id FROM account WHERE name = 'D'LOCK IN SHARE MODE; | |||
| INSERT INTO account VALUES(4, 'C', 'C', 0);（blocked） | |||
| INSERT INTO account VALUES(6, 'E', 'E', 0);（blocked） | |||
| UPDATE account SET balance=balance+100WHERE name = 'H';（Affected rows: 1） |
事务T1查询 name='D'，name 列是普通索引，用加锁规则来分析下：
- 根据规则1，遍历索引name列，锁住的间隙是
(A, D]，由于是普通索引，还会向右继续遍历，因此还会锁住(D, H]这个区间。 - 根据规则4，索引上的等值查询，向右遍历到最后一个不满足等值条件的时候，Next-Key Lock 锁住的
(D, H]会退化为 Gap Lock，只锁住(D, H)。 - 根据规则2，只有访问到的对象才会加锁，这里的查询只用到了普通索引name，所以不会对聚簇索引加锁（但注意如果是 FOR UPDATE 查询，就会对聚簇索引加锁）。
也就是说事务T1对name列普通索引加锁，锁住的间隙是 (A, D]、(D, H)。
因此可以看到事务T2想要往 (A, D] 这个间隙插入一个 'C' 会被阻塞住，事务T3想往 (D, H) 间插入一个 'E' 也会被阻塞。而事务T4更新的是 'H'，不在T1锁住的间隙内，因此可以更新成功。
Limit 语句加锁
还是上面的例子，但这次T1中的查询加了 LIMIT 1，因为满足 name='D' 的数据也只有一条。
| T1 | T2 | T3 |
|---|---|---|
| BEGIN; | BEGIN; | BEGIN; |
| SELECT id FROM account WHERE name = 'D' LIMIT 1LOCK IN SHARE MODE; | ||
| INSERT INTO account VALUES(4, 'C', 'C', 0);（blocked） | ||
| INSERT INTO account VALUES(6, 'E', 'E', 0);（Affected rows: 1） |
但这次的结果却不一样了，事务T3可以向 (D, H) 这个间隙插入一个 'E' 了。
有了 LIMIT 语句之后，结果虽然一样，但加锁的效果是不一样的。因为加了 LIMIT 1 之后，在遍历到 name='D' 之后，已经有一条满足条件的数据了，就不会再往后遍历了，因此锁住的区间就只有 (A, D] 了，所以T3事务可以执行成功。
我们这里虽然是用的 SELECT ... LOCK IN SHARE MODE(或 FOR UPDATE)，但它和 DELETE、UPDATE 的加锁逻辑是类似的。如果我们在根据普通索引来 DELETE/UPDATE，且知道记录数时，那我们就可以在执行 DELETE/UPDATE 时加上 LIMIT，这样不仅可以控制删除/更新数据的条数，让操作更安全，还可以减小加锁的范围，提高数据库并发性能。
例如我将T1事务的SELECT换成UPDATE后，可以看到效果还是一样的。但如果去掉 LIMIT 1，事务T3还是会阻塞住的。
| T1 | T2 | T3 |
|---|---|---|
| BEGIN; | BEGIN; | BEGIN; |
| UPDATE account SET balance=balance+100WHERE name = 'D' LIMIT 1; | ||
| INSERT INTO account VALUES(4, 'C', 'C', 0);（blocked） | ||
| INSERT INTO account VALUES(6, 'E', 'E', 0);（Affected rows: 1） |
主键索引范围查询
按如下顺序执行SQL
| T1 | T2 | T3 |
|---|---|---|
| BEGIN; | BEGIN; | BEGIN; |
| SELECT id FROM account WHERE id >= 5 AND id < 6LOCK IN SHARE MODE; | ||
| INSERT INTO account VALUES(8, 'F', 'F', 0);（blocked） | ||
| UPDATE account SET balance=balance+100WHERE id = 10;（blocked） |
这次事务T1是在主键列上进行的范围查询，注意这里的条件是 id >= 5 AND id < 6，它和直接查询 id=5 是不同的，虽然查询结果相同，但加锁规则是不同的。查询 id=5 很容易理解，加的是 Record Lock。使用加锁规则来分析下 id >= 5 AND id < 6 是加的什么锁：
- 查询
id >= 5，要对 id=5 这条记录加锁，根据规则1，默认加 Next-Key Lock，锁住(1, 5]这个区间。 - 根据规则3，
id=5是唯一索引等值查询，所以 Next-Key Lock 退化为 Record Lock，只锁住id=5这一条记录。 - 接着进行范围查询，根据规则5，唯一索引上的范围查询会访问到不满足条件的第一个值为止，会访问到
id=10这条记录，因此加 Next-Key Lock 锁住(5, 10]这个间隙。
因此T1事务锁住的是 id=5 这条记录以及 (5, 10] 这个区间。
可以看到事务T2想要往 (5,10) 这个间隙插入 id=9 被阻塞住了，符合预期。但 T3 事务更新 id=10 这条记录也被阻塞住了，因此需要注意，规则5所说的访问到不满足条件的第一个值为止，会包含不满足条件的这条记录，也就是 (5,10]。
接着按如下顺序执行SQL
| T1 | T2 | T3 | T4 |
|---|---|---|---|
| BEGIN; | BEGIN; | BEGIN; | BEGIN; |
| SELECT * FROM account WHERE id > 5 AND id <= 15LOCK IN SHARE MODE; | |||
| INSERT INTO account VALUES (14, 'L', 'L', 0);（blocked） | |||
| INSERT INTO account VALUES (16, 'N', 'N', 0);（blocked） | |||
| UPDATE account SET balance=balance+100 WHERE id = 20;（blocked） |
这次T1事务的查询条件是 id > 5 AND id <= 15，按我们的理解，应该是锁住 (5, 10]、(10, 15] 这个区间，事务T2想往(10, 15]这个区间插入id=14，确实被阻塞住了。但事务T3想插入id=16，以及事务T4想更新id=20这条记录也都被阻塞住了。
所以，这里要注意理解原则5，唯一索引上的范围查询会向右访问到不满足条件的第一个值为止，范围查询 id <= 15 时，id=15 这条记录是满足条件的，所以会接着访问，访问到 id=20 这条记录时，才不满足了。所以T1事务锁住的范围是 (5, 10], (10, 15]，(15, 20] 这三个区间。
不过，它这里看起来更像是一个BUG，按照规则3，唯一索引上的等值查询，即查询 id<=15 在匹配到id=15这条记录时就会停止，Next-Key Lock 会退化为 Record Lock，加锁的区间应该是 (5, 10], (10, 15) 以及id=15这条记录，所以不会锁住(15, 20]这个区间才是，而且这明显也是锁得多余了。
普通索引范围查询
按如下顺序执行SQL
| T1 | T2 | T3 | T4 |
|---|---|---|---|
| BEGIN; | BEGIN; | BEGIN; | BEGIN; |
| SELECT id FROM account WHERE name >= 'D' AND name < 'E'LOCK IN SHARE MODE; | |||
| INSERT INTO account VALUES (4, 'C', 'C', 100);（blocked） | |||
| INSERT INTO account VALUES (8, 'F', 'F', 100);（blocked） | |||
| UPDATE account SET balance=100WHERE name = 'H';（blocked） |
这次T1事务是对普通索引 name 进行条件查询，查询条件是 name >= 'D' AND name < 'E'，用加锁规则来分析下：
name>='D'会先找到name='D'这条记录，根据规则1，加 Next-Key Lock，锁住(A, D]这个区间，由于是普通索引，继续往右遍历，因此还会加 Next-Key Lock 锁住(D, H]。- 由于 name 列不是唯一索引，不满足规则3，因此 Next-Key Lock 锁住的
(A, D]不会退化为 Record Lock。 - 由于是范围查询，不是等值查询，因此不满足规则4，Next-Key Lock 锁住的
(D, H]不会退化为 Gap Lock。这是和普通索引等值查询的区别。
因此事务T1锁住的区间是 (A, D]、(D, H] 两个区间。
事务T2想往 (A, D] 区间插入一个 'C'，事务T3想往 (D, H] 区间插入一个 'F'，而事务T4想更新 name='H' 这条记录，T1事务锁住了 (A, D]、(D, H] 两个区间，因此 T2、T3、T4 都需要加锁等待，被阻塞住。注意和普通索引等值查询 的区别，普通索引等值查询 name='D' 时，可以更新 name='H' 这条记录，而范围查询 name>='D' 时不行。
无索引查询
按如下顺序执行：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT * FROM account WHERE balance = 0 FOR UPDATE; | |
| INSERT INTO account(card, name, balance) VALUES ('X', 'X', 100);（blocked） |
这次事务T1查询 balance=0，balance 列上没有任何索引，这时会锁住全表。可以看到事务T2想插入一个 balance=100 会被阻塞住，可以验证这个结论。
幻读
幻读是指在同一事务下，连续执行两次同样的SQL语句看到不同的结果，第二次的SQL语句可能会返回之前不存在的行，幻读是专指读到新插入的行。
幻读的问题
也许我们会觉得既然是当前读，能读到别的事务新插入的行也是正常的，但幻读有两个问题。
首先从语义上来说，幻读的问题在于，事务T1要锁定的是一行数据，如果事务T2新插入了一行，然后事务T1再次锁定读时发现了两行，这从语义上来说是有问题的。
其次，幻读最大的问题在于可能导致数据和binlog日志在逻辑上的不一致性。binlog 默认是 Statement 模式，记录的是更新的SQL语句。
例如按下表执行，事务T1根据 name='D' 更新 balance=300，如果允许幻读，事务T2就可以再插入一行 name='D'，balance=0 的数据，事务T1最后才提交，这时数据库中新插入的那条 name='D' 的数据 balance=0。binlog是在事务提交后才写入的，因此binlog中的顺序则是T2事务中的插入语句，接着才是事务T1中的更新语句。如果将这个binlog应用到主从复制上，从库中所有 name='D' 的数据 balance=300，这就出现了不一致。
| T1 | T2 |
|---|---|
| BEGIN; | |
| UPDATE account SET balance=300 WHERE name='D'; | |
| INSERT INTO account VALUES (6, 'D', 'D', 0); | |
| COMMIT; |
解决幻读
前面的内容已经多次提到过幻读这个问题了，在上一篇文章的末尾，我们提到在RR隔离级别下，基于MVCC的快照读是不会有幻读的问题的，只在当前读的情况下才会出现幻读。因此一般在读取数据时，使用快照读就可以了。
在前面的多项测试中，很容易发现，Next-Key Lock 或者 Gap Lock 就是用于解决幻读问题的，如果只使用 Record Lock 锁住已存在的行记录，那么行之间的间隙是没办法控制的，因此其它事务就可能往这些间隙插入数据，进而导致幻读的问题。
间隙锁是在 可重复读(REPEATABLE READ) 隔离级别下才生效的，因此要避免幻读的问题，需要设置数据库隔离级别为可重复读。
但是间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实对数据库的并发性能会有一定影响。如果确定业务不需要可重复读的保证，可以将数据库隔离级别设置到读已提交(READ COMMITTED)，但这可能会导致数据和 binlog 日志不一致，这时需要把 binlog 模式设置为 row。ROW 模式下，binlog 记录的是每行数据的修改，就不会有 Statement 模式下的那个问题了，但 ROW 模式会产生大量日志内容。
死锁
死锁问题
前面我们测试了那么多锁相关的内容，但这些测试中多个事务之间只是一个事务加锁，然后阻塞其它事务，只要这个事务执行完释放锁，另一个事务就能继续执行。
死锁就不一样了，死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。
例如有下面这样一个场景，根据 name 查询，不存在则插入数据：
| T1 | T2 |
|---|---|
| BEGIN; | BEGIN; |
| SELECT * FROM account WHERE name = 'E' FOR UPDATE; | |
| SELECT * FROM account WHERE name = 'F' FOR UPDATE; | |
| INSERT INTO account(card, name) VALUES ('F', 'F');（blocked） | |
| INSERT INTO account(card, name) VALUES ('E', 'E');（Deadlock found when trying to get lock; try restarting transaction） | |
| （Affected rows: 1） |
事务T1先锁定读 name='E'，加的锁是 Gap Lock，锁住的是 (D, H) 这个间隙，事务T2也是一样的，但间隙锁之间是不会互相阻塞的。事务T2锁住了 (D, H)，但在插入数据时却阻塞住了，它是被T1事务加的 Gap Lock 给阻塞住的。接着事务T1又来插入数据，这时数据库就检测到死锁了，直接抛出死锁异常并重新开始了事务。然后事务T2就得以继续执行事务。
在上面这个示例中，两个事务都持有 (D, H) 这个间隙的 Gap Lock，但接下来的插入操作都要获取这个间隙的插入间隙锁，插入间隙锁和 Gap Lock 是冲突的，因此都要等待对方事务的 Gap Lock 释放，于是就造成了循环等待，导致死锁。
解决死锁
解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在InnoDB存储引擎中，可以用参数innodb_lock_wait_timeout来设置超时的时间。
超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用FIFO的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。
因此，除了超时机制，当前数据库还都普遍采用 wait-for graph（等待图）的方式来进行死锁检测，当检测到死锁后会选择一个最小（锁定资源最少得事务）的事务进行回滚。较之超时的解决方案，这是一种更为主动的死锁检测方式。可以通过参数 innodb_deadlock_detect=on 开启死锁检测，默认开启。
不过，解决死锁的最佳方式就是预防死锁的发生，我们平时编程中，可以通过一些手段来预防死锁的发生。
-
在编程中尽量按照固定的顺序来处理数据库记录，比如有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，就有可能导致死锁；
-
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 Gap Lock 导致的死锁问题；
-
更新表时，尽量使用主键更新；使用普通索引更新时，可能会锁住很多间隙。如果不同时事务使用不同索引来更新，也可能导致死锁。
-
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
-
设置锁等待超时参数，通过
innodb_lock_wait_timeout设置合理的等待超时阈值。在一些高并发的业务中，可以将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
转载自:https://juejin.cn/post/6979793212281995271