简单的死锁分析

在错误日志中检测到以下死锁:

deadlock-list
 deadlock victim=process16fc9dd498
  process-list
   process id=process16fc9dd498 taskpriority=0 logused=0 waitresource=KEY: 7:72057595333771264 (58bb110a434d) waittime=2239 ownerId=8413250494 transactionname=user_transaction lasttranstarted=2016-07-01T22:52:34.180 XDES=0x892f80d28 lockMode=U schedulerid=81 kpid=10732 status=suspended spid=242 sbid=0 ecid=0 priority=0 trancount=3 lastbatchstarted=2016-07-01T22:52:34.183 lastbatchcompleted=2016-07-01T22:52:34.180 lastattention=1900-01-01T00:00:00.180 clientapp=pytds hostname=adj52 hostpid=31708 loginname=app_rw isolationlevel=read committed (2) xactid=8413250494 currentdb=7 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
    executionStack
     frame procname=xxx.dbo.gsp_InsertCustomerInfo_v2 line=32 stmtstart=2358 stmtend=2582 sqlhandle=0x030007004577437e8a39af005ba5000001000000000000000000000000000000000000000000000000000000
update D_CustomerInfo set CustomerName[email protected], [email protected] where cellPhone=@CellPhone     
     frame procname=adhoc line=1 stmtstart=316 sqlhandle=0x010007008d0ab52e709f6cd40100000000000000000000000000000000000000000000000000000000000000
exec gsp_InsertCustomerInfo_v2 
     frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown     
    inputbuf
   exec gsp_InsertCustomerInfo_v2      
   process id=process16fd5b1868 taskpriority=0 logused=40140 waitresource=KEY: 7:72057595738193920 (8111eec6ede3) waittime=2224 ownerId=8411580132 transactionname=user_transaction lasttranstarted=2016-07-01T22:40:56.053 XDES=0x5405ce3a8 lockMode=U schedulerid=52 kpid=9136 status=suspended spid=339 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-07-01T22:52:34.180 lastbatchcompleted=2016-07-01T22:40:56.053 lastattention=2016-07-01T22:40:56.053 clientapp=pytds hostname=adj52 hostpid=33727 loginname=app_rw isolationlevel=read committed (2) xactid=8411580132 currentdb=7 lockTimeout=5000 clientoption1=671219744 clientoption2=128056
    executionStack
     frame procname=xxx.dbo.zxf_InsertOrder_v9 line=1109 stmtstart=62672 stmtend=62860 sqlhandle=0x030007009ab5d3576d78a30036a6000001000000000000000000000000000000000000000000000000000000
UPDATE D_CustomerInfo set isFree = 2,isbaidu=1  where Cellphone = @CellPhone 
    --更新司机状态     
     frame procname=adhoc line=1 stmtstart=2308 sqlhandle=0x01000700bdbd0606b05ecde93100000000000000000000000000000000000000000000000000000000000000
exec zxf_InsertOrder_v9     
     frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown     
    inputbuf
(@CellPhone VARCHAR(MAX),@FromCellPhone VARCHAR(MAX).... 
  resource-list
   keylock hobtid=72057595333771264 dbid=7 objectname=xxx.dbo.D_CustomerInfo indexname=PK_D_CustomerInfo id=lock124ed8ee00 mode=X associatedObjectId=72057595333771264
    owner-list
     owner id=process16fd5b1868 mode=X
    waiter-list
     waiter id=process16fc9dd498 mode=U requestType=wait
   keylock hobtid=72057595738193920 dbid=7 objectname=aidaijiaNew.dbo.D_CustomerInfo indexname=idx_Cellphone__CustomerId_CustomerName_RecommendCode_Amount id=lock7bc3b8380 mode=U associatedObjectId=72057595738193920
    owner-list
     owner id=process16fc9dd498 mode=U
    waiter-list
     waiter id=process16fd5b1868 mode=U requestType=wait

查看代码

process16fc9dd498

xxx.dbo.xxx_InsertCustomerInfo_v2

声明: 更新D_CustomerInfo设置为[受保护的电子邮件]，[受保护的电子邮件]，其中[受保护的电子邮件]

在第7页的索引idx_Cellphone__xxxx的数据行上拥有更新锁: 72057595738193920（8111eec6ede3），并在主键PK_D_CustomerInfo 7的数据行上等待更新锁: 72057595333771264（58bb110a434d）

process16fd5b1868

xxx.dbo.xxx_InsertOrder_v9

声明: UPDATE D_CustomerInfo设置为isFree = 2，isbaidu = 1，其中手机= @CellPhone

在主键PK_D_CustomerInfo的7: 72057595333771264（58bb110a434d）数据行上保留排他锁，并等待idx_Cellphone__xxxx第7页上的索引锁: 72057595738193920（8111eec6ede3）更新行

最终，16fc9dd498成为受害者.

--------------------------------------------------- ---------

通过以下方式可以找到锁定在主键PK_D_CustomerInfo上的内容:

SELECT %%lockres%% AS keyhashvalue,cellphone FROM  D_CustomerInfo
where 
%%lockres%% = ‘(58bb110a434d)‘

那么锁定在索引idx_Cellphone__xxxx 7上的内容是: 72057595738193920（8111eec6ede3）是相同的电话号码吗？我们可以尝试从索引根节点向下查看，看看使用手机13917XX6177的索引页面上的keyhashvalue是否为8111eec6ede3

首先找到索引的根节点:

Select dbo.f_get_page(I.root_page) root_page
FROM sys.partitions P
INNER JOIN SYS.allocation_units A
ON P.partition_id = A.container_id
INNER JOIN SYS.system_internals_allocation_units I
ON A.allocation_unit_id = I.allocation_unit_id
WHERE P.object_id = OBJECT_ID(‘D_CustomerInfo‘)
AND P.index_id = 66

再次查看根节点的内容:

再次找到13916XXXX的ChildPage8503545

....

再次找到ChildPage8506395，并确认索引页上锁定了相同的电话号码13917XX6177（KeyHashValue为8111eec6ede3）

-----------------------------------

死锁形成过程:

1.xxx_InsertOrder_v9在存储过程中打开了一个事务，并首先更新了D_CustomerInfo，但此更新不涉及idx_Cellphone__xxxx上的内容更新死锁分析，因此它仅对主键上的行13917XX6177持有排他锁.

2.xxx_InsertCustomerInfo_v2存储过程请求更新D_CustomerInfo，查询条件为手机，因此首先获取idx_Cellphone__xxxx上第13917XX6177行的更新锁，然后申请主键上第13917XX6177行的更新锁xxx_InsertOrder_v9释放排他锁.

3.xxx_InsertOrder_v9需要在执行后再次更新D_CustomerInfo. 更新查询条件是手机，因此我在idx_Cellphone__xxxx的行13917XX6177上申请了更新锁，但是由于更新锁被xxx_InsertCustomerInfo_v2占用，所以我不得不等待.

因此彼此之间构成了死锁，并且xxx_InsertCustomerInfo_v2成为了受害者.

----------------------------------

死锁解决方案:

在解决方案1.xxx_InsertOrder_v9中，两次更新D_CustomerInfo以专注于一次更新

在解决方案2.xxx_InsertOrder_v9中，当判断客户是否存在时，可以直接从D_CustomerInfo中选择***（带有上锁，行锁），其中手机= XXXX. 这样死锁分析，该更新将不会释放对idx_Cellphone__xxxx的更新锁定，这当然会增加锁定资源的数量并延长锁定时间.

简单的死锁分析

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-151545-1.html