金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > Innodb表导致死锁日志情况分析与归纳,MySQL加锁分

Innodb表导致死锁日志情况分析与归纳,MySQL加锁分

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-03 01:07

 

金沙棋牌app手机下载 1

一条复杂SQL的加锁实现分析

再来看一个稍微复杂点的SQL,用于说明MySQL加锁的另外一个逻辑。SQL用例如下:

Id作为主键,联合索引idx_t1_pu(时间戳,UserId)
SQL:delete from t1 where pubtime > 1 and pubtime < 20 and userid='hdc' and comment is not null;

金沙棋牌app手机下载 2

假定在Repeatable Read隔离级别下,同时,假设SQL走的是idx_t1_pu索引
拆分SQL中的where条件参考

Index key:pubtime > 1 and puptime < 20。用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter:userid = 'hdc' 。在idx_t1_pu索引上进行过滤。
Table Filter:comment is not NULL。在idx_t1_pu索引上无法过滤,只能在聚簇索引上过滤。

在分析出SQL where条件的构成之后,再来看看这条SQL的加锁情况 (RR隔离级别)

金沙棋牌app手机下载 3

由Index Key所确定的范围,被加上了GAP锁。Index Filter锁给定的条件何时过滤,视MySQL的版本而定,在MySQL 5.6版本之前,不支持Index Condition Pushdown,在MySQL Server层过滤,在5.6后支持了ICP,则在index上过滤。若不支持ICP,不满足Index Filter的记录,也需要加上记录X锁,若支持ICP,则不满足Index Filter的记录,无需加记录X锁(图中,用红色箭头标出的X锁)。Table Filter对应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤,因此聚簇索引上也需要X锁。

最后,选取出了一条满足条件的记录[8,hdc,d,5,good],但是加锁的数量,要远远大于满足条件的记录数量。

在Repeatable Read隔离级别下,针对一个复杂的SQL,首先需要提取其where条件。Index Key确定的范围,需要加上GAP锁;Index Filter过滤条件,根据MySQL版本是否支持ICP,加X锁;Table Filter过滤条件,无论是否满足,都需要加X锁。


 

案例描述 在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。
两个sql语句如下: (1)insert into backup_table select * from source_table
(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'
teamUser表的表结构如下:
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`金沙棋牌app手机下载,Id`,`titleWeight`,`score`),
ENGINE=InnoDB
两语句对source_table表的使用情况如下:

死锁原理与分析

理解MySQL如何加锁,有两个比较重要的作用:
写出不会发生死锁的SQL;定位出线上产生死锁的原因;

两个死锁的例子

1 两个Session的两条SQL产生死锁

金沙棋牌app手机下载 4

非常好理解,也是最常见的死锁,每个事务执行两条SQL,分别持有了一把锁,然后加另一把锁,产生死锁。

2 两个Session的一条SQL,产生死锁

金沙棋牌app手机下载 5

第二个用例,虽然每个Session都只有一条语句,仍旧会产生死锁。需要用到本文前面提到的MySQL加锁的规则。
Session 1,从name索引出发,读到的[hdc, 1],[hdc, 6]均满足条件,不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁,加锁顺序为先[1,hdc,100],后[6,hdc,10]。
Session 2,从pubtime索引出发,[10,6],[100,1]均满足过滤条件,同样也会加聚簇索引上的记录X锁,加锁顺序为[6,hdc,10],后[1,hdc,100],跟Session 1的加锁顺序正好相反,如果两个Session恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。

死锁的发生与否,并不在于事务中有多少条SQL语句,死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。分析MySQL每条SQL语句的加锁规则,分析出每条语句的加锁顺序,然后检查多个并发SQL间是否存在以相反的顺序加锁的情况,就可以分析出各种潜在的死锁情况,也可以分析出线上死锁发生的原因。

转载:http://hedengcheng.com/?p=771#_Toc374698321

死锁概述

金沙棋牌app手机下载 6

对于数据库中出现的死锁,通俗地解释就是:不同Session(会话)持有一部分资源,并且同时相互排他性地申请对方持有的资源,然后双方都得不到自己想要的资源,从而造成的一种僵持的现象。
当然,在任何一种数据库中,这种僵持的情况不会一直持续下去,因为一直持续下去双方永远都无法执行,没有任何意义,
在SQL Server中,后台线程会以3秒钟一次的频率检测死锁Session,并且选择其中一个回滚代价相对较低的作为牺牲品,从而使解除不同Session相互僵持的现象。
因此SQL Server中死锁的僵持时间不会超过3秒钟。

