mysql中的锁

解决并发事务带来问题的两种基本方式

并发事务访问相同记录的情况大致可以划分为3种：

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

写-写情况

在写-写情况下，会发生脏写的现象，任何一种隔离级别都不允许这种现象的发生。所以在多个未提交事务相继对一条记录进行改动时，需要让它们排队执行。这个排队过程其实是通过为该记录加锁实现的。这个“锁”本质上是一个内存中的结构。在事务执行前，本来是没有锁的，也就是说一开始是没有锁结构与记录进行关联的 。如图所示。当一个事务想对这条记录进行改动时，首先会看看内存中有没有与这条记录关联的锁结构，如果没有，就会在内存中生成一个锁结构与之关联 。

比如，事务T1要对这条记录进行改动，就需要生成一个锁结构与之关联。

锁结构的两个重要属性

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

如图所示，在事务T1修改这条记录时，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false。这个场景称为获取锁成功，或者加锁成功，然后就可以继续执行操作了。

在事务T1提交之前，另一个事务T2也想对该记录进行改动，那么T2先去看看有没有锁结构与这条记录关联。在发现有一个锁结构与之关联后，T2也生成了一个锁结构与这条记录关联，不过锁结构的is_waiting属性值为true，表示需要等待。这个场景称为获取锁失败，或者加锁失败，或者没有成功地获取到锁。

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

• 获取锁成功，或者加锁成功：在内存中生成了对应的锁结构，而且锁结构的is_waiting属性为false，也就是事务可以继续执行操作。当然并不是所有的加锁操作都需要生成对应的锁结构，有时候会有一种“加隐式锁”的说法。隐式锁并不会生成实际的锁结构，但是仍然可以起到保护记录的作用。
• 获取锁失败，或者加锁失败，或者没有获取到锁：在内存中生成了对应的锁结构，不过锁结构的is_waiting属性为true，也就是事务需要等待，不可以继续执行操作。
• 不加锁：不需要在内存中生成对应的锁结构，可以直接执行操作。

读-写或写-读情况

mysql在REPEATABLE READ隔离级别下很大程度地避免了幻读现象。

怎么避免脏读，不可重复读，幻读这些现象？

• 读操作使用多版本并发控制（MVCC），写操作进行加锁
  • 查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改时看不到的。写操作肯定针对的是最新版本的记录。读记录的历史版本和改动记录的最新版本这两者并不冲突，也就是采用MVCC时，读-写操作并不冲突。
    • 普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用MVCC读取记录。在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行SELECT操作时，都会生成一个ReadView。ReadView的存在本身就保证了事务不可以读取到未提交的事务所作的修改，也就是避免了脏读现象。在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读现象。
• 读，写操作都采用加锁的方式
  • 脏读现象的产生是因为当前事务读取了另一个未提交事务写的一条记录。如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法在读取该记录时再获取到锁了，所以也就不会出脏读现象了。
  • 不可重复读现象的产生是因为当前事务先读取一条记录，另外一个事务对该记录进行了改动。如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也就不会出现不可重复读现象了。
  • 幻读现象的产生是因为某个事务读取了符合某些搜索条件的记录，之后别的事务又插入了符合相同搜索条件的新记录，导致该事务再次读取相同搜索条件的记录时，可以读到别的事务插入的新纪录，这些新插入的记录就称为幻影记录。采用加锁的方式避免幻读现象有点麻烦，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以在读取的时候加锁，并不知道给谁加锁。

很明显，如果采用MVCC方式，读-写操作彼此并不冲突，性能更高；如果采用加锁方式，读-写方式彼此需要排队执行，从而影响性能。

一致性读

事务利用MVCC进行的读取操作称为一致性读（Consistent Read），或者一致性无锁读。

所有普通的SELECT语句在READ COMMITTED，REPEATABLE READ隔离级别下都算是一致性读。

在READ COMMITTED事务隔离级别下，对于快照数据，一致性读总是读取被锁定行的最新一份快照数据。在REPEATABLE READ事务隔离级别下，对于快照数据，一致性读总是读取事务开始时的行数据版本。

SELECT * FROM t;

SELECT * FROM t1 INNER JOIN t2 ON t1.col1=t2.col2;

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

锁定读

共享锁和独占锁

• 共享锁（Shared Lock）：简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
• 独占锁（Exclusive Lock）：也称为排他锁，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。

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

• 如果事务T2想要再获取一个记录的S锁，那么事务T2也会获得该锁，这也就意味着事务T1和T2在记录上同时拥有S锁；
• 如果事务T2想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务T1提交之后将S锁释放掉为止；

如果事务T1首先获取了一条记录的X锁，那么之后无论事务T2是想获取该记录的S锁还是X锁，都会被阻塞，直到事务T1提交之后将X锁释放掉位置。

S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的。

锁定读的语句

在读取记录前就为该记录加锁的读取方式称为锁定读（Locking Read）

• 对读取的记录加S锁

SELECT ... LOCK IN SHARE MODE;

