Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Help with a Deadlock Issue Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 11:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 27, 2014 3:37 AM
Points: 33, Visits: 128
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
Post #1447883
Posted Tuesday, April 30, 2013 7:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 411, Visits: 1,316
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.
Post #1448027
Posted Wednesday, May 1, 2013 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:50 PM
Points: 1,061, Visits: 2,580
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
Post #1448466
Posted Wednesday, May 1, 2013 8:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:01 AM
Points: 7,127, Visits: 12,731
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
Post #1448663
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse