Databases
StackOverflow2013
Postsdbo
POLock partitioning deadlocks
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLock partitioning deadlocks
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload