前一篇MySQL读取的记录和我想象的不一致——事物隔离级别和MVCC

讲了事务在并发执行时可能引发的一致性问题的各种现象。一般分为下面3种情况：

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

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

文章目录

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

• 1.1 如何管理并发事务冲突
• 1.2 MVCC与锁机制

• 2. 行锁

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

• 2.1.1 锁定读的语句

• 2.2 Gap Lock——gap锁
• 2.3 Next-Key Lock——记录锁+gap锁组合
• 2.4 隐式锁

• 3. 一致性读
• 4. 写操作
• 5. 什么是表锁？
• 6. MySQL中的行锁与表锁

• 6.1 其他存储引擎中的锁
• 6.2 InnoDB存储引擎中的锁

• 6.2.1 InnoDB中的表级锁（两个并发事务中的锁表演示）
• 6.2.2 InnoDB中的表级锁——MDL（metadata lock)（举例演示）
• 6.2.2 InnoDB中特殊的表级锁——AUTO-INC锁

• 7. 语句加锁分析——建表语句
• 8. 普通的SELECT语句（RR隔离级别为什么不能完全禁止幻读？上例子）
• 9. 各种语句加锁分析

• 9.1 READ UNCOMMITTED、READ COMMITTED隔离级别下

• 9.1.1 对于使用主键进行等值查询的情况
• 9.1.2 对于使用主键进行范围查询的情况
• 9.1.3 对于使用（唯一）二级索引进行等值查询的情况
• 9.1.4 对于使用（唯一）二级索引进行范围查询的情况
• 9.1.5 全表扫描的情况

• 9.2 REPEATABLE READ隔离级别下

• 9.2.1 对于使用主键进行等值查询的情况
• 9.2.2 对于使用主键进行范围查询的情况
• 9.2.3 对于使用（唯一）二级索引进行等值查询的情况
• 9.2.4 对于使用（唯一）二级索引进行范围查询的情况

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

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隔离级别下很大程度上避免了幻读，但是还是可能出现幻读。

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

1. 利用多版本并发控制（MVCC）配合写操作的锁机制。

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

2. 读、写操作都采用锁机制

  相比于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接着也要访问这条记录：

1. 如果事务T2想要再获取一个记录的S锁，那么事务T2也会获得该锁，也就意味着事务T1和T2在该记录上同时持有S锁。
2. 如果事务T2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务T1提交之后将S锁释放掉。所谓阻塞，就是事务T2已经生成了锁结构，不过锁结构的is_waiting属性为true。

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

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

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

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。

分析：

1. 当事务A执行UPDATE table SET field = 'new value' WHERE id = 1时，InnoDB存储引擎首先需要找到id为1的这条记录，然后会在这条记录上获取一个隐式X锁。
2. 这个隐式X锁会阻止其他事务在事务A完成其操作之前修改或删除这条记录。例如，如果事务B尝试执行UPDATE table SET field = 'another value' WHERE id = 1或DELETE FROM table WHERE id = 1，那么这些操作将会被阻止，因为它们需要在id为1的这条记录上获取自己的X锁，此时事务A的在该记录上加的X锁也生成显式锁结构。
3. 一旦事务A提交或回滚，它就会释放在id为1的这条记录上的X锁。这时如果事务B还在等待获取在这条记录上的X锁，那么它现在就可以获取这个锁，继续执行其操作了。

  隐式锁的生命周期与关联的事务紧密相连。当事务提交或回滚时，其关联的隐式锁也会被自动释放，这是由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数据库使用不同的加锁策略来确保数据的一致性和并发性：

1. INSERT：通常情况下，新插入的记录受到隐式锁的保护，不需要在内存中为其生成对应的锁结构。
2. DELETE：对记录执行DELETE操作时，首先在B+树中定位记录位置，然后获取该记录的排他锁（X锁），最后执行delete mark操作。可以将在B+树中定位记录并获取X锁的过程看作一个锁定读操作。

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

3. UPDATE：更新操作分为以下三种情况：

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

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

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

  假设有一个事务T1：

1. T1获取一条记录的S锁，以便读取该记录。
2. 在T1的后续处理中，发现需要修改该记录。
3. 此时，T1需要将之前获取的S锁升级为X锁以进行修改操作。因此，T1会先释放S锁，然后尝试获取X锁。
4. 如果T1成功获取了X锁，那么可以继续进行修改操作。如果获取失败（比如因为其他事务持有该记录的锁），T1将阻塞，直到能够获取X锁。

在这个例子中，虽然事务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语句，它们可能会触发不同类型的表锁：

1. 读锁（共享锁，S锁）：

LOCK TABLES table_name READ;

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

2. 写锁（独占锁，X锁）：

LOCK TABLES table_name WRITE;

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

3. 意向锁（Intention Locks）：

• 意向共享锁（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来演示

1. 建表

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

2. sessionA开启事务1，手动获取InnoDB存储引擎提供的表t的IX锁