死锁日志打印出的时间点表明,语句(1)运行过程中,当语句(2)开始运行时,发生了死锁。
当mysql检测出死锁时,除了查看mysql的日志,还可以通过show InnoDB STATUS G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱,所以,最好先使用pager less命令,通过文件内容浏览方式查看结果,会更清晰。(以nopager结束)
得到的死锁记录如下:

通常情况下,最简单也是最常见的死锁是发生在不同表级别的,
Session 1 第一步修改A表,第二步修改B表,
Session 2第一步修改B表,第二步修改A表,
当发生Session 1与Session 2推进顺序发生交叉的时候,死锁就发生了,这种结局办法也比较简单,以相同的推进顺序进行操作即可解除死锁。

金沙棋牌app手机下载 7

以下演示一种不用于以上情况,稍微特殊一点的死锁。

金沙棋牌app手机下载 8
根据死锁记录的结果,可以看出确实是这两个语句发生了死锁,且锁冲突发生在主键索引上。那么,为什么两个sql语句会存在锁冲突呢?冲突为什么会在主键索引上呢?语句(2)得到了主键索引锁,为什么还会再次申请锁呢?
锁冲突分析
2.1 innodb的事务与行锁机制
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关,MyISAM不支持事务、采用的是表级锁,而InnoDB支持ACID事务、 行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句作为一个单独的事务来处理。
2.2 两语句加锁情况 在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外,此案例中,语句(1)insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121(ENGINE= MyISAM)加表锁,并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对其使用主键索引。
而语句(2)DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'为删除操作,会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引,于是,还会对相关行的此非聚簇索引加排他锁。
2.3 锁冲突的产生 由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所 以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生 在主键索引上。
常常看到一句话:在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的。那就说明,单个SQL组成的事务锁是一次获得的。而此案例中,语句(2) 已经得到了主键索引的排他锁,为什么还会申请主键索引的排他锁呢?同理,语句(1)已经获得了主键索引的共享锁,为什么还会申请主键索引的共享锁呢?
死锁记录中,事务一等待锁的page no与事务二持有锁的page no相同,均为218436,这又代表什么呢?
我们的猜想是,innodb存储引擎中获得行锁是逐行获得的,并不是一次获得的。下面来证明。
死锁产生过程分析 要想知道innodb加锁的过程,唯一的方式就是运行mysql的debug版本,从gdb的输出中找到结果。根据gdb的结果得到,单个SQL组成的事 务,从宏观上来看,锁是在这个语句上一次获得的,但从底层实现上来看,是逐个记录行查询,得到符合条件的记录即对该行记录的索引加锁。
Gdb结果演示如下:

 

复制代码 代码如下:

同一张表上发生的死锁演示

(gdb) b lock_rec_lock
 Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.
 (gdb) c
 Continuing.
 [Switching to Thread 1168550240 (LWP 5540)]
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 Current language: auto; currently c
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.

不过死锁的种类有很多种,上述的仅是一种最简单最常见的一种死锁,
理论上,只要满足死锁发生的条件:不同Session(会话)排他性地持有一部分资源,并且相互申请对方持有的资源
都会产生死锁,并不仅仅是在不同的表上,而是在不同的资源上,这种资源,可以是同一张表,甚至同一行数据上,以下举例说明。

(说明:”789200″为非聚簇索引,”200″为主键索引)

--TestDeadLock的Id是主键(默认生成聚集索引),Col2字段是唯一性的非聚集索引
create table TestDeadLock
(
    Id int constraint pk_TestDeadLock_id primary key,
    Col2 int constraint uk_TestDeadLock_col2 unique,
    Remark varchar(100)
)

Gdb结果显示,语句(1)(2)加锁的获取记录为多行,即逐行获得锁,这样就解释了语句(2)获得了主键索引锁还再次申请主键索引锁的情况。
由于语句(1)使用了主键索引,而语句(2)使用了非聚簇索引,两个事务获得记录行的顺序不同,而加锁的过程是边查边加、逐行获得,于是,就会出现如下情况:

然后利用SQLQueryStress,开启两个回话,分别按照聚集索引和非聚集索引,删除同一行数据(造测试数据的时候会设置Id和Col2都为1),
如下图所示
一开始先让这两个Session一直执行(空运行),随后往TestDeadLock表中插入一行数据(insert into [TestDeadLock] values (1,1,newid()))
可能需要执行几次尝试,就会观察到其中一个SQLQueryStress中发生了异常信息

金沙棋牌app手机下载 9

金沙棋牌app手机下载 10

