InnoDB的七种锁

最新推荐文章于 2022-06-01 06:46:23 发布

私念

最新推荐文章于 2022-06-01 06:46:23 发布

阅读量155

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/tiancityycf/article/details/103062114

版权

Mysql 专栏收录该内容

36 篇文章 0 订阅

订阅专栏

InnoDB的七种锁

自增锁（Auto-inc Locks）
共享/排他锁（Shared and Exclusive Locks）
意向锁（Intention Locks）
插入意向锁（Insert Intention Locks）
记录锁（Record Locks）
间隙锁（Gap Locks）
临键锁（Next-Key Locks）

默认事务隔离级别为可重复读（Repeated Read, RR）

InnoDB的锁，与索引类型，事务的隔离级别相关

自增锁

自增锁是一种特殊的表级别锁（table-level lock）
从MySQL 5.1开始，InnoDB中提供了一种轻量级互斥量的自增长实现机制
同时InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式
进而提高自增长值插入的性能

innodb_autoinc_lock_mode和插入类型有关，在介绍它之前，我们先来看看都有哪些插入类型

insert-like 任何会产生新记录的语句，都叫上insert-like，
比如：INSERT,INSERT …SELECT,REPLACE,REPLACE …SELECT,and LOAD DATA，总之包括：simple-inserts,bulk-inserts,mixed-mode inserts.
simple inserts
插入的记录行数是确定的：比如：insert into values，replace
但是不包括： INSERT … ON DUPLICATE KEY UPDATE.
bulk inserts
插入的记录行数不能马上确定的，比如： INSERT … SELECT, REPLACE … SELECT, and LOAD DATA
mixed-mode inserts
这些都是simple-insert，但是部分auto increment值给定或者不给定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):
INSERT INTO t1 (c1,c2) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’);
另外一种 mixed-mode insert 就是 INSERT … ON DUPLICATE KEY UPDATE

innodb_autoinc_lock_mode 的说明

0 这个表示tradition 传统
1 这个表示consecutive 连续 (默认)
2 这个表示interleaved 交错 （MySQL 8.0下默认值为2）

1.1 tradition(innodb_autoinc_lock_mode=0) 模式:

它提供了一个向后兼容的能力
在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的， 一个事务可能包涵有一个或多个语句。
它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。
由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。

1.2 consecutive(innodb_autoinc_lock_mode=1) 模式:

对simple insert做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句，**该值会用互斥量mutex去对内存（dict_table_struct.autoinc）中的计数器进行累加操作。**mysqld重启后，从哪里得到AUTO_INCREMENT呢？内存值肯定是丢失了，实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT
Mysql8.0优化:将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。
对于bulk inserts 还是使用传统表锁，该配置下如果不考虑回滚，对于自增列的增长还是连续的。
深入思考：为什么这个模式要产生表级别的锁呢？因为：他要保证bulk insert自增id的连续性，防止在bulk insert的时候，被其他的insert语句抢走auto increment值。
这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

1.3 interleaved(innodb_autoinc_lock_mode=2) 模式

由于这个模式下已经没有了auto_inc锁，对于所有insert-like 自增长值得产生都是通过互斥量mutex,所以这个模式下的性能是最好的
但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的，所以基于statement-base replication会出现问题，因此使用这个模式，任何时候都应该使用row-base replication,才能保证最大的并发性能和主从一致性

不要没事去更新一个auto_increment列的值，否则自增时，有可能跟你更新的值冲突，导致插入失败

共享/排他锁

共享/排它锁是标准的行级锁(row-level locking)

事务拿到某一行记录的共享S锁，才可以读取这一行；
事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；
多个事务可以拿到一把S锁，读读可以并行；
而只有一个事务可以拿到X锁，写写/读写必须互斥；
共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本

意向锁

意向锁，是一个表级别的锁(table-level locking)，但是却表示事务正在读或写某一行记录，而不是整个表。所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。
为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁，主要目的是解决表锁和行锁共存的问题。

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

例如

select ... lock in share mode，要设置IS锁；
select ... for update，要设置IX锁；

事务要获得某些行的S锁，必须先获得表的IS锁

事务要获得某些行的X锁，必须先获得表的IX锁

意向锁之间互不排斥，但除了 IS 与 S兼容外，意向锁会与共享锁/排他锁互斥
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突
InnoDB支持多粒度锁，意向锁在保证并发性的前提下，实现了行锁和表锁共存

解决的问题

如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

插入意向锁

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的）
它是专门针对insert操作的。

普通的Gap Lock 不允许在（上一条记录，本记录）范围内插入数据
插入意向锁Gap Lock 允许在（上一条记录，本记录）范围内插入数据
如果多个事务插入到相同的索引间隙中，如果它们不在间隙中的相同位置插入，则无需等待其他事务。比如说有索引记录4和7，有两个事务想要分别插入5，6，在获取插入行上的独占锁之前，每个锁都使用插入意图锁锁定4和7之间的间隙，但是不要互相阻塞，因为行是不冲突的，意向锁的涉及是为了插入的正确和高效。

