掌控MySQL并发：深度解析锁机制与并发控制

推荐课程

前一篇MySQL读取的记录和我想象的不一致——事物隔离级别和MVCC讲了事务在并发执行时可能引发的一致性问题的各种现象。一般分为下面3种情况：

• 读 - 读情况：并发事务相继读取相同的记录。读取操作本身不会对记录有任何影响，不会引起什么问题，所以允许这种情况发生。
• 写 - 写情况：并发事务相继对相同的记录进行改动。
• 读 - 写或写 - 写情况：也就是一个事务进行读取，另一个事务进行改动。

现在就来看看怎么处理这几种并发问题

1. MySQL的锁机制与冲突解决

1.1 如何管理并发事务冲突

当一个事务想对这条记录进行改动时，首先会看看内存中有没有与这条记录关联的锁结构，如果没有，就会在内存中生成一个锁结构与之关联。比如，事务 T1要对这条记录进行改动，就需要生成一个锁结构与之关联

锁结构有很多信息，在这里只拿出两个比较重要的属性

• trx信息：表示这个锁结构是与哪个事务关联的
• is_waiting：表示当前事务是否在等待

在事务 T1改动了这条记录前，就生成了一个锁结构与该记录关联。因为之前没有别的事务为这条记录加锁，所以 is_waiting就是 false，我们把这个场景就称之为获取锁成功（ 加锁成功），然后就可以继续操作了。

在事务 T1提交之前，另一个事务 T2也想对该记录做改动，先去看看有没有锁结构与这条记录关联。如果有一个锁结构与之关联，那么 T2也生成一个锁结构与这条记录关联，不过锁结构的 is_waiting属性值为 true，表示当前事务需要等待，我们把这个场景就称之为获取锁失败（ 加锁失败）。

事务 T1提交之后，就会把它生成的锁结构释放掉，然后检测一下还有没有与该记录关联的锁结构，发现了事务 T2还在等待获取锁，所以把事务 T2对应的锁结构的 is_waiting属性设置为 false，然后把该事务对应的线程唤醒，让 T2继续执行，此时事务 T2就算获取到锁了

总结：
在 InnoDB中，锁是通过锁队列来管理的。并非所有的事务都有锁结构，只有那些试图获取锁（无论是成功获取还是正在等待获取）的事务才会有对应的锁结构。当事务试图获取一把锁时， InnoDB会为这个事务创建一个锁结构，并将其添加到锁的等待队列中。等待队列是按照请求锁的顺序（即 FIFO）来管理的。只有当前持有锁的事务释放锁之后，等待队列中的下一个事务才能获取到这把锁。

这个锁结构中的 is_waiting属性表示该事务是否正在等待获取锁。如果事务成功获取了锁，那么其对应的锁结构中的 is_waiting属性将被设置为 false，反之，如果事务正在等待获取锁，那么 is_waiting属性将被设置为 true。

; 1.2 MVCC与锁机制

前一篇说过， MySQL在 RR隔离级别下很大程度上避免了幻读，但是还是可能出现幻读。

怎样避免脏读、不可重复读、幻读呢？有两种可选方案

MVCC通过为每个事务生成一个 ReadView，这样读操作就能看到一致性的数据快照。即使在读取过程中，其他事务对数据进行了修改，读操作也只能看到生成 ReadView时的数据状态。这种方法能有效降低读写冲突，提高数据库的并发性能。然而需要注意的是，在 MySQL的可重复读（ RR）隔离级别下，尽管使用了 MVCC技术和 Gap Locking、 Next-Key Locking等技术来避免幻读，但由于 MySQL的具体实现问题，仍然可能出现幻读。为了完全避免幻读，可以选择将隔离级别升级到串行化（ Serializable）。为了避免脏读、不可重复读、幻读，实际开发中一般推荐 RC隔离级别+ Redis分布式锁的方式。

相比于 MVCC方式，这种方法可能会降低性能，因为读写操作需要排队执行。然而，在某些特殊业务场景中，例如需要对数据进行原子性操作的场景，比如处理账户之间的转账，必须确保转账操作的完整性和一致性，避免出现脏读、不可重复读和幻读等问题，这种情况下，使用锁机制就显得尤为重要。

2. 行锁

2.1 Record Lock——记录锁（S锁和X锁）

并发事务的读-读情况通常不会引发问题，但是对于写-写、读-写或写-读这些情况，可能会引起一些问题。为了解决这些问题，我们可以使用 MVCC或加锁策略。加锁策略包括共享锁（ S锁）和独占锁（ X锁）。

• 共享锁（ Shared Locks）：简称 S锁。在事务要读取一条记录时，需要先获取该记录的 S锁。多个事务可以同时对一条记录持有 S锁，但如果一个事务持有 X锁，其他事务则不能获得该记录的 S锁。
• 独占锁（ Exclusive Locks）：也常称排他锁，简称 X锁。在事务要修改一条记录时，需要先获取该记录的 X锁。当一条记录被加上 X锁后，其他事务不能获取该记录的任何锁（无论是 S锁还是 X锁），直到持有 X锁的事务提交。

假如事务 T1首先获取了一条记录的 S锁之后，之后事务 T2接着也要访问这条记录：

如果事务 T1首先获取了一条记录的 X锁之后，那么不管事务 T2接着想获取该记录的 S锁还是 X锁都会被阻塞，直到事务 T1提交。

顾名思义， ** X 锁为独占锁，记录加了 X 锁后，这条记录再加其他的锁都会被阻塞。**

S锁和 X锁的兼容关系如下表：

兼容性X锁S锁X锁不兼容不兼容S锁不兼容兼容

2.1.1 锁定读的语句

• 对读取的记录加 S锁：

SELECT ... LOCK IN SHARE MODE;

在事务处理中，为了保证数据的一致性和完整性，我们可以在读取记录时加上共享锁（ S锁）。通过在普通的 SELECT语句后添加 "LOCK IN SHARE MODE"，事务会为读取到的记录加上 S锁。加上 S锁后，其他事务仍然可以获取这些记录的 S锁（例如，使用 "SELECT ... LOCK IN SHARE MODE"语句读取这些记录），但无法获取这些记录的独占锁（ X锁）。如果其他事务试图获取这些记录的 X锁（例如，使用 "SELECT ... FOR UPDATE"语句读取这些记录或直接修改这些记录），它们将被阻塞，直到当前事务提交并释放这些记录上的 S锁为止。

• 对读取的记录加 X锁：

SELECT ... FOR UPDATE;

当一个事务通过在 SELECT语句后添加 FOR UPDATE来对读取的记录加上排他锁（ X锁）时，它确保在该事务持有锁期间，其他事务无法获取这些记录的共享锁（ S锁）或排他锁（ X锁）。这意味着，其他事务不能使用 SELECT ... LOCK IN SHARE MODE语句读取这些记录，也不能使用 SELECT ... FOR UPDATE语句或直接修改这些记录。在当前事务提交并释放这些记录上的 X锁之前，任何试图获取这些记录的 S锁或 X锁的其他事务都将被阻塞。这种锁策略有助于确保数据的一致性和安全性，但可能会降低系统的并发性能。在实际应用中需要根据业务需求和性能考虑选择合适的锁策略。

2.2 Gap Lock——gap锁

Gap Lock 是锁定记录之间的间隙，而不是锁定记录本身，其主要目的是阻止在锁定范围内插入新记录，从而在大部分情况下防止了幻读现象。

InnoDB存储引擎中的 Gap Locks是自动添加的，并非由用户手动控制。这种锁会在可重复读（ Repeatable Read, RR）隔离级别下的事务中根据操作类型自动被 InnoDB引擎添加。虽然 Gap Locks在大部分情况下能够有效防止幻读现象，但由于 InnoDB的 MVCC（多版本并发控制）机制，在某些特殊情况下仍可能出现幻读。

需要注意的是， ** Gap Locks 只在可重复读（ RR ）隔离级别下才会自动添加。** 在读已提交（ Read Committed, RC）隔离级别下， InnoDB不会使用 Gap Locks。

在 RR隔离级别下会自动添加 Gap Locks的情况：

当执行范围查询（如 SELECT ... WHERE ... BETWEEN或 SELECT ... WHERE ... >等）时， InnoDB会在查询范围内的间隙自动添加 Gap Locks，以防止其他事务在查询范围内插入新记录。

当执行 UPDATE或 DELETE操作时，如果涉及到一个范围内的记录， InnoDB会自动在该范围内的间隙上添加 Gap Locks，防止其他事务在这些间隙中插入新记录。

来举一个 RR隔离级别的例子：

Gap Locks在大部分情况下能够有效防止幻读，假设有两个并发的事务，事务 A和事务 B。

事务 A首先执行一个范围查询：

SELECT * FROM table WHERE id BETWEEN 1 AND 10;

这时 InnoDB会在 (1, 10)查询范围内的间隙自动添加 Gap Locks。然后，事务 B尝试插入一个新记录:

INSERT INTO table (id) VALUES (5);

因为新记录的 id值 (5)在间隙锁定的范围内，这个插入操作将会被阻止，从而防止了幻读现象的发生。

图示举例，建表演示说明一下 RR隔离级别的例子

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

在 RR隔离级别下，当一个事务试图插入一条新记录到某个间隙时， InnoDB会检查这个间隙是否已经被 Gap Lock锁住。如果是，这个插入操作会被阻塞，直到持有 Gap Lock的事务释放锁为止。

假如此刻需要插入一条记录

INSERT INTO hero (number, name, country) VALUES (4, 'New Hero', 'New Country');

