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

锁分区技术使得SQL Server可以更好地应对并发情形,但也有可能带来负面影响,这里通过实例为大家介绍,分析由于锁分区造成的死锁情形.

前段时间园友@JentleWang在我的博客锁分区提升并发,以及锁等待实例中问及锁分区的一些特性造成死锁的问题,这类死锁并不常见,我们在这里仔细分析下.不了解锁分区技术的朋友请先看下我的锁分区那篇实例.

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

                                                                图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(大小写区分)

这里我用win中command启用

Code

Net start mssqlserver /t1229

重新启动后重复上述实例,死锁就不在出现了.

注:此实例只为说明由于锁分区造成的死锁情形,实际生产中此类情形却是罕见的,除非遇到这类情形并且没有更好的规避方式,一般我们还是建议默认此特性.这对提升并发是很有帮助的.

关于锁分区特性.

通过微软的在线文档可以得知,只有当CPU的逻辑数大于等于16时,才会默认开启此特性,而授权又是按照CPU收费的.问题来了,当CPU小于16我如果想利用此特性是否可以呢?这个再给大家介绍一个启动跟踪标记 trace flag 1228.当有两个及以上逻辑CPU时就会启动锁分区特性.不过我们使用时清楚自己的使用场景,是否会因此TF得到好处.由于这是无官方文档记录的特性,使用应只针对特定需求,并应慎重.

结语:SQL Server或是其他数据库系统中任何一个特性的引入总会适应大多数场景,但也会伴随着特定场景的弊端出现,清楚其所带来的利弊并合理使用,使得SQL Server适应场景,我们也能适应SQL Server.