SQL Server死锁诊断--同一行数据在不同索引操作下

时间:2019-05-11 09:47来源:计算机教程
  INNODB的几种锁   共享锁(S-LOCKING) 允许一个事务去读一行,阻止其它事务获得相同数据集的排它锁 死锁概述 排它锁(X-LOCKING) 允许获得排它锁的事务更新数据,阻止其它事务取得相同

 

INNODB的几种锁

 

共享锁(S-LOCKING)允许一个事务去读一行,阻止其它事务获得相同数据集的排它锁

死锁概述

排它锁(X-LOCKING)允许获得排它锁的事务更新数据,阻止其它事务取得相同数据集的共享读锁和排它锁

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

INNODB还独有实现了2种锁

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

意向共享锁(IS)事务打算给数据行加共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁

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

意向独占锁(IX)事务打算给数据行加排它锁,事务在给一个数据行加排它锁前必须先取得该表的IX锁

 

Innodb 行级别的锁基于索引实现的支持并发和一致性

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

注意:

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

1)  在不通过索引条件查询的时候,innodb使用的是表锁

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

2)  由于MySQL的行锁针对索引加锁,不是针对记录加的锁,所以虽然时访问不同行的记录,但是如果是使用相同的索引键,则会出现锁冲突

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

加共享锁:select * from xx where ,….. lock in share mode

图片 1

加排它锁:select * from xx where ….. for update,update delete 也是加排它锁

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

 

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

S

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

 图片 2

可以通过show full processlist,show engine innodb status等命令查看锁状态

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

也可以从视图查看锁 事务状态 information_schma 库下面

图片 3

innodb_trx  innodb_locks innodb_lock_waits

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

innodb_trx

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

Trx_id:innodb存储引擎内部唯一事务ID

得到如下的死锁信息,扩展事件中的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锁
结果:死锁!

Trx_state:当前事务的状态

图片 4

Trx_started:事务开始时间

 

Trx_wait_started:事务开始等待时间

关于waitresource的解读,参考:https://blog.csdn.net/kk185800961/article/details/41687209

Trx_mysql_thread_id MySQL中的线程ID show processlist 显示结果

编辑:计算机教程 本文来源:SQL Server死锁诊断--同一行数据在不同索引操作下

关键词: