株洲网站制作公司有哪些,广州网站开发建设,音乐网页设计模板html,鲜花网站的网络营销与策划书前言
本文将跟大家聊聊 InnoDB 的锁。本文比较长#xff0c;包括一条 SQL 是如何加锁的#xff0c;一些加锁规则、如何分析和解决死锁问题等内容#xff0c;建议耐心读完#xff0c;肯定对大家有帮助的。 为什么需要加锁呢#xff1f; InnoDB 的七种锁介绍 一条 SQL 是…前言
本文将跟大家聊聊 InnoDB 的锁。本文比较长包括一条 SQL 是如何加锁的一些加锁规则、如何分析和解决死锁问题等内容建议耐心读完肯定对大家有帮助的。 为什么需要加锁呢 InnoDB 的七种锁介绍 一条 SQL 是如何加锁的 RR 隔离级别下的加锁规则 如何查看事务加锁情况 死锁案例分析 为什么需要加锁
1.数据库为什么需要加锁呢 在日常生活中如果你心情不好。想要一个人静静不想被比别人打扰你就可以把自己关进房间里并且反锁。 同理对于 MySQL 数据库来说的话一般的对象都是一个事务一个事务来说的。所以如果一个事务内正在写某个 SQL我们肯定不想它被别的事务影响到嘛因此数据库设计大叔就给被操作的 SQL 加上锁。 专业一点的说法: 如果有多个并发请求存取数据在数据就可能会产生多个事务同时操作同一行数据。如果并发操作不加控制不加锁的话就可能写入了不正确的数据或者导致读取了不正确的数据破坏了数据的一致性。因此需要考虑加锁。 1.1 事务并发存在的问题 脏读: 一个事务 A 读取到事务 B 未提交的数据就是脏读。 不可重复读事务 A 被事务 B 干扰到了在事务 A 范围内两个相同的查询读取同一条记录却返回了不同的数据这就是不可重复读。 幻读事务 A 查询一个范围的结果集另一个并发事务 B 往这个范围中插入 / 删除了数据并静悄悄地提交然后事务 A 再次查询相同的范围两次读取得到的结果集不一样了这就是幻读。
1.2 一个加锁和不加锁对比的例子
我们知道 MySQL 数据库有四大隔离级别读已提交RC、可重复读RR、串行化、读未提交。如果是读未提交隔离级别并发情况下它是不加锁的因此就会存在脏读、不可重复读、幻读的问题。
为了更通俗易懂一点还是给大家举个例子吧虽然东西挺简单的。假设现在有表结构和数据如下
CREATE TABLE account ( id int(11) NOT NULL, name varchar(255) DEFAULT NULL, balance int(11) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY un_name_idx (name) USING BTREE) ENGINEInnoDB DEFAULT CHARSETutf8;insert into account(id,name,balance)values (1,Jay,100);insert into account(id,name,balance)values (2,Eason,100);insert into account(id,name,balance)values (3,Lin,100);在 READ-UNCOMMITTED读未提交 隔离级别下假设现在有两个事务 A、B 假设现在 Jay 的余额是 100事务 A 正在准备查询 Jay 的余额 这时候事务 B 先扣减 Jay 的余额扣了 10 最后 A 读到的是扣减后的余额 手动验证了一把流程如下 由上图可以发现事务 A、B 交替执行事务 A 被事务 B 干扰到了因为事务 A 读取到事务 B 未提交的数据, 这就是脏读。为什么存在脏读问题呢这是因为在读未提交的隔离级别下执行写操作并没有对 SQL 加锁因此产生了脏读这个问题。
我们再来看下在串行化隔离级别下同样的 SQL 执行流程又是怎样的呢 为啥会阻塞等待超时呢这是因为串行化隔离级别下对写的 SQL 加锁啦。我们可以再看下加了什么锁命令如下
SET GLOBAL innodb_status_outputON; -- 开启输出
SET GLOBAL innodb_status_output_locksON; -- 开启锁信息输出
SHOW ENGINE INNODB STATUS锁相关的输出内容如下
我们可以看到了这么一把锁lock_mode X locks rec but not gap它到底是一种什么锁呢来来来我们一起来学习下 InnoDB 的七种锁。
2. InnoDB 的七种锁介绍 2.1 共享 / 排他锁
InnoDB 呢实现了两种标准的行级锁共享锁简称 S 锁、排他锁简称 X 锁。 共享锁简称为 S 锁在事务要读取一条记录时需要先获取该记录的 S 锁。 排他锁简称 X 锁在事务需要改动一条记录时需要先获取该记录的 X 锁。
如果事务T1持有行 R 的S锁那么另一个事务T2请求访问这条记录时会做如下处理 T2 请求S锁立即被允许结果 T1和T2都持有 R 行的S锁 T2 请求X锁不能被立即允许, 此操作会阻塞
如果T1持有行 R 的X锁那么T2请求 R 的X、S锁都不能被立即允许T2 必须等待T1释放X锁才可以因为X锁与任何的锁都不兼容。
S锁和X锁的兼容关系如下图表格 X锁和S锁是对于行记录来说的话因此可以称它们为行级锁或者行锁。我们认为行锁的粒度就比较细其实一个事务也可以在表级别下加锁对应的我们称之为表锁。给表加的锁也是可以分为X锁和S锁的哈。
如果一个事务给表已经加了S锁则 别的事务可以继续获得该表的S锁也可以获得该表中某些记录的S锁。 别的事务不可以继续获得该表的X锁也不可以获得该表中某些记录的X锁。
如果一个事务给表加了X锁那么 别的事务不可以获得该表的S锁也不可以获得该表某些记录的S锁。 别的事务不可以获得该表的X锁也不可以继续获得该表某些记录的X锁。
2.2 意向锁
什么是意向锁呢意向锁是一种不与行级锁冲突的表级锁。未来的某个时刻事务可能要加共享或者排它锁时先提前声明一个意向。注意一下意向锁是一个表级别的锁哈。
为什么需要意向锁呢 或者换个通俗的说法为什么要加共享锁或排他锁时的时候需要提前声明个意向锁呢呢 因为 InnoDB 是支持表锁和行锁共存的如果一个事务 A 获取到某一行的排他锁并未提交这时候事务 B 请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的因此事务 B 想对这个表加共享锁时需要保证没有其他事务持有这个表的表排他锁同时还要保证没有其他事务持有表中任意一行的排他锁。 然后问题来了你要保证没有其他事务持有表中任意一行的排他锁的话去遍历每一行这样显然是一个效率很差的做法。为了解决这个问题InnoDB 的设计大叔提出了意向锁。 意向锁是如何解决这个问题的呢 我们来看下
意向锁分为两类 意向共享锁简称IS锁当事务准备在某些记录上加 S 锁时需要现在表级别加一个IS锁。 意向排他锁简称IX锁当事务准备在某条记录上加上 X 锁时需要现在表级别加一个IX锁。
比如 select ... lock in share mode要给表设置IS锁; select ... for update要给表设置IX锁;
意向锁又是如何解决这个效率低的问题呢 如果一个事务 A 获取到某一行的排他锁并未提交, 这时候表上就有意向排他锁和这一行的排他锁。这时候事务 B 想要获取这个表的共享锁表锁此时因为检测到事务 A 持有了表的意向排他锁因此事务 A 必然持有某些行的排他锁也就是说事务 B 对表的加锁表锁请求需要阻塞等待不再需要去检测表的每一行数据是否存在排他锁啦。这样效率就高很多啦。 意向锁仅仅表明意向的锁意向锁之间并不会互斥是可以并行的整体兼容性如下图所示 2.3 记录锁Record Lock
记录锁是最简单的行锁仅仅锁住一行。如SELECT c1 FROM t WHERE c1 10 FOR UPDATE如果 c1 字段是主键或者是唯一索引的话这个 SQL 会加一个记录锁Record Lock
记录锁永远都是加在索引上的即使一个表没有索引InnoDB 也会隐式的创建一个索引并使用这个索引实施记录锁。它会阻塞其他事务对这行记录的插入、更新、删除。
一般我们看死锁日志时都是找关键词比如lock_mode X locks rec but not gap就表示一个 X 型的记录锁。记录锁的关键词就是 rec but not gap。以下就是一个记录锁的日志
RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t trx id 10078 lock_mode X locks rec but not gapRecord 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 ;;2.4 间隙锁Gap Lock
为了解决幻读问题InnoDB 引入了间隙锁(Gap Lock)。间隙锁是一种加在两个索引之间的锁或者加在第一个索引之前或最后一个索引之后的间隙。它锁住的是一个区间而不仅仅是这个区间中的每一条数据。
比如lock_mode X locks gap before rec表示 X 型 gap 锁。以下就是一个间隙锁的日志
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table test2.account
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 3; hex 576569; asc Wei;;1: len 4; hex 80000002; asc ;;2.5 临键锁 (Next-Key Lock)
Next-key 锁是记录锁和间隙锁的组合它指的是加在某条记录以及这条记录前面间隙上的锁。说得更具体一点就是: 临键锁会封锁索引记录本身以及索引记录之前的区间即它的锁区间是前开后闭比如(5,10]。
如果一个会话占有了索引记录 R 的共享 / 排他锁其他会话不能立刻在 R 之前的区间插入新的索引记录。官网是这么描述的 If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. 2.6 插入意向锁
插入意向锁, 是插入一行记录操作之前设置的一种间隙锁。 这个锁释放了一种插入方式的信号。它解决的问题是多个事务在同一个索引同一个范围区间插入记录时如果插入的位置不冲突就不会阻塞彼此。
假设有索引值 4、7几个不同的事务准备插入 5、6每个锁都在获得插入行的独占锁之前用插入意向锁各自锁住了 4、7 之间的间隙但是不阻塞对方因为插入行不冲突。以下就是一个插入意向锁的日志
RECORD LOCKS space id 31 page no 3 n bits 72 index PRIMARY of table test.childtrx id 8731 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1: len 6; hex 000000002215; asc ;; 2: len 7; hex 9000000172011c; asc r ;;...锁模式兼容矩阵横向是已持有锁纵向是正在请求的锁如下 2.7 自增锁
自增锁是一种特殊的表级别锁。它是专门针对AUTO_INCREMENT类型的列对于这种列如果表中新增数据时就会去持有自增锁。简言之如果一个事务正在往表中插入记录所有其他事务的插入必须等待以便第一个事务插入的行是连续的主键值。
官方文档是这么描述的 An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values. 假设有表结构以及自增模式是 1如下
mysql create table t0 (id int NOT NULL AUTO_INCREMENT,name varchar(16),primary key ( id));mysql show variables like %innodb_autoinc_lock_mode%;---------------------------------| Variable_name | Value |---------------------------------| innodb_autoinc_lock_mode | 1 |---------------------------------1 row in set, 1 warning (0.01 sec)设置事务 A 和 B 交替执行流程如下 通过上图我们可以看到当我们在事务 A 中进行自增列的插入操作时另外会话事务 B 也进行插入操作这种情况下会发生 2 个奇怪的现象 事务 A 会话中的自增列好像直接增加了 2 个值。如上图中步骤 7、8 事务 B 会话中的自增列直接从 2 开始增加的。如上图步骤 5、6
自增锁是一个表级别锁那为什么会话 A 事务还没结束事务会话 B 可以执行插入成功呢不是应该锁表嘛
这是因为在参数innodb_autoinc_lock_mode上这个参数设置为1的时候相当于将这种auto_inc lock弱化为了一个更轻量级的互斥自增长机制去实现官方称之为mutex。
innodb_autoinc_lock_mode还可以设置为 0 或者 2 0表示传统锁模式使用表级AUTO_INC锁。一个事务的INSERT-LIKE语句在语句执行结束后释放 AUTO_INC 表级锁而不是在事务结束后释放。 1: 连续锁模式, 连续锁模式对于Simple inserts不会使用表级锁而是使用一个轻量级锁来生成自增值因为 InnoDB 可以提前知道插入多少行数据。自增值生成阶段使用轻量级互斥锁来生成所有的值而不是一直加锁直到插入完成。对于bulk inserts类语句使用 AUTO_INC 表级锁直到语句完成。 2: 交错锁模式, 所有的INSERT-LIKE语句都不使用表级锁而是使用轻量级互斥锁。 INSERT-LIKE: 指所有的插入语句包括INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA 等。 Simple inserts: 指在插入前就能确定插入行数的语句包括INSERT、REPLACE不包含 INSERT…ON DUPLICATE KEY UPDATE 这类语句。 Bulk inserts: 指在插入钱不能确定行数的语句包括INSERT … SELECT/REPLACE … SELECT/LOAD DATA。 3. 一条 SQL 是如何加锁的呢 介绍完 InnoDB 的七种锁后我们来看下一条 SQL 是如何加锁的哈现在可以分 9 种情况进行 组合一查询条件是主键RC 隔离级别 组合二查询条件是唯一索引RC 隔离级别 组合三查询条件是普通索引RC 隔离级别 组合四查询条件上没有索引RC 隔离级别 组合五查询条件是主键RR 隔离级别 组合六查询条件是唯一索引RR 隔离级别 组合七查询条件是普通索引RR 隔离级别 组合八查询条件上没有索引RR 隔离级别 组合九Serializable 隔离级别
3.1 查询条件是主键 RC 隔离级别
在 RC读已提交 的隔离级别下对查询条件列是主键 id 的话会加什么锁呢
我们搞个简单的表初始化几条数据
create table t1 (id int,name varchar(16),primary key ( id));
insert into t1 values(1,a),(3,c),(6,b),(9,a),(10,d);假设给定 SQLdelete from t1 where id 6;id 是主键。在 RC 隔离级别下只需要将主键上id 6的记录加上X锁即可。 我们来验证一下吧先开启事务会话 A先执行以下操作
begin;
//删除id6的这条记录
delete from t1 where id 6;接着开启事务会话 B
begin;
update t1 set nameb1 where id 6;
//发现这个阻塞等待最后超时释放锁了验证流程图如下 事务会话 B 对id6的记录执行更新时发现阻塞了打开看下加了什么锁。发现是因为id6这一行加了一个 X 型的记录锁 如果我们事务 B 不是对id6执行更新而是其他记录的话是可以顺利执行的如下 结论就是在 RC读已提交 的隔离级别下对查询条件是主键 id 的场景会加一个排他锁X 锁或者说加一个 X 型的记录锁。
3.2 查询条件是唯一索引 RC 隔离级别
如果查询条件 id只是一个唯一索引呢那在 RC读提交隔离级别下又加了什么锁呢我们搞个新的表初始化几条数据
create table t2 (name varchar(16),id int,primary key (name),unique key(id));
insert into t2 values(a,1),(c,3),(b,6),(d,9);id 是唯一索引name 是主键的场景下我们给定 SQLdelete from t2 where id 6;。
在 RC 隔离级别下该 SQL 需要加两个X锁一个对应于 id 唯一索引上的id 6的记录另一把锁对应于聚簇索引上的[name’b’,id6]的记录。 为什么主键索引上的记录也要加锁呢 如果并发的一个 SQL是通过主键索引来更新update t2 set id 666 where name b;此时如果 delete 语句没有将主键索引上的记录加锁那么并发的 update 就会感知不到 delete 语句的存在违背了同一记录上的更新 / 删除需要串行执行的约束。 3.3 查询条件是普通索引 RC 隔离级别
如果查询条件是普通的二级索引在 RC读提交隔离级别下又加了什么锁呢 若 id 列是普通索引那么对应的所有满足 SQL 查询条件的记录都会加上锁。同时这些记录对应主键索引也会上锁。 我们初始化下表结构和数据
create table t3 (name varchar(16),id int,primary key (name),key(id));
insert into t3 values(a,1),(c,3),(b,6),(e,6),(d,9);加锁示意图如下 我们来验证一下先开启事务会话 A先执行以下操作
begin;
//删除id6的这条记录
delete from t3 where id 6;接着开启事务会话 B
begin;
update t3 set id7 where name e;
//发现这个阻塞等待最后超时释放锁了实践流程如下 事务会话 B 为什么会阻塞等待超时是因为事务会话 A 的delete语句确实有加普通索引的 X 锁 3.4 查询条件列无索引 RC 隔离级别
如果 id 没有加索引只是一个常规的列在 RC读提交隔离级别下又加了什么锁呢 若 id 列上没有索引MySQL 会走聚簇索引进行全表扫描过滤。每条记录都会加上 X 锁。但是为了效率考虑MySQL 在这方面进行了改进在扫描过程中若记录不满足过滤条件会进行解锁操作。同时优化违背了 2PL 原则。 初始化下表结构和数据
create table t4 (name varchar(16),id int,primary key (name));
insert into t4 values(a,1),(c,3),(b,6),(e,6),(d,9);加锁示意图图下 验证流程如下先开启事务会话 A先执行以下操作
begin;
//删除id6的这条记录
delete from t4 where id 6;接着开启事务会话 B
begin;
//可以执行MySQL因为效率问题解锁了
update t4 set namef where id3;
//阻塞等待
update t4 set namef where id6;验证结果如下
3.5 查询条件是主键 RR 隔离级别
给定 SQLdelete from t1 where id 6;如果 id 是主键的话在 RR 隔离级别下跟 RC 隔离级别加锁是一样的也都是在id 6这条记录上加上X锁。大家感兴趣可以照着 3.1 小节例子自己验证一下哈。
3.6 查询条件是唯一索引 RR 隔离级别
给定 SQLdelete from t1 where id 6;如果 id 是唯一索引的话在 RR 隔离级别下跟 RC 隔离级别加锁也是一样的哈加了两个X锁id 唯一索引满足条件的记录上一个对应的主键索引上的记录一个。
3.7 查询条件是普通索引 RR 隔离级别
如果查询条件是普通的二级索引在 RR可重复读的隔离级别下除了会加X锁还会加间隙Gap锁。Gap 锁的提出是为了解决幻读问题引入的它是一种加在两个索引之间的锁。
假设有表结构和初始化数据如下
CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINEInnoDB;
insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);如果一条更新语句update t5 set dd1 where c 10加锁示意图如下 我们来验证一下吧先开启事务会话 A先执行以下操作
begin;update t5 set dd1 where c 10;接着开启事务会话 B
begin;
insert into t5 values(12,12,12);
//阻塞等待最后超时释放锁了验证流程图如下 为什么会阻塞呢因此c10这个记录更新时不仅会有两把X锁还会把区间10,15加间隙锁因此要插入12,12,12记录时会阻塞。
3.8 查询条件列无索引 RR 隔离级别
如果查询条件列没有索引呢又是如何加的锁呢
假设有表结构和数据如下
CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id)) ENGINEInnoDB;
insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);给定一条更新语句update t5 set dd1 where c 10因为c列没有索引加锁示意图如下 如果查询条件列没有索引主键索引的所有记录都将加上X锁每条记录间也都加上间隙Gap锁。大家可以想象一下任何加锁并发的 SQL都是不能执行的全表都是锁死的状态。如果表的数据量大那效率就更低。 在这种情况下MySQL 做了一些优化即semi-consistent read对于不满足条件的记录MySQL 提前释放锁同时 Gap 锁也会释放。而semi-consistent read是如何触发的呢要么在Read Committed隔离级别下要么在Repeatable Read隔离级别下设置了innodb_locks_unsafe_for_binlog参数。但是semi-consistent read本身也会带来其他的问题不建议使用。 我们来验证一下哈先开启事务会话 A先执行以下操作
begin;
update t5 set dd1 where c 20;接着开启事务会话 B
begin;
insert into t5 values(16,16,16);
//插入阻塞等待
update t5 set dd1 where c 16;
//更新阻塞等待我们去更新一条不存在的c16的记录也会被 X 锁阻塞的。验证如下 3.9 Serializable 串行化
在 Serializable 串行化的隔离级别下对于写的语句比如update account set balance balance-10 where name‘Jay’;跟 RC 和 RR 隔离级别是一样的。不一样的地方是在查询语句如select balance from account where name ‘Jay’;在 RC 和 RR 是不会加锁的但是在 Serializable 串行化的隔离级别即会加锁。
如文章开始第一小节的那个例子就是类似的 4. RR 隔离级别下加锁规则到底是怎样的呢
对于 RC 隔离级别加的排他锁X 锁是比较好理解的哪里更新就锁哪里嘛。但是 RR 隔离级别间隙锁是怎么加的呢我们一起来学习一下。
对 InnoDb 的锁来说面试的时候问的比较多就是Record lock、Gap lock、Next-key lock。接下来我们来学习RR 隔离级别到底一个锁是怎么加上去的。丁奇的 MySQL45 讲有讲到RR 隔离级别是如何加锁的。大家有兴趣可以去订购看下哈非常不错的课程。
首先 MySQL 的版本是5.x 系列 5.7.248.0 系列 8.0.13。加锁规则一共包括两个原则、两个优化和一个bug。 原则 1加锁的基本单位都是next-key lock。next-key lock临键锁是前开后闭区间。 原则 2查找过程中访问到的对象才会加锁。 优化 1索引上的等值查询给唯一索引加锁的时候next-key lock退化为行锁Record lock。 优化 2索引上的等值查询向右遍历时且最后一个值不满足等值条件的时候next-key lock退化为间隙锁Gap lock。 一个 bug唯一索引上的范围查询会访问到不满足条件的第一个值为止。
假设有表结构和数据如下
CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINEInnoDB;
insert into t5 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);分 7 个案例去分析哈 等值查询间隙锁 非唯一索引等值锁 主键索引范围锁 非唯一索引范围锁 唯一索引范围锁 bug 普通索引上存在 “等值” 的例子 limit 语句减少加锁范围
4.1 案例一等值查询间隙锁
我们同时开启 A、B、C 三个会话事务如下 发现事务 B 会阻塞等待而 C 可以执行成功。如下 为什么事务 B 会阻塞呢 这是因为根据加锁原则 1加锁基本单位是next-key lock因此事务会话 A 的加锁范围是5,10]这里为什么是区间5,10]这是因为更新的记录所在的表已有数据的区间就是 5-10 哈又因为next-key lock是左开右闭的所以加锁范围是5,10]。 同时根据优化 2这是一个等值查询 (id6)而 id10 不满足查询条件。所以next-key lock退化成间隙Gap锁因此最终加锁的范围是(5,10)。 然后事务Session B中你要插入的是 9,9 在区间 (5,10) 内而区间 (5,10) 都被锁了。因此事务 B 会阻塞等到。
为什么事务 C 可以正常执行呢
这是因为锁住的区间是(5,10)没有包括 10所以事务 C 可以正常执行。
4.2 案例二非唯一索引等值锁
按顺序执行事务会话 A、B、C如下 发现事务 B 可以执行成功而 C 阻塞等待。如下 为什么事务会话 B 没有阻塞事务会话 C 却阻塞了
事务会话 A 执行时会给索引树c5的这一行加上读共享锁。 根据加锁原则 1加锁单位是next-key lock因此会加上next-key lock(0,5]。 因为 c 只是普通索引所以仅访问c5这一条记录时不会马上停下来需要继续向右遍历查到c10才结束。根据加锁原则 2访问到的都要加锁因此要给(5,10]加next-key lock。 由加锁优化 2等值判断向右遍历最后一个值 10不满足c5 这个等值条件因此退化成间隙锁 (5,10)。 根据加锁原则 2 只有访问到的对象才会加锁事务 A 的这个查询使用了覆盖索引没有回表并不需要访问主键索引因此主键索引上没有加任何锁事务会话 B 是对主键 id 的更新因此事务会话 B 的update语句不会阻塞。 但是事务会话 C要插入一个6,6,6) 的记录时会被事务会话 A 的间隙锁(5,10)锁住因此事务会话 C 阻塞了。
4.3 案例三主键索引范围锁
主键范围查询又是怎么加锁的呢比如给定 SQL:
select * from t5 where id10 and id11 for update;按顺序执行事务会话 A、B、C如下 执行结果如下 发现事务会话 B 中插入 12即insert into t5 values(12,12,12);时阻塞了而插入 6insert into t5 values(6,6,6);却可以顺利执行。同时事务 C 中Update t5 set dd1 where id 15;也会阻塞为什么呢
事务会话 A 执行时要找到第一个id10的行 根据加锁原则 1加锁单位是next-key lock因此会加上next-key lock(5,10]。 又因为 id 是主键也就是唯一值因此根据优化 1索引上的等值查询给唯一索引加锁时next-key lock退化为行锁Record lock。所以只加了id10这个行锁。 范围查找就往后继续找找到id15这一行停下来因此还需要加next-key lock(10,15]。
事务会话 A 执行完后加的锁是id10这个行锁以及临键锁next-key lock(10,15]。这就是为什么事务 B 插入 6 那个记录可以顺利执行插入 12 就不行啦。同理事务 C 那个更新 id15 的记录也是会被阻塞的。
4.4 案例四非唯一索引范围锁
如果是普通索引范围查询又加什么锁呢按顺序执行事务会话 A、B、C如下 执行结果如下 发现事务会话 B 和事务会话 C 的执行 SQL 都被阻塞了。
这是因为事务会话 A 执行时要找到第一个c10的行 根据加锁原则 1加锁单位是 next-key lock因此会加上next-key lock(5,10]。又因为 c 不是唯一索引所以它不会退化为行锁。因此加的锁还是next-key lock(5,10]。 范围查找就往后继续找找到id15这一行停下来因此还需要加next-key lock(10,15]。
因此事务 B 和事务 C 插入的insert into t5 values(6,6,6);和Update t5 set dd1 where c 15; 都会阻塞。
4.5 案例五唯一索引范围锁 bug
前面四种方案中加锁的两个原则和两个优化都已经用上啦那个唯一索引范围 bug 是如何触发的呢
按顺序执行事务会话 A、B、C如下 执行结果如下 发现事务 B 的更新语句Update t5 set dd1 where id 20; 和事务 Cinsert into t5 values(18,18,18);的插入语句均已阻塞了。
这是因为事务会话 A 执行时要找到第一个id15的行根据加锁原则 1加锁单位是next-key lock因此会加上next-key lock(10,15]。因为 id 是主键即唯一的因此循环判断到 id15 这一行就应该停止了。但是实现上InnoDB 会往前扫描到第一个不满足条件的行为止直到扫描到id20。而且由于这是个范围扫描因此索引 id 上的(15,20]这个 next-key lock 也会被锁上。
所以事务 B 要更新 id20 这一行时会阻塞锁住。同样地事务会话 C 要插入id16的一行也会被锁住。
4.6 案例六普通索引上存在 “等值” 的例子
如果查询条件列是普通索引且存在相等的值加锁又是怎样的呢
在原来 t5 表的数据基础上插入
insert into t5 values(28,10,66);则c索引树如下 c 索引值有相等的但是它们对应的主键是有间隙的。比如c10id10和c10id28之间。
我们来看个例子按顺序执行事务会话 A、B、C如下 执行结果如下 为什么事务 B 插入语句会阻塞事务 C 的更新语句不会呢 这是因为事务会话 A 在遍历的时候先访问第一个c10的记录。它根据原则 1加一个 (c5,id5) 到 (c10,id10) 的 next-key lock。 然后事务会话 A 向右查找直到碰到 (c15,id15) 这一行循环才结束。根据优化 2这是一个等值查询向右查找到了不满足条件的行所以会退化成(c10,id10) 到 (c15,id15)的间隙 Gap 锁。即事务会话 A 这个select...for update语句在索引 c 上的加锁范围就是下图灰色阴影部分的 因为 c13 是这个区间内的所以事务 B 插入insert into t5 values(13,13,13);会阻塞。因为根据优化 2已经退化成 (c10,id10) 到 (c15,id15) 的间隙 Gap 锁, 即不包括 c15所以事务 CUpdate t5 set dd1 where c15不会阻塞
4.7 案例七limit 语句减少加锁范围
如果一个 SQL 有 limit会不会对加锁有什么影响呢我们用 4.6 的例子然后给查询语句加个 limit
Select * from t5 where c10 limit 2 for update;事务 A、B 执行如下 发现事务 B 并没有阻塞而是可以顺利执行
这是为什么呢跟上个例子怎么事务会话 B 的 SQL 却不会阻塞了事务会话 A 的select只是加多了一个limit 2。
这是因为明确加了limit 2的限制后因此在遍历到 (c10, id30) 这一行之后满足条件的语句已经有两条循环就结束了。因此索引 c 上的加锁范围就变成了从c5,id5) 到c10,id30) 这个前开后闭区间如下图所示 索引平时我们写 SQL 的时候比如查询select或者delete语句时尽量加一下limit哈你看着这个例子不就减少了锁范围了嘛哈哈。
5. 如何查看事务加锁情况
我门怎么查看执行中的 SQL 加了什么锁呢或者换个说法如何查看事务的加锁情况呢有这两种方法 使用infomation_schema数据库中的表获取锁信息 使用 show engine innodb status 命令
5.1 使用 infomation_schema 数据库中的表获取锁信息
infomation_schema数据库中有几个表跟锁紧密关联的。 INNODB_TRX该表存储了 InnoDB 当前正在执行的事务信息包括事务 id、事务状态比如事务是在运行还是在等待获取某个所等。 INNODB_LOCKS该表记录了一些锁信息包括两个方面1. 如果一个事务想要获取某个锁但未获取到则记录该锁信息。2. 如果一个事务获取到了某个锁但是这个锁阻塞了别的事务则记录该锁信息。 INNODB_LOCK_WAITS: 表明每个阻塞的事务是因为获取不到哪个事务持有的锁而阻塞。
5.1.1 INNODB_TRX
我们在一个会话中执行加锁的语句在另外一个会话窗口即可查看INNODB_TRX的信息啦如下 表中可以看到一个事务 id 为1644837正在运行汇中它的隔离级别为REPEATABLE READ。我们一般关注这几个参数 trx_tables_locked该事务当前加了多少个表级锁。 trx_rows_locked表示当前加了多少个行级锁。 trx_lock_structs表示该事务生成了多少个内存中的锁结构。
5.1.2 INNODB_LOCKS
一般系统中发生某个事务因为获取不到锁而被阻塞时该表才会有记录。
事务 A、B 执行如下 使用select * from information_schema.INNODB_LOCKS;查看 可以看到两个事务 Id 1644842和1644843都持有什么锁就是看那个lock_mode和lock_type哈。但是并看不出是哪个锁在等待那个锁导致的阻塞这时候就可以看INNODB_LOCK_WAITS表啦。
5.1.3 INNODB_LOCK_WAITS
INNODB_LOCK_WAITS 表明每个事务是因为获取不到哪个事务持有的锁而阻塞。 requesting_trx_id表示因为获取不到锁而被阻塞的事务的事务 id blocking_trx_id表示因为获取到别的事务需要的锁而导致其被阻塞的事务的事务 Id。 即requesting_trx_id表示事务 B 的事务 Idblocking_trx_id表示事务 A 的事务 Id。
5.2 show engine innodb status
INNODB_LOCKS 和 INNODB_LOCK_WAITS 在 MySQL 8.0 已被移除其实就是不鼓励我们用这两个表来获取表信息。而我们还可以用show engine innodb status获取当前系统各个事务的加锁信息。
在看死锁日志的时候我们一般先把这个变量innodb_status_output_locks打开哈它是 MySQL 5.6.16 引入的
set global innodb_status_output_locks on;在 RR 隔离级别下我们交替执行事务 A 和 B show engine innodb status 查看日志如下
TRANSACTIONS
------------
Trx id counter 1644854
Purge done for trxs n:o 1644847 undo n:o 0 state: running but idle
History list length 32
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283263895935640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1644853, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 11956, query id 563 localhost ::1 root update
insert into t5 values(6,6,6)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table test2.t5 trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 4; hex 8000000a; asc ;;------------------
TABLE LOCK table test2.t5 trx id 1644853 lock mode IX
RECORD LOCKS space id 267 page no 4 n bits 80 index c of table test2.t5 trx id 1644853 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 4; hex 8000000a; asc ;;这结构锁的关键词需要记住一下哈 lock_mode X locks gap before rec表示 X 型的 gap 锁 lock_mode X locks rec but not gap表示 X 型的记录锁Record Lock lock mode X 一般表示 X 型临键锁next-key 锁
以上的锁日志我们一般关注点是一下这几个地方 TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED表示它在等这个锁 RECORD LOCKS space id 267 page no 4 n bits 80 index c of table test2.t5 trx id 1644853 lock_mode X locks gap before rec insert intention waiting表示一个锁结构这个锁结构的 Space ID 是 267page number 是 4n_bits 属性为 80对应的索引是c这个锁结构中存放的锁类型是 X 型的插入意向 Gap 锁。 0: len 4; hex 8000000a; asc ;;对应加锁记录的详细信息8000000a 代表的值就是 10a 的 16 进制是 10。 TABLE LOCK table test2.t5 trx id 1644853 lock mode IX 表示一个插入意向表锁
这个日志例子其实理解起来就是事务 A 持有了索引 c 的间隙锁~10而事务 B 想获得这个 gap 锁而获取不到就一直在等待这个插入意向锁。
6. 手把手死锁案例分析
如果发生死锁了我们应该如何分析呢一般分为四个步骤 show engine innodb status查看最近一次死锁日志。 分析死锁日志找到关键词TRANSACTION 分析死锁日志查看正在执行的 SQL 看 SQL 持有什么锁又在等待什么锁。
6.1 一个死锁的简单例子
表结构和数据如下
CREATE TABLE t6 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINEInnoDB;
insert into t6 values(5,5,5),(10,10,10);我们开启 A、B 事务执行流程如下
6.2 分析死锁日志
show engine innodb status查看最近一次死锁日志。如下
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-03 22:53:22 0x2eb4
*** (1) TRANSACTION:
TRANSACTION 1644867, ACTIVE 31 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 7068, query id 607 localhost ::1 root statistics
Select * from t6 where id10 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table test2.t6 trx id 1644867 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 6; hex 00000019193c; asc ;;2: len 7; hex dd00000191011d; asc ;;3: len 4; hex 8000000a; asc ;;4: len 4; hex 8000000a; asc ;;*** (2) TRANSACTION:
TRANSACTION 1644868, ACTIVE 17 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 11956, query id 608 localhost ::1 root statistics
Select * from t6 where id5 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table test2.t6 trx id 1644868 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 6; hex 00000019193c; asc ;;2: len 7; hex dd00000191011d; asc ;;3: len 4; hex 8000000a; asc ;;4: len 4; hex 8000000a; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 268 page no 3 n bits 72 index PRIMARY of table test2.t6 trx id 1644868 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000005; asc ;;1: len 6; hex 00000019193c; asc ;;2: len 7; hex dd000001910110; asc ;;3: len 4; hex 80000005; asc ;;4: len 4; hex 80000005; asc ;;先找到关键词TRANSACTION可以发现两部分的事务日志如下 查看正在执行产生死锁的对应的 SQL如下 查看分开两部分的 TRANSACTION分别持有什么锁和等待什么锁。 所谓的死锁其实就是我持有你的需要的锁你持有我需要的锁形成相互等待的闭环。所以排查死锁问题时照着这个思维去思考就好啦。
最后
本文参考了极客时间《MySQL45 讲》其实这个课程挺好的我看了几遍啦。建议有兴趣的小伙伴们都买来看看哈。
如果这篇文章对您有所帮助或者有所启发的话帮忙扫描下发二维码关注一下您的支持是我坚持写作最大的动力。
求一键三连点赞、转发、在看。
参考资料
[1]一条简单的更新语句MySQL 是如何加锁的: https://urlify.cn/f6ZnIn
[2]极客时间《MySQL45 讲》: https://time.geekbang.org/column/article/75659
[3]这次终于懂了InnoDB 的七种锁: https://andyoung.blog.csdn.net/article/details/121689809
[4] MySQL InnoDB 中的锁 - 自增锁AUTO-INC Locks: https://blog.csdn.net/fofcn/article/details/123243225?spm1001.2101.3001.6650.4utm_mediumdistribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-4.pc_relevant_defaultdepth_1-utm_sourcedistribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-4.pc_relevant_defaultutm_relevant_index9 文章转载自： http://www.morning.kfmnf.cn.gov.cn.kfmnf.cn http://www.morning.tgxrm.cn.gov.cn.tgxrm.cn http://www.morning.pphbn.cn.gov.cn.pphbn.cn http://www.morning.nktxr.cn.gov.cn.nktxr.cn http://www.morning.plfy.cn.gov.cn.plfy.cn http://www.morning.fstesen.com.gov.cn.fstesen.com http://www.morning.wmmjw.cn.gov.cn.wmmjw.cn http://www.morning.qfrsm.cn.gov.cn.qfrsm.cn http://www.morning.kxscs.cn.gov.cn.kxscs.cn http://www.morning.nwbnt.cn.gov.cn.nwbnt.cn http://www.morning.zlwg.cn.gov.cn.zlwg.cn http://www.morning.bqwrn.cn.gov.cn.bqwrn.cn http://www.morning.hgcz.cn.gov.cn.hgcz.cn http://www.morning.mhcys.cn.gov.cn.mhcys.cn http://www.morning.bbmx.cn.gov.cn.bbmx.cn http://www.morning.nhpmn.cn.gov.cn.nhpmn.cn http://www.morning.fksdd.cn.gov.cn.fksdd.cn http://www.morning.yhpl.cn.gov.cn.yhpl.cn http://www.morning.lqpzb.cn.gov.cn.lqpzb.cn http://www.morning.lwcqh.cn.gov.cn.lwcqh.cn http://www.morning.phnbd.cn.gov.cn.phnbd.cn http://www.morning.tdqhs.cn.gov.cn.tdqhs.cn http://www.morning.sjwqr.cn.gov.cn.sjwqr.cn http://www.morning.snbry.cn.gov.cn.snbry.cn http://www.morning.wlstn.cn.gov.cn.wlstn.cn http://www.morning.srrzb.cn.gov.cn.srrzb.cn http://www.morning.xmnlc.cn.gov.cn.xmnlc.cn http://www.morning.nqwz.cn.gov.cn.nqwz.cn http://www.morning.lsmgl.cn.gov.cn.lsmgl.cn http://www.morning.jstggt.cn.gov.cn.jstggt.cn http://www.morning.tbqxh.cn.gov.cn.tbqxh.cn http://www.morning.sgwr.cn.gov.cn.sgwr.cn http://www.morning.fdfsh.cn.gov.cn.fdfsh.cn http://www.morning.bmssj.cn.gov.cn.bmssj.cn http://www.morning.ptwrz.cn.gov.cn.ptwrz.cn http://www.morning.dtzsm.cn.gov.cn.dtzsm.cn http://www.morning.tlfyb.cn.gov.cn.tlfyb.cn http://www.morning.sqhtg.cn.gov.cn.sqhtg.cn http://www.morning.mlmwl.cn.gov.cn.mlmwl.cn http://www.morning.ftdlg.cn.gov.cn.ftdlg.cn http://www.morning.mhsmj.cn.gov.cn.mhsmj.cn http://www.morning.trbxt.cn.gov.cn.trbxt.cn http://www.morning.nbhft.cn.gov.cn.nbhft.cn http://www.morning.nkqxb.cn.gov.cn.nkqxb.cn http://www.morning.rfgkf.cn.gov.cn.rfgkf.cn http://www.morning.kkwbw.cn.gov.cn.kkwbw.cn http://www.morning.plchy.cn.gov.cn.plchy.cn http://www.morning.txnqh.cn.gov.cn.txnqh.cn http://www.morning.zqfjn.cn.gov.cn.zqfjn.cn http://www.morning.qwpdl.cn.gov.cn.qwpdl.cn http://www.morning.ymyhg.cn.gov.cn.ymyhg.cn http://www.morning.xdpjf.cn.gov.cn.xdpjf.cn http://www.morning.ghlyy.cn.gov.cn.ghlyy.cn http://www.morning.dbrnl.cn.gov.cn.dbrnl.cn http://www.morning.jjpk.cn.gov.cn.jjpk.cn http://www.morning.yrpd.cn.gov.cn.yrpd.cn http://www.morning.nfpct.cn.gov.cn.nfpct.cn http://www.morning.dgknl.cn.gov.cn.dgknl.cn http://www.morning.bfjtp.cn.gov.cn.bfjtp.cn http://www.morning.fhsgw.cn.gov.cn.fhsgw.cn http://www.morning.kmwsz.cn.gov.cn.kmwsz.cn http://www.morning.ntyks.cn.gov.cn.ntyks.cn http://www.morning.rybr.cn.gov.cn.rybr.cn http://www.morning.pbknh.cn.gov.cn.pbknh.cn http://www.morning.qwgct.cn.gov.cn.qwgct.cn http://www.morning.rhmk.cn.gov.cn.rhmk.cn http://www.morning.dbrdg.cn.gov.cn.dbrdg.cn http://www.morning.krdmn.cn.gov.cn.krdmn.cn http://www.morning.pjbhk.cn.gov.cn.pjbhk.cn http://www.morning.qxwwg.cn.gov.cn.qxwwg.cn http://www.morning.sbqrm.cn.gov.cn.sbqrm.cn http://www.morning.rfqk.cn.gov.cn.rfqk.cn http://www.morning.wklyk.cn.gov.cn.wklyk.cn http://www.morning.cknsx.cn.gov.cn.cknsx.cn http://www.morning.hwbf.cn.gov.cn.hwbf.cn http://www.morning.drpbc.cn.gov.cn.drpbc.cn http://www.morning.lrprj.cn.gov.cn.lrprj.cn http://www.morning.nfbxgtj.com.gov.cn.nfbxgtj.com http://www.morning.cffwm.cn.gov.cn.cffwm.cn http://www.morning.fdhwh.cn.gov.cn.fdhwh.cn
