July 2, 2002 at 9:57 am
I have a database which deadlocks frequently. I have turned on trace flag 1204 and am examining the output. However, some of the deadlock output doesn't make sense. For example, it shows stored procedures holding locks on tables or pages that the stored procedures or any of their dependent objects don't even touch. How reliable is this output when troubleshooting deadlocks?
Secondly, if anyone has some detailed documentation on troubleshooting deadlocks (more detailed than BOL) I would appreciate it.
Finally, many of the deadlocks are caused by sp_executesql statements. Unfortunately, the 1204 output only shows 'sp_executesql' in the input buffer. Is there any way to get the SQL that is actually running behind this sp_executesql?
Thanks,
SB
July 2, 2002 at 11:43 am
Take a look at the following Knowledge Base article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509
One of the reasons the stored procedures may be holding locks on objects they don't touch is they are part of a larger transaction and that just happens to currently be the statement executing. Here's a question, is your application using MTS or COM+? If so, are you explicitly handling transactions? If not, this may be a cause for some of your issues.
If you are running Profiler on the DB, you can catch deadlocks and deadlock chains. This will typically help you identify the actual statements which are causing your deadlocks along with the SPIDs. You can also track transactions.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 2, 2002 at 11:53 am
The application does use MTS/COM, and MTS handles all of the transaction management. Is this a design flaw? What is the best practice for transaction handling when using MTS?
July 2, 2002 at 12:27 pm
MTS handles all transactions, yes. However, it, by default sets transactions and sets them as SERIALIZABLE:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q215520
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 2, 2002 at 12:33 pm
We have already started using hints to lower the isolation level of read-only transactions. However, there is a problem; we have a lot of transactions that are read-only, but the values read are stored into output parameters that are then used to feed INSERTS or UPDATES in other stored procs. In this case, I don't think that using nolock hints would be a good idea, as they may cause uncommitted data to be fed into INSERTS or UPDATES. Am I correct in assuming this?
July 2, 2002 at 12:58 pm
Yes, using NOLOCK may result in the query reading in uncommitted data. If you are reusing those values for other stored procs, you may indeed run into an issue.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply