Solved

How to reduce SQL deadlock caused by update query?

Posted on 2011-09-20
8
502 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore 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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

21 Experts available now in Live!

Get 1:1 Help Now