MySQL中的事务与锁

1、SQL四种语言分类

DCL（Data Control Language)

GRANT 授权、REVOKE 取消授权

DDL（Data Definition Language）

CREATE、ALTER、DROP、TRUNCATE、COMMENT、RENAME
DDL一般需要对表加写锁，DDL不需要commit，会自动隐式提交，因此不能回滚，DDL语句执行时不会生成undo log

DML（Data Manipulation Language）

INSERT、UPDATE、DELETE、MERGE、CALL、EXPLAIN PLAN、LOCK TABLE
MySQL不认为SELECT属于DML，但SELECT ... FOR SHARE/UPDATE从锁的角度可以认为是DML。
DML statements for an InnoDB table operate in the context of a transaction, so their effects can be committed or rolled back as a single unit.
SELECT有时会被归纳为DQL (Data Query Language)

TCL（Transaction Control Language

COMMIT、ROLLBACK、SAVEPOINT、SET TRANSACTION

2、事务隔离级别

数据库事务的ACID四大特性

  • Atomicity原子性
    事务的所有SQL作为原子单位执行，要么全部执行成功，要么全部不执行。如果事务执行成功，事务的所有操作需要全部应用到数据库，如果某条SQL失败导致事务回滚，已经执行成功的SQL需要全部撤销，整个事务不能对数据库产生任何影响。
  • Consistency一致性
    一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，事务执行之前和执行之后都是处于一致的状态，数据库的完整性约束没有被破坏。A账户只要减去了100，B账户则必定加上了100，所有账户的总和是不变的；
  • Isolation隔离性
    如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离，不能被其他事务的操作所干扰，并发事务之间应该是互相察觉不到的，事务提交前对其他事物不可见。
  • Durability持久性
    事务一旦提交，对数据库的修改就是永久的，持久化存储的，即便是数据库遇到故障重启的情况下也不会丢失已提交事务的操作。

2.1、并发处理带来的问题

相对于串行处理来说，并发事务处理能够大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会因隔离级别不同带来一些如下问题：

丢失更新：
多个事务相继更新同一行，并基于最初查询出来的值计算后更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：最后一个事务的更新覆盖了之前其他事务的更新，行记录最终的值是最后一个事务更新后的值。例如：
1、事务A将行记录更新为V1，但并未提交
2、事务B将行记录更新为V2，但并未提交
3、事务A提交
4、事务B提交，提交后行记录最终的值是V2。

另一种丢失更新是：
1、事务A从数据库查询银行卡余额为1000，写入本地缓存中
2、事务B从数据库查询银行卡余额为1000，写入本地缓存中
3、事务A从本地缓存读出余额，减去800后，将新的余额200更新到数据库，然后事务A提交
4、事务B从本地缓存读出余额，减去300后，将新的余额700更新到数据库，然后事务B提交。
最终数据库的余额为700。

脏读：
一个事务对一条记录做修改后，在这个事务提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务就可以读取这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系，这种现象被称为“脏读”

不可重复读：
一个事务在读取某些数据后的某个时间，再次读取之前读过的数据，却发现第二次读出的数据相比于第一次已经发生了改变(update)、或者某些记录已经被删除了(delete)，这种现象被称为“不可重复读”。
不可重复读是针对同一条记录的，发现第二次读的结果和第一次不一样，或者被删除了

幻读：
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象被称为“幻读”
select * from t where id >= 1 and id <= 10; 第一次读出了9条，第二次读出了10条，多出来的一条是幻读。

2.2、事务隔离级别定义

事务隔离性要求系统必须保证事务不受其他并发执行的事务的影响。隔离级别规定了一个事务中所做的修改，在哪些事务内和事务间是可见的，哪些是不可见的，从而一定程度上解决并发带来的数据问题。较低的隔离通常可以执行较高的并发，系统的开销也比较低。

为了解决“隔离”和“并发”的矛盾，MySQL实现了ISO/ANSI SQL92定义的4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用根据自己的业务逻辑需求，通过选择不同隔离级别来平衡“隔离”和“并发”的矛盾。

隔离级别 含义 读数据一致性 脏读 不可重复读 幻读 MVCC 是否支持间隙锁
读未提交（Read Uncommitted） 事务中的修改，即使没有提交，对其他事务都是可见的 最低级别，只能保证不读取物理上损坏的数据 是 是 是 没有 不支持
读已提交（Read Committed） 事务从开始到提交之前，所做的修改对其他事务都不可见 语句级 否 是 是 有，每次一致性非锁定读时重新生成ReadView 不支持，只有行锁
可重复读（Repeatable Read） 同一事务中多次读取同样的记录结果是一致的 事务级 否 否 是 有，第一次一致性非锁定读时生成ReadView，后续的一致性非锁定读复用第一次生成的ReadView 支持行锁和间隙锁
可序列化（Serializable） 在读取的范围内加行锁和间隙锁，强制事务串行执行 最高级别，事务级 否 否 否 没有，所有读操作自动加上for share 支持行锁和间隙锁

2.3、事务隔离级别实现

数据库实现事务隔离的方式，基本上分为以下两种：

2.3.1、基于锁的并发控制，Lock-Based Concurrency Control

读取数据之前，对其加锁，阻止其他事务对数据进行修改。加锁的语句：

  • 锁定读(Locking Read)
    select * from t where id = 1 for share/update，锁定读不生成和使用ReadView，总是会读最新的数据。
事务A 事务B
begin;
select price from products where id = 1;
//有这条记录，price 是 5
// 可重复读下的第一次一致性非锁定读会生成ReadView
begin;
delete from products where id = 1;
commit;
select price from products where id = 1;
// 有这条记录，price 还是5
//可重复读下的一致性非锁定读会复用ReadView
select price from products where id = 1 for share;
//锁定读不使用ReadView，会读最新的数据，此时显示这条记录被删除了
// 如果事务B未提交，这条SQL会被block
commit;
  • insert、update、delete， 总是修改最新的行记录，加写锁，本事务未提交前其他事务不能修改或者加读锁
2.3.2、基于多版本的并发控制协议—MVCC (Multi-Version Concurrency Control)

MVCC默认使用一致性非锁定读 (Consistent Nonlocking Reads)，MVCC只在读已提交和可重复读隔离级别下生效。读不加锁，如果想读的行已经有了写锁，说明有未提交的事务修改了该行数据，此时不会等待写锁的释放，会去读该行的undo log来恢复历史版本数据，因此读到的可能不是最新的数据。

MVCC使得大部分支持行锁的事务引擎不再单纯的使用行锁来进行数据库的并发控制，而是把数据库的行锁和行的多个版本结合起来，可以实现非锁定读，从而提高数据库的并发性能。
https://www.cnblogs.com/zoo-keeper/articles/16110267.html