在普通的SELECT语句后面加LOCK IN SHARE MODE。如果当前事务执行了语句，那么它会为读取到的记录加S锁，这样可以允许别的事务继续获取这些记录的S锁。别的事务也可以使用SELECT ... LOCK IN SHARE MODE;但是不能获取这些记录的X锁（比如使用SELECT ... FROM UPDATE;来读取这些记录，或者直接改动这些记录时）。如果别的事务想要获取这些记录的X锁，那么它们会被阻塞，直到当前事务提交之后将这些记录上的S锁释放掉为止。

• 对读取的记录加X锁

SELECT ... FOR UPDATE;

在普通的SELECT语句后面加上FOR UPDATE。如果当前事务执行了该语句，那么它会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁（比如用SELECT ... LOCK IN SHARE MODE;语句来读取这些记录时），也不允许获取这些记录的X锁（比如使用SELECT ... FOR UPDATE;语句来读取这些记录，或者直接改动这些记录时）。如果别的事务想要获取这些记录的S锁或者X锁，那么它们会被阻塞，直到当前事务提交之后，将这些记录上的X锁释放掉为止。

写操作

平常常用的写操作无非是DELETE，UPDATE，INSERT这三种

• DELETE：对一条记录执行DELETE操作的过程其实是现在B+树中定位到这条记录的位置，然后获取这条记录的X锁，最后再执行delete mark操作。我们也可以把这个“先定位待删除记录在B+树中的位置，然后获取这条记录的X锁的过程”看成获取X锁的锁定读。
• UPDATE：在对一条记录进行UPDATE操作时分为下面3种情况
  • 如果未修改该记录的键值并且被更新的列所占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取记录的X锁，最后在原记录的位置进行修改操作。可以把这个“先定位待修改记录在B+树中的位置，然后再获取记录的X锁的过程”看成是一个获取X锁的锁定读。
  • 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取记录的X锁，之后将该记录彻底删除掉（就是把记录彻底移入垃圾链表），然后再插入一条新记录。可以把这个“先定位待修改记录在B+树中的位置，然后再获取记录的X锁的过程”看成是一个获取X锁的锁定读，与被彻底删除的记录关联的锁也会被转移到这条新插入的记录上来。
  • 如果修改了该记录的键值，则相当于在原记录上执行DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行。
• INSERT：一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应的锁结构。

在一个事务中加的锁一般在事务提交或中止时才会释放。

多粒度锁

针对记录的锁叫行级锁或者行锁。对一条记录加锁，影响的只是这条记录而已。

事务可以在表级别进行加锁，自然就将其称为表级锁或者表锁。对一个表加锁，会影响表中的所有记录。

给表加的锁分为共享锁（S锁）和独占锁（X锁）

• 给表加S锁
  • 如果一个事务给表加了S锁，那么
    • 别的事务可以继续获得该表的S锁
    • 别的事务可以继续获得该表中某些记录的S锁
    • 别的事务不可以继续获得该表的X锁
    • 别的事务不可以继续获得该表中某些记录的X锁
• 给表加X锁
  • 如果一个事务给表加了X锁，那么
    • 别的事物不可以继续获得该表的S锁
    • 别的事务不可以继续获得该表中某些记录的S锁
    • 别的事务不可以继续获得该表的X锁
    • 别的事务不可以继续获得该表中某些记录的X锁

意向锁

• 意向共享锁：简称IS锁，当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
• 意向独占锁：简称IX锁，当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

总结

IS锁，IX锁是表级锁，它们的提出仅仅是为了在之后加表级别的S锁和X锁时，可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录；也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

InnoDB存储引擎中的锁

InnoDB中的表级锁

• 表级别的S锁，X锁
  • 对某个表执行SELECT，INSERT，UPDATE，DELETE语句时，innodb存储引擎是不会为这个表添加表级别的S锁或者X锁。
    • 另外，在对某个表执行一些诸如ALTER TABLE，DROP TABLE的DDL语句时，其他事务在对这个表并发执行诸如SELECT，DELETE，INSERT，UPDATE等语句，会发生阻塞。同理，某个事务在对某个表执行SELECT，DELETE，INSERT，UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程是通过在server层使用一种称为元数据锁的东西实现的。
• 表级别的IS锁，IX锁
  • 当对使用innodb存储引擎的表的某些记录加S锁之前，需要先在表级别加一个IS锁，当对使用innodb存储引擎的表的某些记录加X锁之前，需要先在表级别加一个IX锁。IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时，判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。
• 表级别的AUTO-INC锁
  • 在使用mysql的过程中，可以为表的某些列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋予递增的值。
  • 系统给AUTO-INCREMENT修饰的列进行递增赋值的实现方式主要有下面两个
    • 采用AUTO-INC锁，也就是在执行插入语句时就加一个表级别的AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值。在该语句执行结束后，再把AUTO-INC锁释放掉。这样一来，一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，从而保证一个语句中分配的递增值是连续的。
      • AUTO-INC锁的作用范围只是单个插入语句，在插入语句执行完成之后，这个锁就被释放了。
    • 采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取这个轻量级锁，然后在生成本次插入语句需要用到的AUTO_INCREAMENT修饰的列的值之后，就把该轻量级锁释放掉，而不需要等到整个插入语句执行完后才释放锁。

