Deadlock during Transaction log growth.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Deadlock during Transaction log growth.

Author

Topic

shishirkhandekar
Starting Member

23 Posts

Posted - 2013-08-06 : 17:16:53

Hello, I am just trying to understand SQL Server behavior here so any help would be appreciated.

Knowns (sorry can't post the code here for obvious reasons, I have only replaced the server names and user names in the xml, everything else is as is)
0) We are running SQL Server 2008 R2 SP2
1) Two users were updating two different rows in the same table. I derived this from the XML.
2) The procedure is the same and the statement is the same. It was an UPDATE statement with two inner joins and one left join.

Questions -
Why is the log used different, if the procedure is the same and the statement on the buffer is the same. I would expect similar log use pattern assuming that the same kind of data modifications are being made (except for one transaction running longer than the other, which I don't know at this point)

If transaction log was growing, I can expect blocking but why a deadlock. The symptom is only seen during Tx Log growths and when we pre-grow the log, no deadlocks with the same amount of load on the server.

Here's the XML
<deadlock>
<victim-list>
<victimProcess id="processc10b7b4c8"/>
</victim-list>
<process-list>
<process id="processc10b7b4c8" taskpriority="0" logused="5456" waitresource="KEY: 15:72057594058178560 (b817bcbca4ed)" waittime="854" ownerId="583339834" transactionname="user_transaction" lasttranstarted="2013-08-05T15:40:29.930" XDES="0x146ac4e80" lockMode="U" schedulerid="2" kpid="20280" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-08-05T15:40:30.047" lastbatchcompleted="2013-08-05T15:40:29.980" clientapp=".Net SqlClient Data Provider" hostname="SERVERA" hostpid="55764" loginname="mydomain\account1" isolationlevel="read committed (2)" xactid="583339834" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="1034" stmtstart="71968" stmtend="73006" sqlhandle="0x03000f00a10fa551eacf6a01a3a100000100000000000000">
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 15 Object Id = 1369771937] </inputbuf>
</process>
<process id="process631d048" taskpriority="0" logused="46636" waitresource="KEY: 15:72057594058178560 (fbde43d6518d)" waittime="1" ownerId="583139955" transactionguid="0x2e08010d57ba2340ab93515c8fbf29dc" transactionname="user_transaction" lasttranstarted="2013-08-05T15:40:21.020" XDES="0xde3743b0" lockMode="U" schedulerid="12" kpid="19916" status="suspended" spid="99" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-08-05T15:40:31.040" lastbatchcompleted="2013-08-05T15:40:30.197" clientapp=".Net SqlClient Data Provider" hostname="SERVERB" hostpid="32176" loginname="mydomain\account1" isolationlevel="read committed (2)" xactid="583139955" currentdb="15" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="" line="1034" stmtstart="71968" stmtend="73006" sqlhandle="0x03000f00a10fa551eacf6a01a3a100000100000000000000">
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 15 Object Id = 1369771937] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594058178560" dbid="15" objectname="" indexname="" id="lock60d752200" mode="X" associatedObjectId="72057594058178560">
<owner-list>
<owner id="process631d048" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="processc10b7b4c8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594058178560" dbid="15" objectname="" indexname="" id="lockb432b0300" mode="U" associatedObjectId="72057594058178560">
<owner-list>
<owner id="processc10b7b4c8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process631d048" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-06 : 17:40:37

What is your autogrowth set to?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shishirkhandekar
Starting Member

23 Posts

Posted - 2013-08-07 : 11:24:36

Hello Tara,

We usually set it to 50 GB size with 2 GB auto growth just to get to certain number and size of VLFs. In this case it was a deployment error and the initial size was set to 100 MB with 10% auto growth. The deadlocks occurred only when log was growing.

Thanks
Shishir Khandekar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-07 : 12:53:42

I would fix the autogrowth and then see if the problem repeats.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shishirkhandekar
Starting Member

23 Posts

Posted - 2013-08-07 : 16:48:15

Yes, that fixed the deadlocks. But I am still curious how to support the theory given the XML above.

Thanks
Shishir Khandekar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources