Solved

How to reduce SQL deadlock caused by update query?

Posted on 2011-09-20
8
522 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

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 article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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