InnoDB中的行级锁

行级锁，也称为记录锁。

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

SELECT * FROM hero;

number	name	country
1	l刘备	蜀
3	z诸葛亮	蜀
8	c曹操	魏
15	x荀彧	魏
20	s孙权	吴

表hero的聚集索引如图所示

• Record Lock

前面提到的记录锁就是Record Lock，也就是仅仅把一条记录锁上。

Record Lock有S锁和X锁之分。当一个事务获取了一条记录的S型Record Lock后，其他事务也可以继续获取该记录的S型Record Lock，但是不可以获取X型的Record Lock。当一个事务获取了一条记录的X型Record Lock后，其他事务既不可以继续获取该记录的S型Record Lock，也不可以继续获取X型Record Lock。

• Gap Lock

Gap Lock，官方名称是LOCK_GAP，可以简称GAP锁。

在图中，为number为8的记录加了GAP锁，这意味着不允许其他的事务在number值为8的记录前面的间隙插入新记录，其实就是number列的值在区间（3，8）的新记录是不允许立即插入的。比如有另外一个事务想插入一条number值为4的新记录，首先要先定位到该条记录的下一条记录，也就是number值为8的记录，而这条记录上有一个gap锁，所以就会阻塞插入操作；直到拥有这个gap锁的事务提交了之后将该gap锁释放掉，其他事务才可以插入number列的值在区间（3，8）中的新记录。

GAP锁的提出仅仅是为了防止插入幻影记录而提出的。

如果对一条记录加了GAP锁，并不会限制其他事务对这条记录加Record Lock或者继续加GAP锁。

用户可以通过以下两种方式来显式的关闭Gap Lock

• 将事务的隔离级别设置为READ COMMITTED
• 将参数innodb_locks_unsafe_for_binlog设置为1

对于最后一条记录之后的间隙，也就是hero表中number值为20的记录之后的间隙应该怎么办。

• Infimum记录：表示该页面中最小的记录
• Supremum记录：表示该页面中最大的记录

为了防止其他事务插入number值在区间（20，+∞）的新记录，可以给索引的最后一条记录所在的页面的Supremum记录加上一个gap锁。

• Next-Key Lock

Next-key Lock既可以锁住某条记录，也可以阻止其他事务在该记录前面的间隙插入新记录。

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

• Insert Intention Lock（插入意向锁）

一个事务在插入一条记录时，需要判断插入位置是否已经被别的事务加了GAP锁。如果有的话，插入操作需要等待，直到拥有GAP锁的那个事务提交为止。

事务在等待时也需要在内存中生成一个锁结构，表明有事务想要在某个间隙插入新记录，但是处于等待状态。

这种锁叫做LOCK_INSERT_INTENTION，也可以称为插入意向锁。

现在T1为number值为8的记录加了一个gap锁，然后T2和T3分别想向hero表中插入number值分别为4，5的两条记录。现在number值为8的记录加锁的示意图如图所示；

由于T1持有gap锁，所以T2和T3需要生成一个插入意向锁结构并且处于等待状态。当T1提交后会把它获取的锁都释放掉，这样T2和T3就能获取到对应的插入意向锁。T2和T3之间不会互相阻塞，它们可以同时获得number值为8的插入意向锁，然后执行插入操作。

事实上，插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

• 隐式锁
  • 情景1：对于聚集索引记录来说，有一个trx_id隐藏列，该隐藏列记录着随后改动该记录的事务的事务id。在当前事务中新插入一条聚集索引记录之后，该记录的trx_id隐藏列代表的就是当前事务的事务id。如果其他事务此时想对该记录加S锁或X锁，首先会看一下该记录的trx_id隐藏列代表的事务是否是当前的活跃事务，如果不是的话就可以正常读取；如果是的话，那么就帮助当前事务创建一个X锁的锁结构，该锁结构的is_waiting属性为false；然后为自己也创建一个锁结构，该锁结构的is_waiting属性为true，之后自己进入等待状态。
  • 情景2：对于二级索引记录来说，本身并没有trx_id隐藏列，但是在二级索引页面的Page Header部分有一个PAGE_MAX_TRX_ID属性，该属性代表对该页面做改动的最大的事务id。如果PAGE_MAX_TRX_ID属性值小于当前最小的活跃事务id，那就说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后通过回表操作找到它对应的聚集索引记录，然后再重复情景1的做法。

一个事务对新插入的记录可以不显式地加锁（生成一个锁结构），但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构，最后进入等待状态。

隐式锁起到了延迟生成锁结构的用处。

语句加锁分析

给hero表的name列建一个索引

ALTER TABLE hero ADD INDEX idx_name(name);

现在，hero表就有了两个索引（一个二级索引和一个聚集索引）。

普通的Select语句