3、锁的种类和原理

  • MySQL锁的实现是通过对聚簇索引或者辅助索引上的行记录加锁，具体锁哪些索引的记录是根据查询条件来决定的，锁不能加在undo log上。
  • innoDB的锁的持有单位是事务，锁的持续时间直到事物commit或rollback。
  • MySQL不同的存储引擎支持不同的锁机制，表级锁是在MySQL服务器层实现的，因此所有存储引擎都支持。MyISAM和MEMORY只支持表级锁（MyISAM引擎层也有表锁）；InnoDB既支持行级锁，也支持表级锁。innoDB的行级锁是innoDB引擎自己实现的。Microsoft SQL Server支持行锁、分页锁、键锁、表锁。
  • 事务是由存储引擎实现的，MyISAM引擎不支持事务，innoDB支持事务。

3.1、行级锁

  • 行级共享锁(S Lock)，提高相同行的读读并发和不同行的读写并发
    select * from t where id = 2 for share; 这条SQL使用锁定读(locking read)，锁定读一定会读最新的数据，而一致性非锁定读(MVCC)不一定读最新的数据
  • 行级排他锁(X Lock)，用来保证数据强一致。
    select * from t where id = 2 for update;

行级锁具有并发度高、锁冲突的概率低的优势。但由于粒度小，行锁成本高；

需要注意的是，在RR隔离级别下做更新时，如果查询条件所在的列没有索引，会进行全表扫描，会对聚簇索引上全部行记录和行记录之间的范围加锁，即锁表。

行级排他锁 行级共享锁
行级排他锁 不兼容 不兼容
行级共享锁 不兼容 兼容

3.2、表级锁

表锁分为表级读锁和表级写锁，具体语法是 LOCK TABLES XXX READ|WRITE。在MySQL5.7中，LOCK TABLES READ|WRITE 也是通过对表加MDL读锁或者写锁。

lock tables不仅限制了其他事务的读写，也限制当前事务接下来的读写
举个例子：lock tables t1 read,t2 write，本事务后面只能读t1,写t2, 不能写t1，也不能读写其他表。

一般认为表级锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server层管理的。需要注意以下两点：
1、需要设置autocommit=0，innodb_table_lock=1（也是默认设置），InnoDB层才能知道MySQL Server层加的表锁
2、COMMIT或ROLLBACK不能释放由LOCAK TABLES加的表级锁，必须主动用UNLOCK TABLES释放表锁。使用UNLOCK TABLES会隐含地提交事务，所以UNLOCK TABLES需要在事务结束的时候使用

例如：

SET AUTOCOMMIT= 0; // 等效于begin, start transaction
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

3.3、元数据锁（Mete Data Lock, MDL)

MySQL5.5.3引入了metadata lock， 主要是为了防止事务中DML和DDL等语句的并发，造成的数据不一致问题。其主要解决了2个问题，一个是事务隔离问题，比如在可重复读隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求（有了MDL锁，在会话A未提交前，会话B不能对表结构做修改）；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在应用binlog时，先应用alter，再应用update时就会出现复制错误的现象。

元数据锁是server层的锁，表级锁，当对一个表增删改查时，自动加MDL读锁；当对一个表做结构变更操作(ALTER TABLE，修改字段、索引)时，自动加MDL写锁，需要注意的是DDL操作一般会做全表扫描，因此对大表做DDL操作会阻塞请求较长时间。

事务一旦申请到MDL锁后，直到事务提交才会将锁释放。MDL加锁过程是系统自动控制，无法直接干预，在访问表的时候会自动加上。读读共享，读写互斥，写写互斥。申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，因此长事务会阻塞DDL获取MDL写锁，DDL等待MDL写锁时会阻塞后续所有的查询和DML语句获取MDL读锁。最好的是DDL获取MDL写锁时增加超时时间，超过这个时间主动放弃获取MDL写锁，但目前MySQL还不支持这个语法。另外一个常见的问题是，如果slave上有长事务，主库上的DDL操作在slave上应用时，由于获取不到MDL写锁会导致主丛延时增大。

3.4、意向锁

意向锁为表级别的锁

  • 表级意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加行级共享锁（或者共享间隙锁），事务如果想获得行级共享锁必须先获得表级意向共享锁。
  • 表级意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加行级排他锁（或者排他间隙锁），事务如果想获得行级排他锁必须先获得表级意向排他锁。

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
Table-level lock type compatibility is summarized in the following matrix.（加上自增长锁AI）

X IX S IS AI
X Conflict Conflict Conflict Conflict 不兼容
IX Conflict Compatible Conflict Compatible 兼容
S Conflict Conflict Compatible Compatible 不兼容
IS Conflict Compatible Compatible Compatible 兼容
AI 不兼容 兼容 不兼容 兼容 不兼容

表级意向锁不与行级锁和其他表级意向锁冲突，是为了保证行级共享锁带来的相同行的读读并发和不同行的读写并发。

表级意向锁只与表级读写锁冲突，是为了提高其他需要对表加读写锁的操作(比如DDL命令或者LOCK TABLE命令)判断锁冲突时的效率。

  • 事务 A 获取了某一行的排他锁 SELECT * FROM users WHERE id = 6 FOR UPDATE; 但并未提交：
  • 事务 B 如果想要获取 users 表的表级读锁：LOCK TABLES users READ; 如果没有表级意向锁，此时事务B需要判断下列条件是否满足：
    1. 没有其他事务获得表users的表级排他锁
    2. 没有其他事务获得表users中任意行的行级排他锁
      第一个条件比较好判断，为了检测第二个条件，事务 B 必须去检测表中的每一行是否存在行级排他锁，这是一个效率很差的做法，但是有了表级意向锁之后，条件2就变成：
    3. 没有其他事务获得表users的表级意向排他锁
      这样事务B的判断就会很简单。

参考 https://blog.51cto.com/u_15127568/2713168

3.5、间隙锁 Gap Lock

间隙锁只用于事务隔离级别为可重复读(RR)和可序列化中，用于解决幻读。隔离级别为读已提交(RC)，只会对读取到的记录加行锁，不会加间隙锁

间隙锁也是加在行记录上的，表示锁定这个行记录左边的开区间范围（不包括行记录本身）

间隙锁虽然有读写之分(shared gap lock, exclusive gap lock)，但作用都是一样的，间隙锁相互不冲突，不同事务可以持有相同范围的间隙锁，它的唯一作用就是阻止在间隙内插入新的行。

if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

READ COMMITTED隔离级别下，除了唯一键和外键的约束检查需要使用间隙锁外，其他操作不会使用间隙锁，事务中的语句执行完成后，InnoDB就会把不满足条件的行上的行锁释放，不需要等到事务提交。
There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition

3.6、临键锁 Next-Key Lock

临键锁只用于事务隔离级别为可重复读(RR)和可序列化中，它是RR模式下基本的加锁粒度。

临键锁也是加在行记录上，是行锁和它左边区间的间隙锁的组合（左开右闭）

