title: Lock partitioning deadlocks
body: <p>I have a table with around 3.5 million rows. Lock partitioning [1] is enabled on the database. The table gets a lot of inserts during the day, and I'm getting a lot of deadlocks on lock partitions. These types of deadlocks are described nicely at <a href="http://sqlindian.com/2012/07/07/deadlocks-involving-lock-partitions/" rel="nofollow">http://sqlindian.com/2012/07/07/deadlocks-involving-lock-partitions/</a> but there the author says that these types of deadlocks are extremely rare. In our case they don't seem rare at all!</p>
  
  <p>I could disable lock partitioning with trace flag 1229, but that's not recommended. Does anyone have some advice about how to avoid these types of deadlocks, or how I could analyze the situation further to see why we are getting so many of these "rare" type of deadlocks?</p>
  
  <p>[1] <a href="http://msdn.microsoft.com/en-us/library/ms187504(v=sql.105).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms187504(v=sql.105).aspx</a></p>
  
  <p>UPDATE: Add example deadlock graph</p>
  
  <pre><code>&lt;deadlock&gt;
    &lt;victim-list&gt;
      &lt;victimProcess id="process5004748" /&gt;
    &lt;/victim-list&gt;
    &lt;process-list&gt;
      &lt;process id="process5004748" taskpriority="0" logused="0" waitresource="OBJECT: 5:1423344135:0 " waittime="3008" ownerId="2379819613" transactionname="user_transaction" lasttranstarted="2013-03-14T09:28:55.803" XDES="0x77ab8f950" lockMode="X" schedulerid="11" kpid="5416" status="suspended" spid="507" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-14T09:28:55.817" lastbatchcompleted="2013-03-14T09:28:55.807" clientapp=".Net SqlClient Data Provider" hostname="ExampleHost" hostpid="8664" loginname="ExampleUser" isolationlevel="read uncommitted (1)" xactid="2379819613" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt;
        &lt;executionStack&gt;
          &lt;frame procname="" line="1" stmtstart="616" stmtend="1504" sqlhandle="0x020000002468011b993c824e2e0ce3fd2783a30e8e591641" /&gt;
          &lt;frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" /&gt;
        &lt;/executionStack&gt;
        &lt;inputbuf&gt;
  (@p0 datetime,@p1 bigint ...) INSERT INTO tblExample (Column1, Column2, ...); select SCOPE_IDENTITY()   
        &lt;/inputbuf&gt;
      &lt;/process&gt;
      &lt;process id="processd4a988" taskpriority="0" logused="0" waitresource="OBJECT: 5:1423344135:10 " waittime="3008" ownerId="2379819595" transactionname="user_transaction" lasttranstarted="2013-03-14T09:28:55.663" XDES="0x2fe4323b0" lockMode="X" schedulerid="2" kpid="6756" status="suspended" spid="473" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-14T09:28:55.677" lastbatchcompleted="2013-03-14T09:28:55.667" clientapp=".Net SqlClient Data Provider" hostname="ExampleHost" hostpid="8664" loginname="ExampleUser" isolationlevel="read uncommitted (1)" xactid="2379819595" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt;
        &lt;executionStack&gt;
          &lt;frame procname="" line="1" stmtstart="616" stmtend="1504" sqlhandle="0x020000002468011b993c824e2e0ce3fd2783a30e8e591641" /&gt;
          &lt;frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" /&gt;
        &lt;/executionStack&gt;
        &lt;inputbuf&gt;
          (@p0 datetime,@p1 bigint ...) INSERT INTO tblExample (Column1, Column2, ...); select SCOPE_IDENTITY()
        &lt;/process&gt;
    &lt;/process-list&gt;
    &lt;resource-list&gt;
      &lt;objectlock lockPartition="0" objid="1423344135" subresource="FULL" dbid="5" objectname="" id="lock5d745ae00" mode="X" associatedObjectId="1423344135"&gt;
        &lt;owner-list&gt;
          &lt;owner id="processd4a988" mode="X" /&gt;
        &lt;/owner-list&gt;
        &lt;waiter-list&gt;
          &lt;waiter id="process5004748" mode="X" requestType="wait" /&gt;
        &lt;/waiter-list&gt;
      &lt;/objectlock&gt;
      &lt;objectlock lockPartition="10" objid="1423344135" subresource="FULL" dbid="5" objectname="" id="lock55da8ea00" mode="IX" associatedObjectId="1423344135"&gt;
        &lt;owner-list&gt;
          &lt;owner id="process5004748" mode="IX" /&gt;
        &lt;/owner-list&gt;
        &lt;waiter-list&gt;
          &lt;waiter id="processd4a988" mode="X" requestType="wait" /&gt;
        &lt;/waiter-list&gt;
      &lt;/objectlock&gt;
    &lt;/resource-list&gt;
  &lt;/deadlock&gt;
  </code></pre>
  
  <p>UPDATE 2: Adding INSERT generated by NHibernate</p>
  
  <pre><code>begin transaction with isolation level: ReadUncommitted
  
  INSERT INTO tblExample
              (Column1,
               Column2,
               Column2,
               Column3,
               Column4,
               Column5,
               Column6,
               Column7,
               Column8,
               Column9,
               Column10,
               Column11,
               Column12,
               Column13,
               Column14,
               Column15,
               Column16,
               Column17,
               Column18,
               Column19,
               Column20,
               Column21)
  VALUES      ('2013-03-14T12:47:26.00' /* @p0 */,
               NULL /* @p1 */,
               75 /* @p2 */,
               'Test Text with some characters' /* @p3 */,
               'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22' /* @p4 */,
               2130706433 /* @p5 */,
               NULL /* @p6 */,
               NULL /* @p7 */,
               0 /* @p8 */,
               'Test Title' /* @p9 */,
               '11223344' /* @p10 */,
               0 /* @p11 */,
               '2013-03-14T12:47:26.00' /* @p12 */,
               0 /* @p13 */,
               '2013-03-14T12:47:26.00' /* @p14 */,
               'en' /* @p15 */,
               '2013-03-14T12:47:26.00' /* @p16 */,
               0 /* @p17 */,
               'SomeName' /* @p18 */,
               NULL /* @p19 */,
               917278 /* @p20 */,
               2805683 /* @p21 */);
  
  
  
  select SCOPE_IDENTITY()
  
  commit transaction
  </code></pre>