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 1:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:55 AM
Points: 33, Visits: 112
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
Post #1447741
Posted Monday, April 29, 2013 1:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
Look at Snapshot Isolation. Will increase tempdb use but will keep readers from blocking writers.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447749
Posted Monday, April 29, 2013 2:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:55 AM
Points: 33, Visits: 112
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
Post #1447766
Posted Monday, April 29, 2013 2:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
The best way to fix deadlocks is via T-SQL code, changing the order of your transactions.

Deadlocks occurred because two transactions pin each other, so the 1st one can't complete because is waiting for the 2nd one, but the 2nd one it is also waiting for the 1st one which is still running.

If you change the order on which those are executed, you will fix the deadlock. You can also add a hint, like NO LOCK as part of the select statement. That may alleviate the problem but again, changing the order it's the best way to fix it.

I do NOT recommend you to use SNAPSHOT isolation or anything that can use tempdb without proper testing. tempdb is shared among all databases. If your tempdb can't handle the load, you may create more issues than what you are actually solving.
Post #1447773
Posted Monday, April 29, 2013 2:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
sql-lover (4/29/2013)
The best way to fix deadlocks is via T-SQL code, changing the order of your transactions.

Deadlocks occurred because two transactions pin each other, so the 1st one can't complete because is waiting for the 2nd one, but the 2nd one it is also waiting for the 1st one which is still running.

If you change the order on which those are executed, you will fix the deadlock. You can also add a hint, like NO LOCK as part of the select statement. That may alleviate the problem but again, changing the order it's the best way to fix it.

I do NOT recommend you to use SNAPSHOT isolation or anything that can use tempdb without proper testing. tempdb is shared among all databases. If your tempdb can't handle the load, you may create more issues than what you are actually solving.


And I would not recommend using NO LOCK due to all the potential issues it can introduce.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447774
Posted Monday, April 29, 2013 2:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Lynn Pettis (4/29/2013)
sql-lover (4/29/2013)
The best way to fix deadlocks is via T-SQL code, changing the order of your transactions.

Deadlocks occurred because two transactions pin each other, so the 1st one can't complete because is waiting for the 2nd one, but the 2nd one it is also waiting for the 1st one which is still running.

If you change the order on which those are executed, you will fix the deadlock. You can also add a hint, like NO LOCK as part of the select statement. That may alleviate the problem but again, changing the order it's the best way to fix it.

I do NOT recommend you to use SNAPSHOT isolation or anything that can use tempdb without proper testing. tempdb is shared among all databases. If your tempdb can't handle the load, you may create more issues than what you are actually solving.


And I would not recommend using NO LOCK due to all the potential issues it can introduce.


True!

But using NO LOCK is less dangerous, IMO, than changing to SNAPSHOT isolation level. As a matter of fact, NO LOCK is not the recommended approach either, and will help with select only, that's why I am suggesting changing the actual order of the transactions. But I saw one DBA changing the isolation level without proper testing, and the whole SQL instance started coughing because that, as tempdb was not properly set (right LUN or RAID, size, etc)
Post #1447780
Posted Monday, April 29, 2013 2:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:54 PM
Points: 897, Visits: 7,094
Yes, solving the real problem inside the transactions is the right way to handle deadlocks, but if you need a quick band-aid:

Snapshot isolation may impact performance, so you need to test it to make sure the load is not a problem, but NOLOCK can give you duplicate or incorrect data, and no amount of testing will guarantee that it won't. Give me slower data over bad data any day.




And then again, I might be wrong ...
David Webb
Post #1447783
Posted Monday, April 29, 2013 3:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
sql-lover (4/29/2013)
Lynn Pettis (4/29/2013)
sql-lover (4/29/2013)
The best way to fix deadlocks is via T-SQL code, changing the order of your transactions.

Deadlocks occurred because two transactions pin each other, so the 1st one can't complete because is waiting for the 2nd one, but the 2nd one it is also waiting for the 1st one which is still running.

If you change the order on which those are executed, you will fix the deadlock. You can also add a hint, like NO LOCK as part of the select statement. That may alleviate the problem but again, changing the order it's the best way to fix it.

I do NOT recommend you to use SNAPSHOT isolation or anything that can use tempdb without proper testing. tempdb is shared among all databases. If your tempdb can't handle the load, you may create more issues than what you are actually solving.


And I would not recommend using NO LOCK due to all the potential issues it can introduce.


True!

But using NO LOCK is less dangerous, IMO, than changing to SNAPSHOT isolation level. As a matter of fact, NO LOCK is not the recommended approach either, and will help with select only, that's why I am suggesting changing the actual order of the transactions. But I saw one DBA changing the isolation level without proper testing, and the whole SQL instance started coughing because that, as tempdb was not properly set (right LUN or RAID, size, etc)


You call missing or duplicate data less dangerous? That alone makes me nervous.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447784
Posted Monday, April 29, 2013 3:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 23,033, Visits: 31,555
Oh, and ANY changes (code, schema, database, etc.) should be tested first in a development and QA environment prior to going into production.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447785
Posted Monday, April 29, 2013 6:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Lynn Pettis (4/29/2013)
sql-lover (4/29/2013)
Lynn Pettis (4/29/2013)
sql-lover (4/29/2013)
The best way to fix deadlocks is via T-SQL code, changing the order of your transactions.

Deadlocks occurred because two transactions pin each other, so the 1st one can't complete because is waiting for the 2nd one, but the 2nd one it is also waiting for the 1st one which is still running.

If you change the order on which those are executed, you will fix the deadlock. You can also add a hint, like NO LOCK as part of the select statement. That may alleviate the problem but again, changing the order it's the best way to fix it.

I do NOT recommend you to use SNAPSHOT isolation or anything that can use tempdb without proper testing. tempdb is shared among all databases. If your tempdb can't handle the load, you may create more issues than what you are actually solving.


And I would not recommend using NO LOCK due to all the potential issues it can introduce.


True!

But using NO LOCK is less dangerous, IMO, than changing to SNAPSHOT isolation level. As a matter of fact, NO LOCK is not the recommended approach either, and will help with select only, that's why I am suggesting changing the actual order of the transactions. But I saw one DBA changing the isolation level without proper testing, and the whole SQL instance started coughing because that, as tempdb was not properly set (right LUN or RAID, size, etc)


You call missing or duplicate data less dangerous? That alone makes me nervous.


In terms of performance, yes! What you are mentioning is data related. So depending of the type of business (OLTP vs Data Warehouse) it may not hurt at all.

Do not really want to keep justifying my answer or yours because like I said, the best answer or way to fix deadlocks, is altering the transaction order. But OP asked for a T-SQL tweak, that's the only reason why I mentioned NO LOCK.

I am not a fan of NO LOCK myself either. I think that hides the main problem, but properly used, can be good in certain situations, like in most static tables or databases, or reporting systems.
Post #1447832
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse