了解MySQL死锁日志

锁的种类&概念

  • Shared and Exclusive Locks

    1. Shared lock: 共享锁,官方描述:permits the transaction that holds the lock to read a row

      eg:select * from xx where a=1 lock in share mode
    2. Exclusive Locks:排他锁: permits the transaction that holds the lock to update or delete a row

      eg: select * from xx where a=1 for update
      
  • Intention Locks

    1. 这个锁是加在table上的,表示要对下一个层级(记录)进行加锁
    2. Intention shared (IS):Transaction T intends to set S locks on individual rows in table t
    3. Intention exclusive (IX): Transaction T intends to set X locks on those rows
    4. 在数据库层看到的结果是这样的:

      TABLE LOCK table `lc_3`.`a` trx id 133588125 lock mode IX
      
  • Record Locks

    1. 在数据库层看到的结果是这样的:

      RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133588125 lock_mode X locks rec but not gap
    2. 该锁是加在索引上的(从上面的index PRIMARY of table lc_3.a 就能看出来)
    3. 记录锁可以有两种类型:lock_mode X locks rec but not gap && lock_mode S locks rec but not gap
  • Gap Locks

    1. 在数据库层看到的结果是这样的:

      RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588125 lock_mode X locks gap before rec  
    2. Gap锁是用来防止insert的
    3. Gap锁,中文名间隙锁,锁住的不是记录,而是范围,比如:(negative infinity, 10),(10, 11)区间,这里都是开区间哦
  • Next-Key Locks

    1. 在数据库层看到的结果是这样的:

      RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588125 lock_mode X
    2. Next-Key Locks = Gap Locks + Record Locks 的结合, 不仅仅锁住记录,还会锁住间隙,
      比如: (negative infinity, 10】,(10, 11】区间,这些右边都是闭区间哦
  • Insert Intention Locks

    1. 在数据库层看到的结果是这样的:
      RECORD LOCKS space id 279 page no 3 n bits 72 index PRIMARY of table lc_3.t1 trx id 133587907 lock_mode X insert intention waiting
    2. Insert Intention Locks 可以理解为特殊的Gap锁的一种,用以提升并发写入的性能
  • AUTO-INC Locks

    1. 在数据库层看到的结果是这样的:

      TABLE LOCK table xx trx id 7498948 lock mode AUTO-INC waiting
    2. 属于表级别的锁
    3. 自增锁的详细情况可以之前的一篇文章:

      http://keithlan.github.io/2017/03/03/auto_increment_lock/
      
  • 显示锁 vs 隐示锁

    1. 显示锁(explicit lock)

      显示的加锁,在show engine innoDB status 中能够看到  ,会在内存中产生对象,占用内存  
      eg: select ... for update , select ... lock in share mode   
    2. 隐示锁(implicit lock)

      implicit lock 是在索引中对记录逻辑的加锁,但是实际上不产生锁对象,不占用内存空间  
      
    3. 哪些语句会产生implicit lock 呢?
      eg: insert into xx values(xx)
      eg: update xx set t=t+1 where id = 1 ; 会对辅助索引加implicit lock
    4. implicit lock 在什么情况下会转换成 explicit lock
      eg: 只有implicit lock 产生冲突的时候,会自动转换成explicit lock,这样做的好处就是降低锁的开销
      eg: 比如:我插入了一条记录10,本身这个记录加上implicit lock,如果这时候有人再去更新这条10的记录,那么就会自动转换成explicit lock
    5. 数据库怎么知道implicit lock的存在呢?如何实现锁的转化呢?

      • 对于聚集索引上面的记录,有db_trx_id,如果该事务id在活跃事务列表中,那么说明还没有提交,那么implicit则存在
      • 对于非聚集索引:由于上面没有事务id,那么可以通过上面的主键id,再通过主键id上面的事务id来判断,不过算法要非常复杂,这里不做介绍

记录锁,间隙锁,Next-key 锁和插入意向锁。这四种锁对应的死锁如下:

  • 记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
  • 间隙锁(LOCK_GAP): lock_mode X locks gap before rec
  • Next-key 锁(LOCK_ORNIDARY): lock_mode X
  • 插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

表格信息:

  CREATE TABLE `t_bitfly` (
    `id` bigint(20) NOT NULL DEFAULT '0',
    `num` int(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `num_key` (`num`)
  ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
     

表中数据:

  mysql> select * from t_bitfly;
  +----+------+
  | id | num  |
  +----+------+
  |  1 |    2 |
  |  3 |    5 |
  |  8 |    7 |
  +----+------+
  3 rows in set (0.04 sec)
 

数据库隔离级别为:可重复读(REPEATABLE-READ)

模拟死锁场景:

sisuo.png

结果:


insert into t_bitfly values(5,5)
> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 时间: 0.085s

查询日志 :show engine innodb status ;

结果如下

=====================================
2018-08-05 21:20:27 0x7fd40c082700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 251 srv_active, 0 srv_shutdown, 22663 srv_idle
srv_master_thread log flush and writes: 22905
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 513
OS WAIT ARRAY INFO: signal count 450
RW-shared spins 0, rounds 569, OS waits 286
RW-excl spins 0, rounds 127, OS waits 1
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 569.00 RW-shared, 127.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-08-05 21:15:42 0x7fd40c0b3700
*** (1) TRANSACTION:
TRANSACTION 1095010, ACTIVE 21 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 16, OS thread handle 140548578129664, query id 3052 183.6.50.229 root update
insert into t_bitfly values(7,7)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2514 page no 4 n bits 72 index num_key of table `test`.`t_bitfly` trx id 1095010 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 8; hex 8000000000000008; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 1095015, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 17, OS thread handle 140548711855872, query id 3056 183.6.50.229 root update
insert into t_bitfly values(5,5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2514 page no 4 n bits 72 index num_key of table `test`.`t_bitfly` trx id 1095015 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 8; hex 8000000000000008; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2514 page no 4 n bits 72 index num_key of table `test`.`t_bitfly` trx id 1095015 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 8; hex 8000000000000008; asc         ;;

省略。。。

一些注释:

  • LATEST DETECTED DEADLOCK:标示为最新发生的死锁;
  • (1) TRANSACTION:此处表示事务1开始 ;
  • MySQL thread id 16, OS thread handle 140548578129664, query id 3052 183.6.50.229 root update:此处为记录当前数据库线程id;
  • insert into t_bitfly values(7,7):表示事务1在执行的sql ,不过比较悲伤的事情是show engine innodb status 是查看不到完整的事务的sql 的,通常显示当前正在等待锁的sql;
  • (1) WAITING FOR THIS LOCK TO BE GRANTED:此处表示当前事务1等待获取行锁;
  • (2) TRANSACTION:此处表示事务2开始 ;
  • insert into t_bitfly values(5,5):表示事务2在执行的sql
  • (2) HOLDS THE LOCK(S):此处表示当前事务2持有的行锁;
  • (2) WAITING FOR THIS LOCK TO BE GRANTED:此处表示当前事务2等待获取行锁;

根据死锁日志可以看出:

事务一在执行insert into t_bitfly values(7,7)时,插入意向锁加锁时卡住;

事务二在执行insert into t_bitfly values(5,5)时,持有next-key锁,插入意向锁加锁时卡住。

结合上面执行的sql来分析:

  • 事务一执行delete from t_bitfly where num = 5 ;后,获取了 Gap Locks + Record Locks 也就是 next-key锁;
  • 事务二执行delete from t_bitfly where num = 7 ;后,获取了 Gap Locks + Record Locks 也就是 next-key锁;
  • 事务一执行insert into t_bitfly values(7,7)时,持有next-key锁,插入意向锁,等待事务二的next-key锁解锁;
  • 事务二执行insert into t_bitfly values(5,5)时,持有next-key锁,插入意向锁,等待事务二的next-key锁解锁;

产生死锁。

20 声望
1 粉丝
0 条评论
推荐阅读
万字长文~vue+express+mysql带你彻底搞懂项目中的权限控制(附所有源码)
所谓的权限,其实指的就是:用户是否能看到,以及是否允许其对数据进行增删改查的操作,因为现在开发项目的主流方式是前后端分离,所以整个项目的权限是后端权限控制搭配前端权限控制共同实现的

水冗水孚7阅读 1k

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山6阅读 1.3k评论 3

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 13.8k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 664

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.4k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.2k

20 声望
1 粉丝
宣传栏