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

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

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

文章目录

  • 1. 解决并发事务问题的两种方式
    • 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. 解决并发事务问题的两种方式

1.1 解决写-写冲突的锁机制

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

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

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

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

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

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

我们简单总结一下后面可能用到的内容：

  • 获取锁成功： 在进行某项操作时，MySQL在内存中生成了对应的锁结构，并且这个锁结构的'is_waiting'属性为false。这意味着事务可以继续执行操作。这种情况下，事务已经成功地获得了锁，并且可以对相应的数据项进行操作。这也包括使用”隐式锁”的情况，尽管隐式锁不会生成实际的锁结构，但它仍然能够保护记录不被其他事务修改。

  • 获取锁失败： 这种情况下，MySQL会在内存中为操作生成相应的锁结构，但是这个锁结构的'is_waiting'属性为true。这意味着当前事务需要等待锁被释放才能继续执行操作。这通常发生在当一个事务尝试获取一个已经被其他事务持有的锁时。

  • 不加锁： 这种情况下，MySQL不需要在内存中为操作生成对应的锁结构，事务可以直接进行操作。这种情况通常发生在对只读数据进行操作，或者事务的隔离级别低（比如READ UNCOMMITTED或READ COMMITTED）等情况下。这种情况下，MySQL不需要保护数据项不被其他事务修改，因此不需要加锁。

1.2 解决读-写冲突的MVCC和锁机制

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

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

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

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

  注意：即便在可重复读隔离级别下使用加锁的查询语句，例如SELECT ... FOR SHARE或SELECT ... FOR UPDATE可以避免大部分的幻读情况，但还是无法完全避免。后面会说原因。

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

  相比于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锁释放掉。

如果事务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锁

  前面说过，MySQL在RR隔离级别下是可以很大程度解决幻读现象的，但是由于MySQL实现问题，并不能完全避免。所以官方提出了一种称之为Gap Locks的锁，我们也可以简称为gap锁。

  在InnoDB存储引擎中，Gap Locks是自动添加的，而不是手动加锁。 它们是在事务执行过程中由InnoDB根据隔离级别和操作类型自动实施的。当事务在可重复读（Repeatable Read, RR）隔离级别下执行时，InnoDB会根据需要自动添加Gap Locks来减少幻读现象。

  以下是一些常见的场景，其中InnoDB会自动添加Gap Locks：

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

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

  需要注意的是，Gap Locks只在可重复读（RR）隔离级别下才会自动添加。 在读已提交（Read Committed, RC）隔离级别下，InnoDB不会使用Gap Locks。在RC隔离级别下，InnoDB使用其他锁机制（如Next-Key Locking）来减少幻读现象。

建表演示说明一下

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孙权', '吴');

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

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

  当前事务（事务A）在RR级别下执行此插入操作，InnoDB可能会为新记录的间隙获取Gap Lock，以确保在事务A提交之前，没有其他事务（如事务B）可以在相同的间隙中插入具有相同number值的记录。把number值为8的那条记录加一个gap锁的示意图如下（这里把b+树的索引结构进行超级简化，只把聚集索引叶子结点拿出来）

  Gap Lock是锁定记录之间的间隙，而不是锁定记录本身。其主要目的是防止在锁定范围内插入新记录，从而避免幻读现象。当一个事务试图插入一条新记录时，InnoDB会检查待插入记录的下一条记录上是否已经有一个Gap Lock。如果有，这个插入操作会被阻塞，直到持有Gap Lock的事务释放锁为止。

  如图中假设有一个事务（事务A）已经在number值为3和8之间的间隙上加了Gap Lock。当另一个事务（事务B）试图插入一条number值为4的新记录时，它会首先定位到新记录的下一条记录的number值为8。由于事务A已经在这个间隙上加了Gap Lock，事务B的插入操作将被阻塞，直到事务A释放这个Gap Lock为止。这意味着事务A需要提交或回滚，从而允许事务B在(3, 8)区间插入新记录。

  这种情况下的Gap Lock与范围查询或范围更新/删除操作中的Gap Lock有所不同。在插入操作中获取的Gap Lock主要是为了维护唯一性约束，而不是为了减少幻读现象。

  事务在等待时也需要在内存中生成一个锁结构，表示有事务想在某个间隙中插入新纪录但处于等待状态。这种插入意向锁命名为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释放这些锁。