在不同的隔离级别下，普通的SELECT语句具有不同的表现。

• 在READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版；可能出现脏读，不可重复读和幻读现象。
• 在READ COMMITTED隔离级别下，不加锁；在每次执行普通的SELECT语句时都会生成一个ReadView，这样避免了脏读现象，但没有避免不可重复度和幻读现象。
• 在REPEATABLE READ隔离级别下，不加锁；只在第一次执行普通的SELECT语句时生成一个ReadView，这样就把脏读，不可重复读和幻读现象都避免了。

#事务T1，REPEATABLE READ隔离级别下
BEGIN;

SELECT * FROM hero WHERE number=30;

#此时事务T2执行了:INSERT INTO hero VALUES(30,'g关羽'，‘魏’);语句并提交

UPDATE hero SET country='蜀' WHERE number=30;

SELECT * FROM hero WHERE number=30;
number		name		country
30			g关羽			蜀

在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT语句时生成了一个ReadView，之后T2向hero表中新插入一条记录并提交。ReadView并不会阻止T1执行UPDATE或者DELETE语句来改动这个新插入的记录（由于T2已经提交，因此改动该记录并不会造成阻塞），但是这样一来，这条新记录trx_id隐藏列的值就变成了T1的事务id。之后T1再使用普通的SELECT语句去查询这条记录时就能看到这条记录了，也就可以把这条记录返回给客户端。

因为这个特殊现象的存在，可以认为innodb中的MVCC并不能完全禁止幻读。

• 在SERIALIZABLE隔离级别下，需要分下面的两种情况进行讨论
  • 在系统变量autocommit=0时（禁用自动提交时），普通的SELECT语句会被转换为SELECT ... LOCK IN SHARE MODE这样的语句。也就是在读取记录前需要先获得记录的S锁。
  • 在系统变量autocommit=1（启动自动提交时），普通的SELECT语句并不会加锁，只是利用MVCC生成一个ReadView来读取记录。为什么不加锁呢？因为启用自动提交就意味着一个事务只包含一条语句，而只执行一条语句也就不会出现不可重复读，幻读这样的现象。

锁定读的语句

• 语句1：SELECT ... LOCK IN SHARE MODE;
• 语句2：SELECT ... FOR UPDATE;
• 语句3：UPDATE
• 语句4：DELETE

语句1和语句2时mysql中规定的两种锁定读的语法格式，而语句3和语句4由于在执行过程中需要首先定位到被改动的记录并给记录加锁，因此也可以认为是一种锁定读。

• 匹配模式
  • 在使用索引执行查询时，查询优化器首先会生成若干个扫描区间。针对每一个扫描区间，都可以在该扫描区间中快速地定位到第一条记录，然后沿着这条记录所在的链表就可以访问到该扫描区间内的其他记录，直到某条记录不再扫描区间中为止。如果被扫描的区间是一个单点扫描区间，此时的匹配模式就是精准匹配。比如，为某表的a,b两列建立了一个联合索引idx_a_b(a, b)。
    • 如果扫描区间的边界条件是a=1，那么它对应的扫描区间就是[1,1]。这是个单点扫描区间，此时的匹配模式是精确匹配。
    • 如果形成扫描区间的搜索条件是a=1 AND b=1，那么它对应的扫描区间就是[(1,1), (1,1)]。这也是单点扫描区间，此时的匹配模式也是精确匹配。
    • 如果形成的扫描区间的搜索条件是a=1 AND b>=1，对应的扫描区间就是[(1, 1), (1,+∞))。这不是单点扫描区间，不是精确匹配。
• 唯一性扫描
  • 如果在扫描某个扫描区间的记录前，就能实现确定该扫描区间内最多只包含一条记录的话，那么就把这种情况称为唯一性搜索。
  • 只要查询符合下面这些条件，就可以确定只包含一条记录了
    • 匹配模式为精确匹配
    • 使用的索引是主键或者唯一二级索引
    • 如果使用的索引是唯一二级索引，那么搜索条件不能为“索引列is null”的形式（这是因为对于唯一二级索引列来说，可以存储多个值为NULL的记录）
    • 如果索引列包含多个列，那么在生成扫描区间时，每一个列都得被用到。

事务在执行过程中所获得的锁一般在事务提交或者回滚时才会释放，但是在隔离级别不大于READ COMMITTED时，在某些情况下也会提前将一些不符合搜索条件的记录上的锁释放掉。（这主要是考虑在较低的隔离级别中，可以允许事务更大程度地并发执行）

锁定读的执行看成是依次读取若干个扫描区间中的记录（如果是全表扫描，就把它看成是扫描区间（-∞，+∞）中的聚集索引记录）

在一般情况下，读取某个扫描区间中的记录的过程如下所示：

• 步骤1，首先快速地在B+树叶子节点中定位到该扫描区间中的第一条记录，把该记录作为当前记录
• 步骤2，为当前记录加锁
  • 一般情况下，对于锁定读的语句，在隔离级别不大于READ COMMITTED（指的就是READ UNCOMMITTED，READ COMMITTED）时，会为当前记录加Record Lock。在隔离级别不小于REPEATABLE READ（指的就是REPEATABLE READ，SERIALIZABLE）时，会为当前记录加next-key锁。