如图中假设有一个事务（事务 A）已经在 number值为 3和 8之间的间隙上加了 Gap Lock。当另一个事务（事务 B）试图插入一条 number值为 4的新记录时，它会首先定位到新记录的下一条记录的 number值为 8。由于事务 A已经在 (3, 8) 这个间隙上加了 Gap Lock，事务 B的插入操作将被阻塞，直到事务 A释放这个 Gap Lock为止。这意味着事务 A需要提交或回滚，从而允许事务 B在 (3, 8)区间插入新记录。（这里把 b+树的索引结构进行超级简化，只把聚集索引叶子结点拿出来）

事务在等待时也需要在内存中生成一个锁结构，如下图，注意锁的 type属性的区别。这表示有事务想在某个间隙中插入新纪录但处于等待状态，这种插入意向锁命名为 LOCK_INSERT_INTENTION，后面会讲这个插入意向锁。

加了 gap锁是不允许其他事务往间隙内插入新记录，那对于最后一条记录之后的间隙怎么办呢，也就是 hero表中 number值为 20的记录之后的间隙该咋办呢？这就得提到之前的讲索引时提到的两条伪记录了：

• Infimum记录，表示该页面中最小记录的上一条记录
• Supremum记录，表示该页面中最大记录的下一条记录

为了实现阻止其他事务插入 number值在 (20, +∞)这个区间的新记录，我们可以给索引中的最后一条记录，也就是 number值为 20的那条记录与所在页面的 Supremum记录之间的间隙加上一个 gap锁，画个图就是这样：

这样就可以阻止其他事务插入 number值在 (20, +∞)这个区间的新记录。为了大家理解方便，之后的索引示意图中都会把这个 Supremum记录画出来。

gap 锁可能产生死锁，需要注意，这里举个例子：

假设有两个事务，事务 A和事务 B。将使用如下表：

CREATE TABLE example (
    id INT PRIMARY KEY,
    value INT
) Engine=InnoDB CHARSET=utf8;

现在假设事务 A和事务 B分别执行以下操作：

事务 A开始：

BEGIN;
SELECT * FROM example WHERE value BETWEEN 10 AND 20 FOR UPDATE;

在这个范围查询中，事务 A会在查询范围内的间隙上添加 Gap Locks，以防止其他事务在范围内插入新记录。

事务 B开始：

BEGIN;
SELECT * FROM example WHERE value BETWEEN 15 AND 25 FOR UPDATE;

在这个范围查询中，事务 B会在查询范围内的间隙上添加 Gap Locks。由于事务 A已经在部分范围内持有 Gap Locks，事务 B将被阻塞，等待事务 A释放它们。

接下来，事务 A尝试执行以下操作：

INSERT INTO example (id, value) VALUES (100, 18);

由于事务 B持有 15到 25之间间隙的 Gap Locks，事务 A现在会被阻塞，等待事务 B释放这些锁。

在此时，事务 B尝试执行以下操作：

INSERT INTO example (id, value) VALUES (200, 12);

由于事务 A持有 10到 20之间间隙的 Gap Locks，事务 B现在会继续被阻塞。

在这个例子中，事务 A和事务 B分别持有对方所需要的 Gap Locks，并且它们都在等待对方释放这些锁。这就形成了一个死锁。当 InnoDB检测到死锁时，它会选择一个事务（通常是等待时间较长的事务）作为死锁的受害者，将其回滚以释放锁，从而解决死锁问题。

所以，尽管 Gap Locks是自动添加的，但在某些情况下，它们仍然可能导致死锁。

2.3 Next-Key Lock——记录锁+gap锁组合

我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，该怎么办呢？ InnoDB有一种称为 Next-Key Locks的锁，我们也可以简称为 next-key锁。比方说我们把 number值为 8的那条记录加一个 next-key锁的示意图如下：

next-key锁的本质就是一个记录锁和一个 gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

在 InnoDB存储引擎中， Next-Key Lock主要在以下情况下使用：

• 可重复读（ Repeatable Read, RR）隔离级别：当事务隔离级别为可重复读时， InnoDB会使用 Next-Key Lock来减少幻读现象。在这种隔离级别下，事务执行范围查询、更新或删除操作时， InnoDB会自动添加 Next-Key Lock。
• 范围查询操作：当事务执行范围查询并锁定记录时，例如使用 SELECT ... FROM ... WHERE ... FOR UPDATE或 SELECT ... FROM ... WHERE ... LOCK IN SHARE MODE语句， InnoDB会在查询范围内的记录和相应间隙上添加 Next-Key Lock。这可以确保在事务执行过程中，其他事务不能在查询范围内插入、更新或删除记录。
• 范围更新或删除操作：在执行范围更新或删除操作时，例如使用 UPDATE ... WHERE ...或 DELETE FROM ... WHERE ...语句， InnoDB会在涉及到的记录和相应间隙上添加 Next-Key Lock。这有助于确保在事务执行过程中，其他事务不能在受影响范围内插入新记录或修改现有记录。

这些情况的例子将在后面加锁语句分析的章节详细讲解。

需要注意的是， Next-Key Lock的使用可能会导致一定程度的性能开销，并在一些情况下引发死锁。

Next-Key Lock 可能会导致死锁，举个例子：

假设我们有一个名为 orders的表：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    amount DECIMAL(10, 2)
) Engine=InnoDB CHARSET=utf8;

现在，有两个事务分别执行以下操作：

事务A：

START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 1 FOR UPDATE;

UPDATE orders SET amount = amount + 100 WHERE id = 2;
COMMIT;

事务B：

START TRANSACTION;
SELECT * FROM orders WHERE id = 2 FOR UPDATE;

UPDATE orders SET amount = amount - 100 WHERE customer_id = 1;
COMMIT;

在这个例子中，事务A首先对 customer_id = 1的记录范围加了 Next-Key Lock（记录锁和间隙锁），然后试图更新 id = 2的记录。与此同时，事务 B首先对 id = 2的记录加了 Next-Key Lock，然后试图更新 customer_id = 1的记录。

由于事务 A和事务 B互相等待对方释放锁，导致了死锁。在这种情况下， InnoDB引擎会自动检测到死锁，并中止一个事务，从而释放锁资源，让其他事务继续执行。

这个例子表明， Next-Key Lock可能会导致死锁，因为多个事务可能同时试图锁定相互依赖的记录和间隙。要避免死锁，可以尝试调整事务的执行顺序，或者采用其他隔离级别（如读已提交）。

2.4 隐式锁

隐式锁是为了节省内存中生成锁结构的开销而引入的概念，涵盖了各种情况下，系统自动获取的锁，包括 INSERT， UPDATE， DELETE等操作。

在介绍隐式锁之前，先说说插入意向锁。

插入意向锁（ Insert Intention Lock）是一种特殊的间隙锁，用于处理 INSERT操作中的并发控制。当一个事务试图在一个已经被加了 Gap锁的间隙内插入新的记录时，这个事务就会在这个间隙设置一个插入意向锁，然后这个事务会被阻塞，直到 Gap锁的事务提交或者回滚。

插入意向锁可以被视为一种"预约"机制，表明事务有意在这个间隙内插入新的记录。假设有多个事务，它们都想在不同的间隙上插入记录。这些事务可以在不同的间隙上各自设置一个插入意向锁，然后并发地等待相应间隙的锁被释放。这种并发等待提高了系统的整体并发性能，因为它允许多个事务同时在等待锁，而不是一个接一个地等待。需要注意的是，对于同一个间隙（ gap），在任何给定的时间点，只能有一个事务持有插入意向锁（需要排队）。如果一个事务尝试在一个已经有插入意向锁的间隙中插入新记录，那么该事务必须等待，直到前一个插入意向锁被释放。

一般情况下执行 INSERT语句时不需要在内存中生成锁结构来参与竞争，只有在即将插入的间隙已经被其他事务加了 Gap锁时，本次 INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁。在没有 gap锁竞争的情况下， INSERT操作通常不会生成锁结构。插入新记录之后，事务会在新记录上获取隐式 X锁，防止其他事务同时修改该记录。

注意：为了提高性能和降低内存使用，插入新记录之后，新记录上的 X锁被设计为没有锁结构的隐式锁，只有当其他事务对该记录进行写操作的时候，被升级为显式锁结构（ X锁）。其他事务 普通读(不加锁的读) 这条新记录在任何隔离级别下都不会被阻塞，加 S锁或 X锁的读会被阻塞。

当插入操作完成，新记录已经被成功添加到表中，并且在新记录上成功获取了隐式锁后，插入意向锁就不再需要，会被立即释放。但 新记录上的隐式锁会在事务提交时才被释放。这是为了防止在该事务提交之前，其他事务修改或删除这个新插入的记录。

比如：如果事务 A 插入一条记录 insert into test(id, name, age) value(2, 'lll', 18) ，事务 A 没有提交，事务 B 去 update test set name = 'qwe' where id = 2; 事务 B 会被阻塞，直到事务 A 提交。插入新记录成功后就会在该记录上立马上隐式锁，事务 B 的更新操作让新记录上的隐式锁升级为有显式锁结构的 X 锁，这对于任何隔离级别都成立！

如果事务 B是 select * from test where id = 2不会被阻塞，但是 select * from test where id = 2 lock in share mode;就会被阻塞。

总结：假设事务 T1存在隐式锁，事务 T2在对这条记录加 S锁或 X锁时， InnoDB引擎会首先帮助事务 T1生成锁结构，然后再为事务 T2生成锁结构并进入等待状态。

同样的，来看看 update和 delete的例子

假设我们有一个表，表中有一条记录，其 id字段的值为 1。

事务 A开始运行，并且尝试更新这条记录，例如 UPDATE table SET field = 'new value' WHERE id = 1。

在事务 A完成更新操作之前，事务 B也尝试更新或删除这条记录，例如 UPDATE table SET field = 'another value' WHERE id = 1或 DELETE FROM table WHERE id = 1。

