SQL Server锁分区特性引发死锁解析

本文为大家讲解的是SQL Server锁分区特性引发死锁解析，锁分区技术使得SQL Server可以更好地应对并发情形,但也有可能带来负面影响,这里通过实例为大家介绍,分析由于锁分区造成的死锁情形.感兴趣的同学参考下。

 

Code(执行测试脚本时请注意执行顺序,说明)

步骤1 创建测试数据

use tempdb
go
create table testdlk
(
id int identity(1,1) primary key,
str1 char(3000)
)
go
insert into testdlk(str1) select 'aaa'
insert into testdlk(str1) select 'bbb'
insert into testdlk(str1) select 'ccc'
insert into testdlk(str1) select 'ddd'

步骤2 开启 session 1 执行语句

--session 1 
begin tran 

update testdlk set str1='ttt' where id=1

---session id 55 this example
---rollback tran 
---manual after session 3 rollback session 1

步骤3 开启session 2 执行语句

--session 2
BEGIN TRAN

update testdlk set str1='abc' where id=2 ---update the content of id=2

SELECT * FROM testdlk WITH(TABLOCKX)------ try to get X lock on the object testdlk


rollback tran
---session id 58 this example

步骤4 开启session 3执行数据

--session 3
BEGIN TRAN

update testdlk set str1='abc' where id=3-------update the content of id=3 

SELECT * FROM testdlk WITH(TABLOCKX)--- try to get X lock on the object testdlk

rollback tran
---session id 59 this example

步骤5 创建脚本的session中执行语句

select request_session_id,resource_lock_partition,resource_type,
object_name(resource_associated_entity_id) as object_name,request_mode,request_status 
from sys.dm_tran_locks where resource_database_id=2 and resource_type='OBJECT'

select session_id,blocking_session_id,wait_type,resource_description 
from sys.dm_os_waiting_tasks where blocking_session_id is not null

步骤6 session 1中rollback

Rollback session 1
--when session 1 rollback then session 3 deadlock

当session 1回滚时,session2 session 3造成死锁,session 3牺牲.

原因分析.

通过步骤四我们可以得到相应的会话的锁,及相关等待情况如图1-1