我知道根据错误消息，我正在排除故障，因死锁错误而失败的报告是进程 ID 1017。 我感到困惑的是导致僵局的原因？ 下面的此文本文件中有几个语句，它们都具有与错误相同的时间戳，并且它们似乎都是同一死锁的一部分。 多个 sql 语句是否会导致死锁？寻求有关如何解释此死锁文件的一些帮助。
08/01/2017 17:47:09,spid7s,Unknown,Deadlock encountered .... Printing deadlock information
08/01/2017 17:47:09,spid7s,Unknown,Wait-for graph
08/01/2017 17:47:09,spid7s,Unknown,
08/01/2017 17:47:09,spid7s,Unknown,Node:1
08/01/2017 17:47:09,spid7s,Unknown,PAGE: 8:11:14583091 CleanCnt:2 Mode:IX Flags: 0x3
08/01/2017 17:47:09,spid7s,Unknown,Grant List 2:
08/01/2017 17:47:09,spid7s,Unknown,Owner:0x0000001A725D4100 Mode: IX Flg:0x40 Ref:0 Life:02000000 SPID:1127 ECID:0 XactLockInfo: 0x0000000E8BEAD400
08/01/2017 17:47:09,spid7s,Unknown,SPID: 1127 ECID: 0 Statement Type: UPDATE Line #: 130
08/01/2017 17:47:09,spid7s,Unknown,Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 1454029057]
08/01/2017 17:47:09,spid7s,Unknown,Requested by:
08/01/2017 17:47:09,spid7s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000090209A1C0 Mode: S SPID:1017 BatchID:0 ECID:0 TaskProxy:(0x00000008341A8540) Value:0x7380fc00 Cost:(0/0)
08/01/2017 17:47:09,spid7s,Unknown,
08/01/2017 17:47:09,spid7s,Unknown,Node:2
08/01/2017 17:47:09,spid7s,Unknown,PAGE: 8:14:14546741 CleanCnt:2 Mode:S Flags: 0x3
08/01/2017 17:47:09,spid7s,Unknown,Grant List 0:
08/01/2017 17:47:09,spid7s,Unknown,Owner:0x0000001A6160EFC0 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:1017 ECID:0 XactLockInfo: 0x000000090209A200
08/01/2017 17:47:09,spid7s,Unknown,SPID: 1017 ECID: 0 Statement Type: SELECT Line #: 1
08/01/2017 17:47:09,spid7s,Unknown,Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 1584229640]
08/01/2017 17:47:09,spid7s,Unknown,Requested by:
08/01/2017 17:47:09,spid7s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000E8BEAD3C0 Mode: IX SPID:1127 BatchID:0 ECID:0 TaskProxy:(0x0000000E3D92A540) Value:0xee1af100 Cost:(0/5112)
08/01/2017 17:47:09,spid7s,Unknown,
08/01/2017 17:47:09,spid7s,Unknown,Victim Resource Owner:
08/01/2017 17:47:09,spid7s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000090209A1C0 Mode: S SPID:1017 BatchID:0 ECID:0 TaskProxy:(0x00000008341A8540) Value:0x7380fc00 Cost:(0/0)
08/01/2017 17:47:09,spid16s,Unknown,deadlock-list
08/01/2017 17:47:09,spid16s,Unknown,deadlock victim=process623b4c8
08/01/2017 17:47:09,spid16s,Unknown,process-list
08/01/2017 17:47:09,spid16s,Unknown,process id=process623b4c8 taskpriority=0 logused=0 waitresource=PAGE: 8:11:14583091 waittime=928 ownerId=10726052208 transactionname=SELECT lasttranstarted=2017-08-01T17:47:08.273 XDES=0x90209a1c0 lockMode=S schedulerid=6 kpid=17108 status=suspended spid=1017 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2017-08-01T17:47:08.273 lastbatchcompleted=2017-08-01T17:47:08.263 clientapp=.Net SqlClient Data Provider hostname=***hostpid=8368 loginname=***isolationlevel=read committed (2) xactid=10726052208 currentdb=8 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
08/01/2017 17:47:09,spid16s,Unknown,executionStack
08/01/2017 17:47:09,spid16s,Unknown,frame procname=adhoc line=1 stmtstart=2 sqlhandle=0x020000004bafe30b4e652e881b86ef71b9df0ed329054914
08/01/2017 17:47:09,spid16s,Unknown,@1 varchar(8000)<c/>@2 smallint)SELECT * FROM [vw_OutstandingAdjustedInvoices] WHERE [VendorID]=@1 AND [ServicerRegionCodeID]=@2
08/01/2017 17:47:09,spid16s,Unknown,frame procname=adhoc line=1 stmtstart=2 sqlhandle=0x020000003e0235301d588b82abcced6d1ecdb62efc65b46d
08/01/2017 17:47:09,spid16s,Unknown,SELECT * FROM vw_OutstandingAdjustedInvoices WHERE VendorID='13867' AND ServicerRegionCodeID=1076
08/01/2017 17:47:09,spid16s,Unknown,frame procname=NewInvoice.dbo.sp_ExecuteMySQL line=41 stmtstart=2986 stmtend=3012 sqlhandle=0x03000800086d6d5eeed84b0165a500000100000000000000
08/01/2017 17:47:09,spid16s,Unknown,EXEC (@SQL)
08/01/2017 17:47:09,spid16s,Unknown,inputbuf
08/01/2017 17:47:09,spid16s,Unknown,Proc [Database Id = 8 Object Id = 1584229640]
08/01/2017 17:47:09,spid16s,Unknown,process id=process62e5dc8 taskpriority=0 logused=5112 waitresource=PAGE: 8:14:14546741 waittime=1016 ownerId=10726056691 transactionname=UPDATE lasttranstarted=2017-08-01T17:47:08.440 XDES=0xe8bead3c0 lockMode=IX schedulerid=23 kpid=30140 status=suspended spid=1127 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-08-01T17:47:08.440 lastbatchcompleted=2017-08-01T17:47:08.440 lastattention=2017-08-01T10:26:17.713 clientapp=.Net SqlClient Data Provider hostname=****hostpid=7512 loginname=****isolationlevel=read committed (2) xactid=10726056691 currentdb=8 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
08/01/2017 17:47:09,spid16s,Unknown,executionStack
08/01/2017 17:47:09,spid16s,Unknown,frame procname=NewInvoice.dbo.usp_EInvoice_UpdateReviewedDate line=130 stmtstart=8334 stmtend=9454 sqlhandle=0x0300080001b9aa5699653d00cea200000100000000000000
08/01/2017 17:47:09,spid16s,Unknown,UPDATE
08/01/2017 17:47:09,spid16s,Unknown,dbo.EInvoice
08/01/2017 17:47:09,spid16s,Unknown,SET
08/01/2017 17:47:09,spid16s,Unknown,ReviewedDate = @ReviewedDate
08/01/2017 17:47:09,spid16s,Unknown,<c/>FirstReviewedDate = @FirstReviewedDate
08/01/2017 17:47:09,spid16s,Unknown,<c/>EInvoiceStatusID = @EInvoiceStatusID
08/01/2017 17:47:09,spid16s,Unknown,<c/>DateUpdated = GETDATE()
08/01/2017 17:47:09,spid16s,Unknown,<c/>ServicerClickChargeDate = @ServicerClickChargeDate
08/01/2017 17:47:09,spid16s,Unknown,<c/>ServicerClickChargeAmount = @ServicerClickChargeAmount
08/01/2017 17:47:09,spid16s,Unknown,<c/>OrdinalNumForDptLoanVnd = @OrdinalNumForDptLoanVnd
08/01/2017 17:47:09,spid16s,Unknown,<c/>OrdinalNumForDptLoanReferral = @OrdinalNumForDptLoanReferral
08/01/2017 17:47:09,spid16s,Unknown,<c/>OrdinalNumForReferralTypeLoanVnd = @OrdinalNumForReferralTypeLoanVnd
08/01/2017 17:47:09,spid16s,Unknown,WHERE EInvoiceID = @EInvoiceID
08/01/2017 17:47:09,spid16s,Unknown,inputbuf
08/01/2017 17:47:09,spid16s,Unknown,Proc [Database Id = 8 Object Id = 1454029057]
08/01/2017 17:47:09,spid16s,Unknown,resource-list
08/01/2017 17:47:09,spid16s,Unknown,pagelock fileid=11 pageid=14583091 dbid=8 objectname=NewInvoice.dbo.EInvoice id=lock1a6ead2200 mode=IX associatedObjectId=72057596262678528
08/01/2017 17:47:09,spid16s,Unknown,owner-list
08/01/2017 17:47:09,spid16s,Unknown,owner id=process62e5dc8 mode=IX
08/01/2017 17:47:09,spid16s,Unknown,waiter-list
08/01/2017 17:47:09,spid16s,Unknown,waiter id=process623b4c8 mode=S requestType=wait
08/01/2017 17:47:09,spid16s,Unknown,pagelock fileid=14 pageid=14546741 dbid=8 objectname=NewInvoice.dbo.EInvoice id=lock1a5cbad580 mode=S associatedObjectId=72057596262678528
08/01/2017 17:47:09,spid16s,Unknown,owner-list
08/01/2017 17:47:09,spid16s,Unknown,owner id=process623b4c8 mode=S
08/01/2017 17:47:09,spid16s,Unknown,waiter-list
08/01/2017 17:47:09,spid16s,Unknown,waiter id=process62e5dc8 mode=IX requestType=wait
此数据库未使用"已提交读取快照"隔离，并且报表会话未使用"快照"隔离，因此 SELECT 查询需要 S 锁才能运行。 使用 RCSI，SELECT 不需要任何锁，也不会与更新事务冲突。 请参阅 SQL Server 中的快照隔离
它与更新表 dbo 的存储过程冲突。语音
08/01/2017 17:47:09,spid16s,Unknown,frame procname=NewInvoice.dbo.usp_EInvoice_UpdateReviewedDate line=130 stmtstart=8334 stmtend=9454 sqlhandle=0x0300080001b9aa5699653d00cea200000100000000000000
08/01/2017 17:47:09,spid16s,Unknown,UPDATE
08/01/2017 17:47:09,spid16s,Unknown,dbo.EInvoice
08/01/2017 17:47:09,spid16s,Unknown,SET
08/01/2017 17:47:09,spid16s,Unknown,ReviewedDate = @ReviewedDate
08/01/2017 17:47:09,spid16s,Unknown,<c/>FirstReviewedDate = @FirstReviewedDate
08/01/2017 17:47:09,spid16s,Unknown,<c/>EInvoiceStatusID = @EInvoiceStatusID
08/01/2017 17:47:09,spid16s,Unknown,<c/>DateUpdated = GETDATE()
08/01/2017 17:47:09,spid16s,Unknown,<c/>ServicerClickChargeDate = @ServicerClickChargeDate
08/01/2017 17:47:09,spid16s,Unknown,<c/>ServicerClickChargeAmount = @ServicerClickChargeAmount
08/01/2017 17:47:09,spid16s,Unknown,<c/>OrdinalNumForDptLoanVnd = @OrdinalNumForDptLoanVnd
08/01/2017 17:47:09,spid16s,Unknown,<c/>OrdinalNumForDptLoanReferral = @OrdinalNumForDptLoanReferral
08/01/2017 17:47:09,spid16s,Unknown,<c/>OrdinalNumForReferralTypeLoanVnd = @OrdinalNumForReferralTypeLoanVnd
08/01/2017 17:47:09,spid16s,Unknown,WHERE EInvoiceID = @EInvoiceID
更新会话在此表的一页上拥有 IX 锁，并尝试在此表的另一页上获取 IX 锁。 可能这是一个多语句事务，并且已经更新了其他一些EInvoice行。
选项：
- 将数据库切换到 RCSI，但这需要测试。
- 将报表切换为使用 SNAPSHOT 隔离，但这需要更改应用代码。
- 强制其中一个会话使用表锁，以便在获取任何冲突锁之前阻塞。
我不熟悉这种格式，所以我无法帮助破译您的死锁。它看起来像死锁图的文本版本。
死锁中总是会涉及不止一个语句。在SQL服务器世界中，死锁是两个或多个进程之间对资源(锁)的竞争。几乎每次选择死锁受害者时，都会是一个选择语句。选择命令(读取)保存的资源量最少，并且最容易回滚。SQL 服务器将回滚持有最少资源的进程。这可能就是为什么您的报告程序是选择的受害者。您看到的其他进程的命令都是运行报表时的活动查询。
读取死锁图并非易事。死锁通常发生在长时间读取进程在大量写入期间运行时。有一些方法可以尝试防止死锁。如果您进行网络搜索以防止死锁，那么您会找到有关该主题的一些有用信息，但是，无论您采取什么预防措施，当您的系统足够繁忙时，您都会遇到一个或多个死锁。
理想情况下，您将尝试将写入密集型和读取量级系统拆分为两个领域，OLTP 和 OLAP，但是，这并不总是实用的。
如果报表不会因可能读取未提交的数据而受到负面影响，则可以通过在联接和表选择语句上放置NOLOCK提示或为存储过程指定read uncommitted transaction isolation level来避免报表存储过程中出现此问题。这并不总是理想的，但僵局也不是:)