DeadLock - analysis, please help

Question

Post reply

DeadLock - analysis, please help

avihai marchiano

Valued Member

Points: 69
More actions
August 1, 2010 at 12:52 pm

#387477

I get a deadlock error.
From the trace i understand that the deadlock reason is rowid on table retail-transaction ,but there is no chance that 2 different request work on the same row-id.
each request (transaction) work on different retail-transaction, so i dont underrated how i get deadlock on rowid in table retail-transaction.
Attached the deadlock graph as a xml.
PS - what is the meaning of owner mode X?
Thank you very much!!!!!!

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

Please post the definition of TRN_RETAIL_TRANSACTION_TAB with all indexes.

Mode = 'X' - exclusive lock.

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

avihai marchiano Valued Member Points: 69 More actions · Answer 2

CREATE TABLE [dbo].[TRN_RETAIL_TRANSACTION_TAB](

[TRANSCTION_ID] [int] IDENTITY(1,1) NOT NULL,

[RETAIL_TRANSACTION] [varbinary](max) NULL,

[UPDATED_DATE] [datetime] NOT NULL,

[UPDATED_STORE_ID] [int] NULL,

[UPDATED_POS_ID] [int] NULL,

[IS_CLOSED] [bit] NOT NULL DEFAULT ((0)),

[IS_SUSPENDED] [bit] NOT NULL DEFAULT ((0)),

[SEQUENCE_NUMBER] [int] NOT NULL DEFAULT ((-1)),

[IS_TRAINING_MODE] [bit] NOT NULL DEFAULT ((0))

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_TRN_RETAIL_TRANSACTION_TAB] ON [dbo].[TRN_RETAIL_TRANSACTION_TAB]

(

[SEQUENCE_NUMBER] ASC,

[UPDATED_POS_ID] ASC,

[UPDATED_STORE_ID] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_TRN_RETAIL_TRANSACTION_TAB_TRANSCTION_ID] ON [dbo].[TRN_RETAIL_TRANSACTION_TAB]

(

[TRANSCTION_ID] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

avihai marchiano Valued Member Points: 69 More actions · Answer 3

One of the column in the table is blob, so each row store big data and can be bigger than page , if this help is something.

WayneS SSC Guru Points: 95460 More actions · Answer 4

First, I created this code a little time ago to shred apart the xml of a deadlock graph.

declare @deadlock xml

set @deadlock = 'put deadlock graph here'

select

[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),

[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),

[KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'),

[Index] = Keylock.Process.value('@indexname', 'varchar(200)'),

[IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'),

[RIDLock] = RIDLock.Process.value('@objectname', 'varchar(200)'),

[RIDLockMode] = RIDLock.Process.value('@mode', 'varchar(5)'),

[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,

[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),

[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(3)'),

[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),

[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),

[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),

[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),

[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),

[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')

from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)

LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)

ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') =

Deadlock.Process.value('@id', 'varchar(50)')

LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/ridlock') as RIDLock(Process)

ON RIDLock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') =

Deadlock.Process.value('@id', 'varchar(50)')

Running this against your graph, I did notice two things:

1. This is running from two different hosts: PSRVDUAL2 and SSRVDUAL. This would mean that your assumption

but there is no chance that 2 different request work on the same row-id.

is wrong - if the same query is coming from two different places, it must be being worked on at the same time from each of those places.

2. Both are running using the sa login. This is a very bad practice, and needs to be changed immediately.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

avihai marchiano Valued Member Points: 69 More actions · Answer 5

Sorry for not beeing clear.

There are two iis servers that executing in parallel multiple request.

each request work on its own retail-transaction, means on specific row in the table, so there is no chance that two different request work on the same row.

In the graph you see that the deadlock resource is rid, doesn't its means that two request asking for lock on the same row-id?

during the request the basic work against this table is:

select 1 from TRN_RETAIL_TRANSACTION_TAB where TRANSCTION_ID = SOME_VAL

select * from TRN_RETAIL_TRANSACTION_TAB

UPDATE (or insert) to TRN_RETAIL_TRANSACTION_TAB

avihai marchiano Valued Member Points: 69 More actions · Answer 6

Change the index on the transaction_id from none clustered to clustered solve the problem, but i dont know to explain how.

How actions on different rows cause to deadlock?

Why the owner mode of one is U and need X- how did it happened?

sergey.kudriavtsev Newbie Points: 9 More actions · Answer 7

sergey.kudriavtsev

Newbie

Points: 9

August 16, 2010 at 3:35 am

#1207231

WayneS: Thanks for a useful script!

arbcoll Old Hand Points: 317 More actions · Answer 8

The script can be useful, but it does not return any row from my deadlock XML graph. What could be the reason?

****

I just found the reason: just removed the <TextData> tag from my XML and it returned pretty clear rows :-).

Thanks, very useful script!

arbcoll Old Hand Points: 317 More actions · Answer 9

arbcoll (5/12/2011)
The script can be useful, but it does not return any row from my deadlock XML graph. What could be the reason?
****
I just found the reason: just removed the <TextData> tag from my XML and it returned pretty clear rows :-).
Thanks, very useful script!

I added the /TextData tag in front of all deadlock-list tag appearances, and now I have the rows returned. This because I need to assign the XML graph to the @deadlock variable directly from table.

Thanks again for the script.