锁分区技术使得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.
