mysql 的InnoDB的七种锁

今天遇到一个deadlock的提示,搜索一番之后解决掉。后来发现自己对锁这一块一直没有进行过总结。趁着这次机会把锁总结一下。本来是想写火焰图的,下一次再写吧。

mmp 老是赶不上计划。。

首先mysql常用的两种引擎:1 myISAM  2 InnoDB 。今天只聊一聊 innodb这块的 7种锁。

第一 自增锁(Auto-inc locks)

自增锁是一种特殊的表级别锁(table-level lock)专门针对事务插入 AUTO_INCREMENT类型的列。如果一个事务正在往标种插入记录,所有其他事务必须等待,以便这个事务插入的行是连续的主键值。

Innodb 提供了  innodb_autoinc_lock_mode 配置,可以调节该锁的模式

insert 语句分三种类型:simple insert, bulk insert, mixed inser

innodb_autoinc_lock_mode

innodb_autoinc_lock_mode有三个值:

0, traditional

1, consecutive

2, interleaved

traditional

这种模式下,所有的insert语句在开始时都会获得一个表锁autoinc_lock.该锁会一直持有到insert语句执行结束才会被释放。对于一条insert插入多个行记录的语句,他保证了同一条语句插入的行记录的自增ID是连续的。

这个锁并不是事务级别的锁。

在这种模式下,主从复制时,基于语句复制模式下,主和从的同一张表的同一个行记录的自增ID是一样的。但是同样基于语句复制模式下,interleaved模式,也就是innodb_autoinc_lock_mode=2时则不能保证主从同一张表的同一个行记录的自增ID一样。

这种模式下,表的并发性最低。

consecutive【连续的】

这种模式下,insert语句在开始时会获得一个表锁autoinc_lock, simple insert在获取到需要增加的ID的量后,autoinc_lock就会被释放,不必等到语句执行结束。但对于bulk insert,自增锁会被一直持有直到语句执行结束才会被释放。

这种模式仍然保证了同一条语句插入的行记录的自增ID是连续的。

这种模式下的主从复制表现跟traditional模式下一样,但是性能会有所提高。

interleaved 【插入,夹进】

这种模式下,simple insert语句能保证ID是连续的,但是bulk insert的ID则可能有空洞。

主从复制的同一张表下的同一行id有可能不一样。

如何配置:

修改 /etc/my.cnf 

innodb_autoinc_lock_mode=2 # 把相应的mode值写入重启即可。

第二种:共享和排他锁(Shared and Exclusive Locks)

共享锁(S)允许持有该锁的事务读取行。

排他锁(X)允许持有该锁的事务更新或删除行。

如果事务T1在行 r 上持有共享(S)锁,则其他事务T2对行 r 的锁的请求按如下方式处理:

可以立即授予T2对S锁的请求。 结果,T1和T2都在r上持有S锁。多个事务可以拿到一把S锁。

T2的X锁定请求不能立即授予。只有一个事务可以拿到X锁。

如果事务T1在行r上持有排他(X)锁,则不能立即授予其他事务T2对r上任何类型的锁请求。相反,T2必须等待T1释放其对行r的锁定。

缺点不能充分的并行。

第三种 意向锁 (Intention Locks)

InnoDB支持多种粒度的锁,允许行锁和表锁共存。 例如 LOCK TABLES ... WRITE

等语句在指定的表上持有排他锁(X 锁)。 InnoDB用意向锁来实现多粒度级别的锁。意向锁是表级锁,表示事务稍后对表中的行进行加相关类型的锁(共享或排他)。意向锁有两种类型:

意向共享锁(intention shared lock, IS)表示事务打算在表中的各个行上设置共享锁。

意向排他锁(intention exclusive lock, IX)表示事务打算在表中的各个行上设置排他锁。

IS            IX               S               X

IS   兼容         兼容           兼容          互斥

IX  兼容         兼容           互斥           互斥

如果事务请求的锁与现有锁兼容就授予锁,如果冲突则不会。 事务会一直等到现有的锁释放。如果因为事务请求的锁与现有的锁冲突而无法授予,则可能会导致发生死锁(deadlock)错误。

   第四种  记录锁(Record locks)

 

记录锁是对索引记录的锁定。 例如,SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止任何其他事务插入,更新或删除 t.c1 的值为 10 的行。

即使表没有定义索引,记录锁也能锁定索引记录。因为 InnoDB创建了一个隐藏的聚簇索引并使用此索引做记录锁。

执行:SHOW ENGINE INNODB STATUS\G; 可以看到 

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 

trx id 716 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 4; hex 8000000a; asc     ;;

 1: len 6; hex 00000000274f; asc     'O;;

 2: len 7; hex b60000019d0110; asc        ;;

第五种  间隙锁(Gap Locks)

间隙锁,它封锁索引记录中的间隔。为了防止其他事务在间各种插入数据,以导致 不可重复读。如果事务隔离级别降级为 读提交(read commited RC),间隙锁就会自动失效。

