Click here to monitor SSC
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
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24247 Visits: 37978
Look at Snapshot Isolation. Will increase tempdb use but will keep readers from blocking writers.

Cool
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)
ft55
ft55
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 231
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
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24247 Visits: 37978
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.

Cool
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)
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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)
David Webb-CDS
David Webb-CDS
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 8584
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24247 Visits: 37978
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.

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24247 Visits: 37978
Oh, and ANY changes (code, schema, database, etc.) should be tested first in a development and QA environment prior to going into production.

Cool
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)
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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.
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