【sqlserver】死锁问题求助

liaoliutao

  想知道这是怎么个逻辑造成的死锁，虚心求教下面是sqlserer查询到的死锁错误文档
我初步理解是，
我的spid="110" 进程，查询加了共享锁（S锁）；
然后spid="131"更新单个数据加上了X锁，这时spid="110" 查询分页查询到这条数据时，发现有行级锁（X锁），然后S锁加不上，进行等待
然后spid="131"的锁升级，进行加页锁(IX)，但是页数据中已经有数据加了S锁，所以加不上IX锁，进行等待
然后spid="110" 也在等待，无法释放，由此死锁
但是我还是有点懵，不知道理解的对不对，然后怎么解决。求指点

<event name="xml_deadlock_report" package="sqlserver" timestamp="2023-06-14T03:10:29.277Z">
    <data name="xml_report">
      <type name="xml" package="package0" />
      <value>
        <deadlock>
          <victim-list>
            <victimProcess id="processa0b651088" />
          </victim-list>
          <process-list>
            <process id="processa0b651088" taskpriority="0" logused="10000" waittime="102" schedulerid="6" kpid="956" status="suspended" spid="110" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2023-06-14T10:51:46.287" lastbatchcompleted="2023-06-14T10:51:42.867" lastattention="1900-01-01T00:00:00.867" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" isolationlevel="read committed (2)" xactid="14063109790" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="34" stmtend="2770" sqlhandle="0x02000000b6d8be061bfe6400f596e634d5c83557579b99af0000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 int,@P1 int)SELECT
                              TOP 15 *
                          FROM
                              (
                                  SELECT
                                      ROW_NUMBER () OVER (ORDER BY b.ExpeditedSign DESC,a.id_rf ASC) RowNumber ,
                                a.id_rf AS "id",
                                  FR   </inputbuf>
            </process>
            <process id="processa0b647468" taskpriority="0" logused="10000" waittime="1121286" schedulerid="5" kpid="1132" status="suspended" spid="110" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2023-06-14T10:51:46.287" lastbatchcompleted="2023-06-14T10:51:42.867" lastattention="1900-01-01T00:00:00.867" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" isolationlevel="read committed (2)" xactid="14063109790" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="34" stmtend="2770" sqlhandle="0x02000000b6d8be061bfe6400f596e634d5c83557579b99af0000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 int,@P1 int)SELECT
                              TOP 15 *
                          FROM
                              (
                                  SELECT
                                      ROW_NUMBER () OVER (ORDER BY b.ExpeditedSign DESC,a.id_rf ASC) RowNumber ,
                                a.id_rf AS "id",
                                  FR   </inputbuf>
            </process>
            <process id="process152dce3088" taskpriority="0" logused="10000" waittime="1121288" schedulerid="7" kpid="2460" status="suspended" spid="110" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2023-06-14T10:51:46.287" lastbatchcompleted="2023-06-14T10:51:42.867" lastattention="1900-01-01T00:00:00.867" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" isolationlevel="read committed (2)" xactid="14063109790" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="34" stmtend="2770" sqlhandle="0x02000000b6d8be061bfe6400f596e634d5c83557579b99af0000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 int,@P1 int)SELECT
                              TOP 15 *
                          FROM
                              (
                                  SELECT
                                      ROW_NUMBER () OVER (ORDER BY b.ExpeditedSign DESC,a.id_rf ASC) RowNumber ,
                                a.id_rf AS "id",
                                  FR   </inputbuf>
            </process>
            <process id="process18f49db848" taskpriority="0" logused="10000" waittime="319284" schedulerid="1" kpid="3004" status="suspended" spid="110" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2023-06-14T10:51:46.287" lastbatchcompleted="2023-06-14T10:51:42.867" lastattention="1900-01-01T00:00:00.867" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" isolationlevel="read committed (2)" xactid="14063109790" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="34" stmtend="2770" sqlhandle="0x02000000b6d8be061bfe6400f596e634d5c83557579b99af0000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 int,@P1 int)SELECT
                              TOP 15 *
                          FROM
                              (
                                  SELECT
                                      ROW_NUMBER () OVER (ORDER BY b.ExpeditedSign DESC,a.id_rf ASC) RowNumber ,
                                a.id_rf AS "id",
                                  FR   </inputbuf>
            </process>
            <process id="process1bd8213468" taskpriority="0" logused="0" waitresource="KEY: 5:72057594097369088 (4eae0f7eebdb)" waittime="1121288" ownerId="14063109790" transactionname="SELECT" lasttranstarted="2023-06-14T10:51:46.287" XDES="0x1273201cd0" lockMode="S" schedulerid="4" kpid="1428" status="suspended" spid="110" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2023-06-14T10:51:46.287" lastbatchcompleted="2023-06-14T10:51:42.867" lastattention="1900-01-01T00:00:00.867" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" isolationlevel="read committed (2)" xactid="14063109790" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="34" stmtend="2770" sqlhandle="0x02000000b6d8be061bfe6400f596e634d5c83557579b99af0000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 int,@P1 int)SELECT
                              TOP 15 *
                          FROM
                              (
                                  SELECT
                                      ROW_NUMBER () OVER (ORDER BY b.ExpeditedSign DESC,a.id_rf ASC) RowNumber ,
                                a.id_rf AS "id",
                                  FR   </inputbuf>
            </process>
            <process id="process1a2ae8fc28" taskpriority="0" logused="808" waitresource="PAGE: 5:1:32156047 " waittime="1122916" ownerId="14063110239" transactionname="UPDATE" lasttranstarted="2023-06-14T10:51:46.360" XDES="0x18bd778428" lockMode="IX" schedulerid="2" kpid="2140" status="suspended" spid="131" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-06-14T10:51:46.360" lastbatchcompleted="2023-06-14T10:51:46.327" lastattention="1900-01-01T00:00:00.327" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" loginname="web_user" isolationlevel="read committed (2)" xactid="14063110239" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="122" stmtend="438" sqlhandle="0x020000003bdc0835e95734d79faff8be3785153cd13ea4020000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 nvarchar(4000),@P1 datetime2,@P2 nvarchar(4000),@P3 int)update Edit_Register_Information set EleArrangeUser = @P0,EleArrangeDate = @P1,ElectronicStatus = @P2
    where lngBookDetailID = @P3 and ElectronicStatus ='1'                                   </inputbuf>
            </process>
            <process id="process12f3e93088" taskpriority="0" logused="10000" waittime="1122988" schedulerid="8" kpid="1196" status="suspended" spid="110" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2023-06-14T10:51:46.287" lastbatchcompleted="2023-06-14T10:51:42.867" lastattention="1900-01-01T00:00:00.867" clientapp="Microsoft JDBC Driver for SQL Server" hostname="s053w19-dc01" hostpid="0" loginname="web_user" isolationlevel="read committed (2)" xactid="14063109790" currentdb="5" currentdbname="DataManagementPlatform" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
              <executionStack>
                <frame procname="adhoc" line="1" stmtstart="34" stmtend="2770" sqlhandle="0x02000000b6d8be061bfe6400f596e634d5c83557579b99af0000000000000000000000000000000000000000">
unknown    </frame>
              </executionStack>
              <inputbuf>
(@P0 int,@P1 int)SELECT
                              TOP 15 *
                          FROM
                              (
                                  SELECT
                                      ROW_NUMBER () OVER (ORDER BY b.ExpeditedSign DESC,a.id_rf ASC) RowNumber ,
                                a.id_rf AS "id",
                                  FR   </inputbuf>
            </process>
          </process-list>
          <resource-list>
            <exchangeEvent id="Port1be33a1a00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="5" tid="2" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
              <owner-list>
                <owner id="process152dce3088" />
              </owner-list>
              <waiter-list>
                <waiter id="processa0b651088" />
              </waiter-list>
            </exchangeEvent>
            <exchangeEvent id="Port1be33a1a00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="5" tid="4" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
              <owner-list>
                <owner id="process1bd8213468" />
              </owner-list>
              <waiter-list>
                <waiter id="processa0b647468" />
              </waiter-list>
            </exchangeEvent>
            <exchangeEvent id="Port1be33a1a00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="5" tid="3" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
              <owner-list>
                <owner id="processa0b647468" />
              </owner-list>
              <waiter-list>
                <waiter id="process152dce3088" />
              </waiter-list>
            </exchangeEvent>
            <exchangeEvent id="Port1be33a1a00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="5" tid="1" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
              <owner-list>
                <owner id="processa0b651088" />
              </owner-list>
              <waiter-list>
                <waiter id="process18f49db848" />
              </waiter-list>
            </exchangeEvent>
            <keylock hobtid="72057594097369088" dbid="5" objectname="DataManagementPlatform.dbo.Edit_Register_Information" indexname="PK__Edit_Reg__DD5CCEC7E52812EA" id="lock11a35a2b80" mode="X" associatedObjectId="72057594097369088">
              <owner-list>
                <owner id="process1a2ae8fc28" mode="X" />
              </owner-list>
              <waiter-list>
                <waiter id="process1bd8213468" mode="S" requestType="wait" />
              </waiter-list>
            </keylock>
            <pagelock fileid="1" pageid="32156047" dbid="5" subresource="FULL" objectname="DataManagementPlatform.dbo.Edit_Register_Information" id="lock171c0d8c00" mode="S" associatedObjectId="72057594162053120">
              <owner-list>
                <owner id="process12f3e93088" mode="S" />
              </owner-list>
              <waiter-list>
                <waiter id="process1a2ae8fc28" mode="IX" requestType="wait" />
              </waiter-list>
            </pagelock>
            <exchangeEvent id="Port1be33a1a00" WaitType="e_waitPortOpen" waiterType="Coordinator" nodeId="5" tid="0" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
              <owner-list>
                <owner id="process18f49db848" />
              </owner-list>
              <waiter-list>
                <waiter id="process12f3e93088" />
              </waiter-list>
            </exchangeEvent>
          </resource-list>
        </deadlock>
      </value>
    </data>
  </event>

咬字分开念

GPT:
您的理解基本上是正确的。这是一个典型的死锁场景，其中两个事务分别持有了不同的锁，并且互相等待对方的锁释放，导致了死锁。

解决死锁的方法有很多种，以下是一些常见的方法：

1. 优化查询语句，减少锁的竞争。例如，尽可能使用较低级别的锁（如共享锁），避免使用过多的锁，尽可能缩小锁的范围等。

2. 调整事务隔离级别。如果可能的话，可以将事务隔离级别调整为较低的级别，例如READ COMMITTED，以减少锁的竞争。

3. 优化索引。如果查询语句中使用了索引，可以考虑优化索引以减少锁的竞争。

4. 重试事务。当发生死锁时，可以在代码中捕获死锁异常，然后重试事务。

5. 使用锁超时。可以在代码中设置锁超时时间，当锁超时时，事务会自动回滚，避免死锁的发生。

6. 手动解除死锁。如果死锁发生频率较低，可以手动解除死锁。在SQL Server中，可以使用sp_lock和sp_who2等存储过程来查看锁和进程信息，然后手动杀死进程来解除死锁。

希望以上方法能够帮助您解决死锁问题。

liaoliutao

咬字分开念 发表于 2023-6-14 17:39
GPT:
您的理解基本上是正确的。这是一个典型的死锁场景，其中两个事务分别持有了不同的锁，并且互相等待对 ...

感谢回答，我去找找优化索引如何减少锁的竞争