彻底掌握 MySQL InnoDB 的锁机制

Lucky小黄人

于 2023-02-10 01:15:00 发布

阅读量498

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_41767116/article/details/128963399

版权

本文是对沈剑大佬锁机制十多篇文章的概括总结，文末有全部链接，还参考了 10 多位其他网友的优秀分享。

1、概要

MySQL 中的锁可以按照粒度分为锁定整个表的表级锁(table-level locking)和锁定数据行的行级锁(row-level locking)：

表级锁具有开销小、加锁快的特性；但是表级锁的锁定粒度较大，发生锁冲突的概率高，支持的并发度低；
行级锁具有开销大，加锁慢的特性；但是行级锁的锁定粒度较小，发生锁冲突的概率低，支持的并发度高。

InnoDB 存储引擎同时支持行级锁(row-level locking)和表级锁(table-level locking)，默认情况下采用行级锁。

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。所以如果访问没有命中索引，就无法使用行锁，将要退化为表锁(共享行锁上升为共享表锁，排他行锁上升为排他表锁)。(注意：Oracle的行锁实现机制不同。)

2、全局锁

全局锁就是对整个数据库实例加锁，获得全局锁后的数据库就无法进行数据的更新操作与表结构修改操作。

当你需要让数据库变为只读状态时，可以使用 Flush tables with read lock语句进行加锁，此时数据的更新操作会被阻塞。解锁命令：UNLOCK TABLES;

3、行级锁

InnoDB 实现了以下两种类型的概念行锁：

3.1 排它锁 Shared、共享锁 Exclusive

排他 X 锁和共享 S 锁是 Innodb 的行级概念锁。保证同一行记录修改与删除的串行性，从而保证数据的强一致。

共享锁是读锁，多个事务可以拿到同一行记录的共享锁，所以读读可以并发。排他锁是写锁，同一行记录的排他锁在同一时刻只能有一个事务获得，所以写写是互斥的。实际上读写也是互斥的，也就是有排他锁就不能加共享锁，有共享锁就不能加排他锁。

3.1.1 行共享锁的触发场景

加了lock in share mode 的 select 语句，比如：select … lock in share mode

普通 select 实施不加锁多版本快照读

3.1.2 行排他锁的触发场景

加了 for update 的 select 语句，比如 select … for update
update, delete, insert 都是行级排它锁

3.2 行级锁有三种实现算法：记录锁、间隙锁、临键锁。

在数据库的优化器优化过程中会根据where字段的用到的索引类别，自动的加相应种类的行锁。

根据触发条件的不同，每种锁都有排他锁和共享锁两种类型实现。比如触发条件是加了lock in share mode 的 select 语句，那么可能获取到的就是共享类型的记录锁/间隙锁/临键锁。

3.3 记录锁(Record Locks)(加锁对象是索引节点)

select * from t where id=1 for update; 手动或者自动加了for update 才会加记录锁，否则不加锁，实施快照读。

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

只适用于在唯一索引上使用了唯一查询条件。如果唯一索引不是聚集索引，还会额外锁定聚集索引的索引记录。

3.4 间隙锁(Gap Locks)(实施在索引上)

间隙锁，它封锁索引记录之间的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。是一个左开右闭的区间，即(x,y]的形式。

可以防止间隙内有新数据被插入，以及防止已存在的记录，更新成间隙内的记录。

适用于在唯一索引上使用了范围查询条件。

3.5 临键锁(实施在索引上)

临键锁，是记录锁与间隙锁的组合，既封锁索引记录本身，又封锁索引记录之前的区间。

临键锁针对的是普通索引(注意不是唯一索引)

3.6 插入意向锁(实施在索引上)

间隙锁的一种。

作用：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。用于提高插入并发，因为如果使用间隙锁的话，不允许多个事务同时往同一个索引间隙插入记录，但是使用插入意向锁可以。如果插入的位置冲突呢？怎么办？另一个回滚吗？

比如两个事务都想往id(10,20)插入一条记录，但是两个事务插入记录的id分别是 11 和 12，插入的位置不冲突，所以不会阻塞对方。

4、表级锁

表级锁有两种：表数据锁(常说的表锁)，元数据表锁(MDL，metadata lock，Mysql5.5版本之后加的)。

4.1 元数据锁

MDL锁在语句开始执行时申请，在事务提交后释放。只要有事务在执行，mysql 就会自动加上元数据表锁(MDL)，这样在执行过程中就不能发生表结构变更。