• 步骤3，判断索引条件下推的条件是否成立
  • 索引下推，用来把查询中与被使用索引有关的搜索条件下推到存储引擎中判断，而不是返回到server层再判断。索引条件下推只是为了减少回表次数，也就是减少读取完整的聚集索引记录的次数，从而减少I/O次数。所以它只适合于二级索引，不适合于聚集索引。另外，索引条件下推仅适用于SELECT语句，不适用于UPDATE，DELETE这些需要改动的语句。
  • 在存在索引条件下推的条件时，如果当前记录符合索引条件下推的条件，则跳到步骤4继续执行；如果不符合，则直接获取到当前记录所在单向链表的下一跳记录，将该记录作为新的当前记录，并跳回步骤2。另外，步骤3还会判断当前记录是否符合形成扫描区间的边界条件，如果不符合，则跳过步骤4和步骤5，直接向server层返回一个“查询完毕”的信息。这里需要注意的是，步骤3不会释放锁。
• 步骤4：执行回表操作
  • 如果读取的是二级索引记录，则需要进行回表操作，获取到对应的聚集索引记录并给该聚集索引记录加Record Lock。
• 步骤5，判断边界条件是否成立
  • 如果该记录符合边界条件，则跳到步骤6继续执行，否则在隔离级别不大于READ COMMITTED时，就要释放掉加在该记录上的锁（在隔离级别不小于REPEATABLE READ时，不释放加在该记录上的锁），并且向server层返回一个一个“查询完毕”的信息。
• 步骤6，server层判断其余搜索条件是否成立
  • 除了索引条件下推中的条件以外，server层还需要判断其他搜索条件是否成立。如果成立，则将该记录发送给客户端，否则在隔离级别不大于READ COMMITTED时，就要释放掉加在该记录上的锁（在隔离界别不小于REPEATABLE READ时，不释放加载该记录上的锁）。
• 步骤7，获取当前所在单向链表的下一条记录，并将其作为新的当前记录，并跳回步骤2.

实例1

SELECT * FROM hero WHERE number>1 AND number<=15 AND country='魏' LOCK IN SHARE MODE; 

首先通过搜索条件number>1 AND number<=15来生成扫描区间(1, 15]，也就是需要扫描number值在(1, 15]区间中的所有聚集索引记录。

隔离级别不大于READ COMMITTED的加锁过程

• 对number值为3的聚集索引记录的加锁过程进行分析
  • 步骤1，读取(1, 15]扫描区间的第一条聚集索引记录，也就是number值为3的聚集索引记录。
  • 步骤2，为number值为3的聚集索引记录加S型Record Lock。
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件。
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作。
  • 步骤5，形成扫描区间(1, 15]的边界条件是number>1 AND number<=15，很显然number值为3的聚集索引记录符合该边界条件。
  • 步骤6，server层继续判断number值为3的聚集索引记录是否符合条件number>1 AND number<=15 AND country='魏'。很显然不符合，所以释放掉加在该记录上的锁。
  • 步骤7，获取number值为3的聚集索引记录所在链表的下一条记录，也就是number值为8的聚集索引记录
• 对number值为8的聚集索引记录的加锁过程进行分析
  • 步骤2，为number值为8的聚集索引记录加S型Record Lock。
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件。
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作。
  • 步骤5，形成扫描区间(1,15]的边界条件是number>1 AND number<=15，很显然number值为8的聚集索引记录符合该边界条件
  • 步骤6，server层继续判断number值为8的聚集索引记录是否符合条件number>1 AND number<=15 AND country=‘魏’。很显然符合，所以将其发送给客户端，并且不释放加在该记录上的锁。
  • 步骤7，获取number值为8的聚集索引记录所在单向链表的下一条记录，也就是number值为15的聚集索引记录。
• 对number值为15的聚集索引记录的加锁过程进行分析
  • 步骤2，为number值为15的聚集索引记录的加锁过程进行分析
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作
  • 步骤5，形成扫描区间(1,15]的边界条件是number>1 AND number<=15，很显然number值为15的聚集索引记录符合该边界条件
  • 步骤6，server继续判断number值为15的聚集索引记录是否符合条件number>1 AND number<=15 AND country='魏'。很显然符合，所以将其发送给客户端，并且不释放加在该记录上的锁
  • 步骤7，获取number值为15的聚集索引记录所在链表的下一条记录，也就是number值为20的聚集索引记录
• 对number值为20的聚集索引记录的加锁过程进行分析
  • 步骤2，为number值为20的聚集索引记录加S型Record Lock
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作
  • 步骤5，形成扫描区间(1, 15]的边界条件是number>1 AND number<=15，很显然number值为20的聚集索引记录不符合该边界条件。释放掉加在该记录上的锁，并给server层返回一个“查询完毕”的信息
  • 步骤6，server层收到存储引擎返回的“查询完毕”信息，结束查询