于是,两个事务分别拥有部分锁并等待被对方持有的锁,出现这种资源循环等待的情况,即死锁。此案例中被检测时候的锁冲突就发现在page no为218436和218103的锁上。
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚,此次事务(1)解锁并回滚,语句(2)继续运行直至事务结束。
innodb死锁形式归纳 死锁产生的四要素:互斥条件:一个资源每次只能被一个进程使用;请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;不剥夺条件:进程 已获得的资源,在末使用完之前,不能强行剥夺;循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
Innodb检测死锁有两种情况,一种是满足循环等待条件,还有另一种策略:锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度 时,innodb也会判断为死锁(这是提高性能方面的考虑,避免事务一次占用太多的资源)。这里,我们只考虑满足死锁四要素的情况。
死锁的形式是多样的,但分析到innodb加锁情况的最底层,因循环等待条件而产生的死锁只有可能是四种形式:两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式,再以事例的方式解释这四种死锁情况。
4.1聚簇索引与非聚簇索引介绍 聚簇索引即主键索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面。非聚簇索引(即第二主键索 引)不重新组织表中的数据,索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述,那么,聚簇索引的叶节点就是数据节点,而非聚簇 索引的叶节点仍然是索引节点,通常是一个指针指向对应的数据块。
而innodb在非聚簇索引叶子节点包含了主键值作为指针。(这样是为了减少在移动行或数据分页时索引的维护工作。)其结构图如下:
金沙棋牌app手机下载 11

打开其异常信息的详细内容 ,会发现是死锁

当使用非聚簇索引时,会根据得到的主键值遍历聚簇索引,得到相应的记录。
4.2四种死锁情况 在InnoDB中,使用行锁机制,于是,锁通常是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。
即将分享的四种死锁的锁冲突分别是:不同表的相同记录行索引锁冲突、主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级造成锁队列阻塞。
不同表的相同记录行锁冲突 案例:两个表、两行记录,交叉获得和申请互斥锁
金沙棋牌app手机下载 12

 金沙棋牌app手机下载 13

条件:
A、 两事务分别操作两个表、相同表的同一行记录
B、 申请的锁互斥
C、 申请的顺序不一致

首先查一下表上索引的id,一下分析加锁的过程中会用到。
pk_TestDeadLock_id 是聚集索引,其Id是 72057594050314240
uk_TestDeadLock_col2 是非聚集索引,其Id是 72057594050379776

主键索引锁冲突 案例:本文案例,产生冲突在主键索引锁上
条件:
A、 两sql语句即两事务操作同一个表、使用不同索引
B、 申请的锁互斥
C、 操作多行记录
D、 查找到记录的顺序不一致

金沙棋牌app手机下载 14

主键索引锁与非聚簇索引锁冲突 案例:同一行记录,两事务使用不同的索引进行更新操作

利用sqlserver自带的system_health扩展事件,观察其死锁信息(xml_deadlock_report)

此案例涉及TSK_TASK表,该表相关字段及索引如下:
ID:主键;
MON_TIME:监测时间;
STATUS_ID:任务状态;
索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

SELECT  CAST(xet.target_data AS XML)
FROM    sys.dm_xe_session_targets xet
        JOIN sys.dm_xe_sessions xe ON ( xe.address = xet.event_session_address )
WHERE   xe.name = 'system_health'

select xml_event_data,
xml_event_data.value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') Execution_Time,
xml_event_data.value('(event/data/value)[1]','varchar(max)') Query
from 
(
    SELECT event_table.xml_event_data
    FROM(
            SELECT CAST(event_data AS XML) xml_event_data 
            FROM sys.fn_xe_file_target_read_file(N'your path system_health_*', NULL, NULL, NULL)
        ) AS event_table
        CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
    WHERE  event_xml.value('(./@name)', 'varchar(1000)') IN ('xml_deadlock_report')   
) v 
order by Execution_Time

金沙棋牌app手机下载 15

得到如下的死锁信息,扩展事件中的xml_deadlock_report清楚吧地表明:对于当前这一行数据(8194443284a0一样)
delete from [TestDeadLock] where Id= 1     等待非聚集索引上的锁(waitresource="KEY: 11:72057594050379776 (8194443284a0)" )
delete from [TestDeadLock] where Col2 = 1     等待聚集索引上的锁(waitresource="KEY: 11:72057594050314240 (8194443284a0)" )
两者有死锁,肯定是相互等待对方已经持有的资源(索引上的锁)
因此,当前这个死锁可以这么理解
delete from [TestDeadLock] where Id=1     持有聚集索引上的U锁,申请非聚集索引上的X锁
delete from [TestDeadLock] where Col2 = 1    持有非聚集索引上的X锁,申请聚集索引上的U锁
结果:死锁!

条件:
A、 两事务使用不同索引
B、 申请的锁互斥
C、 操作同一行记录

金沙棋牌app手机下载 16

