锁分区技术使得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 thisexample
---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 ofid=2
SELECT * FROM testdlk WITH(TABLOCKX)------ try to get X lock on the object testdlk
rollback tran
---session id 58 thisexample
步骤3 开启session 3执行数据
--session 3
BEGIN TRAN
update testdlk set str1='abc' where id=3-------update the contentof id=3
SELECT * FROM testdlk WITH(TABLOCKX)--- try to get X lock on the object testdlk
rollback tran
---session id 59 thisexample
步骤4创建脚本的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_idis not null
步骤5 session 1中rollback
Rollback session 1
--when session 1 rollbackthen session 3 deadlock
当session 1回滚时,session2 session 3造成死锁,session 3牺牲.
原因分析.
通过步骤四我们可以得到相应的会话的锁,及相关等待情况如图1-1
图1-1
可以看到session 1(图中55)由于只是更新id=1的列,所以它会在key上加排它锁(图中未列出,感兴趣朋友可以自行查看),而在Object testdlk中某个锁分区中图中为锁分区1加上意向排它锁.
Session 2(图中58)由于更新了id=2的列,所以会在相应Key上加排他锁,并在某个锁分区中加意向排他锁(IX),于此同时由于此事务下面查询有表级TABLOCKX Hint,此时58会尝试在表级上排他锁(X锁).由于X锁需要在所有锁分区中获得,此时58在锁分区0中获得X锁,但由于锁分区1中有55获得了意向排他锁(IX),所以58在锁分区1中尝试获取X锁时状态未Convert,被55阻塞.
Session 3(图中59)由于更新了id=3的列,所以会在相应key上加排他锁,同时在某个锁分区中加意向排他锁(IX),于此同时由于此事务下面查询同样有表级TABLOCKX Hint,这时59也会尝试在表的所有分区中获取X锁.由于58已经获得锁分区0的X锁,所以当59尝试获取锁分区0的X锁时,就会被58阻塞,状态为Wait.
问题来了:),当55回滚时,其上面的锁也将被释放.此时58,59都试图获得表级的所有分区X锁,而又同时在锁分区中持有IX锁,这时死锁就不可避免了.
死锁视图如图1-2所示.
图1-2
问题解决
经过分析,可以看出是由于锁分区的特性导致IX与不同spid中的X互斥导致,那如果能禁用锁分区特性不就没有在个别分区上的IX这回事儿了吗.这里介绍一个启动标记 trace flag 1229,可以禁用锁分区特性.我们可以通过配置管理器中添加启动标记,也可以在command启动时加上响应参数.应当注意当使用配置管理器时,我们应在启动参数末尾配置”-T1229”,如果使用command,这时是t1229(大小写区分)
这里我用command启用
Code
Net start mssqlserver t1229
重新启动后重复上述实例,死锁就不在出现了.
注:此实例只为说明由于锁分区造成的死锁情形,实际生产中此类情形却是罕见的,除非遇到这类情形并且没有更好的规避方式,一般我们还是建议默认此特性.这对提升并发是很有帮助的.
