sql-server – 从连接表中选择时如何避免死锁?

前端之家收集整理的这篇文章主要介绍了sql-server – 从连接表中选择时如何避免死锁?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有两个非常简单的表,让我们称它们为[UserData1]和[UserData2].它们都有[UserId]列作为主键.我正在针对这两个表运行两种类型的查询.一个是SELECT语句,它返回特定用户的组合数据:
  1. SELECT <a subset of columns from both tables>
  2. FROM [UserData1] ud1
  3. FULL OUTER JOIN [UserData2] ud2 ON ud1.[UserId] = ud2.[UserId]
  4. WHERE
  5. ud1.[UserId] = @UserId OR ud2.[UserId] = @UserId

另一个是为特定用户更新两个表中的用户数据的事务:

  1. BEGIN TRANSACTION
  2.  
  3. UPDATE [UserData1]
  4. SET <new values>
  5. WHERE [UserId] = @UserId
  6.  
  7. UPDATE [UserData2]
  8. SET <new values>
  9. WHERE [UserId] = @UserId
  10.  
  11. COMMIT TRANSACTION

这里的问题是在SELECT语句中获取共享表锁的顺序是不确定的,如果sql Server决定在[UserData1]之前锁定[UserData2],这可能(并且实际上)会导致传统的死锁情况.在这种情况下,避免死锁的最佳方法是什么?

将这些表合并到一个表中,对吗?我希望这很容易.假设有理由将它们分开.

READ UNCOMMITTED / NOLOCK提示?假设不能容忍脏读.

SNAPSHOT隔离级别?这样可以解决问题,但我不确定所涉及的开销.

所以问题归结为:有没有办法保证获取连接表的锁定顺序?

起初我认为这可以通过FORCE ORDER查询提示来实现,但后来我通过实验发现它不一定强制执行表被锁定的顺序.在这种特殊情况下的另一个解决方案是为每个表发出单独的SELECT查询,然后在应用程序层中组合两个单行记录集,但是如果我需要为多个用户进行查询,我仍然希望得到所有导致一个记录集.

更新:

