innodb之死锁分析:读和删造成的死锁

一个例子的死锁分析:

环境:innodb引擎,RC隔离级别;

死锁信息:

RECORD LOCKS space id 0 page no 1492482 n bits 904 index `unit_id` of table `51fshenzhen`.`t_refresh_queue` trx id EB9C4A64 lock_mode X locks rec but not gap

表如下:


CREATE TABLE `t_refresh_queue` (
`refresh_queue_id` int(11) NOT NULL AUTO_INCREMENT,
`unit_type` tinyint(1) DEFAULT ‘0‘,
`unit_id` int(11) DEFAULT ‘0‘,
`refresh_time` int(11) DEFAULT ‘0‘,
`broker_id` int(11) DEFAULT ‘0‘,
`agent_id` int(11) DEFAULT ‘0‘,
`company_id` int(11) DEFAULT ‘0‘,
`sector_id` int(11) DEFAULT ‘0‘,
PRIMARY KEY (`refresh_queue_id`),
UNIQUE KEY `unit_id` (`unit_id`,`unit_type`,`refresh_time`),
KEY `index_broker_id` (`broker_id`),
KEY `index_refresh_time` (`refresh_time`)
) ENGINE=InnoDB AUTO_INCREMENT=175716299 DEFAULT CHARSET=gbk;

删除SQL:

DELETE FROM `t_refresh_queue` 
WHERE `unit_id` IN (‘1451914‘,‘1486249‘,‘1486175‘,‘1451826‘,‘1447935‘,‘1447807‘,‘1447624‘,‘1440273‘,‘1469855‘) AND `unit_type` = ‘1‘;

插入SQL:


INSERT INTO `t_refresh_queue` (`unit_type`, `unit_id`, `refresh_time`, `broker_id`, `agent_id`, `sector_id`, `company_id`)
VALUES (‘1‘, ‘1451914‘, ‘1399806000‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1451914‘, ‘1399811400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1451914‘, ‘1399816200‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1451914‘, ‘1399818900‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1451914‘, ‘1399823400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486249‘, ‘1399806000‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486249‘, ‘1399811400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486249‘, ‘1399816200‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486249‘, ‘1399818900‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486249‘, ‘1399823400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486175‘, ‘1399806000‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486175‘, ‘1399811400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘),
(‘1‘, ‘1486175‘, ‘1399816200‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘);

首先、需要明确下面几个内容:

(1)RC禁止了gap锁

(2)in子句是索引最左前缀,因此delete不走索引;

SQL执行过程分析

1、删除操作的sql;

DELETE FROM `t_refresh_queue` WHERE `unit_id` IN
(‘1451914‘,‘1486249‘,‘1486175‘,‘1451826‘,‘1447935‘,‘1447807‘,‘1447624‘,‘1440273‘,‘1469855‘)
and `unit_type` = ‘1‘;

index:unit_id子句IN,不走索引,只能进行全表扫描;

删除执行过程如下:首先进行全表扫描,便利每一条记录,匹配unit_id,unit_type,并对所有便利过的行加X锁;


锁 refresh_queue_id unit_id  unit_type
X rqid1 1447935  1
X rqid2 1486175  1
X rqid4 1486249  1
X rqid7 1440273  1
X rqid8 1451914  1
X rqid9 1469855  1
X rqid23 1447807  1
X rqid34 1451826  1
X rqid45 1447624  1

在具体以[unit_id,unit_type]为(1451914,1)的行为例来说明:

具体sql变为:DELETE FROM `t_refresh_queue` WHERE `unit_id` IN
(‘1451914‘) AND `unit_type` = ‘1‘;为了保留不走索引的特点,还采用in。

锁 refresh_queue_id unit_id unit_type
X ...
X
rqid80        1451913 1
X rqid81        1451914 1
X ...
X
rqid83        1451914
1
X ...
X rqid88        1451914
1
X rqid89        1451914
1
X rqid90        1451915 1
X ...