对表进行增删改查，加 MDL 读锁，执行表结构变更(DDL命令)加 MDL 写锁。读读不互斥、读写和写写都互斥。

4.2 普通表锁如何触发

如果想手动加表锁，可以使用 lock 和 unlock 关键词来加锁和释放锁： lock tables 表名 read/write。比如：lock tables A read,B write。未解锁前不能其它表进行CRUD操作。加上读锁后，任何线程都只能执行都操作，写操作都会被阻塞，包括加锁的线程也是这样。

4.3 意向锁(Intention Locks)

如果要获取表锁，按一般法的方法，首先需要看该表是否已经被其他事务加上了表级锁，然后遍历该表中的每一行是否已经被其他事务加上了行级锁，如果存在行级锁，需要等待行级锁释放，检测行级锁的算法效率很低。为此，InnoDB 引入了另外一种锁：意向锁(Intention Lock)，用于加表锁前快速判定表中是否存在行级锁。

意向锁属于表级锁，由 InnoDB 自动添加，不需要用户干预。

是一种表级锁弱锁，仅仅用于表明意向。分成意向共享锁 (intention shared lock, IS) 和意向排他锁 (intention exclusive lock, IX)，分别表示事务有意向对表里的某些记录加共享行锁和排他行锁。

加共享行所和排他行锁之前会自动获取到对应的意向共享锁或者意向排他锁。由于对每个数据行加锁是互不干扰的，所以意向排他锁跟意向共享锁相互兼容，可以共存。

如果有意向共享锁可以同时加共享行锁和排他行锁，但是如果有意向排他锁，只能加排他行锁？这里感觉有点问题？如果已经有意向共享锁和共享行锁，那理论上来说可以加排他行锁，但是获取排他行锁需要先获取意向排他锁，因为有共享行级锁，所以无法加排他行锁，难道一张表不能同时读写吗？这样效率岂不是太低了？

4.4 自增锁(一类特殊的表锁)

4.4.1 唯一索引约束被违反了会发生什么

当 sql 违反主键和唯一索引约束时，如果存储引擎支持事务，比如innodb，SQL会自动回滚。如果不支持事务，SQL的执行会中断，一个update语句，部分执行成功，部分执行失败，可能造成不符合预期的结果集；

对于 insert 插入语句,可以使用 insert … on duplicate key update xxx 来指定违反约束时的动作；也不会中断。猜测很多 upsert 就是这么做的

插入成功，影响行数为1，插入失败但是修改成功，影响行数为2，插入失败且修改的值等于当前值，影响行数为0。

4.4.2 三种插入形式：简单插入、批量插入、混合插入

4.4.2.1 简单插入(simple insert)：

能够提前知道被插入的行数时的插入。普通的 insert/replace 语句，不管是单条插入还是多条插入，都是简单插入。这类插入很容易保证自增的连续性。(1不包含递归的子查询；2不包含insert … on duplicate key update… ；)

4.4.2.2 批量插入(bulk insert)：

不知道被插入的行数时的插入。如：insert into t1(name) select name from t2;、

由于不能够提前知道多少行插入，在处理自增列时，每插入一行，才会赋值新的自增值。在批量插入事务并发时，“可能”出现同一个事务的自增键不连续。

4.4.2.3 混合插入(mixed-mode insert)：

有些行插入时指定了自增列的值，无需数据库生成；有些行插入时未指定自增列的值(NULL)或者指定了自增列的值为零值或者空，此时需要数据库生成。具体走哪个分支，实际执行时才知道。比如：insert … on duplicate key update…

有些行插入实际上是修改，也无需数据库生成自增键，这类也是混合插入，无法预知到是插入还是修改。比如 insert … on duplicate key update…，这种情况其实会生成自增键，但是插入操作变成修改操作，那么系统生成的自增键就用不上，但是却是实打实的占用了一个系统自增键。

4.4.3 自增锁实现

自增锁是一种特殊的表级别锁(table-level lock)，专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。控制同一sql 语句插入的所有记录的自增id是连续的。

innodb通过 innodb_autoinc_lock_mode可以查看自增锁的状态。取值为0/1/2，默认为 1,

4.4.3.1 innodb_autoinc_lock_mode = 0，表粒度加锁，实施互斥；

所有的 insert 语句("insert like") 都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁，持续时间长，影响了并发插入效率。(sql 语句级别，而非事务级别)

保证了简单插入和批量插入时同一条语句插入的行记录的自增ID是连续的。可以保证主和从复制的同一张表的同一个行记录的自增ID是一样的。

