Solved

How to reduce SQL deadlock caused by update query?

Posted on 2011-09-20
8
497 Views
Last Modified: 2012-05-12
I have an application which is running multiple sql statements simultaneously in different threads, causing various deadlocks which all seem to come from one table. For example the update statement below:

update printjobprinter status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine where printjobid=@printjobid and printerid=@printerid

produce the following deadlock:

- <EVENT_INSTANCE>
  <EventType>DEADLOCK_GRAPH</EventType>
  <PostTime>2011-09-15T14:48:43.070</PostTime>
  <SPID>13</SPID>
- <TextData>
- <deadlock-list>
- <deadlock victim="process84bc48">
- <process-list>
- <process id="process84bc48" taskpriority="0" logused="0" waitresource="KEY: 18:72057594041073664 (bd00a4b6a331)" waittime="3562" ownerId="7232093" transactionname="user_transaction" lasttranstarted="2011-09-15T14:48:39.493" XDES="0x106d4fa0" lockMode="U" schedulerid="1" kpid="3520" status="suspended" spid="56" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-09-15T14:48:39.513" lastbatchcompleted="2011-09-15T14:48:39.513" clientapp=".Net SqlClient Data Provider" hostname="SOFT012" hostpid="4884" loginname="sa" isolationlevel="read committed (2)" xactid="7232093" currentdb="18" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
  <frame procname="adhoc" line="1" stmtstart="246" sqlhandle="0x02000000b0303e1e114a10ecdffdff680f7cf3b30deeb475">update printjobprinter set status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine where printjobid=@printjobid and printerid=@printerid</frame>
  <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>
  </executionStack>
  <inputbuf>(@processedbymachine nvarchar(255),@status int,@statusdate datetime,@printjobid int,@printerid int,@printedtoprinterid int)update printjobprinter set status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine where printjobid=@printjobid and printerid=@printerid</inputbuf>
  </process>
- <process id="processe634d48" taskpriority="0" logused="196" waitresource="KEY: 18:72057594041073664 (c400b2599427)" waittime="3562" ownerId="7232100" transactionname="UPDATE" lasttranstarted="2011-09-15T14:48:39.500" XDES="0x9789010" lockMode="U" schedulerid="1" kpid="588" status="suspended" spid="61" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-09-15T14:48:39.500" lastbatchcompleted="2011-09-15T14:48:39.497" clientapp=".Net SqlClient Data Provider" hostname="SOFT012" hostpid="4884" loginname="sa" isolationlevel="read committed (2)" xactid="7232100" currentdb="18" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
  <frame procname="adhoc" line="1" stmtstart="246" sqlhandle="0x02000000b0303e1e114a10ecdffdff680f7cf3b30deeb475">update printjobprinter set status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine where printjobid=@printjobid and printerid=@printerid</frame>
  <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>
  </executionStack>
  <inputbuf>(@processedbymachine nvarchar(255),@status int,@statusdate datetime,@printjobid int,@printerid int,@printedtoprinterid int)update printjobprinter set status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine where printjobid=@printjobid and printerid=@printerid</inputbuf>
  </process>
  </process-list>
- <resource-list>
- <keylock hobtid="72057594041073664" dbid="18" objectname="TestFisItemTypes.dbo.PrintJobPrinter" indexname="PK_PrintJobPrinter" id="lock5c0f7c0" mode="X" associatedObjectId="72057594041073664">
- <owner-list>
  <owner id="process84bc48" mode="X" />
  </owner-list>
- <waiter-list>
  <waiter id="processe634d48" mode="U" requestType="wait" />
  </waiter-list>
  </keylock>
- <keylock hobtid="72057594041073664" dbid="18" objectname="TestFisItemTypes.dbo.PrintJobPrinter" indexname="PK_PrintJobPrinter" id="lock601d400" mode="X" associatedObjectId="72057594041073664">
- <owner-list>
  <owner id="processe634d48" mode="X" />
  </owner-list>
- <waiter-list>
  <waiter id="process84bc48" mode="U" requestType="wait" />
  </waiter-list>
  </keylock>
  </resource-list>
  </deadlock>
  </deadlock-list>
  </TextData>
  <TransactionID />
  <LoginName>sa</LoginName>
  <StartTime>2011-09-15T14:48:43.070</StartTime>
  <ServerName>SQA19</ServerName>
  <LoginSid>AQ==</LoginSid>
  <EventSequence>864138</EventSequence>
  <IsSystem>1</IsSystem>
  <SessionLoginName />
  </EVENT_INSTANCE>


Can anyone explain why they are causing deadlocks, or give any suggestions as to how to prevent them? Very appreciated!!!

0
Comment
Question by:jssong2000
8 Comments
 
LVL 5

Expert Comment

by:zvytas
ID: 36567647
Please try the following:

update printjobprinter WITH(ROWLOCK)
status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine
where printjobid=@printjobid and printerid=@printerid

Query hint WITH(ROWLOCK) explicitly sets the locking mode. By default SQL Server chooses the most appropriate locking mode, which could be on a table, page or row level. From what you've said it seems that SQL Server is not using row locks, so adding the query hint might help.
0
 

Author Comment

by:jssong2000
ID: 36567781
I got more deadlock using rowlock.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36567972
Deadlock occurs when two processes collide by locking multiple resources in opposite orders.  Usually, two different procedures are involved, but not always.

The classic ways to avoid deadlock are:

1. use locking hints to minimize unnecessary locking, for example on select statements use nolock where dirty reads are not an issue.  on update statements, rowlock can sometimes reduce the scope of locks.

2. Use the least lock-intensive transaction isolation level that you can tolerate.  For example, if you can live with dirty reads, then you may be able to use read uncommitted.  Before manipulating the lock levels, DO UNDERSTAND the implications and side effects as you expose yourself to data corruption.

 
3. make sure all procedures issue locks in the same order.  That means, tables must be in the same order (alphabetic by table name, for example).   Rarely, the conflict can be within a single table.   For example, task A updates printjob 1 then updates printjob 2 whilst task B updates printjob 2 then updates printjob 1.  In that case, you need to be sure the rows are updated in the same order.

A more simplistic approach is to put a common blocking statement at the beginning of the transactions that are deadlocking. This is safe and easy, but single-threads the involved processes so that only one will run at a time.  


0
 
LVL 10

Expert Comment

by:sqlservr
ID: 36572011
You can also set deadlock prority

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }

<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }


or develop your queries wth
no_lock option
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jssong2000
ID: 36580931
I found another query from code, it should be the culprit:

select top 1 * from printjobprinter with (xlock,rowlock) where status = 0 and printerid in (" + printeridlist + ")  order by id asc
This query will generate locks.

The following update query should get this lock to execute. How to fix it to reduce deadlock and avoid dirty read??? Appreciated!!!
0
 

Author Comment

by:jssong2000
ID: 36580935
The update query is:
update printjobprinter status=@status, statusdate=@statusdate, printedtoprinterid=@printedtoprinterid, processedbymachine=@processedbymachine where printjobid=@printjobid and printerid=@printerid

0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36581520
The select with xlock statement is certainly suspicious. It potentially has a large locking footprint.  You can certainly reduce it by removing the xlock hint.  However, I caution you, that the xlock hint was put there intentionaly, so removing may do more harm than good.  In other words, you may fix the deadlock at the expense of lost updates or other insideous data problems.


Before advising further, I wish to know what the printeridlist variable contains. whether your code contains BEGIN TRANSACTION statements, where the Select with xlock statement is in relation to the BEGIN TRANSACTION and the UPdate statement.






0
 

Author Closing Comment

by:jssong2000
ID: 36963801
very helpful thank you.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now