Deadlock with two update statements

Question

Deadlock with two update statements

Dj463

Ten Centuries

Points: 1181
More actions
April 25, 2017 at 11:50 am

#337493

Hi Guys,
I am getting a deadlocks recently on one of my instance of SQL Server. Below are the two statements involved in the deadlock
1) UPDATE Table1 SET Column1=123 WHERE Column2='XYZ'; (This statement is the victim)
2) UPDATE Table1
SET Column2='XYZ', ModifiedColumn='2017-04-19',Usercolumn='sysuser'
where uniqueIDColumn = '12345'
The type of lock is 'Key Lock' . Some indices are heavily fragmented on Table1, so i rebuild those indices. But still getting the deadlocks.
Any suggestions to prevent this deadlock from happening..?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

This scenario looks incomplete. It looks like UPDATE 1 would be blocked by UPDATE 2, but for this to be a deadlock there must be some other DML in session 1 that is blocking something in session 2. How many rows are each of these statements trying to update? Is one of the sessions leaving an uncommitted transaction open? Here's some general guidelines for trying to prevent deadlocks:
https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

Dj463 Ten Centuries Points: 1181 More actions · Answer 2

Chris Harshman - Tuesday, April 25, 2017 12:44 PM
This scenario looks incomplete. It looks like UPDATE 1 would be blocked by UPDATE 2, but for this to be a deadlock there must be some other DML in session 1 that is blocking something in session 2. How many rows are each of these statements trying to update? Is one of the sessions leaving an uncommitted transaction open? Here's some general guidelines for trying to prevent deadlocks:
https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

Each statement is trying to update only one row. There are no open transactions. The deadlock graph shows only shows these two statements.

Dj463 Ten Centuries Points: 1181 More actions · Answer 3

Below is the Deadlock graph I get:

<deadlock>
<victim-list>
<victimProcess id="process5564bc8"/>
</victim-list>
<process-list>
<process id="process5564bc8" taskpriority="0" logused="0" waitresource="KEY: 5:72057594038321152 (a6748733bdf1)" waittime="1208" ownerId="3225800362" transactionname="UPDATE" lasttranstarted="2017-04-25T06:33:02.820" XDES="0x841f63b0" lockMode="U" schedulerid="3" kpid="13372" status="suspended" spid="270" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-04-25T06:33:02.820" lastbatchcompleted="2017-04-25T06:33:02.817" hostname="APP3047" hostpid="1620" loginname="PRD\sqlmiac_svc" isolationlevel="read committed (2)" xactid="3225800362" currentdb="5" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="" line="2" stmtstart="56" sqlhandle="0x0200000077024227260f3403cb264e6628ca2220ff01505f">
</frame>
<frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
</frame>
</executionStack>
<inputbuf>
(@P1 image)USE [MI_AC];
UPDATE MasterItinerary SET pnr=@P1 WHERE RecordLocator='RKSIMR'; </inputbuf>
</process>
<process id="process55e7708" taskpriority="0" logused="34712" waitresource="KEY: 5:72057594044416000 (e39eb36f8f23)" waittime="1209" ownerId="3225800357" transactionname="UPDATE" lasttranstarted="2017-04-25T06:33:02.820" XDES="0x7a1993950" lockMode="X" schedulerid="8" kpid="4840" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-04-25T06:33:02.730" lastbatchcompleted="2017-04-25T06:33:02.727" hostname="APP3048" hostpid="1652" loginname="PRD\sqlmiac_svc" isolationlevel="read committed (2)" xactid="3225800357" currentdb="5" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="" line="2" stmtstart="34" sqlhandle="0x02000000f274190519deb699ad4164737f51962d87b3e42a">
</frame>
<frame procname="" line="2" stmtstart="34" sqlhandle="0x02000000622ce10fdb891b88e8a754838dfdc79cf4e58224">
</frame>
</executionStack>
<inputbuf>
USE [MI_AC];
UPDATE MasterItinerary SET RecordLocator='RKSIMR',SysUser='ac2uAgency',Agency='0000000',Pcc='A001',Status='A',Modified='2017-04-25T06:33:02',LastDate='2017-05-25',StartDate='2017-05-22',AppModified='',Pos='YOW',PosCountry='CA',Origin='YYZ',Destination='RDU',NumberInParty='1',pnrtext='<MasterItinerary><SourceRefs><source ref="RKSIMR" name="AC"><PNRViewRS><PNRIdentification TicketIssued="Y" QueueRetrieved="N" FareDataExists="Y"><RecordLocator>RKSIMR</RecordLocator><CreationDate>2017-04-04</CreationDate><CreationTime>15:23</CreationTime><Ownership><CRSID>AirCanada</CRSID><Agency>0000000</Agency><PseudoCityCode>A001</PseudoCityCode></Ownership><AgencyData><SignOn>ACO</SignOn><DutyCode>GUEST</DutyCode><IATA>0000000</IATA><AgencyName>ac2u agency</AgencyName></AgencyData><BookingOfficeID>ACMOBILEBOOKING</BookingOfficeID><TicketNumber TktType="E">0142175981839</TicketNumber><TicketInfo Source="AC"><TicketNumber TktType="E">0142175981839</TicketNumber><SegmentElementNumber>1</SegmentElementNumber><Se </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594038321152" dbid="5" objectname="" indexname="" id="locka7d62d780" mode="X" associatedObjectId="72057594038321152">
<owner-list>
<owner id="process55e7708" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5564bc8" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594044416000" dbid="5" objectname="" indexname="" id="lock2f50dac80" mode="U" associatedObjectId="72057594044416000">
<owner-list>
<owner id="process5564bc8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process55e7708" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I think there is more to the second update than you provided in your mockup. Looking at the deadlock graph it looks like the query is cutoff in the inputbuf XML element so no way to really know what it is doing.