申请Next-Key锁分为两段，先获取间隙锁，然后获取行锁。如果获取行锁时被阻塞，事务此时可能已经获取到间隙锁，参考后面的死锁情况1。

3.7、自增长锁

MySQL实战45讲/40 39 | 自增主键为什么不是连续的？

自增长锁是特殊的表级排他锁，专门针对事务插入AUTO_INCREMENT类型的列。
innoDB的表只能有一列被设置为AUTO_INCREMENT，且必须是索引的第一列

原理：MySQL5.7之前，每张表的自增长值并不在磁盘上进行持久化，而是每次InnoDB存储引擎启动时，通过执行：SELECT MAX(auto_inc_col) FROM T FOR UPDATE; 取出自增列的最大值，然后存到缓存（内存）中，这样MySQL重启可能会修改一个表的自增长值，比如将当前最大id的行删除然后重启。插入操作如果未指定主键，会获取这个自增长值，加1赋予自增长列。如果自增长值达到最大值，则不会再增长，会获取到重复的值，插入时主键会报duplicate key error。如果插入时指定了主键，如果指定的主键>=当前自增长值，新的自增值就是指定的主键；否则自增值不变。在MySQL 8.0以后，将自增值的变更记录在redo log中，重启的时候依靠redo log恢复重启之前的值，确保重启前后一个表的自增值不变。在MyISAM引擎里面，自增值是被保存在数据文件上的。

注意自增主键不一定是连续的，但一定是递增的。

对于上图中的SQL，第一次执行 insert into Test value(null, 1); 成功插入主键为1的数据，插入后表的自增长值为1；第二次执行 insert into Test value(null, 1); 获取并累加自增长值为2，修改待插入数据的主键为2，这条SQL等效于 insert into Test value(2, 1); 由于列a是唯一键，这条SQL执行失败，但此时表的自增长值已经为2。
第三次执行 insert into Test value(null, 2); 成功插入主键为3的数据。

为了提高插入的性能，在MySQL5.1版本之后，对于普通的insert语句，自增锁每次申请完马上释放，不是在一个事务完成后才释放，以便允许别的事务再次申请。但即便如此，高并发插入时自增长锁仍然可能是瓶颈。

举个例子：假设Test表有主键id和唯一索引列a，有两个并行事务A和B，为了避免两个事务申请到相同的主键id，必须要加自增锁顺序申请

事务A 事务B
begin;
insert into Test values(null,1);
//插入的行(1,1)
begin;
insert into Test values(null,2);
commit;
//插入的行(2,2)
commit;

事务A执行insert语句时，申请到主键id=1之后立即释放自增锁，没有等事务A提交之后释放，所以事务B的插入不被阻塞。

对于 insert into ... select语句，在innodb_autoinc_lock_mode = 1下（默认设置），会在这个语句执行完成后才释放自增长锁，执行这条SQL期间，其他事务不能在目标表中通过生成自增长主键进行插入。但是在binlog_format = row时，可以将innodb_autoinc_lock_mode 设为 2，实现每次申请完自增长锁就释放，提高并发性，且不会有主从同步问题。另外在可重复隔离级别下，如果binlog_format=statement，会对源表中读到的行记录加Next-Key读锁。

3.8、外键中的锁

MySQL 支持外键的存储引擎只有 InnoDB， 在创建外键的时候， 要求父表对应的列必须有索引 ，子表的外键列如果没有显式地加索引，会自动对外键列创建索引 。

另外，在子表的外键列插入或更新之前，需要先使用select for share的方式查询父表的列中是否有对应的值，如果有的话，会对该父表的行记录加读锁；如果没有，子表的数据插入失败。

从父表中删除记录时，也需要先用 select for share 查询子表是否引用了父表中的记录，如果子表引用了，则删除失败，如果子表没有引用，会对子表加间隙锁，然后从父表删除记录，保证父表的记录删除后，子表中一定没有记录引用这条已经被删除的记录。

事务A 事务B
begin;
delete from parent where id = 3;
// 事务A没有commit，持有parent表id = 3的行级写锁
begin;
insert into child values(1, 3);
// child表的第二列是外键，由于事务A没有commit，这条语句申请parent表的锁时会被block

3.9、插入意向锁

插入意向锁是在INSERT操作插入一条记录前，需要先获得的一种特殊的间隙锁(lock_mode X locks gap before rec insert intention)，用以表示插入意向。插入意向锁之间不会互相冲突，当多个事务在同一区间（gap）内插入多条数据时，事务之间不需要互相等待。插入意向锁只会和间隙锁冲突。

INSERT 语句在执行插入之前，需要先获取插入的gap右边行记录上的插入意向锁：
1、如果不是唯一索引，一定可以成功插入（两个未提交事务可以往非唯一索引中插入相同key的行记录），然后获得新插入的行记录上的行级写锁（需要先获得表级意向排他锁）。
2、如果是唯一索引，插入前需要进行 Duplicate Key 判断：1、如果存在相同 Key，则当前事务会尝试获取已有Key的Next-Key读锁，可能需要等待（如果这个相同Key上有行级写锁，说明这个key可能会被回滚或者删除），等到成功插入后，会获得这个新插入行记录上的行级排它锁； 2、如果不存在相同的key，不会尝试获取Next-Key锁，成功插入后，会获得这个新插入行记录上的行级排它锁；

需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

3.9.1、为什么需要插入意向锁

1、在没有插入意向锁之前，insert操作申请的是一般的间隙锁，由于间隙锁会阻止其他事务在相同区间的插入，这样会影响并发插入，有了插入意向锁，insert申请的是插入意向锁，就可以提高并发插入。
2、插入意向锁只会和相同gap内的间隙锁冲突。因此，如果准备插入的gap中有间隙锁，那进行插入的事务在获取插入意向锁时就会被阻塞而等待。插入意向锁的这个性质和间隙锁一起实现了“间隙锁能阻止区间内其他事务的插入”的功能。

3.10 全局锁

全局锁就是对整个数据库实例加读锁，保证整个库处于只读状态，所有的DML（数据的增删改）和 DDL（修改表结构定义）都会被阻塞，MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

全局锁的典型使用场景是做全库逻辑备份，把整个库每个表都select出来存成磁盘。做全库备份时需要对整个数据库加锁的原因是保证数据的一致性，如果此时已经备份过表A，开始备份表B，如果一个事务此时修改了表A和表B，那最终的备份中就不会有表A的最新修改，但是却有表B的最新修改，可能会破坏表A和表B之间的数据一致性。

但是对于innoDB这种支持可重复读隔离级别的事务引擎，做逻辑备份时不用使用FTWRL，可以在开始备份时启动事务，生成ReadView，确保在整个备份过程读到的数据都是处于一致的状态（ReadView创建时的状态）。这样备份过程中，其他事务的DML操作可以不受影响。但是对于不支持事务（比如MyISAM）或者不支持可重复读隔离级别的引擎，就得使用FTWRL。

