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://www.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://www.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://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply