鎖分區(qū)技術(shù)使得SQL Server可以更好地應(yīng)對(duì)并發(fā)情形,但也有可能帶來負(fù)面影響,這里通過實(shí)例為大家介紹,分析由于鎖分區(qū)造成的死鎖情形.
前段時(shí)間園友 @JentleWang 在我的博客 鎖分區(qū)提升并發(fā),以及鎖等待實(shí)例 中問及鎖分區(qū)的一些特性造成死鎖的問題,這類死鎖并不常見,我們?cè)谶@里仔細(xì)分析下.不了解鎖分區(qū)技術(shù)的朋友請(qǐng)先看下我的鎖分區(qū)那篇實(shí)例.
Code(執(zhí)行測(cè)試腳本時(shí)請(qǐng)注意執(zhí)行順序,說明)
步驟1 創(chuàng)建測(cè)試數(shù)據(jù)
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 執(zhí)行語(yǔ)句
--
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 執(zhí)行語(yǔ)句
--
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執(zhí)行數(shù)據(jù)
--
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 創(chuàng)建腳本的session中執(zhí)行語(yǔ)句
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
當(dāng)session 1回滾時(shí),session2 session 3造成死鎖,session 3犧牲.
原因分析.
通過步驟四我們可以得到相應(yīng)的會(huì)話的鎖,及相關(guān)等待情況如圖1-1
??????????????????????????????????????????????????????????????? 圖1-1
?
可以看到session 1(圖中55)由于只是更新id=1的列,所以它會(huì)在key上加排它鎖(圖中未列出,感興趣朋友可以自行查看),而在Object testdlk中某個(gè)鎖分區(qū)中圖中為鎖分區(qū)1加上意向排它鎖.
Session 2(圖中58)由于更新了id=2的列,所以會(huì)在相應(yīng)Key上加排他鎖,并在某個(gè)鎖分區(qū)中加意向排他鎖(IX),于此同時(shí)由于此事務(wù)下面查詢有表級(jí)TABLOCKX Hint,此時(shí)58會(huì)嘗試在表級(jí)上排他鎖(X鎖).由于X鎖需要在所有鎖分區(qū)中獲得,此時(shí)58在鎖分區(qū)0中獲得X鎖,但由于鎖分區(qū)1中有55獲得了意向排他鎖(IX),所以58在鎖分區(qū)1中嘗試獲取X鎖時(shí)狀態(tài)未Convert,被55阻塞.
Session 3(圖中59)由于更新了id=3的列,所以會(huì)在相應(yīng)key上加排他鎖,同時(shí)在某個(gè)鎖分區(qū)中加意向排他鎖(IX),于此同時(shí)由于此事務(wù)下面查詢同樣有表級(jí)TABLOCKX Hint,這時(shí)59也會(huì)嘗試在表的所有分區(qū)中獲取X鎖.由于58已經(jīng)獲得鎖分區(qū)0的X鎖,所以當(dāng)59嘗試獲取鎖分區(qū)0的X鎖時(shí),就會(huì)被58阻塞,狀態(tài)為Wait.
問題來了,當(dāng)55回滾時(shí),其上面的鎖也將被釋放.此時(shí)58,59都試圖獲得表級(jí)的所有分區(qū)X鎖,而又同時(shí)在鎖分區(qū)中持有IX鎖,這時(shí)死鎖就不可避免了.
死鎖視圖如圖1-2所示.
?
????????????????????????????????????????????? 圖1-2
?
問題解決
經(jīng)過分析,可以看出是由于鎖分區(qū)的特性導(dǎo)致IX與不同spid中的X互斥導(dǎo)致,那如果能禁用鎖分區(qū)特性不就沒有在個(gè)別分區(qū)上的IX這回事兒了嗎.這里介紹一個(gè)啟動(dòng)標(biāo)記 trace flag 1229,可以禁用鎖分區(qū)特性.我們可以通過配置管理器中添加啟動(dòng)標(biāo)記,也可以在command啟動(dòng)時(shí)加上響應(yīng)參數(shù).應(yīng)當(dāng)注意當(dāng)使用配置管理器時(shí),我們應(yīng)在啟動(dòng)參數(shù)末尾配置”-T1229”,如果使用command,這時(shí)是t1229(大小寫區(qū)分)
這里我用win中command啟用
Code
Net start mssqlserver /t1229
重新啟動(dòng)后重復(fù)上述實(shí)例,死鎖就不在出現(xiàn)了.
注: 此實(shí)例只為說明由于鎖分區(qū)造成的死鎖情形,實(shí)際生產(chǎn)中此類情形卻是罕見的,除非遇到這類情形并且沒有更好的規(guī)避方式,一般我們還是建議默認(rèn)此特性.這對(duì)提升并發(fā)是很有幫助的.
關(guān)于鎖分區(qū)特性.
通過微軟的在線文檔可以得知,只有當(dāng)CPU的邏輯數(shù)大于等于16時(shí),才會(huì)默認(rèn)開啟此特性,而授權(quán)又是按照CPU收費(fèi)的.問題來了,當(dāng)CPU小于16我如果想利用此特性是否可以呢?這個(gè)再給大家介紹一個(gè)啟動(dòng)跟蹤標(biāo)記 trace flag 1228.當(dāng)有兩個(gè)及以上邏輯CPU時(shí)就會(huì)啟動(dòng)鎖分區(qū)特性.不過我們使用時(shí)清楚自己的使用場(chǎng)景,是否會(huì)因此TF得到好處.由于這是無官方文檔記錄的特性,使用應(yīng)只針對(duì)特定需求,并應(yīng)慎重.
結(jié)語(yǔ): SQL Server或是其他數(shù)據(jù)庫(kù)系統(tǒng)中任何一個(gè)特性的引入總會(huì)適應(yīng)大多數(shù)場(chǎng)景,但也會(huì)伴隨著特定場(chǎng)景的弊端出現(xiàn),清楚其所帶來的利弊并合理使用,使得SQL Server適應(yīng)場(chǎng)景,我們也能適應(yīng)SQL Server.
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