BEGIN;
-- InnoDB存储引擎会对表t加表级别的X锁
LOCK TABLES t WRITE;

3.sessionB开启事务2，尝试对读取的记录加S锁和X锁，尝试增删改查

BEGIN;
-- 对读取的记录加S锁
select * from t lock in share mode;
-- 对读取的记录加X锁
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锁的锁定读，所以会被阻塞。

4. 记得解锁，将所有的表锁解除，然后事务都COMMIT提交

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

COMMIT;

5. 开启事务3，手动获取InnoDB存储引擎提供的表t的IS锁

BEGIN;
LOCK TABLES t READ;

6. 开始事务4，尝试对读取的记录加S锁和X锁，尝试增删改查

BEGIN;
-- 对读取的记录加S锁
select * from t lock in share mode;
-- 对读取的记录加X锁
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锁的查询会成功。

7. 最后记得给表解锁，解除所有的表级锁，提交事务

UNLOCK TABLES;

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

总结：

1. 两个事务之间，表锁是兼容的，带字母I开头的锁（意向锁）是兼容的。
2. 判断表锁和行锁的兼容性时，可以去掉字母I，例如S锁与X锁不兼容，那么S锁一定与IX锁不兼容。
3. 在同一个事务或没有事务的情况下：
   如果表上加了IX锁，可以对表进行增删改操作，但不允许进行任何查询（包括普通查询、加X锁查询、加S锁查询）。
   如果表上加了IS锁，则不允许进行任何带有X锁的操作，包括增删改操作以及加X锁的查询。

  请尽量避免在使用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修饰的列递增赋值的原理主要是两个：

1. AUTO-INC锁：在执行插入语句时，对于无法预先确定要插入多少条记录的场景，如INSERT ... SELECT、REPLACE ... SELECT或LOAD DATA等。执行插入语句之前MySQL会在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增值。该锁在语句执行结束后释放，保证一个语句中分配的递增值是连续的。

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

2. 轻量级锁：如果在执行插入语句前可以确定具体要插入多少条记录（如单个或多个固定的值），比如上面举的关于表t的例子中，INSERT INTO t(c) VALUES('aa'), ('bb');在语句执行前就可以确定要插入2条记录，MySQL会采用轻量级锁。在为插入语句生成AUTO_INCREMENT的列值时获取该轻量级锁，生成值后立即释放锁，而不需要等待整个插入语句执行完毕。这种方式可以避免锁定表，提高插入性能。

  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');
-- 此时，假设为'Alice'分配的id值为1

事务T2（主数据库）:

START TRANSACTION;
INSERT INTO users(name) VALUES('Bob');
-- 此时，假设为'Bob'分配的id值为2

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

事务T2（主数据库）:

COMMIT;

事务T1（主数据库）:

COMMIT;

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

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

从数据库：

-- 由于事务T2先提交，从数据库首先应用事务T2的更改
INSERT INTO users(id, name) VALUES(2, 'Bob');

-- 接下来，从数据库应用事务T1的更改
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隔离级别为什么不能完全禁止幻读？

举个例子

-- 事务T1，REPEATABLE READ隔离级别下 
mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 

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

-- 此时事务T2执行了：INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交 （trx_id为记录着T2的事务id）

-- 这里事务T1进行更新，这条记录的trx_id为变为T1的事务id
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)

1. 事务T1开始，但ReadView尚未创建。
2. 事务T1在执行第一次SELECT操作时，InnoDB会为它创建一个ReadView，记录下此时活跃的所有事务的ID（m_ids）以及下一个要分配的事务ID（max_trx_id）。然后，事务T1在这个ReadView中只能看到在此ReadView创建时活跃的事务创建的数据版本。
3. 事务T1首次查询number = 30的记录时，没有找到任何记录，因为在ReadView创建时这个新的记录并不存在。
4. 接着，事务T2插入了一个number = 30的记录，并提交。
5. 当事务T1执行更新操作UPDATE hero SET country = '蜀' WHERE number = 30;时，由于更新操作需要获取最新的数据版本，所以T1会看到T2插入的这个新的记录，并对其进行更新。此时，这个新的记录的trx_id隐藏列会被设置为T1的事务ID。（建议先了解下版本链）。
6. 当T1再次执行SELECT语句查询这条记录时，由于记录的创建者事务ID（creator_trx_id）等于T1的事务ID，T1能够看到这条记录。这意味着在这种特殊情况下，InnoDB中的MVCC机制不能完全禁止幻读。

  如对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;

加锁的步骤是：

1. 为 number 值为 8 的聚簇索引记录加上X锁 。
2. 为该聚簇索引记录对应的二级索引记录（也就是 name 值为 ‘c曹操’ ， number 值为 8 的那条二级索引记录）加上X锁 ，最后将聚簇索引和二级索引对应的记录更新。

如图：

之前为了区分事务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;

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