分析：

隐式锁的生命周期与关联的事务紧密相连。当事务提交或回滚时，其关联的隐式锁也会被自动释放，这是由 InnoDB的事务管理机制自动处理的，不需要用户手动干预。

隐式锁起到了延迟生成锁结构的用处。 如果别的事务在执行过程中不需要获取与该隐式锁相冲突的锁，就可以避免在内存中生成锁结构。 这只是锁在实现上的一个内存节省方案，这对用户时透明的。无论使用隐式锁还是通过在内存中显式生成锁结构来保护记录，起到的作用是一样的。

除此之外， INSERT操作在下边两种特殊情况下也会进行加锁操作：

• 遇到 duplicate key：当插入操作导致唯一约束或主键约束冲突时， InnoDB引擎会加锁以防止数据不一致。
• 外键检查：当插入操作涉及到具有外键关系的表时， InnoDB引擎会进行外键检查并加锁以确保数据引用完整性。

举个具体的例子来说明隐式锁和上述两种特殊情况：

1. 遇到 duplicate key ：

我们创建一个名为 students的表，其中的 student_id字段是主键，是唯一的。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);

接着，我们在事务 A中插入一条记录：

BEGIN;
INSERT INTO students (student_id, name) VALUES (1, 'Tom');

在这个阶段，事务 A还没有提交。然后我们在另一个事务 B中尝试插入 student_id为 1的记录：

BEGIN;
INSERT INTO students (student_id, name) VALUES (1, 'Jerry');

事务 B将被阻塞，这还是我们上面分析过的，在事务 A中，当尝试插入一条 student_id为 1的记录时， InnoDB存储引擎会在这条新记录上获取一个隐式 X锁。事务 B中尝试插入另一条 student_id为 1的记录时， student_id为 1记录上的隐式 X锁会升级为有锁结构的显式 X锁，这个 X锁会阻止其他事务修改这条记录，直到事务 A完成（提交或回滚）

如果没有主键或唯一索引约束，两个并发的事务在插入数据时，通常不会互相阻塞。假设这里 student_id 没有主键约束，事务 B的插入操作不会被阻塞，因为没有主键或唯一索引约束阻止在 students表中插入多个具有相同 student_id的记录，这两个 INSERT操作都能成功完成，即使它们在并发执行。

2. 外键检查：

假设我们有两个表，一个是 orders表，一个是 order_items表。 order_items表有一个外键约束，引用 orders表的 order_id。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

在这种情况下，如果我们试图在 order_items表中插入一条记录

INSERT INTO order_items (item_id, order_id, product_name) VALUES (1, 1, 'Apple');

InnoDB需要检查这个插入操作是否违反了外键约束，它将查找 orders表中 order_id为 1的记录，如果 order_id为 1的记录存在，那么在查找和验证外键约束的过程中，会获取这条记录上的 S锁。如果 order_id为 1的记录不存在，那么实际上并没有具体的行可以加锁，那么插入操作将失败，因为这违反了外键约束。

这种锁定策略是为了保护数据的引用完整性。如果不加锁，那么可能会出现这样的情况：事务 A 中， InnoDB 查找查找 orders 表中的记录并进行外键检查的同时，事务 B 中的一个操作删除了 order_id 为 1 的记录。这样即使外键检查通过，插入操作也可能会违反外键约束，为了防止这种情况， InnoDB 在查找和检查外键约束的过程中会加锁。

3. 一致性读

事务利用 MVCC进行的读取操作称为一致性读（ Consistent Read），或者一致性无锁读（有的资料也称之为快照读）。所有普通的 SELECT语句在 READ COMMITTED、 REPEATABLE READ隔离级别下都算是一致性读。
比如这些都是一致性读：

select * from test;
select * from a join b on a.col1 = b.col2;

一致性读并不会对表中的任何记录加锁，其他事务可以自由的对表中的记录进行改动。

4. 写操作

在常见的写操作（ INSERT、 DELETE、 UPDATE）中， MySQL数据库使用不同的加锁策略来确保数据的一致性和并发性：

我们可以把这个定位记录在 B+树中位置，然后再获取记录的 X锁的过程看成是一个获取 X锁的锁定读。

• a. 如果未修改记录索引的键值且被更新列的存储空间在修改前后未变化，则先在 B+树中定位记录位置，然后获取记录的排他锁（ X锁），最后在原记录位置进行修改操作。
• b. 如果未修改记录索引的键值但至少有一个被更新列的存储空间发生变化，则先在 B+树中定位记录位置，获取记录的排他锁（ X锁），然后将记录彻底删除（移入垃圾链表），最后插入一条新记录，与被删除的记录关联的锁会转移到新插入的记录上。
• c. 如果修改了记录索引的键值，则相当于先对原记录执行 DELETE操作，再进行 INSERT操作，加锁操作需遵循 DELETE和 INSERT的规则。

在一些特殊情况下的 INSERT操作也会在内存中生成的锁结构。后面再说。

在一个事务中加的锁一般在事务提交或中止时才会释放。一个特殊情况是"锁升级"。在某些情况下，事务可能需要在执行过程中升级已经持有的某个锁，比如从共享锁（ S锁）升级到排他锁（ X锁）。这种情况下，事务可能会在执行过程中先释放较低级别的锁（如 S锁），然后再申请较高级别的锁（如 X锁）。以下是一个例子：

假设有一个事务 T1：

在这个例子中，虽然事务 T1尚未提交或中止，但它在执行过程中提前释放了 S锁，以便进行锁升级。

5. 什么是表锁？

在数据库中，表锁是一种锁定整张表的机制，它可以分为共享锁（ S锁）和独占锁（ X锁）。与行锁（针对单条记录的锁）相比，表锁的粒度较粗，涵盖整张表的所有记录。

如果想对整张表加 S锁，首先要确保表中没有任何一条记录加了 X锁，如果有记录加了 X锁，则需要等待 X锁释放才能对整张表加 S锁。

如果想对整张表加 X锁，首先要确保表中没有任何一条记录加了 X或者 S锁，如果有记录加了 X或者 S锁，需要等待对应的记录把 S锁和 X锁释放后才能对整张表加 X锁。

为了提高在给整张表加锁时，判断表中记录是否已经被锁定的效率，数据库引入了意向锁（ Intention Lock）。

意向锁包括意向共享锁（ IS锁）和意向独占锁（ IX锁）。当事务准备在某条记录上加 S锁时，需要先在表级别加一个 IS锁；当事务准备在某条记录上加 X锁时，需要先在表级别加一个 IX锁。 意向锁仅记录了对表中记录的锁定意图，避免了遍历整个表来查看记录是否上锁的低效操作。

意向锁可以与其他意向锁兼容，这意味着多个事务可以同时在一个表上持有 IS锁和 IX锁。然而，当一个事务想要在整张表上加 S锁或 X锁时，它需要检查表上的意向锁：如果想要加 S锁，需要确保没有 IX锁；如果想要加 X锁，则需要确保没有 IS锁和 IX锁。这样，意向锁可以提高检查表中记录锁定状态的效率。

总之，表锁和意向锁共同作用，提高了数据库在处理锁定问题时的效率。表锁负责锁定整张表，而意向锁则在表级别记录锁定意图，加快了锁定状态的判断过程。

以下是一些常见的 SQL 语句，它们可能会触发不同类型的表锁：

LOCK TABLES table_name READ;

读锁允许多个事务同时读取被锁定表中的数据，但不允许其他事务对表进行写操作。在一个事务对表加了读锁之后，其他事务也可以对同一表加读锁，但不能加写锁。

LOCK TABLES table_name WRITE;

写锁仅允许持有写锁的事务访问和修改被锁定表中的数据。在一个事务对表加了写锁之后，其他事务无法获取该表上的读锁或写锁。

• 意向共享锁（ Intention Shared Lock， IS锁）：
  当事务准备在某条记录上加 S锁时，需要先在表级别加一个 IS锁。 IS锁并不会直接阻止其他事务访问表中的数据，而是用来表示事务打算在表的某些行上加 S锁。
• 意向独占锁（ Intention Exclusive Lock， IX锁）：
  当事务准备在某条记录上加 X锁时，需要先在表级别加一个 IX锁。 IX锁并不会直接阻止其他事务访问表中的数据，而是用来表示事务打算在表的某些行上加 X锁。

请注意， MySQL中的 InnoDB存储引擎在大多数情况下会自动处理锁的类型，因此在实际应用中，我们通常不需要手动使用 LOCK TABLES语句。 InnoDB存储引擎默认使用行锁（记录锁）来保证事务的隔离性。只有在特殊情况下，例如需要手动锁定整个表以执行某些维护操作时，我们才可能需要使用表锁。

6. MySQL中的行锁与表锁

MySQL支持多种存储引擎，不同存储引擎对锁的支持也是不一样的，我们这里重点讨论 InnoDB存储引擎中的锁。

6.1 其他存储引擎中的锁

对于 MyISAM、 MEMORY、 MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。

比如在 Session 1中对一个表执行 SELECT操作，就相当于为这个表加了一个表级别的 S锁，如果在 SELECT操作未完成时， Session 2中对这个表执行 UPDATE操作，相当于要获取表的 X锁，此操作会被阻塞，直到 Session 1中的 SELECT操作完成，释放掉表级别的 S锁后， Session 2中对这个表执行 UPDATE操作才能继续获取 X锁，然后执行具体的更新语句。

因为使用 MyISAM、 MEMORY、 MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎实际上最好用在只读场景下，或者用在大部分都是读操作或者单用户的情景下。
另外，在 MyISAM存储引擎中有一个称之为 Concurrent Inserts的特性，支持在对 MyISAM表读取时同时插入记录，这样可以提升一些插入速度。

