mysql的几种锁_初中常见七种沉淀

2022-08-18 14:34:43 浏览数 (1)

​

大家好，我是架构君，一个会写代码吟诗的架构师。今天说一说mysql的几种锁_初中常见七种沉淀,希望能够帮助大家进步!!!

一、死锁示例

考虑下面一个MySQL死锁的示例：

有如下一张表：

代码语言：javascript复制
CREATE TABLE `test` (
  `id` int(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

只听到从架构师办公室传来架构君的声音:

花枝已尽莺将老，桑叶渐稀蚕欲眠。有谁来对上联或下联?

表中有如下数据：

代码语言：javascript复制
mysql> SELECT * FROM test;
 ---- ------ 
| id | name |
 ---- ------ 
|  1 | 1    |
|  5 | 5    |
| 10 | 10   |
| 15 | 15   |
| 20 | 20   |
| 25 | 25   |
 ---- ------ 
6 rows in set (0.00 sec)

当数据库的隔离级别为Repeatable Read或Serializable时,我们来看这样的两个并发事务（场景一）：

session1

session2

begin;

​

​

begin;

select * from test where id = 12 for update;

​

​

select * from test where id = 13 for update;

insert into test(id, name) values(12, "test1");

​

锁等待中

insert into test(id, name) values(13, "test2");

锁等待解除

死锁，session 2的事务被回滚

上面两个并发事务一定会发生死锁（这里之所以限定RR和Serializable两个隔离级别，是因为只有这两个级别下才会有间隙锁/临键锁，而这是导致死锁的根本原因，后面会详细分析）。

我们再来看另外一个并发场景（场景二）：

session1

session2

begin;

​

​

begin;

select * from test where id = 12 for update;

​

​

select * from test where id = 16 for update;

insert into test(id, name) values(12, "test1");

​

commit;

insert into test(id, name) values(16, "test2");

​

commit;

在这个并发场景下，两个事务均能成功提交，而不会有死锁。

在上面的示例中，我们发现，select ... for update虽然可以用于解决数据库的并发操作，但在实际项目中却需要慎重使用，原因是当查询条件对应的记录不存在时，很容易造成死锁。而造成死锁的原因和MySQL的锁机制有关。本文将详细介绍常见的七种锁机制，了解了这些锁机制之后就能理解造成场景一死锁的根本原因以及场景一和场景二差异的原因。

二、MySQL的八种锁

  • 行锁（Record Locks）
  • 间隙锁（Gap Locks）
  • 临键锁（Next-key Locks）
  • 共享锁/排他锁（Shared and Exclusive Locks）
  • 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）
  • 插入意向锁（Insert Intention Locks）
  • 自增锁（Auto-inc Locks） 实际上，MySQL官网中还提到了一种预测锁，这种锁主要用于存储了空间数据的空间索引，本文暂不讨论。

1、行锁

这MySQL的官方文档中有以下描述：

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

这句话说明行锁一定是作用在索引上的。

2、间隙锁

在MySQL的官方文档中有以下描述：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

这句话表明间隙锁一定是开区间，比如（3，5）或者。在MySQL官网上还有一段非常关键的描述：

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.

这段话表明间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

在MySQL官网上关于间隙锁还有一段重要描述：

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

这段话表明，在RU和RC两种隔离级别下，即使你使用select ... in share mode或select ... for update，也无法防止幻读（读后写的场景）。因为这两种隔离级别下只会有行锁，而不会有间隙锁。这也是为什么示例中要规定隔离级别为RR的原因。

3、临键锁

在MySQL的官方文档中有以下描述：

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

这句话表明临键锁是行锁 间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。

在MySQL的官方文档中还有以下重要描述：

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.

个人觉得这段话描述得不够好，很容易引起误解。这里更正如下：InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

4、共享锁/排他锁

在MySQL的官方文档中有以下描述：

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) locks。

  • A shared (S) lock permits the transaction that holds the lock to read a row.
  • An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

这段话明确说名了共享锁/排他锁都只是行锁，与间隙锁无关，这一点很重要，后面还会强调这一点。其中共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一个事务并发更新或删除某一行记录所需要持有的锁，比如select ... for update。

不过这里需要重点说明的是，尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是行锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的隔离级别以及查询的数据是否存在。关于这一点，后面分析场景一和场景二的时候还会提到。

5、意向共享锁/意向排他锁

在MySQL的官方文档中有以下描述：

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

The intention locking protocol is as follows:

  • Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
  • Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

这段话说明意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

​

X

IX

S

IS

X

互斥

互斥

互斥

互斥

IX

互斥

兼容

互斥

兼容

S

互斥

互斥

兼容

兼容

IS

互斥

兼容

兼容

兼容

这里需要重点关注的是IX锁和IX锁是相互兼容的，这是导致上面场景一发生死锁的前置条件，后面会对死锁原因进行详细分析。

6、插入意向锁(IIX)

在MySQL的官方文档中有以下重要描述：

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

这段话表明尽管插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于间隙锁，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。这里我们再回顾一下共享锁和排他锁：共享锁用于读取操作，而排他锁是用于更新或删除操作。也就是说插入意向锁、共享锁和排他锁涵盖了常用的增删改查四个动作。

7、示例分析

到此为止，我们介绍了MySQL常用的七种锁的前六种，理解了这六种锁之后，才能很好地分析和理解开头给出的两个场景。我们先来分析场景一：

session1

session2

begin;

​

​

begin;

select * from test where id = 12 for update; 先请求IX锁并成功获取 再请求X锁，但因行记录不存在，故得到的是间隙锁（10，15）

​

​

select * from test where id = 13 for update; 先请求IX锁并成功获取 再请求X锁，但因行记录不存在，故得到的是间隙锁（10，15）

insert into test(id, name) values(12, "test1"); 请求插入意向锁（12），因事务二已有间隙锁，请求只能等待

​

锁等待中

insert into test(id, name) values(13, "test2"); 请求插入意向锁（13），因事务一已有间隙锁，请求只能等待

锁等待解除

死锁，session 2的事务被回滚

在场景一中，因为IX锁是表锁且IX锁之间是兼容的，因而事务一和事务二都能同时获取到IX锁和间隙锁。另外，需要说明的是，因为我们的隔离级别是RR，且在请求X锁的时候，查询的对应记录都不存在，因而返回的都是间隙锁。接着事务一请求插入意向锁，这时发现事务二已经获取了一个区间间隙锁，而且事务一请求的插入点在事务二的间隙锁区间内，因而只能等待事务二释放间隙锁。这个时候事务二也请求插入意向锁，该插入点同样位于事务一已经获取的间隙锁的区间内，因而也不能获取成功，不过这个时候，MySQL已经检查到了死锁，于是事务二被回滚，事务一提交成功。

分析并理解了场景一，那场景二理解起来就会简单多了：

session1

session2

begin;

​

​

begin;

select * from test where id = 12 for update; 先请求IX锁并成功获取 再请求X锁，但因行记录不存在，故得到的是间隙锁（10，15）

​

​

select * from test where id = 16 for update; 先请求IX锁并成功获取 再请求X锁，但因行记录不存在，故得到的是间隙锁（15，20）

insert into test(id, name) values(12, "test1"); 请求插入意向锁（12），获取成功

​

commit;

insert into test(id, name) values(16, "test2"); 请求插入意向锁（16），获取成功

​

.commit;

场景二中，两个间隙锁没有交集，而各自获取的插入意向锁也不是同一个点，因而都能执行成功。

8、自增锁

最后，我们再来介绍下自增锁。在MySQL的官方文档中有以下描述：

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

这段话表明自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。为了便于介绍innodb_autoinc_lock_mode参数，我们先将需要用到自增锁的Insert语句进行分类：

1）Insert语句分类

  1. “INSERT-like” statements(类INSERT语句) （这种语句实际上包含了下面的2、3、4）

所有可以向表中增加行的语句，包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA。包括“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts.

  1. “Simple inserts”

可以预先确定要插入的行数（当语句被初始处理时）的语句。 这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。

  1. “Bulk inserts”

事先不知道要插入的行数（和所需自动递增值的数量）的语句。 这包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。 InnoDB在处理每行时一次为AUTO_INCREMENT列分配一个新值。

  1. “Mixed-mode inserts”

这些是“Simple inserts”语句但是指定一些（但不是全部）新行的自动递增值。 示例如下，其中c1是表t1的AUTO_INCREMENT列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种类型的“Mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，其在最坏的情况下实际上是INSERT语句随后又跟了一个UPDATE，其中AUTO_INCREMENT列的分配值不一定会在 UPDATE 阶段使用。

2）InnoDB AUTO_INCREMENT锁定模式分类

3）InnoDB AUTO_INCREMENT锁定模式含义

1.在复制环节中使用自增列

mysql主从复制有三种模式：row，statement，mixed三种。

  • row模式：

在row模式下，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有sql多表关联的情况。由于row模式是直接将主库中的每一行数据在从库进行复写，因而row模式的优点是不会存在主从不一致的问题；而row模式的缺点就是会产生大量的binlog日志。

  • statement模式：

在statement模式下，每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。statement模式的优点是不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能，因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息；statement模式的缺点是，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

  • mixed模式：

从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。

由此可知，如果你在使用基于语句的复制(statement-based replication)请将innodb_autoinc_lock_mode设置为0或1，并在主从上使用相同的值。 如果使用innodb_autoinc_lock_mode = 2（“interleaved”）或主从不使用相同的锁定模式的配置，自动递增值不能保证在从机上与主机上相同。

如果使用基于行的或混合模式的复制，则所有自动增量锁定模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感（混合模式会在遇到不安全的语句是使用基于行的复制模式）。

2. “Lost” auto-increment values and sequence gaps

在所有锁定模式（0,1和2）中，如果生成自动递增值的事务回滚，那些自动递增值将“丢失”。 一旦为自动增量列生成了值，无论是否完成“类似INSERT”语句以及包含事务是否回滚，都不能回滚。 这种丢失的值不被重用。 因此，存储在表的AUTO_INCREMENT列中的值可能存在间隙。

3. Specifying NULL or 0 for the AUTO_INCREMENT column

在所有锁定模式（0,1和2）中，如果用户在INSERT中为AUTO_INCREMENT列指定NULL或0，InnoDB会将该行视为未指定值，并为其生成新值。

4. 为AUTO_INCREMENT列分配一个负值

在所有锁定模式（0,1和2）中，如果您为AUTO_INCREMENT列分配了一个负值，则InnoDB会将该行为视为未指定值，并为其生成新值。

5. 如果AUTO_INCREMENT值大于指定整数类型的最大整数

在所有锁定模式（0,1和2）中，如果值大于可以存储在指定整数类型中的最大整数，则InnoDB会将该值设置为指定类型所允许的最大值。

6. Gaps in auto-increment values for “bulk inserts”

当innodb_autoinc_lock_mode设置为0（“traditional”）或1（“consecutive”）时,任何给定语句生成的自动递增值是连续的，没有间隙，因为表级AUTO-INC锁会持续到 语句结束,并且一次只能执行一个这样的语句。

当innodb_autoinc_lock_mode设置为2（“interleaved”）时，在“bulk inserts”生成的自动递增值中可能存在间隙，但只有在并发执行“INSERT-Like”语句时才会产生这种情况。

对于锁定模式1或2，在连续语句之间可能出现间隙，因为对于批量插入，每个语句所需的自动递增值的确切数目可能不为人所知，并且可能进行过度估计。

7. 由“mixed-mode inserts”分配的自动递增值

考虑一下场景,在“mixed-mode insert”中,其中一个“simple insert”语句指定了一些（但不是全部）行的AUTO-INCREMENT值。 这样的语句在锁模式0,1和2中表现不同。innodb_autoinc_lock_mode=0时,auto-increment值一次只分配一个,而不是在开始时全部分配。当innodb_autoinc_lock_mode=1时，不同于innodb_autoinc_lock_mode=0时的情况，因为auto-increment值在语句一开始就分配了，但实际可能使用不完。当innodb_autoinc_lock_mode=2时，取决于并发语句的执行顺序。

8. 在INSERT语句序列的中间修改AUTO_INCREMENT列值

在所有锁定模式（0,1和2）中，在INSERT语句序列中间修改AUTO_INCREMENT列值可能会导致duplicate key错误。

4）InnoDB AUTO_INCREMENT计数器初始化

如果你为一个Innodb表创建了一个AUTO_INCREMENT列，则InnoDB数据字典中的表句柄包含一个称为自动递增计数器的特殊计数器，用于为列分配新值。 此计数器仅存在于内存中，而不存储在磁盘上。

要在服务器重新启动后初始化自动递增计数器，InnoDB将在首次插入行到包含AUTO_INCREMENT列的表时执行以下语句的等效语句。

代码语言：javascript复制
SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB增加语句检索的值，并将其分配给表和表的自动递增计数器。 默认情况下，值增加1。此默认值可以由auto_increment_increment配置设置覆盖。

如果表为空，InnoDB使用值1。此默认值可以由auto_increment_offset配置设置覆盖。

如果在自动递增计数器初始化前使用SHOW TABLE STATUS语句查看表, InnoDB将初始化计数器值,但不会递增该值。这个值会储存起来以备之后的插入语句使用。这个初始化过程使用了一个普通的排它锁来读取表中自增列的最大值。InnoDB遵循相同的过程来初始化新创建的表的自动递增计数器。

在自动递增计数器初始化之后，如果您未明确指定AUTO_INCREMENT列的值，InnoDB会递增计数器并将新值分配给该列。如果插入显式指定列值的行，并且该值大于当前计数器值，则将计数器设置为指定的列值。

只要服务器运行，InnoDB就使用内存中自动递增计数器。当服务器停止并重新启动时，InnoDB会重新初始化每个表的计数器，以便对表进行第一次INSERT，如前所述。

服务器重新启动还会取消CREATE TABLE和ALTER TABLE语句中的AUTO_INCREMENT = N表选项的效果（可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值，也可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值）。

参考博客：

  1. MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking MySQL官网
  2. 【原创】惊！史上最全的select加锁分析(Mysql) - 孤独烟 - 博客园 史上最全的select加锁分析
  3. InnoDB并发插入，居然使用意向锁？_HelloWorld搬运工-CSDN博客 InnoDB并发插入，居然使用意向锁
  4. innodb锁-插入意向锁_ignorewho的博客-CSDN博客_插入意向锁 插入意向锁
  5. 【原创】新说Mysql事务隔离级别 - 孤独烟 - 博客园 MySQL事务隔离级别
  6. AUTO-INC锁和AUTO_INCREMENT在InnoDB中处理方式 - 简书AUTO-INC锁和AUTO_INCREMENT在InnoDB中处理方式
  7. MySQL自增锁模式innodb_autoinc_lock_mode参数详解 - 简书 MySQL自增锁模式innodb_autoinc_lock_mode参数详解
  8. MySQL 死锁套路：三个事务插入有一个回滚_wanghao112956的博客-CSDN博客 MySQL 死锁套路：三个事务插入有一个回滚
  9. MySQL :: MySQL 5.7 Reference Manual :: 14.7.3 Locks Set by Different SQL Statements in InnoDB Locks Set by Different SQL Statements in InnoDB
  10. binlog三种模式的区别（row，statement，mixed）_我喂自己袋盐-CSDN博客_binlog row模式 binlog三种模式的区别（row，statement，mixed）
数据库 云数据库SQLServer 云推荐引擎 sql

0 人点赞