SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with a Deadlock Issue


Help with a Deadlock Issue

Author
Message
ft55
ft55
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 231
Thank you for all the comments so far.

Could you expand on what you mean by reordering the transaction and how i might relate that to a select statement

I also noticed a blog that mentioned using try and catch around the statement so that if the statement failed due to a deadlock error it would automatically try again which sound quite neat ?

Thanks
Frant101
sql-lover
sql-lover
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4349 Visits: 1930
ft55 (4/29/2013)
Thank you for all the comments so far.

Could you expand on what you mean by reordering the transaction and how i might relate that to a select statement

I also noticed a blog that mentioned using try and catch around the statement so that if the statement failed due to a deadlock error it would automatically try again which sound quite neat ?

Thanks
Frant101


Ok...

Transaction 1 gets a lock on a given resource, let's called A.
Transaction 2, acquires a lock on resource B.
Now, transaction 1 also requires a lock on B, but that's not possible, because transaction 2 still holds a lock on same resource. At the same time, Transaction 2 needs a lock on A, but because 1 is still holding same lock, waiting for 2, none of those can't finish.

We have a deadlock here.

You fix above, if you make transaction 1 short enough, so by the time if holds the lock on B, transaction 2 has not started yet. That way, transaction 1 can complete without any problems. Then later transaction 2 can be called and will complete too, because nothing is holding a lock on A, which at some point will be needed by transaction 2.

It's purely a logic problem.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2618 Visits: 2582
ft55 (4/29/2013)
Recently we have started seeing some deadlocks reported on a database instance. I have enabled the trace -T1222 and am trying to interpret what has been returned in the logs : (the actual queries are quite lengthy)

Spid 161 is running this query (line 14 of proc [1]):
SELECT ...... from table1 .....
Spid 98 is running this query (adhoc line 1 of proc [p2]):
UPDATE .....from table1
Spid 161 is waiting for a Shared page lock on index table1.idx1.
(Spid 98 holds a conflicting IX lock.)
Spid 98 is waiting for an (IX page lock on index table1.idx2.
(Spid 161 holds a conflicting Shared lock.)

Just after some advice on what I can look at to resolve the deadlock. My hands are a bit tied as the update command is being called by a suppliers application and this and the underlying tables cant be altered.

The Select command is an in house process that we can look at changing but I'm not sure what can be done to the select statement to stop the lock. I have seen the NOLOCK table hint but I'm a little reluctant to suggest this as a solution.

Thanks for any help
Frant101


You may want to examine the indexing of your table1. The UPDATE to table1 requires corresponding updates to every affected index, and your blocking seems to be occurring when the UPDATE is attempting to update the indexes. If you have redundant or unused indexes on table1, removing them may allow the UPDATE to complete efficiently enough to avoid the blocking.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38006 Visits: 14411
ft55 (4/29/2013)
Hi,

Thanks for the response I was looking for a solution that could be used just at the transaction level rather than at the a database level. I'm slightly nervous of doing that in case it has an adverse effect on the suppliers application.

If I could change something or add a hint to the in-house select script that would be the ideal solution.

Thanks again
Frant101

Do not confuse Snapshot Isolation with Read Committed Snapshot Isolation. Snapshot Isolation is enabled at the database level, however it is only utilized when a SQL batch explicitly asks for it, i.e. it will have no effect on existing clients unless code changes are made to explicitly ask for it. For your in-house batch you could utilize it, but other batches submitted by pre-existing code would not be affected. Enabling it does however fire up the version store in tempdb regardless of whether anyone is explicitly asking to run code within the snapshot isolation mode, so those concerns still apply.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search