6.2 InnoDB存储引擎中的锁

InnoDB存储引擎既支持表锁，也支持行锁。表锁粒度粗，占用资源较少，有时候仅仅需要锁住几条记录，但使用表锁，相当于为表中的所有记录都加锁，并发性能比较差。行锁粒度更细，可以实现更精准的并发控制。

6.2.1 InnoDB中的表级锁（两个并发事务中的锁表演示）

InnoDB存储引擎提供的表级 S锁或者 X锁只会在一些特殊情况下（比如系统崩溃恢复时）用到。在这里，我用 local、 local2这 2个不同连接作为 会话A、 会话B来演示

CREATE TABLE t (
  id int NOT NULL AUTO_INCREMENT,
  c varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;

LOCK TABLES t WRITE;

BEGIN;

select * from t lock in share mode;

select * from t for update;
insert t VALUES(2, '张三');
update t set c = '张' where id = 2;
delete from t where id = 2;
select * from t;

增删改查全部被阻塞，篇幅原因就不重复截图了。

在演示的时候发生了一个现象。 sessionA的事务中锁表，此时 sessionB开启事务查询被阻塞，将 sessionA事务中的表解锁， sessionB中的查询就会成功，但是即便此时 sessionA提交事务再开启事务进行锁表的时候会被阻塞，只有将 sessionB中的事务提交之后，才可以在 sessionA中进行锁表，否则 sessionA的锁表操作会被阻塞。想自己验证锁表例子的小伙伴要格外注意。

这里可以看到，当事务 1 对整张表加了 IX 锁之后，事务 2 的增删改查全部被阻塞，即事务 2 对表中的记录加 X 锁或者 S 锁或隐式锁都会被阻塞。

上面说过， DELETE和 UPDATE去定位记录的时候就是对记录加 X锁的锁定读，所以会被阻塞。

- 解除表锁之后就可以对任意记录进行操作了
UNLOCK TABLES;

COMMIT;

BEGIN;
LOCK TABLES t READ;

BEGIN;

select * from t lock in share mode;

select * from t for update;

insert t VALUES(2, '张三');
update t set c = '张' where id = 2;
delete from t where id = 2;
select * from t;

可以看到，当表加了 S锁（就是 IS锁）之后，对记录加 X锁或隐式锁都会被阻塞。查询或者加 S锁的查询会成功。

UNLOCK TABLES;

通过前面的举例，这里用表格来个总结

兼容性IXISXSIX兼容兼容不兼容不兼容IS兼容兼容不兼容兼容X不兼容不兼容不兼容不兼容S不兼容兼容不兼容兼容

总结：

请尽量避免在使用 InnoDB存储引擎的表上使用 LOCK TABLES这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。

6.2.2 InnoDB中的表级锁——MDL（metadata lock)（举例演示）

MySQL 5.5版本中引入了 MDL，在对某个表执行一些诸如 ALTER TABLE、 DROP TABLE这类的 DDL语句时，其他事务对这个表并发执行诸如 SELECT、 INSERT、 DELETE、 UPDATE的语句会发生阻塞。这个过程其实是通过在 server层使用一种称之为元数据锁（ Metadata Locks，简称 MDL）来实现的， MDL不需要显式使用，在访问一个表的时候会被自动加上。

• 当对一个表执行增删改查操作（ DML语句）时，会自动加上 MDL读锁。
• 当对一个表执行结构变更操作（ DDL语句）时，会自动加上 MDL写锁。
• MDL读锁之间不互斥，允许多个线程同时对一张表进行增删改查操作。
• MDL读写锁之间和写锁之间是互斥的，以确保表结构变更操作的安全性。

总结：当我们思考 DDL和 DML之间锁的关系的时候，就需要往 MDL锁的方向思考。

同理，某个事务中对某个表执行 SELECT、 INSERT、 DELETE、 UPDATE语句时，在其他会话中对这个表执行 DDL语句也会发生阻塞。

注意： 事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

虽然 MDL锁是系统默认会加的，但不能忽略一个问题， 给一个表加个字段，导致整个库挂了，之前的同事就出现过这个问题，这里分析一下原因

在这里，我用 localhost、 localhost2、 localhost3这 3个不同连接作为会话 A、会话 B、会话 C来演示

先建表，添加数据

CREATE TABLE test1 (
    id INT,
    name VARCHAR(100)
) Engine=InnoDB CHARSET=utf8;

insert into test1 values(1, '张三');
insert into test1 values(2, '张三2');
insert into test1 values(3, '张三3');
insert into test1 values(4, '张三4');

第一步， sessionA开启事务，进行查询，这个没什么问题，执行查询操作，加 MDL读锁，执行完并没有释放

BEGIN;
select * from test1;

第2步， sessionB给表添加一列字段，尝试拿 MDL写锁，会发现阻塞了

ALTER TABLE test1
add column sex varchar(2);

此时双击点开数据库也发现卡死了

第 3步， sessionC查询，仍然被阻塞，第 2步的加 MDL写锁还在阻塞，后续记录的读写锁都会被阻塞，此时相关的接口都会响应超时。

如果这个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session再请求的话，这个库的线程很快就会爆满。

这种情况等待 sessionA的事务执行完就会自动释放锁，后续操作会正常执行，但是如果 sessionA的事务很长，可能会等待很久。如果此时 sessionA的事务进行 update操作，会有 Deadlock found when trying to get lock; try restarting transaction提示，这表示 MySQL在尝试获取锁时遇到了死锁。当死锁发生时，数据库系统会选择一个事务作为"牺牲者"并终止它，以解除死锁并让其他事务继续执行。所以 MDL锁的影响就是可能会等待很久，但是一般都不会真正造成死锁，只需要等待事务执行完毕释放 MDL锁即可，后面流程正常执行。

6.2.2 InnoDB中特殊的表级锁——AUTO-INC锁

我们可以为表的某个列添加 AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值。我们把上面演示锁表的 t表拿下来说明：