1. 先到聚簇索引中定位到满足 number <= 8 的第一条记录，也就是 number 值为 1 的记录，然后为其加锁。
2. 判断一下该记录是否符合二级索引的条件（包括ICP索引下推条件）。

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

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

3. 判断一下该记录是否符合范围查询的边界条件
     在本例中是利用主键 number 进行范围查询，InnoDB规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件，也就是 number <= 8 这个条件。如果符合的话将其返回给 server层继续处理，否则的话需要释放掉在该记录上加的锁，并给 server层返回一个查询完毕的信息。对于 number 值为 1 的记录是符合这个条件的，所以会将其返回到 server层继续处理。

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

4. 将该记录返回到 server层继续判断
     在server层会把所有的where子句的条件都判断一遍，如果满足条件，那么就把它发送给客户端并释放锁，如不满足条件也会释放锁。这索引条件不是在第3步中已经判断过了么，判断where子句的全部条件岂不是又把索引条件判断一次？

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

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

5. 然后刚刚查询得到的这条记录（也就是 number 值为 1 的记录）组成的单向链表继续向后查找，得到了 number 值为 3 的记录，然后重复1、 2、 3、 4 、5 这几个步骤。

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

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

# 事务T1
BEGIN;
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;
...

然后再到事务 T2 中执行：

# 事务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;

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

1. 为 number 值为 8 的聚簇索引记录加上X锁 。
2. 上一步中的记录索引记录对应的二级索引记录加上X锁 。
3. 为 number 值为 15 的聚簇索引记录加上X锁 。
4. 上一步中的记录索引记录对应的二级索引记录加上X锁 。
5. 为 number 值为 20 的聚簇索引记录加上X锁 。
6. 上一步中的记录索引记录对应的二级索引记录加上X锁 。

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

图示如下

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

如果是下边这个语句：

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曹操' 条件的二级索引记录，然后进行回表操作。所以这个语句的加锁顺序：

1. 先对 name 列为 'c曹操' 二级索引记录进行加S锁。
2. 再对相应的聚簇索引记录进行加S锁

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

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

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

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

# 在事务 T1 中运行
SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
# 在事务 T2 中运行
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 <= 8 LOCK IN SHARE MODE;这个例子的时候，在使用 number <= 8 这个条件的语句中，需要把 number 值为 15 的记录也加一个锁，之后又判断它不符合边界条件而把锁释放掉。而对于查询条件 name <= 'c曹操' 的语句来说，执行该语句需要使用到二级索引。

InnoDB规定

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

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

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

  但是这里有个问题：先对一条记录加了锁，然后再判断该记录是不是符合二级索引的条件（包括ICP），如果不符合直接跳到下一条记录或者直接向server层报告查询完毕，这个过程中并没有把那条被加锁的记录上的锁释放掉！！！本例中使用的查询条件是 name <= 'c曹操' ，在为 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 <= 'c曹操' 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示：

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

1. 当SELECT...FOR UPDATE或SELECT...FOR SHARE查询走二级索引的时候，二级临界记录会被加锁，即使条件不满足也不会释放锁。临界记录不会回表，所以也不用给聚簇索引对应的临界记录加锁。
2. 当SELECT...FOR UPDATE或SELECT...FOR SHARE查询走聚簇索引的时候，临界记录会被加锁，如果条件不满足则会释放锁。这整个过程不涉及二级索引也不会对二级索引加锁。
3. 当UPDATE语句条件走二级索引时，在锁定读的过程一定回表，临界记录先对二级索引和聚簇索引加锁，不满足条件就释放聚簇索引和二级索引对应记录的锁。
4. 当UPDATE语句条件走聚簇索引时，临界记录只对聚簇索引加锁判断，不满足则释放聚簇索引的锁，这整个过程不涉及二级索引也不会对二级索引加锁。

• 使用 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锁 ，然后：

1. 如果该聚簇索引记录不满足条件，直接把该记录上的锁释放掉。
2. 如果该聚簇索引记录满足条件，则会对相应的二级索引记录加上X锁 （ DELETE 语句会对所有二级索引列加锁， UPDATE 语句只会为更新的二级索引列对应的二级索引记录加锁）。

  本例子只有一个二级索引列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 的后边就好了，所以需要：

1. 为 number 值为 8 的记录加一个S锁 。
2. 为 number 值大于 8 的记录都加一个 S型next-key锁 （包括 Supremum 伪记录）。

  为什么这里把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 <= 10 条件后，这条临界记录(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 <= 8 的条件，虽然这条记录在 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 <= 8 这个条件进行加锁的时候会把 number 值为 15 的记录也加一个锁，之后判断不符合条件后再释放掉，现在换成二级索引就不用为下一条记录加锁了么？
  是的，这主要是因为我们开启了索引条件下推 ，对于二级索引记录来说，可以先在存储引擎层判断给定条件 name <= 'c曹操' 是否成立，如果不成立就不返回给 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 <= 'c曹操' 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉，这个和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会在事务结束时释放该锁。