图片 7

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

 

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 显示结果

 

Trx_query:事务运行的SQL语句

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

innodb_locks

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

Lock_id:锁的ID

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

Lock_trx_id:事务ID

图片 5

LOCK_MODE:锁的模式

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

LOCK_TYPE:所得类型表锁还是行锁

图片 6

LOCK_TABLE:要加锁的表

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

LOCK_INDEX:锁的索引

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

LOCK_SPACE:innodb存储引擎表空间ID号

 

LOCK_PAGE:被锁住的页的数量,若是表锁,则该值为null

如何解决?

LOCK_REC:被锁住行的数量,若是表锁则该值为NULL

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

LOCK_DATA:被锁住的行的主键值,若是表锁时,则该值为NULL;

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

通过select* from
information_schema.INNODB_LOCK可查看

参考:
https://www.cnblogs.com/Uest/p/4998527.html
https://blogs.msdn.microsoft.com/apgcdsd/2012/02/27/sql-serverdeadlock/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

innodb_lock_waits

 

Requesting_trx_id:申请资源的事务ID

 

Request_lock_id:申请锁的ID

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

Blocking_trx_id:阻塞锁的ID

 

哪个事务被哪个事务阻塞很明显通过该innodb_lock_waits看

一致性非锁定在MVCC读取当前数据库里面的数据在读取的数据正在被修改不会产生锁等待(对当前数据拍照片)读没有加锁
没有加共享锁 没有被阻塞

访问不同的记录不会发生等待 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然时访问不同的行记录。但是如果是使用相同的索引键,会出现锁冲突的,应用设计的时候要注意

不同隔离级别,和不同索引类型的加锁处理分析

 

图片 7

结论:

RR  
2.innodb_locks_unsafe_for_binlog=0

1.任何辅助索引上的锁,或者非索引列上的锁,最终都要回溯到主键上,在主键上也要加一把锁

2.任何叶子节点上的S或X锁之前,都会在根节点上加一个IS或IX锁,也就是表级别的IS,IX锁

3.主键索引=record
lock(但外键约束,唯一性约束检测仍然使用 gap lock)

4.唯一辅助索引=record
lock(但外键约束,唯一性约束检测仍然使用 gap lock)

5.非唯一辅助索引=next-key-lock(RC隔离级别=record
lock)

Recordlock:单个记录上的锁,至少锁定一行记录;

Gap
lock(间隙锁)
:在索引记录间隙上的锁,或者是第一条索引记录之前,最后一条索引记录之后上的间隙锁(两条记录中间的缝隙) 锁定两个记录中间的缝隙;

Next-keylock(下一键锁)索引记录锁以及索引记录之间的间隙锁,二者的组合锁;

记录锁至少锁定一条记录(普通,主键,唯一 索引)或是无任何索引innodb会对rowid加锁(左右两边加自身的记录);

设置RC隔离级别或者是启用innodb_lock_unsafe_for_binlog的其他影响

1.在mysql评估完where条件后,会释放找不到相应记录的记录锁

2.在update语句中,innodb使用“半一致性读“,会返回提交后的最新版本号,以便判是否匹配update语句中的where条件

gap lock避免幻读:

假设一个SQL:select * from child where
id>=100 for update;

Id字段当前有2个值:90,102
这时候gap是90—-102之间,如果只有recode lock 就没办法再阻止101这个id
(就会发生幻读再次读取后可以看到101这个id值);

有了next-key
lock后,可以阻止写入101这个id确保两次读取的结果是一样的,不会发生幻读;

有唯一属性索引时,就无需使用gap
lock(扫描包含多个字段的唯一索引中的部分字段除外);

还有一种叫做意向插入(insertionintention)的gap
lock,如果两个事务往同一个gap
lock中写入数据,但写入位置不一样时,是无须等待,可以直接写入因此没有冲突

设定pkid =3

T1:insert into
t(pkid)values(4)

T2:insert into t (pkid)
values(5)

Gap
lock仅用于防止往gap上写入新记录(避免幻读),因此无论是S-GAP
还是X-GAP锁其实作用是一样的。

Innode引擎监控的开启的方法
  

