SQL Server deadlock between transactional INSERTs and single SELECT

Question

The deadlock occurs between SELECT...JOIN query that is running without any explicit transaction and a transaction with INSERTs in it.

INSERTs have the order we cannot change (insert Person, then insert Employment). It seems however the tables used in the SELECT clause are locked in opposite order (Employment then Person).

Unfortunately it is not so easy to change the SELECT (i.e. the order of tables involved) because it is generated by a complex legacy engine.

Is there any way to lock tables used in a SELECT...JOIN query in the order we need?

I do not want to go to a complex solution like Snapshot isolation. I do not want to use NOLOCK in that SELECT either.

Deadlock graph xml:

<deadlock>
  <victim-list>
    <victimProcess id="process8bfdd2188" />
  </victim-list>
  <process-list>
    <process id="process8bfdd2188" taskpriority="0" logused="0" waitresource="PAGE: 223:1:769193 " waittime="4340" ownerId="17504759932" transactionname="SELECT" lasttranstarted="2016-04-02T03:03:25.527" XDES="0x63f8f1cc0" lockMode="S" schedulerid="2" kpid="27920" status="suspended" spid="954" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2016-04-02T03:03:25.523" lastbatchcompleted="2016-04-02T03:03:25.523" lastattention="1900-01-01T00:00:00.523" clientapp="Security Services" hostname="A-PRI0010IIS" hostpid="29908" loginname="SWE2078262_EOUser" isolationlevel="read committed (2)" xactid="17504759932" currentdb="223" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="58" sqlhandle="0x020000000b6f1301768ce09dcc48ead49292f31b2305871b0000000000000000000000000000000000000000">
select distinct em.EmploymentId.. from employment.Employment as em left join employment.Person as p on ...  </frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@dp1 datetime,@dp2 datetime)select em.EmploymentId from employment.Employment as em left join employment.Person as p on ...</inputbuf>
    </process>
    <process id="process8e5957498" taskpriority="0" logused="120220" waitresource="OBJECT: 223:1938105945:0 " waittime="4207" ownerId="17504759159" transactionname="user_transaction" lasttranstarted="2016-04-02T03:03:25.333" XDES="0x254d02d08" lockMode="IX" schedulerid="1" kpid="23832" status="suspended" spid="924" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-04-02T03:03:25.703" lastbatchcompleted="2016-04-02T03:03:25.703" lastattention="1900-01-01T00:00:00.703" clientapp="Security Services" hostname="A-PRI0010IIS" hostpid="29908" loginname="SWE2078262_EOUser" isolationlevel="read uncommitted (1)" xactid="17504759159" currentdb="223" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="360" sqlhandle="0x020000002d54221bf934de11467adec42e4bb5413da44e120000000000000000000000000000000000000000">
insert [employment].[Employment]([EmploymentId], [CompanyId], [PersonId], ...</frame>
        <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
(@0 uniqueidentifier,@1 uniqueidentifier,@2 uniqueidentifier,@3 varchar(30),@4 datetime2(7),@5 datetime2(7),@6 nvarchar(50),@7 decimal(18,2),@8 nvarchar(50),@9 bit,@10 varchar(15))insert [employment].[Employment]([EmploymentId], [CompanyId], [PersonId]...
   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="769193" dbid="223" subresource="FULL" objectname="SWE2078262_Core.employment.Person" id="lock1f1566080" mode="IX" associatedObjectId="72057594248167424">
      <owner-list>
        <owner id="process8e5957498" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process8bfdd2188" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
    <objectlock lockPartition="0" objid="1938105945" subresource="FULL" dbid="223" objectname="SWE2078262_Core.employment.Employment" id="lock2341db7480" mode="S" associatedObjectId="1938105945">
      <owner-list>
        <owner id="process8bfdd2188" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="process8e5957498" mode="IX" requestType="wait" />
      </waiter-list>
    </objectlock>
  </resource-list>
</deadlock>

"i.e. the order of tables involved" - which is generally irrelevant anyway, unless `OPTION FORCE ORDER` has been applied to the `SELECT`. It's the job of the optimizer to determine what order tables are accessed in and there's no guarantee it matches the original textual order from the query. At a bit of a loss with how to help you here though, since you don't seem to want to change *any* of the code. — Damien_The_Unbeliever, Apr 11 '16 at 10:11
Damien_The_Unbeliever, actually I can change the code for `SELECT` but I did not want to do changes in logic of that code generator. So, to add i.e. `FORCE ORDER` is possible but the order itself is defined by code logic and I do not want to change that logic. — Vlad, Apr 18 '16 at 09:07

score 0 · Answer 1 · answered Apr 11 '16 at 11:22

You still probably want to use one of these:

Optimistic concurrency, i.e. Snapshot isolation mode for your db and the select query.
Repeat deadlocked transaction. I mean you need to catch the SqlException in your C# code (I assume you use C#) and repeat the query (it can be insert or select).

SQL Server deadlock between transactional INSERTs and single SELECT

1 Answers1