Dj463 Ten Centuries Points: 1181 More actions · Answer 5

Lynn Pettis - Tuesday, April 25, 2017 1:45 PM
I think there is more to the second update than you provided in your mockup. Looking at the deadlock graph it looks like the query is cutoff in the inputbuf XML element so no way to really know what it is doing.

The below is the second update statement, it was cutoff in deadlock graph but got it from front end trace file
USE [MI_AC];
UPDATE MasterItinerary SET RecordLocator='RKSIMR',SysUser='ac2uAgency',Agency='0000000',Pcc='A001',Status='A',Modified='2017-04-25T06:33:02',LastDate='2017-05-25',StartDate='2017-05-22',AppModified='',Pos='YOW',PosCountry='CA',Origin='YYZ',Destination='RDU',NumberInParty='1',pnrtext='<MasterItinerary><SourceRefs><source ref="RKSIMR" name="AC"><PNRViewRS><PNRIdentification TicketIssued="Y" QueueRetrieved="N" FareDataExists="Y"><RecordLocator>RKSIMR</RecordLocator><CreationDate>2017-04-04</CreationDate><CreationTime>15:23</CreationTime><Ownership><CRSID>AirCanada</CRSID><Agency>0000000</Agency><PseudoCityCode>A001</PseudoCityCode></Ownership><AgencyData><SignOn>ACO</SignOn><DutyCode>GUEST</DutyCode><IATA>0000000</IATA><AgencyName>ac2u agency</AgencyName></AgencyData><BookingOfficeID>ACMOBILEBOOKING</BookingOfficeID><TicketNumber TktType="E">0142175981839</TicketNumber><TicketInfo Source="AC"><TicketNumber TktType="E">0142175981839</TicketNumber><SegmentElementNumber>1</SegmentElementNumber><SedmnCategory="R">ACMobileBooking-XXXXac3f</Text></History></PNRViewRS></source></SourceRefs></MasterItinerary>'
WHERE uniqueID=9215532;

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

What is the cardinality of the RecordLocator column?
I ask because you're talking to the guy that brought Expedia.com to its knees for a couple of minutes by adding a low cardinality index to a heavily updated table.
The page splits were what got me. The fix was for me to juggle a couple of the first 2 key columns in the index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

It looks like you edited the deadlock graph to take the object and index names out, unfortunately that's made it hard to see what's going on. Can you either edit the XML and put the names back (obsfucated is fine, but I need to be able to see what locks are on what indexes), or attach an unmodified .xdl file.

Also can you please post the definition of the MasterItinerary table and all indexes (CREATE TABLE and CREATE INDEX statements)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dj463 Ten Centuries Points: 1181 More actions · Answer 8

GilaMonster - Wednesday, April 26, 2017 3:46 AM
It looks like you edited the deadlock graph to take the object and index names out, unfortunately that's made it hard to see what's going on. Can you either edit the XML and put the names back (obsfucated is fine, but I need to be able to see what locks are on what indexes), or attach an unmodified .xdl file.
Also can you please post the definition of the MasterItinerary table and all indexes (CREATE TABLE and CREATE INDEX statements)?

Attached are the table, index definition and deadlock graph

Dj463 Ten Centuries Points: 1181 More actions · Answer 9

Jeff Moden - Tuesday, April 25, 2017 9:57 PM
What is the cardinality of the RecordLocator column?
I ask because you're talking to the guy that brought Expedia.com to its knees for a couple of minutes by adding a low cardinality index to a heavily updated table.
The page splits were what got me. The fix was for me to juggle a couple of the first 2 key columns in the index.

Cardinality is pretty good on record locator column.

Dj463 Ten Centuries Points: 1181 More actions · Answer 10

Since rebuilding the indices, after a week the fragmentation of couple of indices is back to 85%-90%. Any suggestions on what i could do..?

aprathour.89 Default port Points: 1495 More actions · Answer 11

Over a time when data modification (insert , update , delete) takes a place indexes becomes fragmented. Indexes needs maintenance time to time.
you may schedule a weekly maintenance job for Index rebuild.

https://www.simple-talk.com/sql/database-administration/rebuilding-indexes-using-the-ssms-database-maintenance-wizard/

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

UniqueID you use in 2nd update is not actually unique.

It's only a part of PK, but there is no unique constraint where it's the only column.

So, when you update a record using it as a filter SQL Server cannot be sure another transaction won't add another record with the same UniqueID to the set.

So it must lock the entire PK from any possible updates.

RecordLocator is not unique either (according to the table design).

So, again, when it's used for a filter in an UPDATE statement it must be locked from any possible change in another transaction.

It must be clear now: you have 2 UPDATE statements which block each other by design.

Fix the PK (leave only UniqueID in it), enforce uniqueness of RecordLocator - and the deadlock will be a thing of the past.

_____________
Code for TallyGenerator