CODEWITHSUNDEEP
sqlsql-serverdatabase-deadlocks
markpirvine
markpirvine

Reputation: 1592

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:

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...

Upvotes: 0

Views: 1043

Answers (2)

Dale K
Dale K

Reputation: 27201

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.

Upvotes: 1

seanb
seanb

Reputation: 6685

Broadly speaking, deadlocks like this occur when two transactions have locks on tables and neither can proceed because each transaction is locking the tables needed by the other.

I presume that the SELECT is actually part of transaction (rather than just a stand-alone query). If it was just a stand-alone query, and the UPDATE transaction had started, then the SELECT query would have just waited until the UPDATE had finished. However, something in the same transaction as the SELECT is locking something that the UPDATE then needs.

Without seeing the queries, your broad options are

  • Ask yourself whether transactions are necessary? Can some or all of the statements be moved out of the transactions?
  • For both the transactions, UPDATE tables in the same order (e.g., both transactions update table A first, then table B second - rather than one doing A then B and the other doing B then A).
    • ... and if possible, update each table only once

Brent Ozar has a great video on deadlocks - I highly recommend it - even though it's almost an hour, it's a great demonstration and how to fix it. Indeed, my answers here are pretty much based on his video.

Upvotes: 0

Related Questions