Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to reduce SQL deadlock caused by update query?

Posted on 2011-09-20
8
Medium Priority
?
544 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 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