再对这四条数据进行删除操作,保持行级X锁,获取页级X锁,标记对应页为dirty,等待之后的purge的时候进行物理删除,释放行级X锁,释放页级X锁;

2、对于插入操作

执行插入操作的时候,走索引unit_id,并且是完全覆盖唯一索引。继续来以unit_id=1451914来分析,对于下面五条数据,插入过程:


(‘1‘, ‘1451914‘, ‘1399806000‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘)          键值:[1451914,1,1399806000]
(‘1‘, ‘1451914‘, ‘1399811400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘) 键值:[1451914,1,1399811400]
(‘1‘, ‘1451914‘, ‘1399816200‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘) 键值:[1451914,1,1399816200]
(‘1‘, ‘1451914‘, ‘1399818900‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘) 键值:[1451914,1,1399818900]
(‘1‘, ‘1451914‘, ‘1399823400‘, ‘74146‘, ‘0‘, ‘0‘, ‘2758‘) 键值:[1451914,1,1399823400]

首先在索引unit_id中查找对应键值,顺序为[unit_id,unit_type,refresh_time]

其中Index key:unit_id,unit_type,refresh_time
 决定了插入范围 [1451914,1,1399805999]~[1451914,1,1399806001]

  Index filer:is null

  TABLE
FILE:
broker_id,agent_id,sector_id,company_id

首先查看是否存在[1451914,1],存在,则查找[1451914,1,1399805999]~[1451914,1,1399806001]中是否存在[1451914,1,1399806000],并且在[1451914,1,1399805999]和[1451914,1,1399806001]索引键值上添加S锁。结果如下:

锁 unit_id unit_type refresh_time refresh_queue_id
S
 1451914 1      1399805999    rqid8100001
...
S
 1451914 1      1399806001    rqid8100010

索引中有[1451914,1,1399806000]:二级索引不用发生改变,在[1451914,1,1399806000]上加上S锁,并返回refresh_queue_id
id值,并且根据TABLE
FILE
信息来匹配;所有便利过的行添加X锁,对该位置的数据页添加X锁,写入数据,释放数据页的X锁,对数据行添加X锁,进行下一行;

索引中没有[1451914,1,1399806000]:二级索引需要发生改变。获取数据页X锁,写入行数据,主键ID自增,释放数据页X锁,添加主键X锁,修改二级索引,加入[1451914,1,1399806000]值其他二级索引值该挪的挪位置。

知道这个过程后,我们来看如何发生的死锁:

innodb之死锁分析:读和删造成的死锁

时间: 05-20

innodb之死锁分析:读和删造成的死锁的相关文章

<转>一个最不可思议的MySQL死锁分析

1 死锁问题背景 1 1.1 一个不可思议的死锁 1 1.1.1 初步分析 3 1.2 如何阅读死锁日志 3 2 死锁原因深入剖析 4 2.1 Delete操作的加锁逻辑 4 2.2 死锁预防策略 5 2.3 剖析死锁的成因 6 3 总结 7 死锁问题背景 做MySQL代码的深入分析也有些年头了,再加上自己10年左右的数据库内核研发经验,自认为对于MySQL/InnoDB的加锁实现了如指掌,正因如此,前段时间,还专门写了一篇洋洋洒洒的文章,专门分析MySQL的加锁实现细节:<MySQL加锁处理分

[转载] 数据库分析手记 —— InnoDB锁机制分析

作者:倪煜 InnoDB锁机制常常困扰大家,不同的条件下往往表现出不同的锁竞争,在实际工作中经常要分析各种锁超时.死锁的问题.本文通过不同条件下的实验,利用InnoDB系统给出的各种信息,分析了锁的工作机制.通过本文可以帮助大家了解InnoDB锁的基本原理,常见的冲突.死锁,以及对InnoDB事务日志信息的解读. 1. 索引基本原理 InnoDB主要使用行级锁(row lock),其行锁是通过在索引项上加锁而实现的,如果MySQL的执行计划没有用到索引,那么行锁也就无意义了,所以了解锁之前需要了

