Solved

How to reduce SQL deadlock caused by update query?

Posted on 2011-09-20
8
530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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