4.4.3.2 innodb_autoinc_lock_mode = 1，表粒度加锁，实施互斥；

同样，所有的 insert 语句("insert like") 都要在语句开始的时候得到一个表级的 auto_inc 锁，但是对简单插入做了优化，由于简单插入插入的记录数能提前预知到，在获取到所需的自增键数量后，autoinc_lock 就会被释放,不必等到语句执行结束。但对于bulk insert，自增锁会被一直持有直到语句执行结束才会被释放。性能有所提高。

仍就可以保证简单插入和批量插入时同一条语句插入的行记录的自增ID是连续的。也可以保证主和从复制的同一张表的同一个行记录的自增ID是一样的。

4.4.3.3 innodb_autoinc_lock_mode = 2，不加锁

不加自增锁，效率最高，但是 simple insert 语句因为自增键是一次性分配的，所以仍能保证ID是连续的，但是 bulk insert的ID则可能有空洞。主从复制的同一张表下的同一行id有可能不一样。

4.5 从不同角度的锁分类

处理锁的态度	悲观锁、乐观锁
锁的粒度	行锁、表锁、全局锁
锁是否互斥的特性	共享锁、排他锁
算法锁	临键锁、间隙锁、记录锁
状态锁	意向共享锁、意向排他锁

5、数据库 4 类隔离级别下各种 sql 分别会使用什么锁

5.1 未提交读

select 不加锁，可能出现读脏；

5.2 提交读

普通select快照读，锁 select /update /delete 除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间外，都只会使用记录锁，可能出现不可重复读和幻读；

5.3 可重复读

普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；

(1)普通的select 使用无锁快照读(snapshot read)

(2)加锁的select(select ... lock in share mode / select ... for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：

在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)
其他索引查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读
非唯一索引上的查询：临键锁。

(3)insert语句，用排它锁封锁被插入的索引记录，并在在插入区间加插入意向锁(insert intention lock)，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。

5.4 可串行化

select 隐式转化为 select ... in share mode。

6、问题

问：主从复制时，执行并发批量插入，可能导致不同库里同一张表下的同一行id有可能不一样，如何避免

把 innodb_autoinc_lock_mode 设置成 1，则在批量插入时会加上自增表锁，保证从库回放 sql 时得到的自增 id 跟主库一样。

问：同一张表可以同时有多个事务进行写操作吗

如果事务之间操作的是不同的记录行，可以同时进行，如果操作的是相同的记录行，则不能同时进行，因为事务之间会因为获取行锁冲突而阻塞，未获取到行锁的事务需要等获取到行锁再继续执行。

问：如果发生全表扫描，是直接顺序扫描聚集索引的叶子节点吗

暂未掌握。

问：事务里的 sql 语句执行过程中，加上表锁或者行锁后，这些锁是 sql 执行完毕就释放，还是持续到事务执行完毕才释放，为什么不 sql 执行完毕就释放？即上面的这些锁是语句级别的，还是事务级别的？

事务执行完毕才释放。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议(两个阶段指的是加锁阶段和解锁阶段)。

比如使用使用临键锁来避免幻读的问题，加锁后不确定事务内后续是否仍有同样的范围查询语句会执行，如果每次 sql 执行完毕就释放锁，多次范围查询语句之间仍旧有可能在范围之间插入数据，导致幻读，那临键锁就没有意义了。所以为了保证每次查到的结果都一样，即保证不出现幻读，只能是把临键锁持续到事务完成才释放。

问：事务内sql加的锁都是在事务完成才释放，这个有什么指导意义

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，减少这些记录行的加锁时间，降低锁冲突概率。

假设你用淘宝积分购买了一件商品，那么这个动作需要至少有三条 sql，扣减你的积分、扣减商品数量、增加订单记录，假设淘宝所有数据都在一个数据库里，那么这三个 sql 可以用一个事务包裹起来，这时会把商品扣减sql放在最后，你的积分扣减放在最前面，因为商品扣减的锁冲突的概率最大，最可能影响并发，你的积分扣减锁冲突最小。

问：既然锁的持有时间这么长，除了降低并发外还可能导致什么问题？

增大死锁的概率。锁的持有时间越长，死锁的概率就越大。

问：如何处理死锁

策略一：直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置；默认为 50s，即如果不开启死锁检测，则在发生死锁之后，会等待 50s 后回滚事务释放锁。
策略二：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。innodb 默认开启死锁检测，但是死锁检测会消耗大量的CPU资源。