本文为大家讲解的是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牺牲.