在这个例子中，事务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语句。它是一种广义的术语，指的是在执行某些操作时，系统自动获取的锁，而无需用户显式地请求这些锁。这些操作可以包括INSERT，UPDATE，DELETE等。

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

  虽然在被Gap锁保护的间隙内不能插入新的记录，但是可以在这个间隙设置插入意向锁，以表示有事务希望在这个间隙插入新的记录。这样做的一个主要目的是提高并发性能。

  有人会问，明明插入都被Gap锁阻塞了，插入意向锁为什么说是提高并发性能？

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

插入意向锁是隐式锁的一种，但隐式锁不仅仅指插入意向锁，它包括了在各种情况下，由系统自动获取的锁。

  一般情况下执行INSERT语句时不需要在内存中生成锁结构的，如果即将插入的间隙已经被其他事务加了gap锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁（Insert Intention Lock）。

  需要注意的是，插入意向锁只在事务之间有间隙锁竞争时使用。在没有间隙锁竞争的情况下，INSERT操作通常不会生成锁结构。

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

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

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

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

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

首先创建两个表，一个主表orders和一个从表order_items，它们之间存在外键关系：

CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,order_number VARCHAR(20) NOT NULL) ENGINE=InnoDB;CREATE TABLE order_items (item_id INT PRIMARY KEY AUTO_INCREMENT,order_id INT,product_name VARCHAR(50),FOREIGN KEY (order_id) REFERENCES orders(order_id)) ENGINE=InnoDB;
  1. 遇到duplicate key：
    在这个例子中，我们向orders表中插入两条相同的order_number：
INSERT INTO orders (order_number) VALUES ('ORDER-123');-- 事务T1START TRANSACTION;INSERT INTO orders (order_number) VALUES ('ORDER-123');COMMIT;

事务T1插入相同的order_number会导致唯一键冲突，因此InnoDB会加锁并阻止事务T1的提交，以确保数据一致性。

  1. 外键检查：
    在这个例子中，我们向从表order_items插入一条记录，但提供的order_id在主表orders中不存在：
-- 事务T2START TRANSACTION;INSERT INTO order_items (order_id, product_name) VALUES (999, 'Product A');COMMIT;

由于order_id 999在主表orders中不存在，InnoDB会执行外键检查并加锁，阻止事务T2的提交以保持数据引用完整性。

至于隐式锁的例子，我们可以考虑以下情况：

-- 事务T1START TRANSACTION;INSERT INTO orders (order_number) VALUES ('ORDER-456');COMMIT;-- 事务T2START TRANSACTION;SELECT * FROM orders WHERE order_id = 2 FOR UPDATE; -- 假设order_id为2的记录是刚才插入的记录COMMIT;

  在这个例子中，事务T1向orders表中插入一条新记录，此时隐式锁生效。当事务T2试图在同一条记录上加上X锁（排他锁）时，事务T1的隐式锁会被升级为显式锁结构，事务T2会被阻塞，直到事务T1提交。这说明隐式锁在需要时可以延迟生成锁结构，从而提高性能和降低内存使用。

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锁的锁定读。

  1. 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;

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

  1. 写锁（独占锁，X锁）：
LOCK TABLES table_name WRITE;

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

  1. 意向锁（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;
  1. 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锁的锁定读，所以会被阻塞。

  1. 记得解锁，将所有的表锁解除，然后事务都COMMIT提交
- 解除表锁之后就可以对任意记录进行操作了UNLOCK TABLES;COMMIT;
  1. 开启事务3，手动获取InnoDB存储引擎提供的表t的IS锁
BEGIN;LOCK TABLES t READ;

  1. 开始事务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锁的查询会成功。

  1. 最后记得给表解锁，解除所有的表级锁，提交事务
UNLOCK TABLES;

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

兼容性IXISXS
IX兼容兼容不兼容不兼容
IS兼容兼容不兼容兼容
X不兼容不兼容不兼容不兼容
S不兼容兼容不兼容兼容