插入的过程

假设现在有记录 10， 30， 50 且为主键，需要插入记录 25

找到小于等于25的记录，这里是 10
找到记录10的下一条记录，这里是 30
判断下一条记录30 上是否有锁

判断 30 上面如果没有锁，则可以插入
判断 30 上面如果有Record Lock，则可以插入
判断 30 上面如果有Gap Lock/Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) /（10, 30] ；在30上增加insert intention lock（此时处于waiting状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（ transaction）将被唤醒，此时记录30 上才能获得 insert intention lock ，然后再插入记录25

注意：一个事物 insert 25 且没有提交，另一个事物 delete 25 时，记录25上会有 Record Lock

select * from a;
+----+
| a  |
+----+
| 5 |
| 10 |
| 13 |
| 20 |
+----+

//事务A
select * from a where a<=13 for update

//事务B
insert into a values (12)

//事务C
insert into a values (11)

事务A不提交，事务B，C被阻塞了，这时候查看

show engine innodb status\G

mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140018685810432, query id 240 localhost root update
--等待插入的SQL
insert into a values(12)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
--插入记录12的事物等待中，等待获得插入意向锁(lock_mode X locks gap before rec insert intention waiting)

此时事务A commit

事务B输出：Query OK, 1 row affected (17.40 sec)
前提条件是insert操作的锁没有超时
事务B未提交，再执行：show engine innodb status\G

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root

记录锁

记录锁，它封锁索引记录，例如： select * from t where id=1 for update;

记录锁锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

间隙锁

它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
开区间，不包括双端端点

select * from t 
    where id between 7 and 15 
    for update;

这个SQL语句会封锁区间，以阻止其他事务id=10的记录插入。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”
如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
左闭右开区间

但当查询唯一索引的时候，且记录存在，Next-Key Lock 会进行优化，将其降级为RecordLock，即仅锁住索引本身，不是范围。
但当查询唯一索引的时候，且记录不存在，使用Gap Lock
但当查询唯一索引的时候，使用范围查询 > <，使用Gap Lock + Record Lock,锁上界，不锁下界

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

总结：

InnoDB使用共享锁，可以提高读读并发；
排他锁，为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；
InnoDB使用插入意向锁，可以提高插入并发；
记录锁锁定索引记录；
间隙锁锁定间隔，防止间隔中被其他事务插入；
临键锁锁定索引记录+间隔，防止幻读；

普通select
加锁select
update与delete
insert
各类SQL语句分别加了什么锁？

select

普通的select是快照读，而select … for update或select … in share mode则会根据情况加不同的锁

如果在唯一索引上用唯一的查询条件时（ where id=1），加记录锁

否则，其他的查询条件和索引条件，加间隙锁（BETWEEN AND ）或Next-Key 锁（可重复隔离级别）

update与delete

如果在唯一索引上使用唯一的查询条件来update/delete，加记录锁

否则，符合查询条件的索引记录之前，都会加Next-Key 锁

注：如果update的是聚集索引，则对应的普通索引记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。

insert

insert和update与delete不同，它会用排它锁封锁被插入的索引记录，同时，会在插入区间加插入意向锁，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。

查看锁等待情况

select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;

显示锁 vs 隐示锁

显示锁(explicit lock)
显示的加锁，在show engine innoDB status 中能够看到，会在内存中产生对象，占用内存
eg: select … for update , select … lock in share mode
隐示锁(implicit lock)
implicit lock 是在索引中对记录逻辑的加锁，但是实际上不产生锁对象，不占用内存空间
哪些语句会产生implicit lock 呢？
eg: insert into xx values(xx)
eg: update xx set t=t+1 where id = 1 ; 会对辅助索引加implicit lock
implicit lock 在什么情况下会转换成 explicit lock
eg：只有implicit lock 产生冲突的时候，会自动转换成explicit lock,这样做的好处就是降低锁的开销
eg: 比如：我插入了一条记录10，本身这个记录加上implicit lock，如果这时候有人再去更新这条10的记录，那么就会自动转换成explicit lock
数据库怎么知道implicit lock的存在呢？如何实现锁的转化呢？
对于聚集索引上面的记录，有db_trx_id,如果该事务id在活跃事务列表中，那么说明还没有提交，那么implicit则存在
对于非聚集索引：由于上面没有事务id，那么可以通过上面的主键id，再通过主键id上面的事务id来判断，不过算法要非常复杂，这里不做介绍

metadata lock

这是Server 层实现的锁，跟引擎层无关
当你执行select的时候，如果这时候有ddl语句，那么ddl会被阻塞，因为select语句拥有metadata lock，防止元数据被改掉

私念

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
InnoDB的七种锁

InnoDB的七种锁自增锁（Auto-inc Locks）共享/排他锁（Shared and Exclusive Locks）意向锁（Intention Locks）插入意向锁（Insert Intention Locks）记录锁（Record Locks）间隙锁（Gap Locks）临键锁（Next-Key Locks）默认事务隔离级别为可重复读（Repeated Read,...
复制链接

扫一扫