一个最不可思议的MySQL死锁分析

一个最不可思议的MySQL死锁分析 死锁问题背景 做MySQL代码的深入分析也有些年头了,再加上自己10年左右的数据库内核研发经验,自认为对于MySQL/InnoDB的加锁实现了如指掌,正因如此,前段时间,还专门写了一篇洋洋洒洒的文章,专门分析MySQL的加锁实现细节:<MySQL加锁处理分析>. 但是,昨天"润洁"同学在<MySQL加锁处理分析>这篇博文下咨询的一个MySQL的死锁场景,还是彻底把我给难住了.此死锁,完全违背了本人原有的锁知识体系,让我百思不得

InnoDB锁机制分析

InnoDB锁机制常常困扰大家,不同的条件下往往表现出不同的锁竞争,在实际工作中经常要分析各种锁超时.死锁的问题.本文通过不同条件下的实验,利用InnoDB系统给出的各种信息,分析了锁的工作机制.通过本文可以帮助大家了解InnoDB锁的基本原理,常见的冲突.死锁,以及对InnoDB事务日志信息的解读. 1. 索引基本原理 InnoDB主要使用行级锁(row lock),其行锁是通过在索引项上加锁而实现的,如果MySQL的执行计划没有用到索引,那么行锁也就无意义了,所以了解锁之前需要了解一点索引原

Mysql死锁如何排查:insert on duplicate死锁一次排查分析过程

前言 遇到Mysql死锁问题,我们应该怎么排查分析呢?之前线上出现一个insert on duplicate死锁问题,本文将基于这个死锁问题,分享排查分析过程,希望对大家有帮助. 死锁案发还原 表结构: CREATE TABLE `song_rank` ( `id` int(11) NOT NULL AUTO_INCREMENT, `songId` int(11) NOT NULL, `weight` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`

InnoDB源码分析--缓冲池(三)

转载请附原文链接:http://www.cnblogs.com/wingsless/p/5582063.html 昨天写到了InnoDB缓冲池的预读:<InnoDB源码分析--缓冲池(二)>,最后因为着急看欧洲杯,没有把线性预读写完,今天接着写. 线性预读是由这个函数实现的:buf_read_ahead_linear,和随机预读一样,首先是要确定区域边界,这个边界内被访问过的page如果达到一个阈值(BUF_READ_AHEAD_LINEAR_THRESHOLD),就会触发预读操作.边界的算法

MySQL系列:innodb源码分析之redo log恢复

在上一篇<innodb源码分析之重做日志结构>中我们知道redo log的基本结构和日志写入步骤,那么redo log是怎么进行数据恢复的呢?在什么时候进行redo log的日志推演呢?redo log的推演只有在数据库异常或者关闭后,数据库重新启动时会进行日志推演,将数据库状态恢复到关闭前的状态.那么这个过程是怎么进行的呢?以下我们逐步来解析. 1.recv_sys_t结构 innodb在MySQL启动的时候,会对重做日志文件进行日志重做,重做日志是通过一个recv_sys_t的结构来进行数

MySQL死锁分析

1. 测试描述 环境说明:RHEL 6.4 x86_64 + MySQL 5.5.37,事务隔离级别为RC 测试表: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT '0', `b` int(11) DEFAULT

Raid1源代码分析--读流程

我阅读的代码的linux内核版本是2.6.32.61.刚进实验室什么都不懂,处于摸索阶段,近期的任务就是阅读raid1的源码.第一次接触raid相关的东西,网上分析源码的资料又比较少,不详细.逐行阅读代码,做了笔记.如果要对raid1的读流程有个整体上的把握,需要将笔记中的主线提炼出来,这里不写了.理解不足或者有误之处,希望批评指正. 读流程主要涉及以下函数: 请求函数make_request 读均衡read_balance 回调函数raid1_end_read_request 读出错处理rai