隔离级别不小于REPEATABLE READ时的加锁过程

• 对number值为3的聚集索引记录的加锁过程分析

  • 步骤1，读取(1, 15]扫描区间的第一条聚集索引记录，也就是number值为3的聚集索引记录
  • 步骤2，为number值为3的聚集索引记录加S型next-key锁
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作
  • 步骤5，形成扫描区间(1, 15]的边界条件是number>1 AND number<=15，很显然number值为3的聚集索引记录符合该边界条件
  • 步骤6，server层继续判断number值为3的聚集索引记录是否符合条件number>1 AND number<=15 AND country='魏'。很显然符合，所以将其发送到客户端，并且不释放加在该记录上的锁
  • 步骤7，获取number值为3的聚集索引记录所在单向链表的下一条记录，也就是number值为8的聚集索引记录

• 对number值为8的聚集索引记录的加锁过程进行分析

  • 步骤2，为number值为8的聚集索引记录加S型next-key锁
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作
  • 步骤5，形成扫描区间(1, 15]的边界条件是number>1 AND number<=15，很显然number值为8的聚集索引记录符合该边界条件
  • 步骤6，server层继续判断number值为8的聚集索引记录是否符合条件number>1 AND number<=15 AND country='魏'。很显然符合，所以将其发送到客户端，并且不释放加在该记录上的锁
  • 步骤7，获取number值为8的聚集索引记录所在链表的下一条记录们也就是number值为15的聚集索引记录

• 对number值为15的聚集索引记录的加锁过程进行分析

  • 步骤2，为number值为15的聚集索引记录加S型next-key锁
  • 步骤3，由于读取的是聚集索引记录，所以没有索引条件下推的条件
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作
  • 步骤5，形成扫描区间(1, 15]的边界条件是number>1 AND number<=15，很显然number值为15的聚集索引记录符合该边界条件
  • 步骤6，server层继续判断number值为15的聚集索引记录是否符合条件number>1 AND number<=15 AND country='魏'。很显然符合，所以将其发送到客户端，并且不释放加在该记录上的锁
  • 步骤7，获取number值为15的聚集索引记录所在链表的下一条记录们也就是number值为20的聚集索引记录

• 对number值为20的聚集索引记录的加锁过程分析

  • 步骤2，对number值为20的聚集索引记录加S型next-key锁
  • 步骤3，由于读取的事聚集索引记录，所以没有索引条件下推的条件
  • 步骤4，由于读取的本身就是聚集索引记录，所以不需要执行回表操作
  • 步骤5，形成扫描区间(1, 15]的边界条件number>1 AND number<=15，很显然number值为20的聚集索引记录不符合该边界条件。由于现在的隔离级别不小于REPEATABLE TABLE，所以不会释放加在该记录上的锁，之后给server层返回一个“查询完毕”的信息
  • 步骤6，server层收到存储引擎返回的“查询完毕”信息，结束查询

实例2

SELECT * FROM hero FORCE INDEX(idx_name) WHERE name>'c曹操' AND name<='x荀彧' AND country!='吴' LOCK IN SHARE MODE;

隔离级别不大于READ COMMITTED时的加锁过程

• 对name值为’l刘备’的二级索引记录的加锁过程进行分析
  • 步骤1，读取在(‘c曹操’，‘x荀彧’]扫描区间的第一条二级索引记录，也就是name值为’l刘备’的二级索引记录
  • 步骤2，为name列值为’l刘备’的二级索引记录加S型Record Lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’l刘备’的二级索引记录符合索引条件下推的条件
  • 步骤4，由于读取的是二级索引记录，所以需要对该表执行回表操作，找到相应的聚集索引记录，也就是number值为1的聚集索引记录，然后为该聚集索引记录加一个S型Record Lock。
  • 步骤5，形成扫描区间(‘c曹操’，‘x荀彧’]的边界条件是name>'c曹操' AND name<='x荀彧'，很显然name值为’l刘备’的二级索引记录符合该边界条件
  • 步骤6，server层继续判断name值为’l刘备’的二级索引记录对应的聚集索引记录是否符合条件country!='吴'。很显然符合，所以将其发送到客户端，并且不释放加再改记录上的锁。
  • 步骤7，获取name值为’l刘备’的二级索引记录所在链表的下一个记录，也就是name值为’s孙权’的二级索引记录
• 对name值为’s孙权’的二级索引记录的加锁过程进行分析
  • 步骤2，为name列值为’s孙权’的二级索引记录加S型Record Lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’s孙权’的二级索引记录符合索引条件下推的条件
  • 步骤4，由于读取的是二级索引记录，所以需要对该表执行回表操作，找到相应的聚集索引记录，也就是number值为20的聚集索引记录，然后为该聚集索引记录加一个S型Record Lock。
  • 步骤5，形成扫描区间(‘c曹操’，‘x荀彧’]的边界条件是name>'c曹操' AND name<='x荀彧'，很显然name值为’s孙权’的二级索引记录符合该边界条件
  • 步骤6，server层继续判断name值为’s孙权’的二级索引记录对应的聚集索引记录是否符合条件country!='吴'。很显然不符合，所以释放掉加在该二级索引记录以及对应的聚集索引记录上的锁
  • 步骤7，获取name值为’l刘备’的二级索引记录所在链表的下一个记录，也就是name值为’x荀彧’的二级索引记录
• 对name值为’x荀彧’的二级索引记录的加锁过程分析
  • 步骤2，为name列值为’x荀彧’的二级索引记录加S型Record Lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’x荀彧’的二级索引记录符合索引条件下推的条件
  • 步骤4，由于读取的是二级索引记录，所以需要对该表执行回表操作，找到相应的聚集索引记录，也就是number值为15的聚集索引记录，然后为该聚集索引记录加一个S型Record Lock。
  • 步骤5，形成扫描区间(‘c曹操’，‘x荀彧’]的边界条件是name>'c曹操' AND name<='x荀彧'，很显然name值为’x荀彧’的二级索引记录符合该边界条件
  • 步骤6，server层继续判断name值为’x荀彧’的二级索引记录对应的聚集索引记录是否符合条件country!='吴'。很显然符合，所以将它发送到客户端，并且不释放对应的聚集索引记录上的锁
  • 步骤7，获取name值为’l刘备’的二级索引记录所在链表的下一个记录，也就是name值为’x荀彧’的二级索引记录
• 对name值为’z诸葛亮’的二级索引记录的加锁过程分析
  • 步骤2，为name列值为’z诸葛亮’的二级索引记录加S型Record Lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’z诸葛亮’的二级索引记录不符合索引条件下推的条件，由于它还不符合边界条件，所以就不再去找当前记录的下一条记录了。因此跳过步骤4和步骤5，直接向server层报告“查询完毕”信息
  • 步骤4，跳过
  • 步骤5，跳过
  • 步骤6，server层收到存储引擎报告的“查询完毕”信息，结束查询

隔离级别不小于REPEATABLE READ时的加锁过程分析

• 对name值为’l刘备’的二级索引记录的加锁过程进行分析
  • 步骤1，读取在(‘c曹操’，‘x荀彧’]扫描区间的第一条二级索引记录，也就是name值为’l刘备’的二级索引记录
  • 步骤2，为name列值为’l刘备’的二级索引记录加S型next-key lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’l刘备’的二级索引记录符合索引条件下推的条件
  • 步骤4，由于读取的是二级索引记录，所以需要对该表执行回表操作，找到相应的聚集索引记录，也就是number值为1的聚集索引记录，然后为该聚集索引记录加一个S型Record Lock。
  • 步骤5，形成扫描区间(‘c曹操’，‘x荀彧’]的边界条件是name>'c曹操' AND name<='x荀彧'，很显然name值为’l刘备’的二级索引记录符合该边界条件
  • 步骤6，server层继续判断name值为’l刘备’的二级索引记录对应的聚集索引记录是否符合条件country!='吴'。很显然符合，所以将其发送到客户端，并且不释放加再改记录上的锁。
  • 步骤7，获取name值为’l刘备’的二级索引记录所在链表的下一个记录，也就是name值为’s孙权’的二级索引记录
• 对name值为’s孙权’的二级索引记录的加锁过程进行分析
  • 步骤2，为name列值为’s孙权’的二级索引记录加S型next-key lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’s孙权’的二级索引记录符合索引条件下推的条件
  • 步骤4，由于读取的是二级索引记录，所以需要对该表执行回表操作，找到相应的聚集索引记录，也就是number值为20的聚集索引记录，然后为该聚集索引记录加一个S型Record Lock。
  • 步骤5，形成扫描区间(‘c曹操’，‘x荀彧’]的边界条件是name>'c曹操' AND name<='x荀彧'，很显然name值为’s孙权’的二级索引记录符合该边界条件
  • 步骤6，server层继续判断name值为’s孙权’的二级索引记录对应的聚集索引记录是否符合条件country!='吴'。很显然不符合，所以释放掉加在该二级索引记录以及对应的聚集索引记录上的锁
  • 步骤7，获取name值为’l刘备’的二级索引记录所在链表的下一个记录，也就是name值为’x荀彧’的二级索引记录
• 对name值为’x荀彧’的二级索引记录的加锁过程分析
  • 步骤2，为name列值为’x荀彧’的二级索引记录加S型next-key lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’x荀彧’的二级索引记录符合索引条件下推的条件
  • 步骤4，由于读取的是二级索引记录，所以需要对该表执行回表操作，找到相应的聚集索引记录，也就是number值为15的聚集索引记录，然后为该聚集索引记录加一个S型Record Lock。
  • 步骤5，形成扫描区间(‘c曹操’，‘x荀彧’]的边界条件是name>'c曹操' AND name<='x荀彧'，很显然name值为’x荀彧’的二级索引记录符合该边界条件
  • 步骤6，server层继续判断name值为’x荀彧’的二级索引记录对应的聚集索引记录是否符合条件country!='吴'。很显然符合，所以将它发送到客户端，并且不释放对应的聚集索引记录上的锁
  • 步骤7，获取name值为’l刘备’的二级索引记录所在链表的下一个记录，也就是name值为’x荀彧’的二级索引记录