当执行update、delete操作时,会修改表中的数据信息。由于innodb存储引擎中索引的数据存储结构,会根据修改语句使用的索引以及修改信息 的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时,会首先加使用的索引锁,然后,如果修改了主键信息,会加主键索引锁和所有非聚簇索引锁,修改 了非聚簇索引列值会加该种非聚簇索引锁。
此案例中,事务一使用非聚簇索引查找并修改主键值,事务二使用主键索引查找并修改主键值,加锁顺序不同,导致同时运行时产生资源循环等待。
锁升级造成锁队列阻塞 案例:同一行记录,事务内进行锁升级,与另一等待锁发送锁队列阻塞,导致死锁

 

金沙棋牌app手机下载 17

关于waitresource的解读,参考:

条件:
A、 两事务操作同一行记录
B、 一事务对某一记录先申请共享锁,再升级为排他锁
C、 另一事务在过程中申请这一记录的排他锁

 

避免死锁的方法 InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险,这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说,建议的避免死锁的方法如下:
1.如果使用insert…select语句备份表格且数据量较大,在单独的时间点操作,避免与其他sql语句争夺资源,或使用select into outfile加上load data infile代替 insert…select,这样不仅快,而且不会要求锁定
2. 一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。
3.更新或者删除表格数据,sql语句的where条件都是主键或都是索引,避免两种情况交叉,造成死锁。对于where子句较复杂的情况,将其单独通过sql得到后,再在更新语句中使用。
4. sql语句的嵌套表格不要太多,能拆分就拆分,避免占有资源同时等待资源,导致与其他事务冲突。
5. 对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断,如果事务意外结束,重新运行该事务,减少对功能的影响。

两个SQL对同一行数据的加锁顺序分析

在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。...

上述分析只是根据已有现象推测其过程,如果能够观察到每一个sql语句执行过程中的锁的申请与释放顺序,问题就更容易理解了。
以下利用profile观察两个语句执行过程中对锁的申请和释放顺序

观察一下delete from [TestDeadLock] where Id = 1 这句sql的执行过程的锁的申请顺序
profile里就很清楚,对于delete from [TestDeadLock] where Id = 1
先申请聚集索引(72057594050314240)page层面上的意向排它锁(IX),转为行级别的排它锁(X),再申请非聚集索引(72057594050379776)的page层面意向排它锁(IX),转换为行级别排它锁(X)

金沙棋牌app手机下载 18

对于delete from [TestDeadLock] where Col2 = 1
先申请非聚集索引(72057594050379776)上page层面的意向更新锁(IU),转为行级别更新锁锁(U),再申请page层面聚集索引(72057594050314240)的意向排它锁(IX),转换为行级别排它锁(X)

金沙棋牌app手机下载 19

通过以上加锁顺序的分析,印证了上述加锁方式的推测,不难理解两个SQL语句为什么会发生死锁。
仍然回到死锁的概念上:不同Session(会话)排他性地持有一部分资源,并且同时申请对方持有的资源
这种相互持有的资源,可以是不同表上的资源,可以是同一个表上的资源,甚至可以是同一行数据的不同资源(不同索引的资源)
只要发生不同Session相互排他性地持有对方想要的资源,死锁就会发生。

这种方式是双方根据不同的索引同时delete引起的死锁,类似上述情况,可以延伸到双方同时update,双方同时delete或者update,双方同时update或者select等等
只要是索引推进顺序不一致,都有可能引起死锁的发生,此类问题可以归结为同一行数据上,不同索引操作引起的死锁。

 

如何解决?

对于常见的不同表上的推进顺序不当造成的死锁,只要改进持锁的顺序即可,也就是按照同一种方式来操作不同表中的数据。
对于上述的问题,不是不同表上的推进顺序造成的,而是同一张表的同一行数据的资源推进顺序不当导致的,在sql语句层面看起来并没有什么不妥当的,因此只能从锁的范围或者隔离级别上进行调整。
1,尝试从业务入手,是否能够按照统一的方式对数据进行操作。
2,使用队列消除并发操作的峰值。
3,尝试tablockx,一次性锁定整个表。
4,尝试改变隔离级别,尝试序列化隔离级别。

最后佛系一下:
很多问题都喜欢用奇怪解释,其实很多问题并不奇怪,只是不知道而已,
技术上的问题,不知道也没什么大不了,知道了更没什么大不了,知道也仅仅是知道而已,不知道经历一次就知道了,知不知道都没有任何值得自豪或者自卑的
你的知识死角不能否定你的技术能力,应用层面的东西,只不过是在人家制定好的规则上玩游戏而已,谁也不要装。

参考:


 

 

需要注意的是:扩展事件中记录的事件发生的时间,都是标准时间(格林威治时间),而其errorlog中或者自定义异常中的时间,都是当前时间

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:Innodb表导致死锁日志情况分析与归纳,MySQL加锁分

关键词:

上一篇:没有了

下一篇:常用数据类型