本人今天遇到的情况就是因为间隙锁,事务隔离级别为 可重复读(read repeatable)

区间锁是锁定索引记录之间的区间,或锁定在第一个或最后一个索引记录之前的区间上。 例如,SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事务将值15插入到列 t.c1 中,无论列中是否存在任何此类值,因为该范围内所有现有值之间的区间都被锁定。

区间可能跨越单个索引值,多个索引值,甚至可能为空。

区间锁是性能和并发之间权衡的一部分,用于某些事务隔离级别而不是其他级别。

使用唯一索引(unique index )锁定行以搜索唯一行的语句不需要区间锁(表里仅有一条数据)。

(这不包括搜索条件仅包括多列唯一索引的一些列的情况; 在这种情况下,确实会发生区间锁定。)

例如,如果id列具有唯一索引,则以下语句仅对id值为100的行使用索引记录锁,并且其他会话是否在前一个间隙中插入行无关紧要:

SELECT * FROM child WHERE id = 100;

如果id未编入索引或具有非唯一索引,则该语句会锁定前一个区间。

这里还值得注意的是,不同的事务在相同区间上可以存在相互冲突的锁类型。 例如,事务A可以在区间上持有共享区间锁(区间S锁),而事务B在同一区间上持有排他区间锁(区间X锁)。 允许区间锁冲突的原因是,如果从索引中清除记录,则必须合并由不同事务保留在记录上的区间锁。

在InnoDB中区间锁被“抑制”了,这意味着它们的唯一目的是防止其他事务插入区间。区间锁可以共存。一个事务占用的区间锁定不会阻止另一个事务在同一个区间上进行区间锁定。 共享和排他区间锁之间没有区别。它们彼此不冲突,它们执行相同的功能。

如果将事务隔离级别更改为READ COMMITTED或启用系统变量innodb_locks_unsafe_for_binlog,就可以明确禁用区间锁。

此时,搜索和索引扫描会禁用区间锁定,只能用于外键约束检查和重复键检查。

使用 READ COMMITTED 隔离级别或启用 innodb_locks_unsafe_for_binlog 还有其他影响。 MySQL评估 WHERE 条件后,将释放非匹配行的记录锁。对于UPDATE 语句,InnoDB 执行“半一致(semi-consistent)”读取,以便将最新提交的版本返回给MySQL,以便MySQL可以确定该行是否与 UPDATE 的 WHERE 条件匹配。

        在 /etc/my.cnf 

[mysqld]

innodb_locks_unsafe_for_binlog=ON;

关闭区间锁,这个可能会遇到幻读。所以最好使用主键来更新。

第六种 插入意向锁(insert intention locks)

  插入意向锁是一种Gap锁,不是意向锁,在insert操作时产生。

(2)在多事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。

(3)假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。

(4)插入意向锁不会阻止任何锁,对于插入的记录会持有一个记录锁。

普通的Gap Lock 不允许 在 (上一条记录,本记录) 范围内插入数据

插入意向锁Gap Lock 允许 在 (上一条记录,本记录) 范围内插入数据

插入意向锁的作用是为了提高并发插入的性能, 多个事务 同时写入 不同数据 至同一索引范围(区间)内,并不需要等待其他事务完成,不会发生锁等待

第七种  临键锁(Next-key Locks)

Next-Key锁是索引记录上的记录锁和索引记录之前的区间上的区间锁的组合。

InnoDB用以下方式执行行级锁定:当它搜索或扫描表索引时,它会在遇到的索引记录上设置共享锁或排它锁。 因此,行级锁实际上是索引记录锁。索引记录上的Next-Key锁也会影响该索引记录之前的“区间”。也就是说,Next-Key锁是索引记录锁加上索引记录之前的区间上的区间锁。如果一个会话在索引记录R上具有共享锁或排他锁,则另一个会话不能在索引顺序中的R之前的区间中插入新的索引记录。

假设索引包含值10,11,13和20。此索引的可能的Next-Key锁定包括以下间隔,其中圆括号表示排除区间端点,方括号表示包含端点:

(-∞, 10]

(10, 11]

(11, 13]

(13, 20]

(20, +∞)

对于最后一个区间,Next-Key 锁将区间锁定在索引中最大值之上,而“supremum”伪记录的值高于索引中实际的任何值。supremum不是真正的索引记录,因此,实际上,此Next-Key锁仅锁定最大索引值之后的间隙。

默认情况下,InnoDB 在 REPEATABLE READ (RR) 事务隔离级别运行,并禁用系统变量innodb_locks_unsafe_for_binlog 。在这种情况下,InnoDB 使用 Next-Key 锁进行搜索和索引扫描,从而防止幻像行

链接:https://blog.csdn.net/u010841296/article/details/84204701 

链接:https://www.jianshu.com/p/af2f5fed3b6d

链接:https://www.jianshu.com/p/f72b7bfa7467

链接:https://blog.csdn.net/weixin_34293902/article/details/87450202 

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注