鎖分區(qū)技術(shù)使得SQL Server可以更好地應(yīng)對(duì)并發(fā)情形,但也有可能帶來(lái)負(fù)面影響,這里通過(guò)實(shí)例為大家介紹,分析由于鎖分區(qū)造成的死鎖情形.

前段時(shí)間園友@JentleWang在我的博客鎖分區(qū)提升并發(fā),以及鎖等待實(shí)例中問(wèn)及鎖分區(qū)的一些特性造成死鎖的問(wèn)題,這類(lèi)死鎖并不常見(jiàn),我們?cè)谶@里仔細(xì)分析下.不了解鎖分區(qū)技術(shù)的朋友請(qǐng)先看下我的鎖分區(qū)那篇實(shí)例.

Code(執(zhí)行測(cè)試腳本時(shí)請(qǐng)注意執(zhí)行順序,說(shuō)明)

步驟1 創(chuàng)建測(cè)試數(shù)據(jù)

use tempdbgocreate table testdlk(id int identity(1,1) PRimary key,str1 char(3000))goinsert into testdlk(str1) select 'aaa'insert into testdlk(str1) select 'bbb'insert into testdlk(str1) select 'ccc'insert into testdlk(str1) select 'ddd'

步驟2 開(kāi)啟 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 開(kāi)啟session 2 執(zhí)行語(yǔ)句

--session 2BEGIN TRANupdate testdlk set str1='abc' where id=2 ---update the content of id=2SELECT * FROM testdlk WITH(TABLOCKX)------ try to get X lock on the object testdlkrollback tran---session id 58 this example

步驟4 開(kāi)啟session 3執(zhí)行數(shù)據(jù)

--session 3BEGIN TRANupdate 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 testdlkrollback 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犧牲.

原因分析.

通過(guò)步驟四我們可以得到相應(yīng)的會(huì)話的鎖,及相關(guān)等待情況如圖1-1