总结：

  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锁的作用范围只是单个插入语句，在插入语句执行完成后，这个锁就被释放了。这与前面介绍的锁在结束时释放时不一样的。

  1. 轻量级锁：如果在执行插入语句前可以确定具体要插入多少条记录（如单个或多个固定的值），比如上面举的关于表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的事务idmysql> 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。这个ReadView包含了当前已经激活的所有事务ID（m_ids）以及应该分配的下一个事务ID（max_trx_id）。事务T1只能读取在此ReadView创建时激活的事务创建的数据版本。
  3. 事务T1首次查询number = 30的记录时，没有找到任何记录，因为在ReadView创建时不存在这样的记录。
  4. 接着，事务T2插入了一个number = 30的记录，并提交。
  5. 当事务T1执行更新操作UPDATE hero SET country = '蜀' WHERE number = 30;时，InnoDB会在执行更新操作时尝试获取新记录的最新版本，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条件的记录，从而减少回表的可能。

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

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

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

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

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

  1. 然后刚刚查询得到的这条记录（也就是 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 中执行：

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

然后再到事务 T2 中执行：

# 事务T2BEGIN;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 持有了二级索引记录的锁，在等待获取聚簇索引记录上的锁
  • 事务 T2 持有了聚簇索引记录的锁，在等待获取二级索引记录上的锁。

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

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

  • 使用 SELECT ... FOR UPDATE语句时
SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

  这种情况与 SELECT ... LOCK IN SHARE MODE 语句的加锁情况类似，分别给访问到的二级索引记录和对应的聚簇索引记录加锁，只不过加的是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刘备'的记录进行查询的语句，那么会造成死锁，并且事务T1没有提交。

  • 使用 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加锁的查询(不管S还是X锁)条件都走二级索引的情况下，临界记录只对二级索引加锁判断，不满足并不会释放锁，也不用给聚簇索引对应的临界记录加锁。如需回表，那临界记录也要给聚簇索引对应的记录加锁。
  2. select加锁的查询(不管S还是X锁)条件都走聚簇索引的情况下，临界记录只对聚簇索引加锁判断，不满足就释放锁，不用给二级索引加锁。
  3. update条件走二级索引时，在锁定读的过程一定回表，临界记录先对二级索引和聚簇索引加锁，不满足条件就释放聚簇索引和二级索引对应记录的锁。
  4. update条件走聚簇索引时，临界记录只对聚簇索引加锁判断，不满足则释放聚簇索引的锁，不用给二级索引加锁。即便更新二级索引列，临界记录也只对聚簇索引加锁判断。
  • 使用 DELETE ... 来为记录加锁
DELETE FROM hero WHERE name >= 'c曹操';

和

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

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

9.1.5 全表扫描的情况

  • 使用 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会把这些二级索引列name、phone、user_id对应的记录都加上锁，然后把聚簇索引和这些二级索引中对应的记录都删掉，而UPDATE更新几个二级索引列就锁定几个对应的二级索引记录。

9.2 REPEATABLE READ隔离级别下

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 的新记录，所以需要在 number 值为 8 的记录上加一个 gap锁 ，也就是不允许别的事务插入 number 值在 (3, 8) 这个区间的新记录。如下图

  总结：如果查询的主键值不存在，为了尽可能防止幻读现象，MySQL 将在范围内的下一个存在的记录（在你的例子中是 number = 8 的记录）上加上间隙锁（Gap Lock），这样可以防止其他事务在该范围内插入新的记录。

  如果在 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 <= 8 LOCK IN SHARE MODE;

  在加锁时会把 number 值为 1、3、8、15 这四条记录都加上 S型next-key 锁，不过之后 server层判断 number 值为 15 的记录不满足 number <= 8 条件后，这条临界记录(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之间的新记录。这样，如果当前事务再次执行相同的查询，会发现多了一条新记录，也就是发生了幻读现象。为了防止这种情况发生，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;

这条 UPDATE 语句并没有更新二级索引列，加锁方式和上边所说的 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锁。

如果是下边这个语句

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 INSHARE 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锁 ， server层判断前边在说为 number <= 8 这个条件进行加锁时，会把 number 值为 15 的记录也加一个锁，之后 server层判断不符合条件后再释放掉，现在换成二级索引就不用为下一条记录加锁了么？
 是的，这主要是因为我们开启了索引条件下推 ，对于二级索引记录来说，可以先在存储引擎层判断给定条件 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 仍然会锁定满足查询条件的第一条记录。

  1. 对于范围查询：
  • 在 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———————–