Deadlocks on update

Question

Deadlocks on update

trans54

Ten Centuries

Points: 1357
More actions
February 16, 2009 at 11:51 pm

#134818

Hi all,
We have this table:
create table StoreTransactionsID(
TransactionsIDint
WebID varchar(64)
SiteCode varchar(10)
Purge bit
ModifiedDate datetime
ModifiedBy varchar(50))
The identity is on TransactionsID column and clustered index is on TransactionsID column.
No other indexes exists.
Sometimes when the stored procedured executes with the following code it deadlocks.
UPDATE StoreTransactionsID
SET
Purge = 1,
ModifiedBy = sysuser_sysname(),
ModifiedDate = GETDATE()
WHERE TransactionsID <= 87389
AND SiteCode = 'IAG'
AND Purge != 1
i checked the execution plan and the subtree cost is 29.0 and it has parralelism.
i added an index on SiteCode, TransactionsID and Purge and subtree cost reduced to 3.0 but now i see clustered index update in execution plan.
My undertanding is that it is still bad since clustered index update and nonclustered index can generate another deadlock.
Is this correct?
And what is another option here to eliminate clustered index update and deadlocks?
Thanks

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

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

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

Enable traceflag 1222. It will write the deadlock graph into the error log whenever a deadlock occurs. Please post the deadlock graph here and we'll try and help.

A single query can't (except under really unusual circumstances) deadlock. It requires two or more queries that are requesting incompatible locks. To fix it, we need to know both queries and the locks involved.

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

trans54 Ten Centuries Points: 1357 More actions · Answer 2

Thank you,

i added index on SiteCode,Purge

and this eliminates deadlocks on parallel threads.

But, i am still not happy since the cpu is more that 1000 and the duration is more than 1000.

The number of reads for each execution is 39000.So basically it reads the entire table.

i also checked the new index and in addition to seeks new index does cluster index lookups for this update:

UPDATE StoreTransactionsID

SET

Purge = 1,

ModifiedBy = sysuser_sysname(),

ModifiedDate = GETDATE()

WHERE TransactionsID <= 87389

AND SiteCode = 'IAG'

AND Purge != 1

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

Post the execution plan please (saved as a .sqlplan file, zipped and attached)

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

trans54 Ten Centuries Points: 1357 More actions · Answer 4

Thanks, i added the actual execution plan from Profiler. This plan did not show the use of nonclustered index which i added to the table but it seems that it helped to resolve parallelism and dealocks.

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

How many rows does that update? The exec plan (which is an estimated plan) says 1.

What's the clustered index on?

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

trans54 Ten Centuries Points: 1357 More actions · Answer 6

It updates around 40-60 rows and clustered index on LinkShareTransactionID.

Also, how can i distinguish between an actual plan and estimated plan in this case?

I got this plan from profiler by selecting showplan xml event. is this an estimated plan?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

It captures the estimated execution plan in XML format with full compile-time details in the TextData data column of the trace.

Showplan Statistics Profile:

Occurs during run time. It displays the actual execution plan with full run-time details in textual format.

http://technet.microsoft.com/en-us/library/ms190233.aspx

MJ

trans54 Ten Centuries Points: 1357 More actions · Answer 8

trans54

Ten Centuries

Points: 1357

February 20, 2009 at 3:12 pm

#947986

Good to know, thank you.