sql - 需要死锁图解释帮助
问题描述
我的一个朋友遇到了一个 Java 应用程序（Spring+Hibernate）的问题，该应用程序由于异常而随机崩溃：
事务（进程 ID 57）与另一个进程在锁资源上死锁，并已被选为死锁牺牲品
我认为这是由于滥用@Transactional注释造成的。事实上，他的应用程序中的每个查询都是具有“已提交读”隔离级别的显式事务。我已经要求他向我提供死锁图（见下文），但我无法理解。
如果我是正确的：进程 process1bc993e8108 (select) 持有表 TABLEA 上的共享锁。进程 process1bc9a25bc28 (insert) 正在等待对表 TABLEA 设置意向排他锁。但同时，同一个进程已经对表 TABLEA 持有意向排他锁 ??? 并且 process1bc9a10bc28 (select) 被选为牺牲品，因为它正在等待将共享锁放在 TABLEA 上？我在这里迷路了......对我来说这是简单的阻塞，我在这里找不到任何循环阻塞（又名死锁）
所以，这是我的问题：
- 我是否正确，这是由于显式事务和移动到单个查询的隐式事务将解决问题？
- 激活“已提交读”的“快照”选项可能是一个潜在的解决方案吗？或者他们应该默认降低事务隔离级别并只为多命令事务保持“读取提交”？
- 如何在 SQL 编辑器中重现死锁图？我应该
select + insert + select同时运行以重现它吗？
我真的很感激任何帮助。
<deadlock>
<victim-list>
<victimProcess id="process1bc9a10bc28" />
</victim-list>
<process-list>
<process id="process1bc9a10bc28" taskpriority="0" logused="0" waitresource="PAGE: 6:1:2283030 " waittime="1948" ownerId="125034381" transactionname="implicit_transaction" lasttranstarted="2020-02-26T15:09:44.357" XDES="0x1be848c6040" lockMode="S" schedulerid="5" kpid="7160" status="suspended" spid="72" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc994bf848" taskpriority="0" logused="10000" waittime="1496" schedulerid="1" kpid="7436" status="suspended" spid="72" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc9a158108" taskpriority="0" logused="10000" waittime="1499" schedulerid="2" kpid="4624" status="suspended" spid="72" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc993e84e8" taskpriority="0" logused="10000" waittime="1498" schedulerid="4" kpid="13552" status="suspended" spid="72" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc9a2c48c8" taskpriority="0" logused="10000" waittime="1499" schedulerid="3" kpid="9660" status="suspended" spid="72" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc993e8108" taskpriority="0" logused="10000" waittime="2502" schedulerid="4" kpid="15204" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" loginname="xcaf" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc993b1c28" taskpriority="0" logused="10000" waittime="1497" schedulerid="6" kpid="8708" status="suspended" spid="72" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc993ceca8" taskpriority="0" logused="10000" waittime="1498" schedulerid="5" kpid="11632" status="suspended" spid="72" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2020-02-26T15:09:44.367" lastbatchcompleted="2020-02-26T15:09:44.363" lastattention="1900-01-01T00:00:00.363" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" isolationlevel="read committed (2)" xactid="125034381" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="18" stmtend="164" sqlhandle="0x02000000baccf036bb7c864a41cd9425eebee165c997e0b00000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int)select sum(AMOUNT) from TABLEA where IND_REJECT='Y' and ID_BILL= @P0 </inputbuf>
</process>
<process id="process1bc9a25bc28" taskpriority="0" logused="35648" waitresource="PAGE: 6:1:5073184 " waittime="2077" ownerId="125033657" transactionname="implicit_transaction" lasttranstarted="2020-02-26T15:09:39.767" XDES="0x1c19a7b0ba0" lockMode="IX" schedulerid="6" kpid="13120" status="suspended" spid="69" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-02-26T15:09:44.803" lastbatchcompleted="2020-02-26T15:09:44.793" lastattention="1900-01-01T00:00:00.793" clientapp="jTDS" hostname="LP097MID8513" hostpid="123" loginname="xcaf" isolationlevel="read committed (2)" xactid="125033657" currentdb="6" currentdbname="server1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="1126" stmtend="2768" sqlhandle="0x0200000025025e075b272ac2bce1ec6cdcbc590a955eb7d30000000000000000000000000000000000000000">unknown </frame>
</executionStack>
<inputbuf>(@P0 int,@P1 int,@P2 nvarchar(4000),@P3 int,@P4 nvarchar(4000),@P5 int,@P6 int,@P7 nvarchar(4000),@P8 datetime,@P9 datetime,@P10 decimal(38,2),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 nvarchar(4000),@P21 nvarchar(4000),@P22 nvarchar(4000),@P23 nvarchar(4000),@P24 nvarchar(4000),@P25 nvarchar(4000),@P26 datetime,@P27 nvarchar(4000),@P28 datetime,@P29 nvarchar(4000),@P30 int,@P31 nvarchar(4000),@P32 datetime,@P33 int)insert into dbo.TABLEA ( </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="2283030" dbid="6" subresource="FULL" objectname="server1.dbo.TABLEA" id="lock1be67e3cd00" mode="IX" associatedObjectId="72057594048610304">
<owner-list>
<owner id="process1bc9a25bc28" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process1bc9a10bc28" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe1bedeed8a80" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="5" tid="4" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc9a10bc28" />
</owner-list>
<waiter-list>
<waiter id="process1bc994bf848" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1bedeed8b80" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="5" tid="5" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc9a10bc28" />
</owner-list>
<waiter-list>
<waiter id="process1bc9a158108" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1bedeed8880" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="5" tid="2" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc9a10bc28" />
</owner-list>
<waiter-list>
<waiter id="process1bc993e84e8" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1bedeed8c80" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="5" tid="6" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc9a10bc28" />
</owner-list>
<waiter-list>
<waiter id="process1bc9a2c48c8" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1bedeed8380" WaitType="e_waitPipeGetRow" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc993b1c28" />
<owner id="process1bc993e84e8" />
<owner id="process1bc993ceca8" />
<owner id="process1bc994bf848" />
<owner id="process1bc9a158108" />
<owner id="process1bc9a2c48c8" />
</owner-list>
<waiter-list>
<waiter id="process1bc993e8108" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1bedeed8780" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="5" tid="1" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc9a10bc28" />
</owner-list>
<waiter-list>
<waiter id="process1bc993b1c28" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe1bedeed8980" WaitType="e_waitPipeGetRow" waiterType="Consumer" nodeId="5" tid="3" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process1bc9a10bc28" />
</owner-list>
<waiter-list>
<waiter id="process1bc993ceca8" />
</waiter-list>
</exchangeEvent>
<pagelock fileid="1" pageid="5073184" dbid="6" subresource="FULL" objectname="server1.dbo.TABLEA" id="lock1bf59410980" mode="S" associatedObjectId="72057594048610304">
<owner-list>
<owner id="process1bc993e8108" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process1bc9a25bc28" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
CREATE TABLE TABLEA (
ID_TABLEA numeric(10,0) IDENTITY(1,1) NOT NULL,
ID_BILL numeric(10,0) NOT NULL,
AMOUNT numeric(16,2) NULL,
IND_REJECT char(1) COLLATE French_CI_AS NULL,
CONSTRAINT PK__TABLEA__0F1F9EE614270015 PRIMARY KEY (ID_TABLEA),
)
解决方案
推荐阅读
- google-cloud-functions - firebase云功能上限和时间限制
- linux - 如何使用 cmake 通过 gRPC 构建 Qt Quick Application（使用 Linux）
- angular - 带有角度 pwa 的独立模式问题
- flutter - 圆角半径圆环图问题与飞镖中的描边油漆
- javascript - NuxtJS 和 Firebase Web SDK Beta v9：如何将实时监听器添加到 Firestore？
- php - 将输入按钮转换为href
- java - 使用 java 反射 API 递归遍历对象实例（具有大量嵌套的自定义对象）
- cookies - 未来第一方 Cookie 是否也会被抛弃？
- java - 防止 Maven Cargo 插件将标准输出捕获到文件
- swift - 如何删除swiftUI列表上的切换下拉列表