这是死锁跟踪的摘录:

  1. Deadlock encountered .... Printing deadlock information
  2. Wait-for graph
  3.  
  4. Node:1
  5. KEY: 17:72057594039173120 (e21762ccf3dc) CleanCnt:3 Mode:X Flags: 0x1
  6. Grant List 1:
  7. Owner:0x00000020F75B0480 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:72 ECID:0 XactLockInfo: 0x00000020EB13ED68
  8. SPID: 72 ECID: 0 Statement Type: UPDATE Line #: 1
  9. Input Buf: Language Event: (@UserId bigint,@DataColumn2 int)update
  10. Requested by:
  11. ResType:LockOwner Stype:'OR'Xdes:0x00000020FC98DA40 Mode: S SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000020DAB38608) Value:0xf75abbc0 Cost:(0/0)
  12.  
  13. Node:2
  14. KEY: 17:72057594039107584 (e21762ccf3dc) CleanCnt:9 Mode:S Flags: 0x1
  15. Grant List 1:
  16. Owner:0x00000020EEBFE580 Mode: S Flg:0x40 Ref:1 Life:00000000 SPID:75 ECID:0 XactLockInfo: 0x00000020FC98DA80
  17. SPID: 75 ECID: 0 Statement Type: SELECT Line #: 1
  18. Input Buf: Language Event: (@UserId bigint)select [t].[UserId],t.[DataColumn2],t1.[DataColumn1]
  19. Requested by:
  20. ResType:LockOwner Stype:'OR'Xdes:0x00000020EB13ED28 Mode: X SPID:72 BatchID:0 ECID:0 TaskProxy:(0x0000001F671C6608) Value:0xf75b5400 Cost:(0/456)
  21.  
  22. Victim Resource Owner:
  23. ResType:LockOwner Stype:'OR'Xdes:0x00000020FC98DA40 Mode: S SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000020DAB38608) Value:0xf75abbc0 Cost:(0/0)
  24. deadlock-list
  25. deadlock victim=process20fda2ccf8
  26. process-list
  27. process id=process20fda2ccf8 taskpriority=0 logused=0 waitresource=KEY: 17:72057594039173120 (e21762ccf3dc) waittime=4526 ownerId=3416711 transactionname=SELECT lasttranstarted=2013-07-11T18:42:20.943 XDES=0x20fc98da40 lockMode=S schedulerid=20 kpid=2800 status=suspended spid=75 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-07-11T18:42:20.950 lastbatchcompleted=2013-07-11T18:42:20.950 lastattention=1900-01-01T00:00:00.950 clientapp=.Net sqlClient Data Provider hostname=hostname hostpid=27716 loginname=loginname isolationlevel=read committed (2) xactid=3416711 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
  28. executionStack
  29. frame procname=adhoc line=1 stmtstart=36 sqlhandle=0x020000001fcbbe1423a0c65cc8411344c6040e879195af3a0000000000000000000000000000000000000000
  30. select [t].[UserId],t1.[DataColumn1] from [UserData1] t1 full outer join [UserData2] t on t1.[UserId]=t.[UserId] where t.[UserId]=@UserId or t1.[UserId]=@UserId option (force order)
  31. frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  32. unknown
  33. inputbuf
  34. (@UserId bigint)select [t].[UserId],t1.[DataColumn1] from [UserData1] t1 full outer join [UserData2] t on t1.[UserId]=t.[UserId] where t.[UserId]=@UserId or t1.[UserId]=@UserId option (force order)
  35. process id=process20fd055498 taskpriority=0 logused=456 waitresource=KEY: 17:72057594039107584 (e21762ccf3dc) waittime=4525 ownerId=3416764 transactionname=user_transaction lasttranstarted=2013-07-11T18:42:20.960 XDES=0x20eb13ed28 lockMode=X schedulerid=9 kpid=6024 status=suspended spid=72 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-07-11T18:42:20.970 lastbatchcompleted=2013-07-11T18:42:20.970 lastattention=1900-01-01T00:00:00.970 clientapp=.Net sqlClient Data Provider hostname=hostname hostpid=27716 loginname=loginname isolationlevel=read committed (2) xactid=3416764 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
  36. executionStack
  37. frame procname=adhoc line=1 stmtstart=508 sqlhandle=0x02000000c0d74a32597ec460559a2d5dbdc92f7746cdce270000000000000000000000000000000000000000
  38. update UserData2 set [LastModified]=getutcdate(),[DataColumn2]=[DataColumn2]+@DataColumn2Increment where [UserId]=@UserId
  39. frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  40. unknown
  41. inputbuf
  42. (@UserId bigint,@DataColumn2Increment int)update UserData2 set [LastModified]=getutcdate(),[DataColumn2]=[DataColumn2]+@DataColumn2Increment where [UserId]=@UserId
  43. resource-list
  44. keylock hobtid=72057594039173120 dbid=17 objectname=database_name.dbo.UserData1 indexname=1 id=lock20ec75b380 mode=X associatedObjectId=72057594039173120
  45. owner-list
  46. owner id=process20fd055498 mode=X
  47. waiter-list
  48. waiter id=process20fda2ccf8 mode=S requestType=wait
  49. keylock hobtid=72057594039107584 dbid=17 objectname=database_name.dbo.UserData2 indexname=1 id=lock20ec07f600 mode=S associatedObjectId=72057594039107584
  50. owner-list
  51. owner id=process20fda2ccf8 mode=S
  52. waiter-list
  53. waiter id=process20fd055498 mode=X requestType=wait

显然,在[UserData1]之前,运行SELECT语句的进程在[UserData2]表上获取了一个锁,尽管有FORCE ORDER提示.

解决方法

使用READ COMMITTED时,select不应该参与死锁,因为它一次只能获得一个锁.读取锁定的行后,可以立即释放锁定.

我非常建议你打开快照隔离.它将解决这个问题.熟悉所涉及的3个开销:增加行大小,tempdb写入和微小的读取开销.大多数时候他们没有意义.

猜你在找的MsSQL相关文章