Deadlock Transactions -- help needed

Question

Post reply

Deadlock Transactions -- help needed

tfeuz

Ten Centuries

Points: 1196
More actions
December 27, 2010 at 2:58 pm

#388509

Please see the XML deadlock description that I have -- I seem to be getting this deadlock quite frequently.
A few notes:
- Neither the 'select' or the 'update' queries ar run from within a transaction (the is not begintrans/committrans functionality in these areas)
- The table that is being used for the update statement has a clustered index on LBMXDocumentId
- When I run the queries in SQL 2008 and display the execution plans --- there are only 'seeks' being done (no scans)
I have been chasing this around for days and i cannot seem to track it down -- although I can repeat it quite easily with my test data.
Any help would be appreciated.
<deadlock-list>
<deadlock>
<victim-list>
<victimProcess id="process80308748" />
</victim-list>
<process-list>
<process id="process80308748" taskpriority="0" logused="0" waitresource="KEY: 6:72057595275313152 (07006e48e6bb)" waittime="508" ownerId="7777685" transactionname="SELECT" lasttranstarted="2010-12-27T16:32:06.450" XDES="0xec4f3070" lockMode="S" schedulerid="1" kpid="2716" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2010-12-27T16:32:06.013" lastbatchcompleted="2010-12-27T16:32:05.967" lastattention="2010-12-27T16:31:33.677" clientapp="LBMX_DJ_Poster" hostname="NEWDELL" hostpid="6260" loginname="LBMXSQLC\Administrator" isolationlevel="read committed (2)" xactid="7777685" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="2" sqlhandle="0x020000009eb6962bf21e13740c7fca4daebf4abdadbc50eb" />
</executionStack>
<inputbuf>
select a.invoicekey,a.BuyingGroupInvoiceNumber,a.lbmxdocumentid,a.invoicenumber,SenderInterchangeId,ReceiverInterchangeId from isa_section f,gs_section e ,st_section d,invoiceheader a,filestatistics s Where f.ISAKey = e.ISAKey and s.StatisticsKey = f.StatisticsKey and e.gskey = d.gskey and d.stkey = a.stkey and a.ReceivedMailboxAck = 0 and lastprocessid >= 4000 and a.status <> 0 and SenderInterchangeId in ( select z.lbmxsenderid from config_tradingpartner z Where f.SenderInterchangeId = z.lbmxsenderid and f.ReceiverInterchangeId = z.lbmxreceiverid and generatepdf = 1 and documenttype = '810' ) and sentmailboxfile is null and OriginalFileName LIKE 'E:\TradingPartners\Timbrmart Winnipeg\out\Mailbox\20101204204704_2010_12_04_20_45_572010_12_04_18_29_54_10.xml' </inputbuf>
</process>
<process id="process396fb88" taskpriority="0" logused="2308" waitresource="KEY: 6:72057595283898368 (090409633a0b)" waittime="508" ownerId="7777666" transactionname="UPDATE" lasttranstarted="2010-12-27T16:32:06.433" XDES="0xec4f23b0" lockMode="X" schedulerid="1" kpid="4500" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-12-27T16:32:06.433" lastbatchcompleted="2010-12-27T16:32:06.433" lastattention="2010-12-27T16:31:45.007" clientapp="LBMX_DJ_Poster" hostname="NEWDELLC4" hostpid="736" loginname="LBMXSQLC\Administrator" isolationlevel="read committed (2)" xactid="7777666" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="64" sqlhandle="0x02000000687899325ef745a7a303944701d49394fd491dae" />
<frame procname="" line="1" sqlhandle="0x020000006c449101b1b5969fb34e3a37449459266ccceb8e" />
</executionStack>
<inputbuf>
update invoiceheader set sentmailboxfile = '12/27/2010 4:33:57 PM',receivedmailboxack = 1 where lbmxdocumentid = 100152323 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057595275313152" dbid="6" objectname="" indexname="" id="lock800b2900" mode="X" associatedObjectId="72057595275313152">
<owner-list>
<owner id="process396fb88" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process80308748" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057595283898368" dbid="6" objectname="" indexname="" id="lock8579c480" mode="S" associatedObjectId="72057595283898368">
<owner-list>
<owner id="process80308748" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process396fb88" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>

Viewing 15 posts - 1 through 15 (of 27 total)

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

tfeuz Ten Centuries Points: 1196 More actions · Answer 1

Just a side note -- the invoiceheader table has 16 indexes defined on it.....

Most of them generated by running the sql profiler and accepting its' recommendations.

Not sure if that helps...

TF

AppleMouth SSC-Addicted Points: 450 More actions · Answer 2

Have you changed the isolation level?