4、 加锁和释放锁的时机

二阶段锁，Two-Phase Locking

锁操作分为两个阶段：加锁阶段与解锁阶段。
加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。

引入2PL是为了保证事务的隔离性，即多个并发事务在发生资源冲突的情况下等同于串行的执行。

通过此项原理，我们需要把最热点的记录，即锁的冲突可能性最高的记录， 放到事务最后，这样可以显著的提高吞吐量。因为如果一个事务在最开始先获取热点记录的锁，然后再执行其他操作，最后事务提交释放锁，这样这个事务持有热点锁的时间就会很长，容易让其他事务等待。

5、innoDB锁算法

RR隔离级别下的加锁原则

判断innoDB对哪些数据加锁，最好是结合MVCC中幻读的例子，看需要怎么加锁解决幻读

原则1：可重复读中加锁的基本单位是next-key lock，即前开后闭的区间
原则2：查找过程中访问到的对象（即便不符合where条件）会加锁，没有访问到的对象不加锁，比如覆盖索引。
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters
原则3: 在索引中查询时会一直查找到右边第一个不满足条件的行记录

// mysql-8.0.15-macos10.14-x86_64   Repeatable Read
// select * from performance_schema.data_locks;
// SHOW ENGINE INNODB STATUS;

CREATE TABLE `table_int3` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c1` (`c1`),
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into table_int3 values (1, 10, 100), (7, 70, 700), (13, 130, 1300), (20, 200, 2000);

+----+-----+------+
| id | c1  | c2   |
+----+-----+------+
|  1 |  10 |  100 |
|  7 |  70 |  700 |
| 13 | 130 | 1300 |
| 20 | 200 | 2000 |
+----+-----+------+

5.1、等值查询

5.1.1、等值查询且有结果

1、非唯一索引上的等值查询，如果查到了行记录，会对该行记录加Next-Key，并对右边的行记录加间隙锁，等于锁住该行记录以及左右两边的开区间。需要锁住左右两边区间的原因是：如果后面再插入c1 = 70的行记录，新的行记录可能插在左边或者右边

  • select id from table_int3 where c1 = 70 for share; // 行记录存在，覆盖索引
    存在的锁：

    • 1、idx_c1索引c1 = 70的行记录的Next-Key锁

    • 2、idx_c1索引c1 = 130的行记录的间隙锁
      目的是阻止idx_c1索引上插入c1 = 70的新的行记录，但显然间隙锁范围有点大

    • 这条SQL只对idx_c1中的行记录加锁，因此另一个事务的 update table_int3 set c2 = 701 where id = 7 可以执行，因为这个update没有修改idx_c1，update需要的锁：

      • 1、聚簇索引id = 7行记录的行级写锁，聚簇索引是in-place更新；2、idx_c2上需要删除的c2 = 700行记录的行级写锁；3、idx_c2上新插入的c2 = 701行记录的行级写锁

    • 但另一个事务的 update table_int3 set c2 = 701 where c1 = 70 会被block，update需要的锁：与select ... where c1 = 70 for update类似

      • 1、idx_c1索引c1 = 70的行记录的Next-Key锁，会被block；2、idx_c1索引c1 = 130的行记录的间隙锁； 3、聚簇索引id = 7行记录的行级写锁，聚簇索引是in-place更新；4、idx_c2上需要删除的c2 = 700行记录的行级写锁；5、idx_c2上新插入的c2 = 701行记录的行级写锁。
      • 如果update where c1 = 70 不对idx_c1索引加锁，会出现如下情况：
1、update table_int3 set c2 = 701 where c1 = 70  // 0 rows affected，假如最开始idx_c1中没有c1 = 70的行记录
2、select c1 from table_int3 where c1 = 70 for share // 返回了一行。因为idx_c1中没有锁，在这之间其他事务可以插入数据并且commit。
  • select * from table_int3 where c1 = 70 for share; // 行记录存在，不是覆盖索引
    • 1、idx_c1索引c1 = 70的行记录的 Next-Key锁
    • 2、idx_c1索引c1 = 130的行记录的间隙锁
    • 3、聚簇索引id = 7的行记录的行级读锁
      如果不是覆盖索引，需要回表查

2、优化：如果是唯一索引上的等值查询，且查到了行记录，可以利用唯一索引不重复的特性，将Next-Key Lock退化为行级锁，减少加锁的范围。但如果唯一索引是由多个列组成，这个优化仅在对这多个列都使用等值查询且查到记录时才生效。

  • select * from table_int3 where id = 7 for share; // 行记录存在
    • 1、表级意向读锁（后续忽略表级意向锁）
    • 2、聚簇索引id = 7的行记录的行级读锁
5.1.2、等值查询但没有结果

索引（不论是否唯一索引）上的等值查询，如果没有查到行记录，会对查找条件右边第一个行记录加间隙锁（不是Next-Key Lock），锁住查找条件所在的开区间

  • select * from table_int3 where id = 5 for share; // 行记录不存在，唯一索引

    • 1、聚簇索引id = 7的行记录的间隙锁

  • select * from table_int3 where c1 = 50 for share; // 行记录不存在，非唯一索引

    • 1、idx_c1索引c1 = 70的行记录的间隙锁
      虽然不是覆盖索引，但因为行记录不存在，不需要回表查数据，所以聚簇索引上没有锁。
5.1.3 间隙锁扩大问题

由于只是加间隙锁而没有行锁，另一个事务B可以删除间隙锁所在的行记录并提交。如果事务B的undo log可以清理，在purge线程清理掉这个软删的行记录时，purge线程会把该删除行记录上的间隙锁放到的下一个行记录中。但如果删除的事务没有提交，此时间隙锁还是会在原来的行记录上，这个行记录只是被软删而已（因为该事务的undo log还不能被清理，则该删除的行记录也不能被清理）。

  • select * from table_int3 where id = 12 for share;
    事务A先在聚簇索引id = 13行记录上加间隙锁
  • delete from table_int3 where id = 13;
    • 事务B可以删除聚簇索引 id = 13行记录和对应辅助索引上的行记录，事务B提交后，事务A继续持有聚簇索引id = 13行记录上的间隙锁，等到purge线程清理掉事务B的undo log后，事务A不会再持有聚簇索引id = 13行记录上的间隙锁，因为这条行记录已经被purge线程清理掉了，事务A会持有聚簇索引id = 20行记录上的间隙锁。
    • 如果在事务B创建之前事务A中出现过一致性非锁定读，则事务B提交后，由于事务B的undo log不能被清理，事务A会一直持有聚簇索引id = 13行记录上的间隙锁，间隙锁不会移动。
5.1.4 间隙锁拆分现象

如果一个事务使用间隙锁或Next-Key锁定了如下范围 (7, 13)，这个事务再往这个范围插入记录10时，其锁定的范围变成了(7, 10], (10, 13)，这两个范围分别对应一把锁

5.2、范围查询（不论是否唯一索引）

对索引中满足范围条件的行记录（可能没有）和向右查找到的第一个不满足条件的行记录（一定有）加Next-Key锁，锁住查找条件所在的区间。对于范围条件，InnoDB要去找“第一个值”的时候，是按照等值去找的，用的是等值判断的加锁规则；找到第一个值以后，要在索引内找“下一个值”，用的是范围查找的加锁规则。

  • select id from table_int3 where id >= 7 and id < 13 for share; // 范围中有一个行记录
    对于范围中左边的等号，等效于 id = 7 和 id > 7 and id < 13 两个查询条件的联合加锁结果，不能把 id = 7 看作是范围条件的一部分

    • 1、聚簇索引 id = 7 的行记录的行级读锁
    • 2、聚簇索引 id = 13 的行记录的Next-Key锁

  • select * from table_int3 where id >= 7 and id <= 13 for share; // 范围中有两个行记录
    对于范围中右边的等号，不能看作两个查询条件的联合加锁结果，应该把右边的id = 13看作是范围的一部分，由于范围查询会向右查找到的第一个不满足条件的行记录为止，因此加锁的是：

    • 1、聚簇索引 id = 7 的行记录的行级读锁
    • 2、聚簇索引 id = 13 的行记录的Next-Key锁
    • 3、聚簇索引 id = 20 的行记录的Next-Key锁（因为这里id是唯一索引，这个锁其实不需要，这个是MySQL的一个bug：“唯一索引上的范围查询，会访问到不满足条件的第一个值为止。”）

  • select id from table_int3 where c1 > 150 for share; // 辅助索引，范围中有一个行记录，覆盖索引

    • 1、idx_c1索引 c1=200 行记录上的Next-Key锁
    • 2、idx_c1索引supremum 行记录上的Next-Key锁

  • select * from table_int3 where c1 > 50 and c1 < 100 for share; // 辅助索引，范围中有一个行记录，不是覆盖索引

    • 1、idx_c1索引 c1=70 行记录上的Next-Key锁
    • 2、idx_c1索引 c1=130 行记录上的Next-Key锁
    • 3、聚簇索引id = 7行记录的行级读锁

  • select id from table_int3 where c1 >= 70 and c1 <= 130 for share; // 辅助索引，范围中有两个行记录，覆盖索引
    范围中左边的等号看作单独的等值查询的结果，然后取并集。范围中右边的等号看作是范围的一部分，由于范围查询会向右查找到的第一个不满足条件的行记录为止，因此加锁的是：

    • 1、idx_c1索引 c1=70 行记录上的Next-Key锁
    • 2、idx_c1索引 c1=130 行记录上的Next-Key锁
    • 3、idx_c1索引 c1=200 行记录上的Next-Key锁（因为这里是非唯一索引，所以这个锁是需要的，但这个锁可以优化成对 c1=200 行记录加间隙锁）

5.3、select ... for update

在上述基于for share的规则上，对于等值查询或者范围查询，如果是通过辅助索引进行查询的，并且辅助索引中有满足条件的行记录（其实只要对辅助索引加了行锁，就会对聚簇索引中对应行加行锁），即便是在覆盖索引的情况下，也需要对聚簇索引上的行记录加行级写锁，但不需要锁住其他的辅助索引。这种加锁规则会导致覆盖索引失效。

一定需要对聚簇索引行记录加锁的原因是：

  1. for share其实是告诉MySQL，我锁住只是为了读，我后面不会修改我锁住的数据，所以MySQL不需要锁聚簇索引的行记录。
  2. for update 是告诉MySQL，我锁住这些数据是为了写，我后面会修改我刚刚读过的数据，由于修改一定会导致聚簇索引的行记录发生变化，所以需要对聚簇索引加写锁（如果for update不加锁，那后面update的时候可能会被block），但修改不一定导致该行记录对应的其他的辅助索引的行记录发生变化，所以不需要锁住其他的辅助索引。

示例分析

  • select id from table_int3 where c1 = 70 for update; // 等值查询且有结果，覆盖索引

    • 1、idx_c1索引c1 = 70的行记录的Next-Key锁
    • 2、idx_c1索引c1 = 130的行记录的间隙锁
    • 3、聚簇索引id = 7行记录的行级写锁

  • select id from table_int3 where c1 > 50 and c1 < 100 for update; // 范围查询，覆盖索引，有一条记录满足条件

    • 1、idx_c1索引c1 = 70的行记录的Next-Key锁
    • 2、idx_c1索引c1 = 130的行记录的Next-Key锁
    • 3、聚簇索引id = 7行记录的行级写锁
    • 4、聚簇索引id = 13行记录的行级写锁
      因为已经对idx_c1索引c1 = 130的行记录加了行级写锁，所以这里对聚簇索引上对应的行记录加写锁，但个人觉得对这个行记录加锁没必要

  • select id from table_int3 where c1 = 50 for update; // 等值查询，没有结果，覆盖索引

    • 1、idx_c1索引c1 = 70的行记录的间隙锁

  • select * from table_int3 where id > 5 and id < 10; // 聚簇索引上的范围查询，有一条记录满足条件

    • 1、聚簇索引id = 7行记录的Next-Key锁
    • 2、聚簇索引id = 13行记录的Next-Key锁

5.4、insert

insert没有where条件，只会对聚簇索引和辅助索引中新插入的行记录加行级写锁

insert操作会先获得表级意向排他锁，插入意向锁。

5.5、delete

delete的执行分两步，首先根据where条件进行查找，查找过程中的加锁规则和select for update相同，然后删除查找到的记录(设置 deleted_bit = 1)，第二步会对聚簇索引和所有辅助索引中需要删除的行记录加行级写锁

  • delete from table_int3 where id > 5 and id < 10;
    首先是类似select for update 的加锁规则

    • 1、聚簇索引id = 7行记录的Next-Key锁
    • 2、聚簇索引id = 13行记录的Next-Key锁
      然后是对辅助索引中需要删除的行记录加行级写锁
    • 3、idx_c1索引c1 = 70的行记录的行级写锁
    • 4、idx_c2索引c2 = 700的行记录的行级写锁

  • delete from table_int3 where c1 = 70; // 等值查询且有结果

    • 1、idx_c1索引c1 = 70的行记录的Next-Key锁
    • 2、idx_c1索引c1 = 130的行记录的间隙锁
    • 3、聚簇索引id = 7行记录的行级写锁
    • 4、idx_c2索引c2 = 700行记录的行级写锁
    • 另一个事务如果执行 delete from table_int3 where c2 = 1300 是可以正常commit的，commit后idx_c1索引c1 = 130的行记录的间隙锁会消失，变成idx_c1索引c1 = 200的行记录的间隙锁，发生间隙锁扩大现象。

  • delete from table_int3 where c1 > 50 and c1 < 100; // 范围查询，有一条记录满足条件

    • 1、idx_c1索引c1 = 70的行记录的Next-Key锁
    • 2、idx_c1索引c1 = 130的行记录的Next-Key锁
    • 3、聚簇索引id = 7行记录的行级写锁
    • 4、聚簇索引id = 13行记录的行级写锁（与select for update类似，因为已经对idx_c1索引c1 = 130的行记录加了行级写锁，所以这里对聚簇索引上对应的行记录加写锁，但个人觉得对这个行记录加锁没必要）
    • 5、idx_c2索引c2 = 700行记录的行级写锁
    • 6、这条SQL没有对idx_c2索引c2 = 1300行记录的行级写锁。因为这条记录不会被删除，没有必要加锁。其他事务的select c2 from table_int3 where c2 = 1300 for share可以执行

5.6、update

update的执行分两步，首先根据where条件进行查找，查找过程中的加锁规则和select for update相同，然后对查找到的行记录进行修改，第二步会对聚簇索引中需要修改（区分是否修改主键）的行记录加行级写锁，以及辅助索引中删除和插入的行记录加行级写锁，因为辅助索引不能in-place更新。

  • update table_int3 set id = 10 where id = 7;
    修改主键，聚簇索引和辅助索引都是先删除后插入

    • 1、聚簇索引id = 7行记录的行级写锁
      另一个事务如果执行 select id from table_int3 where id = 7 for share 会被block
    • 2、聚簇索引id = 10行记录的行级写锁
      另一个事务如果执行 select id from table_int3 where id = 10 for share 会被block
    • 3、idx_c1索引c1 = 70的行记录(id = 7)的行级写锁
    • 4、idx_c1索引c1 = 70的行记录(id = 10)的行级写锁
    • 5、idx_c2索引c2 = 700的行记录(id = 7)的行级写锁
    • 6、idx_c2索引c2 = 700的行记录(id = 10)的行级写锁

  • update table_int3 set c1 = 80 where c1 = 70;
    通过辅助索引修改非主键列，聚簇索引in-place更新，辅助索引先删除再插入
    这里判断加锁范围可以分两步，首先类似select for update where c1 = 70的加锁范围：

    • 1、idx_c1索引c1 = 70的行记录的Next-Key锁
    • 2、idx_c1索引c1 = 130的行记录的间隙锁
    • 3、聚簇索引id = 7的行记录的行级写锁
      然后是update在idx_c1上新插入的行记录(c1 = 80, id = 7)的行级写锁
    • 4、idx_c1索引c1 = 80的行记录的Next-Key锁
      新插入的行记录本来只有行级写锁，但插入的位置在idx_c1索引c1 = 130的间隙锁范围内，所以新插入的行记录上也有间隙锁，c1 = 130的间隙锁还是存在。

5.7 limit 语句的加锁

mysql> select * from table_int3 order by c1;
+----+-----+------+
| id | c1  | c2   |
+----+-----+------+
|  1 |  10 |  100 |
|  7 |  70 |  700 |
| 10 |  70 |  700 |
| 15 |  70 |  700 |
| 13 | 130 | 1300 |
| 20 | 200 | 2000 |
+----+-----+------+
6 rows in set (0.00 sec)
  • delete from table_int3 where c1 = 70 limit 2; // c1 = 70共3个记录，删除前两个记录。因为limit的原因，遍历完idx_c1索引c1 = 70的前两个行记录后语句就结束了
    • 1、idx_c1索引c1 = 70, id = 7行记录的Next-Key锁
    • 2、idx_c1索引c1 = 70, id = 10行记录的Next-Key锁
    • 3、聚簇索引id = 7行记录的行级写锁
    • 4、聚簇索引id = 10行记录的行级写锁
    • 5、idx_c2索引c2 = 700, id = 7行记录的行级写锁
    • 6、idx_c2索引c2 = 700, id = 10行记录的行级写锁

5.8 order by desc导致加锁方向的变化

  • select * from table_int3 where id >= 7 and id <= 13 order by id desc for share; // 范围中有两个行记录
    因为是按id的降序排列，会先找到id = 13的记录，然后从13开始向id递减方向遍历，因此加锁的方向是索引递减方向。这里应该把范围左边id = 7的条件看作是范围的一部分，范围查询会向左查找直到第一个不满足条件的行记录为止，因此加锁的是：

    • 1、聚簇索引 id = 20 的行记录的间隙锁
      不知道为什么要加这个锁？个人觉得因为id是唯一索引，这个锁没有必要，如果是一般索引，那这个间隙锁是需要的
    • 2、聚簇索引 id = 13 的行记录的Next-Key锁
    • 3、聚簇索引 id = 7 的行记录的Next-Key锁
    • 4、聚簇索引 id = 1 的行记录的Next-Key锁
      • 因为这里id是唯一索引，这个锁其实不需要，这个是MySQL的一个bug：“唯一索引上的范围查询，会访问到不满足条件的第一个值为止。”
      • 如果条件是 where id > 7 and id <= 13 order by id desc for share; 则不会对 聚簇索引 id = 1 的行记录加Next-Key锁，因为遍历到id = 7的行记录时就停止了

  • select c1 from table_int3 force index(idx_c1) order by c1 desc limit 1 for share;

    • 1、聚簇索引 supremum 行记录的Next-Key锁
      这个锁是需要的
    • 2、聚簇索引 id = 20 的行记录的Next-Key锁
      加锁的基本单位是Next-Key锁

5.9、全表扫描

不管是等值查询还是范围查询，如果执行计划为全表扫描或者全索引扫描，那会对聚簇索引或者辅助索引的所有行记录（包括supremum行记录）加Next-Key锁，类似锁表

注：在实际的实现中，MySQL有一些改进，全表扫描时，如果发现某条行记录不满足where条件，会调用unlock_row方法，对不满足条件的行记录放锁 (违背了Two-Phase Locking原则)。保证最后只会持有满足条件的行记录上的锁，但是对每条行记录的加锁操作还是不能省略的，只是变成加锁后发现不满足条件立马放锁。这个特性叫semi-consistent read，需要设置 innodb_locks_unsafe_for_binlog参数，但是semi-consistent read会带来其他问题，不建议使用。

5.10、事务对删除的行记录加锁

正常情况下，事务不应该对已经删除的行记录加锁，但是还是有特殊情况
如果事务A将某些行记录删除(deleted_mark = 1)：

  • 在一致性非锁定读时，MVCC不会考虑行记录上的deleted_mark是否为1，也不会考虑行记录上的锁，只会基于ReadView判断是否需要使用undo record恢复出历史可见版本，再通过恢复出来的历史可见版本的deleted_mark 判断是否出现在最终结果中
  • 在锁定读时，如果事务A未提交，此时事务A至少会持有删除的行记录的行级写锁（可能还有间隙锁），另一个事务B执行时，这个删除的行记录对事务B是可见的，如果满足事务B的where条件（加锁条件），事务B需要对这条删除的行记录加锁，此时会发生锁等待。如果事务A提交了，事务B会立即获得这个已经删除行记录上的锁，等到这个行记录被purge线程清理后， 事务B不再持有删除行记录上的锁，purge线程会将这个锁转换成其他地方的锁。
+----+-----+------+
| id | c1  | c2   |
+----+-----+------+
|  1 |  10 |  100 |
|  7 |  70 |  700 |
| 13 | 130 | 1300 |
| 20 | 200 | 2000 |
+----+-----+------+
示例1 删除行记录的行锁转换成间隙锁
事务A 事务B
begin;
delete from table_int3 where id = 13;
// 事务A持有聚簇索引id = 13行记录的行级写锁，idx_c1索引 c1 = 130行记录的行级写锁， idx_c2索引 c2 = 1300行记录的行级写锁
begin;
select id from table_int3 where id = 13 for share;
// 因为事务A未提交，聚簇索引id = 13行记录虽然deleted_bit = 1，但仍然对事务B可见，事务B需要获得聚簇索引id = 13行记录的行级读锁，所以会被block
commit;
// 事务A 释放自己持有的锁		 事务B恢复执行，返回0行记录，事务B立即获得聚簇索引id = 13行记录的行级读锁，等到purge线程将这个行记录清理后，事务B会持有聚簇索引id = 20行记录的间隙锁
示例2 间隙锁扩大
事务A 事务C
begin;
delete from table_int3 where id = 13;
// 事务A持有聚簇索引id = 13行记录的行级写锁，idx_c1索引 c1 = 130行记录的行级写锁， idx_c2索引 c2 = 1300行记录的行级写锁
begin;
select id from table_int3 where c1 = 70 for share;
// 成功执行，返回一条记录
// 事务C持有idx_c1索引c1 = 70行记录的Next-Key锁，idx_c1索引c1 = 130行记录上的间隙锁（注意此时idx_c1索引c1 = 130的行记录的deleted_bit = 1）
commit;
// 事务A 释放自己持有的锁		 // 事务C继续持有idx_c1索引c1 = 130行记录上的间隙锁，等到purge线程清理掉事务A的undo log和删除的行记录后，事务C获得idx_c1索引c1 = 200行记录的间隙锁，发生间隙锁扩大现象

5.11、一个复杂的例子

引用自 https://cloud.tencent.com/developer/article/1033697
对于SQL：delete from t1 where pubtime > 1 and pubtime < 20 and userid = 'hdc' and comment is not null
在RR隔离级别的加锁范围如下所示（图中的锁范围不太准确）

SQL中的where条件的拆分结果为：

  • Index key：pubtime > 1 and puptime < 20。
    用于确定在idx_t1_pu索引上的查询范围。查询范围内和第一个不满足查询范围的行记录加Next-Key锁。
    注意这个例子中，对于辅助索引，不能只对满足 where pubtime > 1 and pubtime < 20 and userid = 'hdc' 条件的行记录加Next-Key锁，必须对辅助索引满足where pubtime > 1 and pubtime < 20（包括pubtime = 3的那条）条件的行记录加Next-Key锁。
  • Index Filter：userid = ‘hdc’ 。
    用于在idx_t1_pu索引上进行过滤，但不属于Index Key。
    在MySQL 5.6版本之前，由于不支持Index Condition Pushdown(ICP)，Index Filter的生效时机类似Table Filter，此时需要对聚簇索引中id = 4的行记录加行级写锁。
    在5.6后支持了ICP，Index Filter可以在索引上过滤，此时不需要对聚簇索引中id = 4的行记录加行级写锁，但辅助索引中pubtime = 3的行记录还是会加Next-Key锁。
  • Table Filter：comment is not NULL。
    此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上读出数据后再过滤。这个例子中判断加锁范围时可以忽略Table Filter

6、死锁原理与分析

死锁情况1

+----+-----+------+
| id | c1  | c2   |
+----+-----+------+
|  1 |  10 |  100 |
|  7 |  70 |  700 |
| 13 | 130 | 1300 |
| 20 | 200 | 2000 |
+----+-----+------+
事务A 事务B
begin;
select * from table_int3 where id = 13 for update;
// 事务A持有聚簇索引id = 13行记录的行级写锁
begin;
select * from table_int3 where id <= 7 for share;
// block
// 事务B成功申请到聚簇索引id = 1, id = 7 行记录的Next-Key读锁，但在等待id = 13行记录的Next-Key读锁，但事务B已经获得id = 13行记录上的间隙锁
insert into table_int3 values(11, 110, 1100);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction;
// 事务A申请聚簇索引id = 13行记录的插入意向锁时发生死锁，事务A被自动回滚		 事务B恢复执行，成功申请到聚簇索引id = 13行记录的Next-Key读锁，返回2行记录

死锁情况2

解释这个例子，需要知道一条SQL语句对行记录加锁是一条一条行记录分开加的，而不是所有的锁一起加的，因此才会发生这个死锁现象

6.1、加锁的粒度

上图中，当Update SQL被发给MySQL后，MySQL Server根据where条件和索引生成执行计划，首先调用InnoDB引擎的“取满足条件的第一行”这个接口读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。第一条记录操作完成，之后MySQL Server循环调用innoDB引擎的“取满足条件的下一行”这个接口再读取下一条记录，重复上述过程，直至没有满足条件的记录为止。对于没有索引的表，则调用的是取表中第一行数据和取表中的下一条数据。MySQL server会把最终结果先缓存到net_buffer（大小由参数net_buffer_length决定）中，如果net_buffer满了会先给client，再继续从 innoDB获取数据。MySQL采用的是边算边发的策略，如果客户端读结果不及时，会堵住MySQL的查询过程，因此即便是全表扫描，在MySQL Server层占用的内存最大就是net_buffer_length。
数据库的rows_examined（explain命令中的rows）表示MySQL Server层的执行器在这个SQL执行过程中扫描了多少行，执行器每次调用引擎获取数据行的时候会累加。但是执行器调用一次，在 innoDB引擎内部则扫描了多行，因此存储引擎实际扫描行数跟rows_examined并不是完全相同的。

根据上图的交互，针对一条当前读（locking read）的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。innoDB先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后再读取下一条加锁，直至读取完毕。

死锁情况2的解释

针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会对name索引上的行记录加锁，而且会对聚簇索引上的行记录加行级写锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会对聚簇索引上的行记录加行级写锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]，跟Session 1的加锁顺序正好相反。如果两个Session恰好都持有了第一把锁，再请求加第二把锁，死锁就发生了。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。使用上面提到的锁算法，分析每条SQL语句的加锁范围、加锁类型和加锁顺序，然后检查多个并发SQL间是否存在对相同的行记录以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况。

6.2、innoDB死锁检测与自动回滚

innoDB目前采用wait-for graph（等待图）的方式进行死锁检测，并会优先回滚占有资源较少的事务。
事务执行过程中如果发生死锁，会收到下面的错误：

mysql> select * from table_int3 where id = 7 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

然后 innoDB会自动回滚当前事务

6.3、innoDB唯一索引中的死锁问题

在可重复读下，由于innoDB的特殊实现，如果某个列是唯一索引（主键也是唯一索引），当事务往唯一索引列插入数据发现已有重复数据时（Duplicate Key），事务会尝试获取重复Key行记录上的Next-Key读锁（如果没有重复数据，则不会获取这个锁，事务成功插入后，会获得新插入行记录的行级写锁），由于Next-Key是间隙锁和行锁的组合，如果重复Key上已有行级写锁，事务获取Next-Key会发生阻塞，但此时事务可能已经获得重复Key行记录上的间隙锁。

mysql> select * from table_int3;
+----+-----+------+
| id | c1  | c2   |
+----+-----+------+
|  1 |  10 |  100 |
|  7 |  70 |  700 |
| 13 | 130 | 1300 |
| 20 | 200 | 2000 |
+----+-----+------+
4 rows in set (0.00 sec)
示例1
事务A 事务B
begin;
INSERT INTO table_int3 VALUES (7,80, 800);
// 主键已有重复数据，这条SQL执行失败，但事务A此时持有聚簇索引id = 7行记录的Next-Key读锁
begin;
INSERT INTO table_int3 VALUES (7,80, 800);
// 主键已有重复数据，这条SQL执行失败，但事务B此时持有聚簇索引id = 7行记录的Next-Key读锁
update table_int3 set c1 = 90 where id = 7;
// 事务A等待事务B释放聚簇索引id = 7行记录的行级读锁
update table_int3 set c1 = 90 where id = 7;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
事务B等待事务A释放聚簇索引id = 7行记录的行级读锁，发生死锁，事务B被rollback，事务A恢复执行

多个事务在唯一索引上并发执行 insert on duplicate key update时很容易发生死锁现象，原因与这个示例类似。因为 insert on duplicate key update 其实是先判断插入的行是否产生重复key错误，如果有重复key，对该现有的行加上Next-Key读锁后返回该行数据给mysql，然后mysql执行duplicate后的update操作，对该行记录加上写锁，进行update修改。注意，如果插入的行和多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个唯一索引冲突的行。

示例2
事务A 事务B
begin;
INSERT INTO table_int3 VALUES (10,100, 1000);
// 由于主键上没有重复数据，插入成功，事务A持有聚簇索引id = 10行记录的行级写锁
begin;
INSERT INTO table_int3 VALUES (10,100, 1000);
// 主键已有重复数据，这条SQL会被block，事务B获取聚簇索引id = 10行记录的Next-Key读锁时被阻塞，但事务B其实已经获得聚簇索引id = 10行记录的间隙锁
INSERT INTO table_int3 VALUES (9,90, 900);
// 事务A获取聚簇索引id = 10行记录的插入意向锁时（说明上一步中事务A没有获取id = 10的插入意向锁），innoDB发现事务B已经持有聚簇索引id = 10行记录的间隙锁，innoDB回滚事务B后，事务A继续执行		 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Query OK, 1 row affected (0.00 sec)

注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

7、锁等待超时的注意事项

innoDB中，参数innodb_lock_wait_timeout控制锁等待的时间，默认是50s，一旦锁等待超过这个时间事务就会报错。

mysql>select * from t where a = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

与死锁发生时自动回滚事务不同，参数innodb_rollback_on_timeout 控制在发生锁等待超时后是否自动回滚当前事务，默认是OFF。由于默认参数是OFF，因此在事务执行过程中如果发生锁等待超时，只是事务当前执行的SQL失败了，但事务之前执行的SQL还是成功的，事务需要主动commit或者rollback，否则这个事务之前获得的锁不会释放。

8、锁升级

innoDB中不存在锁升级机制，因为其锁的实现比较高效。每个页中有个bit数组，采用类似位图的方式，管理这个页中行记录上的锁。因此，不管对这个页中的行记录加多少个锁，锁资源的开销都是一样。

在Microsoft SQL Server中，锁是一种稀有资源，锁消耗的内存资较多，Microsoft SQL Server会在下面的情况下将行锁、分页锁和键锁升级为表锁来降低锁占用的资源：
1、一句单独的SQL语句持有的锁数量超过阈值（默认5000）；
2、锁占用的内存超过40%
但是锁升级带来的问题是锁粒度的降低导致的并发性能降低

9、隐式锁与显式锁

insert操作加的行级写锁是隐式锁（不会主动显示），只有检测到Key冲突的时候才会把隐式锁转为显式锁。
隐式锁你可以理解为乐观锁，也就是正常来说不加锁或共享锁，但是遇到冲突则加锁或升级为排它锁。显式锁，那就是真的锁上了。
不管插入还是更新操作，innoDB都是先以乐观方式进行，因此先加索引S锁，当发现会产生页分裂时，再加悲观锁（X锁）

10、锁优化经验

避免死锁有哪些方法？

  • 以固定的顺序操作多个表和行，避免并发事务间相互等待锁而死锁。
  • 大事务拆成多个小事务。大事务更容易发生死锁，如果业务允许，将大事务拆小，通过多次commit及时释放不需要的锁。
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
  • 降低隔离级别。将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁，同时可以提高并发性和吞吐量。
  • 为表添加合理的索引。

其他优化经验

  • 避免长事务。长事务意味着系统里面会存在很老的ReadView，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间，还占用很多锁资源。长事务会阻塞DDL获取MDL写锁，DDL等待MDL写锁时会阻塞后续所有的查询和DML语句获取MDL读锁。
  • 高并发的表，业务生成主键。mysql的自增锁在高并发插入场景下性能不佳，建议业务方使用独立的id生成器生成主键，避免性能和不一致的问题。
  • 热点锁尽量放到最后获取，因为事务commit才会释放锁，如果一开始就获取热点锁，那持有热点锁的时间会变长，容易造成更多事务等待。
  • 如果某行经常更新，可以通过将一行改成逻辑上的多行来减少锁冲突。

11、锁等待排查方法

MySQL实战45讲-19 为什么我只查一行的语句，也执行这么慢？
通过show processlist命令查看当前线程Waiting for table metadata lock或者 Waiting for table flush。然后通过performance_schema和sys系统库，通过查询sys.schema_table_lock_waits表，找出阻塞当前线程的其他线程的process id，把这个process id用kill 命令断开即可。连接被断开的时候，会自动回滚这个连接里面正在执行的线程，同时释放持有的锁。

参考

https://cloud.tencent.com/developer/article/1033697

posted @ 2022-03-07 17:22  zoo-keeper  阅读(193)  评论(0)    收藏  举报