title: Hibernate ORM T-SQL Queries causing Deadlocks in SQL Server 2016
body: <p>I have a SQL Server 2016 database and we have been using the Hibernate Java (ORM) as Front End to access the database.</p>
  
  <p>The below scenario is causing the deadlock: one of the Transaction is doing the select operations involving many tables in joins as below:</p>
  
  <pre><code>select * 
  from table 1 
  inner join table2_ on table1PK = table2FK 
  left outer join table3 on table1PK = table3FK 
  left outer join table4_ on table4FK = table3PK 
  ... 
  where Table1PK = '1234' and table3.Status &lt;&gt; 'NA' 
  </code></pre>
  
  <p>Another transaction is doing the <code>UPDATE</code> on any of the table involved in the above joins</p>
  
  <pre><code>Update Table4 
  set columnA = 'XYZ' 
  where Table4PK = 'ABC'
  </code></pre>
  
  <p>Frequently getting the deadlocks between this 2 Transactions.</p>
  
  <p>Below were the analysis gathered:</p>
  
  <ol>
  <li>All the tables involved in the <code>Select</code> statement joins are on the PK FK relationship</li>
  <li>All PK &amp; FK Columns have indexes (PK clustered &amp; FK nonclustered)</li>
  <li>Since the queries are generated from the ORM Hibernate hence it retrieves all the columns in the <code>Select</code> statement</li>
  <li>Execution Plan does a nonclustered index seek followed by the Key Lookup for all the tables</li>
  <li>Select statement used the Shared S locks and Update statements are using the Exclusive X locks </li>
  <li>Page Level Locks are disabled for the above tables</li>
  <li>Statistics and Indexes are up to date / Frequently updated</li>
  </ol>
  
  <p>Can't use the READ Committed Snapshot and Read Uncommitted isolation Levels due to the Application Logic.</p>
  
  <p>Any suggestions to avoid the deadlocks for the above scenario.</p>
  
  <p>Deadlock Trace:</p>
  
  <pre><code>    &lt;deadlock&gt;
       &lt;victim-list&gt;
        &lt;victimProcess id="process2ba81fa8c8" /&gt;
       &lt;/victim-list&gt;
       &lt;process-list&gt;
        &lt;process id="process2ba81fa8c8" taskpriority="0" logused="0"                     
      waitresource="KEY: 7:72057597045899264 (5a800aed3519)" waittime="2537" ownerId="1798795" transactionname="SELECT" lasttranstarted="2019-12-12T13:17:02.820" XDES="0x2841883a40" lockMode="S" schedulerid="6" kpid="10140" status="suspended" spid="429" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-12-12T13:17:02.823" lastbatchcompleted="2019-12-12T13:17:02.820" lastattention="1900-01-01T00:00:00.820" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="1798795" currentdb="" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt;
           &lt;executionStack&gt;
            &lt;frame procname="adhoc" line="1" stmtstart="24" stmtend="33928" sqlhandle="0x02000000a700b227b6df842918a30ca7f4b4cd5ff967f9810000000000000000000000000000000000000000"&gt;
        unknown    &lt;/frame&gt;
            &lt;frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"&gt;
        unknown    &lt;/frame&gt;
           &lt;/executionStack&gt;
           &lt;inputbuf&gt;
  (@P0 bigint)select ... from ABC inner join XYZ  &lt;/inputbuf&gt;
          &lt;/process&gt;
          &lt;process id="process2ba81f8108" taskpriority="0" logused="5812788" waitresource="KEY: 7:72057596983508992 (c750150781e8)" waittime="2306" ownerId="1795488" transactionname="implicit_transaction" lasttranstarted="2019-12-12T13:17:02.180" XDES="0x2bb290d478" lockMode="X" schedulerid="5" kpid="9876" status="suspended" spid="382" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-12-12T13:17:03.053" lastbatchcompleted="2019-12-12T13:17:03.053" lastattention="1900-01-01T00:00:00.053" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="1795488" currentdb="" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"&gt;
           &lt;executionStack&gt;
            &lt;frame procname="adhoc" line="1" stmtstart="1462" stmtend="3664" sqlhandle="0x020000007bd3bd2f098d8e394643ad11df2b1027b5b67fde0000000000000000000000000000000000000000"&gt;
  unknown    &lt;/frame&gt;
           &lt;/executionStack&gt;
                 &lt;inputbuf&gt;(@P0 bigint,@P1 datetime2,@P2 datetime2,@P3 varchar(8000),@P4 varchar(8000),@P5 datetime2,)update [dbo].[XYZ] set []=@P0, []=@P1, []=@P2, []=@P3, []=@P4,&lt;/inputbuf&gt;
                &lt;/process&gt;
               &lt;/process-list&gt;
               &lt;resource-list&gt;
          &lt;keylock hobtid="72057597045899264" dbid="7" objectname="DB.dbo.ABC" indexname="IX_ABC_XYZFK" id="lock2afc8ca500" mode="X" associatedObjectId="72057597045899264"&gt;
           &lt;owner-list&gt;
            &lt;owner id="process2ba81f8108" mode="X" /&gt;
           &lt;/owner-list&gt;
           &lt;waiter-list&gt;
            &lt;waiter id="process2ba81fa8c8" mode="S" requestType="wait" /&gt;
           &lt;/waiter-list&gt;
          &lt;/keylock&gt;
          &lt;keylock hobtid="72057596983508992" dbid="7" objectname="DB.dbo.XYZ" indexname="PK_XYZ" id="lock2afd023080" mode="U" associatedObjectId="72057596983508992"&gt;
           &lt;owner-list&gt;
            &lt;owner id="process2ba81fa8c8" mode="S" /&gt;
           &lt;/owner-list&gt;
           &lt;waiter-list&gt;
            &lt;waiter id="process2ba81f8108" mode="X" requestType="convert" /&gt;
           &lt;/waiter-list&gt;
          &lt;/keylock&gt;
         &lt;/resource-list&gt;
        &lt;/deadlock&gt;
  </code></pre>