锁监控:
打开innodb的锁监控:
CREATE TABLE innodb_lock_monitor (a
INT) ENGINE=INNODB;    
5.6.16可以使用:
 –两个都需要打开
set GLOBAL
innodb_status_output=ON;
set GLOBAL
innodb_status_output_locks=ON; 
表空间监控:  
打开innodb表空间监控:
CREATE TABLE innodb_tablespace_monitor
(a INT) ENGINE=INNODB;
表监控:
打开innodb表监控:
CREATE TABLE innodb_table_monitor (a
INT) ENGINE=INNODB;
打开监视器以后
innodb_monitor和innodb_lock_monitor会每隔15秒会向错误日志中记录InnoDB监控信息;
innodb_table_monitor和innodb_tablespace_monitor是每隔64秒;
innodb_monitor和innodb_lock_monitor两种监视器的输出结果基本类似,后者会有更多关于锁的信息,而前一个实际上就是show
innodb status;
innodb_table_monitor会将系统中所有innodb的表的一些结构和内部信息输出;
innodb_tablespace_monitor输出的是tablespace的信息,注意该monitor输出的只是共享表空间的信息,如果使用innodb_file_per_table为每个表使用独立的表空间,则这些表空间的信息是不会包含在输出中的。
停止InnoDB监控
drop table innodb_monitor;
drop table
innodb_lock_monitor;
drop table
innodb_table_monitor;
drop table
innodb_tablespace_monitor;
重点的锁类型
如果辅助索引上的搜索及锁定是排它的,则会取回其相应的聚集索引,并且在它上面加锁;
对无索引的字段检索更新时升级成表级别锁(表中全部记录被锁,除非在RC或innodb_locks_unsafe_for_binlog=1
模式下 采用semi-consitent read机制);
insert into T select … from S where
T表上排它record lock
事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog并且隔离级别不是serializable时,S表上采用无锁一致性读,否则(rr),加排它next-key
lock(RC不加锁。RR加next-key lock);
insert 排它record lock,而非next-key
lock,但在写入新记录之前需要加意向gap lock(insertion intention gap
lock);
insert…on duplicate key update
排它next-key lock(即使被update的记录上)会同时并发执行;
create table…select 和insert…select
一样;
replace 没冲突/重复时 和insert一样
否则(有冲突时先delete后insert)加next-key-lock;
replace into t select … from S where
或者update T … where col IN(SELECT…FROM S..),都会在S表上加next-key
lock;
auto..increment列上写新数据时,索引末尾设置排它锁,请求自增列计数器时,INNODB使用一个AUTO-INC表锁,只对请求的那个SQL有影响,不会影响整个事务,该锁被持有时,其他会话不能往INNODB表中写入新行;
select…from
一致性非锁定读除非是serializable隔离级别,在其影响的索引记录上设置一个共享锁(简单的select…from是不加锁的);
lock in shared mode,使用共享next-key
lock;
for update使用排它next-key
lock锁,会阻止lock in shared mode请求;
update/delete,排它next-key lock.
死锁 
死锁不会卡,有一个会立刻回滚,再次提交即可,show
engine innodb status
只显示最后死锁的信息,设置innodb_print_all_deadlocks=1,在日志中记录全部死锁信息;
自动检测死锁,并优先回滚最小事务(影响较小的事务),加表锁时,不会发生死锁;
事务中如果select调用存储函数/存储过程失败了,对用的SQL会回滚事务,如果再显示执行ROLLBACK,那么整个事务都回滚;
事务回滚时,会释放全部的锁,个别情况下,如果个别SQL因为某些错误回滚事务的话它所持有的行锁可能无法释放,因为INNODB的行锁信息并没有记录时那个SQL持有的,这时候建议执行一次显示的ROLL
BACK。
避免死锁
事务尽快提交,小事务越不容易发生死锁;
加for update lock in shared
mode读锁时最好降低事务隔离级别,例如用RC级别降低死锁发生概率;
事务中涉及多个表,或者涉及多行记录时,每个事务的操作顺序都要保持一致,降低死锁发生概率,最好用存储过程/存储函数固化;
通过索引等方式优化SQL效率,降低死锁发生概率,减少扫描/锁范围,降低概率。