April 29, 2013 at 1:42 pm
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
April 29, 2013 at 1:56 pm
Look at Snapshot Isolation. Will increase tempdb use but will keep readers from blocking writers.
April 29, 2013 at 2:26 pm
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
April 29, 2013 at 2:34 pm
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.
April 29, 2013 at 2:40 pm
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.
April 29, 2013 at 2:52 pm
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)
April 29, 2013 at 2:57 pm
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.
April 29, 2013 at 3:00 pm
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.
April 29, 2013 at 3:08 pm
Oh, and ANY changes (code, schema, database, etc.) should be tested first in a development and QA environment prior to going into production.
April 29, 2013 at 6:51 pm
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.
April 29, 2013 at 11:47 pm
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
April 30, 2013 at 7:35 am
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.
May 1, 2013 at 9:13 am
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
May 1, 2013 at 8:26 pm
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply