Search code examples
sqlsql-serverdatabase-deadlocks

SQL Deadlock on Select Update

I'm trying to solve a SQL Deadlock issue. Below is a system_health report

<deadlock>
  <victim-list>
    <victimProcess id="process87d03ccf8" />
  </victim-list>
  <process-list>
    <process id="process87d03ccf8" taskpriority="0" logused="0" waitresource="KEY: 7:72057901332627456 (f323ae9efc53)" waittime="1087" ownerId="20788909869" transactionname="SELECT" lasttranstarted="2020-12-03T23:13:56.500" XDES="0x338706d10" lockMode="S" schedulerid="6" kpid="38240" status="suspended" spid="103" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-12-03T23:13:56.490" lastbatchcompleted="2020-12-03T23:13:56.490" lastattention="1900-01-01T00:00:00.490" clientapp=".Net SqlClient Data Provider" hostname="ID45846" hostpid="58020" loginname="ubuser" isolationlevel="read committed (2)" xactid="20788909869" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="x.dbo.OrderData_GetByOrderID" line="6" stmtstart="124" sqlhandle="0x03000700cddb7412e6afdc00a0a9000001000000000000000000000000000000000000000000000000000000"></frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 7 Object Id = 309648333]   </inputbuf>
    </process>
    <process id="process32f127868" taskpriority="0" logused="112" waitresource="KEY: 7:72057901332692992 (004616e83cc3)" waittime="1087" ownerId="20788909868" transactionname="UPDATE" lasttranstarted="2020-12-03T23:13:56.500" XDES="0x81bad63a8" lockMode="X" schedulerid="15" kpid="66292" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-03T23:13:56.490" lastbatchcompleted="2020-12-03T23:13:56.490" lastattention="1900-01-01T00:00:00.490" clientapp=".Net SqlClient Data Provider" hostname="ID45846" hostpid="58020" loginname="ubuser" isolationlevel="read committed (2)" xactid="20788909868" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="x.dbo.OrderData_Set" line="36" stmtstart="1662" sqlhandle="0x030007002608c15b50af010156ac000001000000000000000000000000000000000000000000000000000000"></frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 7 Object Id = 1539377190]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057901332627456" dbid="7" objectname="unidbmaster.dbo.OrderData" indexname="PK_OrderData" id="lock494546200" mode="X" associatedObjectId="72057901332627456">
      <owner-list>
        <owner id="process32f127868" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process87d03ccf8" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057901332692992" dbid="7" objectname="unidbmaster.dbo.OrderData" indexname="IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue" id="lock73ecc1b80" mode="S" associatedObjectId="72057901332692992">
      <owner-list>
        <owner id="process87d03ccf8" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process32f127868" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Based on this is the following correct:

  • The Update process has a lock on PK_OrderData and the Select process wants it?
  • The Select process has a lock on IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue and the Update process wants it?
  • The Select process is selecting the same row that the Update process wants to Update

I'm assuming that one way to solve this issue would be to delete the IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue index, however it is used elsewhere.

So my question is, what options are there to fix this issue? I know I could add a directive to the Select statement to say 'dirty data is ok' however that feels wrong...

Solution

  • The deadlock between the select and the update occurs because the select uses the index which finds the record(s) fastest first, then uses the base table (clustered index) to retrieve the rest of the information since you are selecting all columns.

    The update on the other hand is updating the base table (clustered index) first, which then causes all other indexes which reference the columns being updated to be updated after that.

    As you can see the order of access of the indexes is reversed, and because both statements are happening at exactly the same time they deadlock.

    select * from table where fk = @Id

    One possible solution is another index just on the foreign key column. Assuming this column isn't part of the update it won't be affected by the update.

    Another possible solution is to restrict the columns you are selecting to just those you need (select * is nearly always a bad idea) and to create a covering index of all the columns you are selecting as well as the foreign key. In this way the select will only hit a single index.