• 对name值为’z诸葛亮’的二级索引记录的加锁过程分析
  • 步骤2，为name列值为’z诸葛亮’的二级索引记录加S型next-key lock
  • 步骤3，本语句的索引条件下推的条件为name>'c曹操' AND name<='x荀彧'，很显然name值为’z诸葛亮’的二级索引记录不符合索引条件下推的条件，由于它还不符合边界条件，所以就不再去找当前记录的下一条记录了。因此跳过步骤4和步骤5，直接向server层报告“查询完毕”信息
  • 步骤4，跳过
  • 步骤5，跳过
  • 步骤6，server层收到存储引擎报告的“查询完毕”信息，结束查询

上面两个实例都是以SELECT ... LOCK IN SHARE MODE;语句为例介绍记录加锁的。SELECT ... FOR UPDATE;语句的加锁过程与SELECT ... IN SHARE MODE语句类似，只不过为记录加的是X锁。

对于UPDATE语句来说，加锁方式与SELECT ... LOCK IN SHARE MODE;语句类似，不过，如果更新了二级索引列，那么所有被更新的二级索引记录在更新之前都需要加X型Record Lock。

对于DELETE语句来说，加锁方式与SELECT ... FOR UPDATE语句类似，只不过如果表中包含二级索引，那么二级索引记录在被删除之前都需要加X型Record Lock。

特殊情况

• 在REPEATABLE READ隔离级别下，当查询的索引含有唯一属性时，InnoDB存储引擎会对next-key lock进行优化，将其降级为Record Lock。
• 当隔离级别不大于READ COMMITTED时，如果匹配模式为精准匹配，则不会为扫描区间后面的下一条记录加锁。
• 当隔离级别不小于REPEATABLE READ时，如果匹配模式为精准匹配，则会为扫描区间后面的下一条记录加GAP锁。
• 当隔离级别不小于REPEATABLE READ时，如果匹配模式不是精准匹配，并且没有找到匹配的记录，则会为该扫描区间后面的下一条记录加next-key锁。
• 当隔离级别不小于REPEATABLE READ时，如果适用的是聚集索引，并且扫描的扫描区间是左闭区间，而且定位到的第一条聚集索引记录的number值正好与扫描区间中的最小值相同，那么会为该聚集索引记录加Record Lock。
• 无论是哪个隔离级别，只要是唯一性搜索，并且读取的记录没有被标记为“已删除”（记录头信息中的deleted flag为1），就为读取到的记录加Record Lock。
• 在扫描某个扫描区间中的记录时，一般都是按照从左到右的顺序进行扫描，但是有些情况下需要从右到左进行扫描。那么当隔离级别不小于REPEATABLE READ，并且按照从右到左的顺序扫描区间中的记录时，会给匹配到的第一条记录的下一条加GAP锁。

半一致性读的语句

半一致性读（Semi-Consistent Read）是一种夹在一致性读和锁定读之间的读取方式。

当隔离级别不大于READ COMMITTED且执行UPDATE语句时将使用半一致性读。所谓半一致性读，就是当UPDATE语句读取到已经被其他事务加了X锁的记录时，InnoDB会将该记录的最新提交版本读出来，然后判断该版本是否与UPDATE语句中的搜索条件相匹配。如果不匹配，则不对该记录加锁，从而跳到下一条记录；如果匹配，则再次读取该记录并对其加锁。

INSERT语句

遇到重复键
在插入一条新记录时，首先要确定这条新记录应该插入到B+树哪个位置。如果在确定位置的时候发现，现有记录的主键或者唯一二级索引列与待插入记录的主键或者唯一二级索引列相同。此时会报错。

• 对聚集索引中重复的键加S锁，不过加的锁在不同隔离级别下是不同的。
  • 当隔离级别不大于READ UNCOMMITTED时，加的是S型Record Lock
  • 当隔离级别不小于REPEATABLE READ时，加的是S型next-key锁
• 如果是唯一二级索引的列重复
  • 无论是哪个隔离级别，如果在插入新记录时遇到唯一二级索引列重复，都会对那条唯一二级索引记录加next-key锁

死锁

死锁是指两个或两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一个阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。

超时机制虽然简单，但是仅通过超时后对事务进行回滚的方式来处理，或者说根据FIFO的顺序选择回滚对象。这样就不太好了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会更多。

因此，除了超时机制，当前数据库还都普通采用wait-for graph（等待图） 的方式来进行死锁检测。

发生死锁的概率与以下几点因素有关

• 系统中事务的数量（n），数量越多发生死锁的概率越大
• 每个事务操作的数量（r），每个事务操作的数量越多，发生死锁的概率越大
• 操作数据的集合（R），越小则发生死锁的概率越大