If you have SNAPSHOT isolation enabled, maybe set the UPDATE transaction to SNAPSHOT isolation?

I'm assuming that both transactions work when they are run from query analyzer?

A nod is as good as a wink to a blind bat.

tfeuz Ten Centuries Points: 1196 More actions · Answer 3

is this what you mean?

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

UPDATE Invoiceheader set......

Commit Tran

If so -- no I have not tried it.

Do I need to do the same for the read queries? or can they be left with the "deafault" isolation level?

Thx

TF

LutzM SSC Guru Points: 107049 More actions · Answer 4

Is there any chance for you to post the actual execution plan for the SELECT statement being part of the deadlock?

My shot in the dark would be the SELECT is a rather long running query due to missing indexes or large data volume (or both).

If query optimization won't help it might be an option to create an indexed view or to use the "old-fashioned" divide'n'conquer method: store the result of the select without the invoiceheader table in a temp table (indexed on the columns used in the join to the invoiceheader table) and join that intermediate table to the invoiceheader.

The interesting part of it: Will your expected result include the row just being updated (since sentmailboxfile will be set to a non-NULL value and therefore should be excluded from the final select)? What will the busienss case dictate?

As a side note: is there a specific reason to use "OriginalFileName LIKE ..." together with a fixed value (no percentage sign...)?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tfeuz Ten Centuries Points: 1196 More actions · Answer 5

Lutz,

A couple of quick answers before I get to posting the plan...

- I do not think it is a query optimization problem. The query runs relatively fast in the analyzer (less than 1 second)

- On average the query probably returns less than 1000 rows. In most cases it is simply 1 row

- I know for a fact the update and the sql are operating on different rows in the database. I am suspicious that because the update and the select are being run at the same time from different (parallel) process process that the rows exist on the same page in the database -- causing the page to be locked.

- interesting idea on the divide and conquer -- that will server to reduce the select time even more because it has done all the heavy work by pre joining the tables in advance?????

thanks

Tf

I changed the "like" to an "=" already. This was inherited code.....

AppleMouth SSC-Addicted Points: 450 More actions · Answer 6

That's it.

The SNAPSHOT isolation is new and I have not personally used it yet. A better solution may be to set the SELECT statement to a 'READ UNCOMMITED' isolation level.

It looks like the Update statement is putting an exclusive lock on one of the tables and that is tripping up the Select, with a lockMode="X". By placing the Select statement into Read Uncommitted it should still be able to read Locked tables, you would just be missing uncommitted updates (not a big deal).

I think?

A nod is as good as a wink to a blind bat.

LutzM SSC Guru Points: 107049 More actions · Answer 7

tfeuz (12/28/2010)
Lutz,
A couple of quick answers before I get to posting the plan...
- I do not think it is a query optimization problem. The query runs relatively fast in the analyzer (less than 1 second)
- On average the query probably returns less than 1000 rows. In most cases it is simply 1 row
- I know for a fact the update and the sql are operating on different rows in the database. I am suspicious that because the update and the select are being run at the same time from different (parallel) process process that the rows exist on the same page in the database -- causing the page to be locked.
- interesting idea on the divide and conquer -- that will server to reduce the select time even more because it has done all the heavy work by pre joining the tables in advance?????
thanks
Tf
...

I, personally, don't consider a query "fast" if it runs around 1sec 😀

Even if you know the update is on a different row than the select, SQL Server might be forced into table lock (e.g. due to missing index or lock escalation) or just a page lock affecting both rows. The execution plan might shed some light on it...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tfeuz Ten Centuries Points: 1196 More actions · Answer 8

Lutz,

Here is the plan. Right now the DB is empty as I am just queing another test....I am not sure if the plan will change once the dat ais there...I can post that in the morning

<?xml version="1.0" encoding="utf-16"?>

<Batch>

<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0197223" StatementText="select a.invoicekey,a.BuyingGroupInvoiceNumber,a.lbmxdocumentid,a.invoicenumber,SenderInterchangeId,ReceiverInterchangeId from isa_section f,gs_section e ,st_section d,invoiceheader a,filestatistics s Where f.ISAKey = e.ISAKey and s.StatisticsKey = f.StatisticsKey and e.gskey = d.gskey and d.stkey = a.stkey and a.ReceivedMailboxAck = 0 and lastprocessid >= 4000 and a.status <> 0 and SenderInterchangeId in ( select z.lbmxsenderid from config_tradingpartner z Where f.SenderInterchangeId = z.lbmxsenderid and f.ReceiverInterchangeId = z.lbmxreceiverid and generatepdf = 1 and documenttype = '810' ) and sentmailboxfile is null and OriginalFileName LIKE 'E:\TradingPartners\Timbrmart Winnipeg\out\Mailbox\20101204204704_2010_12_04_20_45_572010_12_04_18_29_54_10.xml' " StatementType="SELECT" QueryHash="0x4B2C29D88F0D7284" QueryPlanHash="0x030F7991A819A2DB">

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

<ScalarOperator ScalarString="[LBMXDocument].[dbo].[InvoiceHeader].[ReceivedMailboxAck] as [a].[ReceivedMailboxAck]=(0) AND [LBMXDocument].[dbo].[InvoiceHeader].[SentMailboxFile] as [a].[SentMailboxFile] IS NULL AND [LBMXDocument].[dbo].[InvoiceHeader].[LastProcessId] as [a].[LastProcessId]>=(4000) AND [LBMXDocument].[dbo].[InvoiceHeader].[Status] as [a].[Status]<>(0)">

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</RangeColumns>

</ScalarOperator>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

LutzM SSC Guru Points: 107049 More actions · Answer 9

Can you attach the plan as a .sqlplan file? (I'm getting an invalid character error when trying to open the file...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tfeuz Ten Centuries Points: 1196 More actions · Answer 10

tfeuz

Ten Centuries

Points: 1196

December 28, 2010 at 3:51 pm

#1266959

Here you go

TF

LutzM SSC Guru Points: 107049 More actions · Answer 11

Please expand the Nonclustered Index _dta_index_InvoiceHeader_6_1108198998__K64_K65_K4_1 on InvoiceHeader to INCLUDE(STKey,BuyingGroupInvoiceNumber,InvoiceNumber).

Side note: It looks like the estimated execution plan, not the actual....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

tfeuz Ten Centuries Points: 1196 More actions · Answer 12

Thanks Lutz -- I will try that.

A couple of quick questions (since I am new at this):

1. How did you review the plan and come up with that recommendation? (What pointed you in this direction?)

2. Do you have any links to any article that will help me with my plan reading skills?

3. The indexes were all generated from the DTA from a trace file that was taken over the period of several hours. How come DTA did not make the suggestion you gave? does that mean we should not take their recommendations as gospel?

Any tips you can give me in terms of interpreting the plans and cross referencing it with my queries in the code wuld be greatly appreciated!

TF

tfeuz Ten Centuries Points: 1196 More actions · Answer 13

Just a second followup --

I tried to include the columns you suggested. It turns out there were already in excess of 10 columns included.

The columns currently included matched what you had -- with the exception of STKey.

I tried to include that column and got an error "you are attempting to include a column that already belongs in the index key..... a column cannot paritipate in both.....

TF

LutzM SSC Guru Points: 107049 More actions · Answer 14

tfeuz (12/28/2010)
Thanks Lutz -- I will try that.
A couple of quick questions (since I am new at this):
1. How did you review the plan and come up with that recommendation? (What pointed you in this direction?)
2. Do you have any links to any article that will help me with my plan reading skills?
3. The indexes were all generated from the DTA from a trace file that was taken over the period of several hours. How come DTA did not make the suggestion you gave? does that mean we should not take their recommendations as gospel?
Any tips you can give me in terms of interpreting the plans and cross referencing it with my queries in the code wuld be greatly appreciated!
TF

To answer your questions in order:

#1: There is an index seek with a key lookup on the very right side. That's usually a sign of a non-covering index. (SQL Server decides ot use that index to find the primary key value and picks the columns not already covered by the index.)

The best fit index would be an index with all columns of your invoiceheader table involved in this query (aka covering index) without any additional columns. To make the index as narrow as possible, put the columns used in the SELECT clause or columns that are rather wide and/or not very selective in the INCLUDE part of the index.

My recommendation is based on the output list of the KeyLookup, since those obviously are the columns not covered by the index SQL Server decided to use.

#2: I usually use two sources to point to:

Bart Duncans blog and Gails blog[/url]

#3: DTA is a tool to give you a hint towards columns involved in index optimization. I strongly vote against taking it for granted. 😉

Indexing strategy is slightly more complicated: You need to keep in mind that each and every index you have needs to be updated with every INSERT/DELETE/UPDATE statement as soon as there are values modified or added being part of the index. So, whenever you add a new index, you might slow down your insert and update statements.

(I'm not sure how it affects filtered indexes though, since I never used it so far...) Speaking of it: You might benefit from a covering filtered index with a.ReceivedMailboxAck = 0 and sentmailboxfile is null (assuming you're using SS2K8, since you posted in the related forum).

In order to investigate any further we'd need the table def for InvoiceHeader including each and every index definition. We might need to eliminate (=compress) some of the indexes and on top/instead create one that specifically serves the query in question.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]