CREATE TABLE t (
  id int NOT NULL AUTO_INCREMENT,
  c varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行 insert语句

INSERT INTO t(c) VALUES('aa'), ('bb');

由于这个表的 id字段声明了 AUTO_INCREMENT，系统会自动为它赋上递增的值。

系统实现这种自动给 AUTO_INCREMENT修饰的列递增赋值的原理主要是两个：

需要注意的是，这个 AUTO-INC锁的作用范围只是单个插入语句，在插入语句执行完成后，这个锁就被释放了。这与前面介绍的锁在结束时释放时不一样的。

InnoDB中的 innodb_autoinc_lock_mode系统变量，它可以控制为 AUTO_INCREMENT修饰的列分配值时使用的锁机制。 innodb_autoinc_lock_mode可以设置为 0、 1或 2，以控制 InnoDB为 AUTO_INCREMENT修饰的列分配值时所采用的锁机制。

• 值为 0：一律采用 AUTO-INC锁。适用于插入语句中无法预先确定要插入多少条记录的场景。
• 值为 1：混合锁模式。在插入记录数量确定时采用轻量级锁，不确定时使用 AUTO-INC锁。
• 值为 2（默认值）：一律采用轻量级锁。适用于插入记录数量确定的场景。

注意：当 innodb_autoinc_lock_mode值为 2时，可能会导致不同事务中的插入语句为 AUTO_INCREMENT修饰的列生成的值是交叉的。在有主从复制的场景中，这可能导致数据不一致，因此被认为是不安全的。在这种情况下，建议将 innodb_autoinc_lock_mode设置为 1，以便在必要时使用 AUTO-INC锁来确保数据的一致性。

对于主从复制的场景，举个更具体的例子来说明当 innodb_autoinc_lock_mode设置为 2时，可能导致的数据不一致问题。

首先，假设我们有一个具有 AUTO_INCREMENT主键的表：

CREATE TABLE users (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设我们的 innodb_autoinc_lock_mode设置为 2（轻量级锁），我们在主数据库上有两个并发事务：

事务 T1（主数据库）:

START TRANSACTION;
INSERT INTO users(name) VALUES('Alice');

事务 T2（主数据库）:

START TRANSACTION;
INSERT INTO users(name) VALUES('Bob');

此时，假设事务 T2先于事务 T1提交，然后将更改同步到从数据库：

事务 T2（主数据库）:

COMMIT;

事务 T1（主数据库）:

COMMIT;

在此示例中，由于轻量级锁的使用，虽然 T1事务先于 T2事务开始，但 'Alice'和 'Bob'获得的 AUTO_INCREMENT值是交叉的。在主数据库上， Alice的 id为 1， Bob的 id为 2。

现在，当这些更改被同步到从数据库时，可能会发生以下情况：

从数据库：

INSERT INTO users(id, name) VALUES(2, 'Bob');

INSERT INTO users(id, name) VALUES(1, 'Alice');

在这种情况下，虽然在主数据库中， Alice的 id值为 1， Bob的 id值为 2，但在从数据库中，由于事务的提交顺序，会导致数据不一致，这可能会导致从数据库中的数据与主数据库中的数据不一致。

如果我们将 innodb_autoinc_lock_mode设置为 1（混合模式），在这种情况下， InnoDB会在需要时使用 AUTO-INC锁，从而确保分配的 AUTO_INCREMENT值是连续的，避免了交叉值问题。这样，无论事务提交的顺序如何，从数据库中的数据都将与主数据库保持一致。

总结：

• S（共享）锁、X（排他）锁、IS（意向共享）锁、IX（意向排他）锁：这些是InnoDB存储引擎的表锁。
• AUTO-INC锁：一种特殊类型的表锁，用于保护表中的AUTO_INCREMENT列。
• MDL锁（Metadata Locks）：这是MySQL服务器层面上的表锁，它的目的是防止多个会话在操作表的元数据（如表结构）时发生冲突。MDL锁主要用于DDL操作（如ALTER TABLE、CREATE INDEX等），但也会在DML操作（如SELECT、INSERT、UPDATE和DELETE）中使用。

7. 语句加锁分析——建表语句

这里还是用 3.2节说过的例子， 记住这些语句，后面小节全部围绕这些记录展开讲解。

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

ALTER TABLE hero ADD INDEX idx_name (name);

8. 普通的SELECT语句（RR隔离级别为什么不能完全禁止幻读？上例子）

普通的 SELECT 语句在：

• READ UNCOMMITTED 隔离级别下，不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。
• READ COMMITTED 隔离级别下，不加锁，在一个事务中每次执行普通的 SELECT 语句时都会生成一 个 ReadView ，这样解决了脏读问题，但没有解决不可重复读和幻读问题。
• REPEATABLE READ 隔离级别下，不加锁，在一个事务中第一次执行普通的 SELECT 语句时生成一 个 ReadView ，这样把脏读、不可重复读问题解决了， 但是幻读却没法完全禁止。

分析下， ** RR 隔离级别为什么不能完全禁止幻读？**

举个例子

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE number = 30;
Empty set (0.01 sec)

mysql> UPDATE hero SET country = '蜀' WHERE number = 30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM hero WHERE number = 30;
+
| number | name | country |
+
| 30 | g关羽 | 蜀 |
+
1 row in set (0.01 sec)

如对 ReadView不了解见这里ReadView， trx_id记录着这条记录被哪个事务修改过。

• SERIALIZABLE 隔离级别下，需要分为两种情况讨论：
  • 在系统变量 autocommit=0 时，也就是禁用自动提交时，普通的 SELECT 语句会被转为 SELECT ... LOCK IN SHARE MODE 这样的语句，也就是在读取记录前需要先获得记录的 S锁 ，具体的加锁情况和 REPEATABLE READ 隔离级别下一样，我们后边再分析。
  • 在系统变量 autocommit=1 时，也就是启用自动提交时，普通的 SELECT 语句并不加锁，只是利用 MVCC 来生成一个 ReadView 去读取记录。 为啥不加锁呢？因为启用自动提交意味着一个事务中只包含一条语句，一条语句也就没有啥不可重复读、幻读这样的问题了。

9. 各种语句加锁分析

我们把下边四种语句放到一起讨论：

• 语句一： SELECT ... LOCK IN SHARE MODE;
• 语句二： SELECT ... FOR UPDATE;
• 语句三： UPDATE ...
• 语句四： DELETE ...

语句一和语句二是 MySQL中规定的两种锁定读的语法格式，而语句三和语句四由于在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读 。

9.1 READ UNCOMMITTED、READ COMMITTED隔离级别下

在 READ UNCOMMITTED 下语句的加锁方式和 READ COMMITTED 隔离级别下语句的加锁方式基本一致，所以就放到一块儿说了。

9.1.1 对于使用主键进行等值查询的情况

• *使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

这个语句执行时只需要访问一下聚簇索引中 number 值为 8 的记录，所以只需要给它加一个 S锁就好了，如图所示：

select加锁查询(不管是 S还是 X锁)，只有当事务提交的时候锁才会释放。

SELECT ... LOCK IN SHARE MODE语句在 MySQL中表示一个共享锁，也就是读锁。它允许事务读取一行数据，但不允许其他事务对其进行写操作。然而其他事务仍然可以读取这行数据。举个例子：

事务 A：

BEGIN;
SELECT * FROM hero WHERE number = 1 LOCK IN SHARE MODE;

在事务 A中，我们对 number = 1的行加了读锁。然后，在事务 B中：

事务 B：

BEGIN;
SELECT * FROM hero WHERE number = 1 LOCK IN SHARE MODE;

事务 B也可以读取这行数据，因为共享锁允许多个事务同时读取。但是，如果我们在事务 C中尝试更新这行数据：

事务 C：

BEGIN;
UPDATE hero SET name = 'New Name' WHERE number = 1;

事务 C会被阻塞，直到事务 A和 B完成并释放他们的共享锁。

注意：如果使用的是唯一索引或主键索引，并且查询条件是等值匹配，那么 InnoDB 先检查条件，如果满足才加锁。 假设有多条 number=1的记录，会先判断 number等于 1才会去加锁，而不是先加锁再去判断是否等于 1。

• *使用 SELECT ... FOR UPDATE 来为记录加锁

SELECT * FROM hero WHERE number = 8 FOR UPDATE;

这个语句执行时只需要访问一下聚簇索引中 number 值为 8 的记录，所以只需要给它加一个 X锁就行了。

为了区分 S锁和 X锁，我们之后在示意图中就把加了 S锁的记录染成蓝色，把加了 X锁的记录染成紫色。

总结： SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE 的查询在主键查询时只需要为相应的聚簇索引记录加上 S锁或 X锁。

• *使用 UPDATE ... 来为记录加锁

UPDATE hero SET country = '汉' WHERE number = 8;

这条 UPDATE 语句并没有更新二级索引列，加锁方式和上边所说的 SELECT ... FOR UPDATE语句一致，聚簇索引加上 X锁后再更新就好了。

如果 UPDATE 语句中更新了二级索引列

UPDATE hero SET name = 'cao曹操' WHERE number = 8;

加锁的步骤是：

如图：

之前为了区分事务 id才把主键 id命名为 number，这里知道 number就是主键即可

总结：在 UPDATE ... 操作中，如果更新了二级索引列，则会先为聚簇索引记录加上 X锁，然后为对应的二级索引记录加上 X锁，并更新这些记录。

• *使用 DELETE ... 来为记录加锁

DELETE FROM hero WHERE number = 8;

" DELETE表中的一条记录"意味着对聚簇索引和所有的二级索引中对应的记录做 DELETE 操作，本例子中就是要先把 number 值为 8 的聚簇索引记录执行 DELETE 操作，然后把对应的二级索引记录删除，所以加锁的步骤和上边更新带有二级索引列的 UPDATE 语句一致，就不画图了。

总结： DELETE ... 操作会先删除聚簇索引记录，然后删除所有对应的二级索引记录，所以加锁的步骤与更新带有二级索引列的 UPDATE 语句一致。

9.1.2 对于使用主键进行范围查询的情况

• *使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE number  8 LOCK IN SHARE MODE;

这个语句的执行过程有点复杂。

对于聚簇索引而言不需要回表，它本身就包含着全部的列，也起不到减少 IO 操作的作用。在本例中搜索条件是 number ，而 number 列又是聚簇索引列，所以本例中不需要判断该记录是否符合二级索引的条件，后面讲二级索引的例子时会用到这一步。

ICP (Index Condition Pushdown) 索引下推是 MySQL 5.6及以后版本引入的一个优化特性，可以在存储引擎层面就过滤掉不满足 where索引条件的记录，从而减少回表的可能，而判断非索引条件主要是由 server层的执行器来完成的。

每找到一条符合条件的记录都会返回 server层，而不是等找完所有符合条件的记录才返回。

这个原因我在另一篇文章讲过 一条SQL如何被MySQL架构中的各个组件操作执行的？

存储引擎根据索引条件加载到内存的数据页(16KB)有多数据，可能有不满足索引条件的数据，如果执行器不再次进行索引和非索引条件判断，则无法判断哪些记录是满足条件的，虽然在存储引擎判断过索引条件，但是在 server层的执行器还是会判断所有条件进行过滤。

上述步骤是在 MySQL 5.7.21这个版本中验证的，不保证其他版本有无出入

这个过程有个问题，就是当找到 number 值为 8 的那条记录的时候，还得向后找一条记录（也就是 number 值为 15 的记录），在存储引擎读取这条记录的时候，也就是上述的第 1 步中，就得为这条记录加锁，然后在第 3步时，判断该记录不符合 number 这个条件，又要释放掉这条记录的锁，这个过程导致 number 值为 15 的记录先被加锁，然后把锁释放掉，过程就是这样：

第 1步一定加锁，第 3步根据判断条件，不满足了才解锁。

经过我的测试，对于这个例子：

• 在 MySQL 5.7中，不管是什么隔离级别，在 server层可以返回给客户端的满足条件的记录，都是加了 S锁的记录，如果开启一个新事务对这些记录 update修改并提交，语句虽然执行成功，但是记录并没有更新为新的值。如果不开启事务直接 update修改，会被阻塞等待。那就可以理解为，在当前事务提交之前，这些返回给客户端的记录无法被修改。
• 在 MySQL8.0中，不管是什么隔离级别，在 server层可以返回给客户端的满足条件的记录，都是加了 S锁的记录，不管是开启一个新事务对这些记录 update修改并提交，还是不开启事务直接 update修改，都会被阻塞等待。 *和 MySQL 5.7 是同样的结论，在当前事务提交之前，这些返回给客户端的记录无法被修改。

我们在这个步骤的第3步讨论的， InnoDB规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件。这是因为在大多数情况下，将锁定和条件检查两步分开来进行会更高效，因为这样可以尽早地释放不需要的锁，减少了资源的占用。

举个例子：

SELECT * FROM t1 WHERE key1 = 10 AND key2 = 20 FOR UPDATE;

假设 key1 是一个索引。 InnoDB 首先会找到所有 key1 = 10 的记录，并对它们加锁。然后，它会检查这些记录是否满足 key2 = 20 这个条件。如果一个记录不满足，那么 InnoDB 会立即释放对这个记录的锁，而不需要等到事务结束。这种策略可以有效地减少锁的数量和持有时间，从而提高并发性能。但是这种策略并不能在所有情况下都提高性能。如果大部分记录都满足查询条件，那么这种策略可能会导致更多的锁操作，从而降低性能。因此，这是一种权衡，并且需要根据具体的工作负载和数据分布来调整。

如果你先在事务 T1 中执行：

BEGIN;
SELECT * FROM hero WHERE number  8 LOCK IN SHARE MODE;
...

然后再到事务 T2 中执行：

BEGIN;
SELECT * FROM hero WHERE number = 15 FOR UPDATE;
...

这是没有问题的，因为在 T2 执行时，事务 T1 已经释放掉了 number 值为 15 的记录的锁，但是 如果你先执行 T2 ，再执行 T1 ，由于 T2 已经持有了 number 值为 15 的记录的锁，事务 T1 将因为获取不到这个锁而等待。

再看一个使用主键进行范围查询的例子

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

这个语句的执行过程其实和我们举的上一个例子类似。根据第 1步先到聚簇索引中定位到满足 number >= 8 这个条件的第一条记录，也就是 number 值为 8 的记录，沿着由记录组成的单向链表一路向后找，每找到一条记录，就会先为其加上锁，然后根据第 3步判断该记录符不符合范围查询的边界条件，最后把这条记录返回给 server层 ，根据第 4 步 server层再判断 number >= 8 这个条件是否成立，如果成立的话就发送给客户端，否则的话就结束查询。最后 InnoDB 存储引擎找到索引中的 Supremum 伪记录之后，在存储引擎内部就可以立即判断这是一条伪记录，不必要返回给 server层处理，也没必要给它也加上锁（也就是说在第 1步中就压根儿没给这条记录加锁）。整个过程会给 number 值为 8 、 15 、 20 这三条记录加上 S锁，如下图

如果在事务 A中执行了这个的查询语句

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

然后在事务 B中尝试读取或写入被锁定的行，例如：

事务 B：

BEGIN;
SELECT * FROM hero WHERE number = 15;

或

BEGIN;
UPDATE hero SET name = 'Another Name' WHERE number = 15;

那么，事务 B可以正常执行查询操作，因为它只需要读取数据，而不需要修改数据。然而事务 B的更新操作会被阻塞(尝试获取 number=15这行记录的 X锁)，因为它试图修改已经被事务 A锁定的行（ number=15的这行记录已经加上了 S锁）。

普通的 SELECT 查询不会被阻塞，即便该记录持有 X 锁。 除了 SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE语句，其他的 SELECT语句都属于普通查询。比如 SELECT * FROM hero WHERE number = 15;

注意：

假设更新和查询的是表中不存在的数据，比如表中不存在 number=10的记录

事务 B：

BEGIN;
SELECT * FROM hero WHERE number = 10;

或

BEGIN;
UPDATE hero SET name = 'Another Name' WHERE number = 10;

这种情况事务 B对 number为 10的查询和更新操作都不会被阻塞，虽然更新操作需要获取 X锁，但如果没有找到需要更新的记录，那么就不会试图获取 X锁， UPDATE操作可以立即成功。

有人可能会疑问，在 RR隔离级别下， SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 会使用间隙锁（ gap locks），同样是执行这些例子，事务 B更新 number=10的记录，会被阻塞吗？

答案也是不会。间隙锁是 InnoDB用来防止其它事务在这个"间隙"中插入新的行，这里没有需要插入的行，事务 B只是要找到对应记录加上 X锁，别搞错了。

• *使用 SELECT ... FOR UPDATE 语句来为记录加锁：

和 SELECT ... LOCK IN SHARE MODE语句类似，只不过加的是 X锁 。

• *使用 UPDATE ... 来为记录加锁

UPDATE hero SET country = '汉' WHERE number >= 8;

这条 UPDATE 语句并没有更新二级索引列，因为 country不是索引列，加锁方式和上边所说的 SELECT ... FOR UPDATE语句一致，对应的聚簇索引加上 X锁后再更新即可。

如果 UPDATE 语句中更新了二级索引列

UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

这时候会 首先更新聚簇索引记录，再更新对应的二级索引记录，所以加锁的步骤就是：

无论这些行是否最终被更新或删除，其他事务不能读取或写入这些行，直到当前事务完成。

图示如下

凡是查询条件是主键或者是聚簇索引其他列，那么先锁聚簇索引，再锁二级索引，如果查询条件是二级索引列，那么锁的顺序正好相反，后面会讲到。

如果是下边这个语句：

UPDATE hero SET country = '汉' WHERE number  8;

则会对 number 值为 1 、 3 、 8 聚簇索引记录以及它们对应的二级索引记录加 X锁 ，加锁顺序和上边语句中的加锁顺序类似，都是先对一条聚簇索引记录加锁后，再给对应的二级索引记录加锁。之后会继续对 number 值为 15 的 聚簇索引记录加锁，但是随后 InnoDB 存储引擎判断它不符合边界条件，随即会释放掉该聚簇索引记录上的锁（注意这个过程中 没有对 number 值为 15 的聚簇索引记录对应的二级索引记录加锁）。

和刚刚第 3步类似，给聚簇索引加锁之后判断范围查询的边界条件，如果不满足就直接释放锁了，告诉 server层查询结束，不会再去对应的二级索引加锁。

• *使用 DELETE ... 来为记录加锁

DELETE FROM hero WHERE number >= 8;

和

DELETE FROM hero WHERE number  8;

这两个语句的加锁情况和 更新带有二级索引列的 UPDATE 语句一致，删除聚簇索引也会删除二级索引，就不画图了。

9.1.3 对于使用（唯一）二级索引进行等值查询的情况

在 READ UNCOMMITTED和 READ COMMITTED隔离级别下，使用 普通的二级索引和唯一二级索引进行加锁的过程是一样的，就放在一起讲。

• *使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

这个语句的执行过程是先通过二级索引 idx_name 定位到满足 name = 'c曹操' 条件的二级索引记录，然后进行回表操作。所以这个语句的加锁顺序：

idx_name是一个普通的二级索引，到 idx_name索引中定位到满足 name= 'c曹操'这个条件的第一条记录后，就可以沿着这条记录一路向后找。可是从我们上边的描述中可以看出来，并没有对下一条二级索引记录进行加锁。

总结：对于 SELECT ... LOCK IN SHARE MODE 查询，它会先通过二级索引找到满足条件的记录，然后对这些记录加 S锁，再回表找到对应的聚簇索引记录并对它们加 S锁。对于等值查询， InnoDB存储引擎会先判断下一条记录是否满足查询条件，如果不满足就不加锁并返回，否则就加锁。

注意等值匹配是先判断再锁，不锁多余的，而范围条件是先锁再判断，不满足就释放。

来看一个发生死锁的例子，还是利用上面的查询例子

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

UPDATE hero SET name = '曹操' WHERE number = 8;

在事务 T1、 T2中运行上面的语句，如注释。这两个语句都是要对 number 值为 8 的聚簇索引记录和对应的二级索引记录加锁，不同的是加锁的顺序不一样。这个 UPDATE 语句是先对聚簇索引记录进行加 X锁，后对二级索引记录进行加 X锁，而 SELECT语句加 S锁顺序正好相反，如果在不同事务中运行上述两个语句，则可能出现

• 事务 T1 持有了二级索引记录的 S锁，在等待对应聚簇索引记录上的 X锁释放。
• 事务 T2 持有了聚簇索引记录的 X锁，在等待对应二级索引记录上的 S锁释放。

两个事务都在等待对方释放锁，发生了死锁，两个事务都无法运行下去，必须选择一个进行回滚，对性能影响比较大。

总结：如果两个事务分别执行 SELECT ... LOCK IN SHARE MODE 和 UPDATE ... 语句，由于它们加锁的顺序不同，可能会导致死锁。

• *使用 SELECT ... FOR UPDATE 语句时

SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

这种情况与 SELECT ... LOCK IN SHARE MODE 语句的加锁情况类似，分别给访问到的二级索引记录和对应的聚簇索引记录加锁，只不过加的是 X锁。上一个死锁的例子也在这里存在，是 X锁和 X锁的竞争。

• *使用 UPDATE ... 来为记录加锁

与更新二级索引记录的 SELECT ... FOR UPDATE的加锁情况类似，如果被更新的列是二级索引或者二级索引的一部分，那么对应的二级索引记录也会被加 X锁。

• *使用 DELETE ... 来为记录加锁

与 SELECT ... FOR UPDATE的加锁情况类似，如果被更新的列是二级索引或者二级索引的一部分，那么对应的二级索引记录也会被加 X锁。

9.1.4 对于使用（唯一）二级索引进行范围查询的情况

• 使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;

因为优化器会计算使用二级索引进行查询的成本，在成本较大时可能选择以全表扫描的方式来执行查询，所以我们这里使用 FORCE INDEX(idx_name)来强制使用二级索引 idx_name来执行查询。

这个语句的执行过程其实是先到二级索引中定位到满足 name >= 'c曹操' 的第一条记录，也就是 name 值为 c曹操 的记录，然后就可以沿着这条记录的链表一路向后找，从二级索引 idx_name 的示意图中可以看出，后面所有的记录都满足 name >= 'c曹操' 的这个条件，所以所有的二级索引记录都会被加 S锁 ，它们对应的聚簇索引记录也会被加 S锁 。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它相应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推。如下图

再来看下边这个语句：

SELECT * FROM hero FORCE INDEX(idx_name) WHERE name  'c曹操' LOCK IN SHARE MODE;

前边说 SELECT * FROM hero WHERE number 这个例子的时候，在使用 number 这个条件的语句中，需要把 number 值为 15 的记录也加一个锁，之后又判断它不符合边界条件而把锁释放掉。而对于查询条件 name 的语句来说，执行该语句需要使用到二级索引。

InnoDB规定

• 如果一条记录不符合二级索引中的条件(包括索引下推条件 ICP)，且这条记录不是最后一条记录的话，则跳到下一条记录继续判断。

比如条件变为 name IN ('c曹操', 's孙权')，遇到 'l刘备'记录不满足条件，还会继续跳到下一条记录判断

• 如果这已经是最后一条记录，那么直接向 server层报告查询完毕。

但是这里有个问题：先对一条记录加了锁，然后再判断该记录是不是符合二级索引的条件（包括 ICP），如果不符合直接跳到下一条记录或者直接向 server层报告查询完毕，这个过程中并没有把那条被加锁的记录上的锁释放掉！！！本例中使用的查询条件是 name ，在为 name 值为 'c曹操' 的二级索引记录以及它对应的聚簇索引加锁返回server层之后，会接着判断二级索引中的下一条记录，也就是 name 值为 'l刘备' 的那条二级索引记录，由于该记录不符合二级索引的条件，而且是范围查询的最后一条记录，会直接向 server层报告查询完毕，这个过程并不会释放 name 值为 'l刘备' 的二级索引记录上的锁，也就导致了语句执行完毕时的加锁情况如下所示

这会出现什么问题呢？举个例子
假如 T1 执行了上述语句并且尚未提交('l刘备'的二级索引记录上锁了)， T2 再执行这个语句：

SELECT * FROM hero WHERE name = 'l刘备' FOR UPDATE;

T2中的语句需要对 name 值为 'l刘备'的二级索引记录加 X锁 ，而 T1中仍然持有 name 值为 'l刘备'的二级索引记录上的 S锁 ，这就造成了 T2获取不到锁而进入等待状态。如果后面 T1这个事务还有对 'l刘备'的记录进行查询的语句，那么会造成死锁。

• 使用 SELECT ... FOR UPDATE语句时：

和 SELECT ... LOCK IN SHARE MODE语句类似，只不过加的是 X锁 。

• 使用 UPDATE ... 来为记录加锁

UPDATE hero SET country = '汉' WHERE name >= 'c曹操';

假设该语句执行时使用了 idx_name二级索引来进行锁定读 ，那么它的加锁方式和上边所说的 SELECT ... FOR UPDATE语句一样，先二级索引加锁再聚簇索引加锁。如果有其他二级索引列也被更新，那么也会为对应的二级索引记录进行加锁，就不赘述了。

还有一种情况

UPDATE hero SET country = '汉' WHERE name  'c曹操';

我们前边说 根据索引条件无需回表的情况只适用于 SELECT 语句，也就是说 ** UPDATE 语句过程中的锁定读都会进行回表（更新需要聚簇索引和二级索引对应的记录都更新）**，那么这个语句就会为 name 值为 'c曹操' 和 'l刘备' 的二级索引记录以及它们对应的聚簇索引进行加锁，之后在判断边界条件时发现 name 值为 'l刘备' 的二级索引记录不符合 name 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示：

这里把最后一条满足条件记录的下一条记录称为临界记录， 这里对范围查询最后一步临界记录的索引加锁问题进行总结：

• 使用 DELETE ... 来为记录加锁

DELETE FROM hero WHERE name >= 'c曹操';

和

DELETE FROM hero WHERE name  'c曹操';

这两个语句采用二级索引来进行锁定读 ，那么它们的加锁情况和更新带有二级索引列的 UPDATE 语句一致，就不画图了。

9.1.5 全表扫描的情况

首先确保你知道什么是全表扫描，全表扫描的数据和聚集索引的数据在 InnoDB表空间中的存储位置是相同的，也就是说它们的内存地址也是相同的，全表扫描是在聚集索引的叶子结点从左到右依次扫描。这一点我在这里说过==> 一条SQL如何被MySQL架构中的各个组件操作执行的？

• 使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;

由于 country 列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个 S锁 ，然后返回给 server层 ，如果 server层判断 country = '魏' 这个条件是否成立，如果成立则将其发送给客户端，否则会释放掉该记录上的锁。

• 使用 SELECT ... FOR UPDATE给记录加锁

加锁的情况与 SELECT ... LOCK IN SHARE MODE类似，只不过加的是 X锁 ，就不赘述了。

• 对于 UPDATE ... 和 DELETE ... 的语句（更新和删除要同时处理聚簇索引和二级索引）

在遍历聚簇索引中的记录，都会为该聚簇索引记录加上 X锁 ，然后：

本例子只有一个二级索引列 name，假如有多个二级索引列 name、 phone、 user_id。

当在执行 DELETE操作时， InnoDB将对满足删除条件的聚簇索引记录以及对应的所有二级索引记录（例如 name、 phone、 user_id等）加上 X锁，并删除这些记录。

而在执行 UPDATE操作时，只有那些将被更新的二级索引列（例如如果只更新 name列，那么只有 name索引中满足条件的记录）的记录会被加锁，而其它的二级索引列（例如 phone、 user_id等）则不会被加锁。

9.2 REPEATABLE READ隔离级别下

请先阅读 READ UNCOMMITTED、 READ COMMITTED隔离级别下的例子，因为不会赘述太多相同的东西。

9.2.1 对于使用主键进行等值查询的情况

• 使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

主键具有唯一性，在一个事务中下次再执行这个查询语句的时候肯定不会有别的事务插入 number 值为 8的记录，所以这种情况下和 READ UNCOMMITTED、READ COMMITTED 隔离级别下一样，我们只需要为这条 number 值为 8的记录加一个 S锁就好了，如图所示：

如果我们查询了查询的主键值不存在，比如

SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;

由于 number 值为 7 的记录不存在，为了尽可能禁止幻读现象，在当前事务提交前还需要防止别的事务插入 number 值为 7 的新记录，所以需要在 (3, 8) 的间隙加一个 gap锁 ，也就是不允许别的事务插入 number 值在 (3, 8) 这个区间的新记录。如下图

总结：如果查询的主键值不存在，为了尽可能防止幻读现象， MySQL 将在目标查询位置之前的存在的记录和之后的存在的记录之间的间隙加上间隙锁。

如果在 READ UNCOMMITTED、READ COMMITTED 隔离级别下一样查询了一条主键值不存在的记录，那么什么锁也不需要加，因为在 READ UNCOMMITTED、READ COMMITTED 隔离级别下允许幻读现象的存在。

其他的查询语句（如 UPDATE, DELETE 等）在使用主键进行等值查询时，锁定行为与 READ UNCOMMITTED 和 READ COMMITTED 隔离级别是类似的，这里就不赘述了。

9.2.2 对于使用主键进行范围查询的情况

• 使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

因为要解决幻读问题，所以禁止别的事务插入 number 值符合 number >= 8 的记录，又因为主键本身就是唯一的，所以我们不用担心在 number 值为 8 的前边有新记录插入，只需要保证不要让新记录插入到 number 值为 8 的后边就好了，所以需要：

为什么这里把 Supremum 伪记录也加上 next-key锁？其实只加 gap锁就已经满足要求了，这么做是因为 InnoDB对 Supremum记录上加 next-key锁时就是当作 gap锁看待的，只不过为了节省锁结构（我们前边说锁的类型不一样的话不能被放到一个锁结构中）才这么做的而已。

再举个特殊点的例子

SELECT * FROM hero WHERE number  10 LOCK IN SHARE MODE;

在加锁时会把 number 值为 1、3、8、15 这四条记录都加上 S型 next-key 锁，不过之后 server层判断 number 值为 15 的记录不满足 number 条件后，这条临界记录(number 值为 15 的记录)与 READ UNCOMMITTED/READ COMMITTED 隔离级别下的处理方式不同， REPEATABLE READ 隔离级别下并不会把锁释放掉，让我们来看看不释放这个锁的原因

所以现在的加锁的图示就是这样

这样如果别的事务想要插入的新记录的 number 值在 (-∞, 1) 、(1, 3) 、(3, 8) 、(8, 15) 之间的话，是会进入等待状态的。

注意：在 REPEATABLE READ隔离级别下，如果查询的范围内有主键值不存在， MySQL 仍然会在不满足临界值的第一条记录上加上 S型 next-key 锁，以防止幻读现象。即使范围的边界值（如例子中的 number = 15 的记录）不满足查询条件， MySQL 也不会释放对它的锁。这样做的原因是，如果当前事务没有对 number = 15的记录加锁，那么在当前事务结束之前，其他事务可能会插入一个 number值在 8和 15之间的新记录（比如主键 number = 9的记录）。这样，如果当前事务再次执行相同的查询，会发现多了一条 number = 9新记录，也就是发生了幻读现象。为了防止这种情况发生， MySQL会在满足查询条件的最大值之后的第一条记录（即 number = 15）上加上 S型 next-key锁，直到当前事务结束。

总结：使用 SELECT ... LOCK IN SHARE MODE 进行范围查询时， MySQL 将满足条件的记录和大于查询值的第一条记录（包括 Supremum 伪记录，但是没有 Infimum记录）加上 S型 next-key 锁。这样的锁定行为可以防止其他事务在该范围内插入新的记录。没有 Infimum记录是因为 next-key锁是加在下一条记录的，这里 Infimum记录的下一条记录是 number=1的记录。

• 使用 SELECT ... FOR UPDATE语句时

和 SELECT ... LOCK IN SHARE MODE语句类似，只不过需要将上边提到的 S型 next-key锁替换成 X型 next-key锁 。

• 使用 UPDATE ... 来为记录加锁

如果 UPDATE 语句没有更新二级索引列

UPDATE hero SET country = '汉' WHERE number >= 8;

加锁方式和上边所说的 SELECT ... LOCK IN SHARE MODE语句一致。

如果 UPDATE 语句中更新了二级索引列

UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

对聚簇索引加锁的情况和 SELECT ... FOR UPDATE一致，对 number 值为 8 的记录加 X锁 ，对 number 值 15、 20 的记录以及 Supremum 记录加 X型 next-key锁 。但是也会对 number 值为 8、15、20的 二级索引记录加 X锁。加锁顺序是先聚簇索引的一条记录加 next-key锁，再到二级索引对应记录加 X锁，再到下一条聚簇索引，以此类推。

如果是下边这个语句

UPDATE hero SET country = '汉' WHERE number  8;

则会对 number 值为 1、3、8、15的聚簇索引记录加 X型 next-key ，但是由于 number 值为 15 的聚簇索引记录不满足 number 的条件，虽然这条记录在 REPEATABLE READ 隔离级别下不会将它的锁释放掉，但是也并不会对这条聚簇索引记录对应的二级索引记录加锁，也就是说只会为二级索引记录的 number 值为 1、3、8 的记录加X锁。

• 使用 DELETE ... 来为记录加锁

DELETE FROM hero WHERE number >= 8;

和

DELETE FROM hero WHERE number  8;

这两个语句的加锁情况和更新带有二级索引列的 UPDATE 语句一致，就不画图了。

9.2.3 对于使用（唯一）二级索引进行等值查询的情况

由于 hero 表并没有唯一二级索引，我们把原先的 idx_name 修改为唯一二级索引。

ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);

• 使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

由于唯一二级索引具有唯一性，在一个事务中下次再执行这个查询语句的时候肯定不会有别的事务插入多条 name 值为 'c曹操' 的记录，所以这种情况下和 READ UNCOMMITTED、READ COMMITTED 隔离级别下一样，我们只需要为这条 name 值为 'c曹操' 的二级索引记录加一个 S锁 ，然后再为它对应的聚簇索引记录加一个 S锁就好了，其实和 READ UNCOMMITTED、READ COMMITTED 隔离级别下加锁方式是一样的，如图：

注意是先对二级索引记录加锁，再对聚簇索引加锁。

前面说了，如果查询条件不是二级索引，那么先锁聚簇索引，再锁二级索引，如果查询条件是二级索引列，那么先锁二级索引，再锁聚簇索引。

如果对唯一二级索引等值查询的值并不存在，比如

SELECT * FROM hero WHERE name = 'g关羽' LOCK IN SHARE MODE;

在唯一二级索引 uk_name 中，键值比 'g关羽' 大的第一条记录的键值为 'l刘备' ，所以需要在这条二级索引记录上加一个 gap锁 ，如图所示

这里只对二级索引记录进行加锁，并不会对聚簇索引记录进行加锁。

总结：当使用 SELECT ... LOCK IN SHARE MODE对唯一二级索引进行等值查询时，会先对二级索引记录加 S锁，然后再对对应的聚簇索引记录加 S锁。如果查询的值不存在，只会在大于查询值的第一条二级索引记录之前的一个间隙加 gap锁。

• 使用 SELECT ... FOR UPDATE语句时

SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

这种情况下与 SELECT ... LOCK IN SHARE MODE 语句的加锁情况类似，只是将锁类型换为 X锁。

• 使用 UPDATE ... 来为记录加锁

与 SELECT ... FOR UPDATE的加锁情况类似，但如果表中还有其他二级索引列，则这些对应的二级索引列也会被加锁。

• 使用 DELETE ... 来为记录加锁

与 SELECT ... FOR UPDATE的加锁情况类似，但如果表中还有其他二级索引列，则这些对应的二级索引列也会被加锁。

9.2.4 对于使用（唯一）二级索引进行范围查询的情况

• 使用 SELECT ... LOCK IN SHARE MODE 来为记录加锁

SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN
SHARE MODE;

这个语句的执行过程其实是先到二级索引中定位到满足 name >= 'c曹操' 的第一条记录，也就是 name 值为 'c曹操' 的记录，然后就可以沿着这条记录的链表一路向后找，从二级索引 idx_name 的示意图中可以看出，所有的用户记录都满足 name >= 'c曹操' 的这个条件，所以所有的二级索引记录都会被加 S型 next-key锁 ，它们对应的聚簇索引记录也会被加 S锁 ，二级索引的 Supremum 伪记录也会被加 S型 next-key锁 。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它对应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推，如下图：

再来看下边这个语句

SELECT * FROM hero WHERE name  'c曹操' LOCK IN SHARE MODE;

这个语句先会为 name 值为 'c曹操' 的二级索引记录加 S型 next-key锁，以及它对应的聚簇索引记录加 S锁 。然后还要给下一条 name 值为 'l刘备' 的二级索引加 S型 next-key锁 ， 在前边在说主键范围查找时，为 number 这个条件进行加锁的时候会把 number 值为 15 的记录也加一个锁，之后判断不符合条件后再释放掉，现在换成二级索引就不用为下一条记录加锁了么？是的，这主要是因为我们开启了索引条件下推 ，对于二级索引记录来说，可以先在存储引擎层判断给定条件 name 是否成立，如果不成立就不返回给 server层 了，从而避免了不必要的加锁。

总结：使用 SELECT ... LOCK IN SHARE MODE进行范围查询时，会按照查询范围内的顺序，对每条二级索引记录以及对应的聚簇索引记录加 S锁。如果查询的值不存在， 不会在大于查询值的第一条二级索引记录上加 gap锁。

• 使用 SELECT ... FOR UPDATE语句时

和 SELECT ... LOCK IN SHARE MODE 语句类似，只不过加的是 X锁。

• 使用 UPDATE ... 来为记录加锁

UPDATE hero SET country = '汉' WHERE name >= 'c曹操';

这条 UPDATE 语句并没有更新二级索引列，加锁方式和上边所说的 SELECT ... FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为这些二级索引记录进行加锁，就不赘述了。

UPDATE FORCE INDEX(idx_name)hero SET country = '汉' WHERE name  'c曹操';

我们前边说根据索引条件无需回表的情况只适用于 SELECT 语句，也就是说 UPDATE 语句过程中的锁定读都会进行回表，那么这个语句就会为 name 值为 'c曹操' 和 'l刘备' 的二级索引记录以及它们对应的聚簇索引进行加锁，之后在判断边界条件时发现 name 值为 'l刘备' 的二级索引记录不符合 name 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉，这个和READ UNCOMMITTED、READ COMMITTED隔离级别下是一样的，图示可以往上找READ UNCOMMITTED、READ COMMITTED隔离级别中讲过的例子。

• 使用 DELETE ... 来为记录加锁

DELETE FROM hero WHERE number >= 8;

和

DELETE FROM hero WHERE number  8;

这两个语句的加锁情况和更新带有二级索引列的 UPDATE 语句一致，就不画图了。

10. 各种隔离级别下各种查询情况综合总结

1. 对于等值查询：

• 在 READ UNCOMMITTED 和 READ COMMITTED 隔离级别下：

使用 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE：对于二级索引或聚簇索引查询， MySQL 首先锁定满足查询条件的索引记录，然后锁定相应的聚簇索引或二级索引记录。如果查询条件的记录不存在， MySQL 不会锁定任何记录。

使用 UPDATE 或 DELETE： MySQL 会首先锁定满足查询条件的索引记录，然后锁定相应的聚簇索引或二级索引记录。如果查询条件的记录不存在， MySQL 仍然会锁定满足查询条件的第一条记录。

• 在 REPEATABLE READ 隔离级别下：

使用 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE：对于二级索引或聚簇索引查询， MySQL 首先锁定满足查询条件的索引记录，然后锁定相应的聚簇索引或二级索引记录。如果查询条件的记录不存在， MySQL 会锁定在查询条件之后的第一条记录。

使用 UPDATE 或 DELETE： MySQL 会首先锁定满足查询条件的索引记录，然后锁定相应的聚簇索引或二级索引记录。如果查询条件的记录不存在， MySQL 仍然会锁定满足查询条件的第一条记录。

2. 对于范围查询：

• 在 READ UNCOMMITTED 和 READ COMMITTED 隔离级别下：

使用 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE： MySQL 会锁定在查询范围内的所有记录，并且会锁定范围内的第一条不满足查询条件的记录。

使用 UPDATE 或 DELETE： MySQL 会锁定在查询范围内的所有记录，并且会锁定范围内的第一条不满足查询条件的记录。

• 在 REPEATABLE READ 隔离级别下：

使用 SELECT ... LOCK IN SHARE MODE 或 SELECT ... FOR UPDATE： MySQL 会锁定在查询范围内的所有记录，并且会锁定范围内的第一条不满足查询条件的记录。

使用 UPDATE 或 DELETE： MySQL 会锁定在查询范围内的所有记录，并且会锁定范围内的第一条不满足查询条件的记录。

• 在所有隔离级别和查询类型下

如果查询涉及到二级索引，那么 MySQL总是先锁定二级索引记录，然后锁定对应的聚簇索引记录。这是因为 MySQL在处理查询时，首先需要通过二级索引找到相应的聚簇索引记录。

• 对于锁定的释放

在 READ UNCOMMITTED， READ COMMITTED和 REPEATABLE READ隔离级别下， MySQL在事务提交或回滚时释放所有的锁。然而，对于满足查询条件的下一条记录的锁，如果发现该记录不满足查询条件，那么在 READ COMMITTED和 READ UNCOMMITTED隔离级别下， MySQL会立即释放该锁，而在 REPEATABLE READ隔离级别下， MySQL会在事务结束时释放该锁。

欢迎一键三连~

有问题请留言，大家一起探讨学习

----------------------Talk is cheap, show me the code-----------------------