SQL Server Performance Forum – Threads Archive

Deadlocking on same stored proc at same line

I am working on a multiuser 3 tier app, (UI and midtier in .NET), and DB is SQL server 2000 (SP2) While doing concurrency tests, we are noticing deadlocking is occurring at the exact same line within a stored proc. We noticed the problem was occurring within a DTC transaction, even after removing these we still have the same problem. Below shows the deadlocking trace 2003-08-15 13:15:49.60 spid4 Node:1 2003-08-15 13:15:49.60 spid4 KEY: 17:1415676091:1 (18001a0d993f) CleanCnt:1 Mode: X Flags: 0x0 2003-08-15 13:15:49.60 spid4 Grant List 1:: 2003-08-15 13:15:49.60 spid4 Owner:0x42bd8d80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:59 ECID:0 2003-08-15 13:15:49.60 spid4 SPID: 59 ECID: 0 Statement Type: INSERT Line #: 334 2003-08-15 13:15:49.60 spid4 Input Buf: RPC Event: usp_CreatePaymentsFromTemplate;1 2003-08-15 13:15:49.60 spid4 Requested By: 2003-08-15 13:15:49.60 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:58 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x473F3570) Value:0x42bd0880 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/78C) 2003-08-15 13:15:49.60 spid4 2003-08-15 13:15:49.60 spid4 Node:2 2003-08-15 13:15:49.60 spid4 KEY: 17:1415676091:1 (1600281f132a) CleanCnt:1 Mode: X Flags: 0x0 2003-08-15 13:15:49.60 spid4 Grant List 0:: 2003-08-15 13:15:49.60 spid4 Owner:0x42bd0780 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0 2003-08-15 13:15:49.60 spid4 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 334 2003-08-15 13:15:49.60 spid4 Input Buf: RPC Event: usp_CreatePaymentsFromTemplate;1 2003-08-15 13:15:49.60 spid4 Requested By: 2003-08-15 13:15:49.60 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:59 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x473ED570) Value:0x42bd8f60 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/85<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2003-08-15 13:15:49.60 spid4 Victim Resource Owner: 2003-08-15 13:15:49.60 spid4 ResType:LockOwner Stype:’OR’ Mode: S SPID:58 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0x473F3570) Value:0x42bd0880 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />0/78C) NOTE: the stored procs fight for the same resource at the same time and the same Mode. I have looked at hyperthreading, multiprocessor issues but none of them seem to resolve the problem. ALL suggestions are welcome. Cheers

Can you post the code for the SQL being executed? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Have you thought about transaction isolation levels?
Nathan H.O.
Moderator
SQL-Server-Performance.com

We looked at isolation levels, especially as we were using COM+ to manage our DTC transactions from .NET, and as default (in com+ 1.0 you cant change) the isolation level is serialisable, so we manually changed it to READ COMMITTED on every call. The problem must originate somewhere in the DTC transaction because we have managed to get deadlocking by two threads in a simple proc – shown below. CREATE PROCEDURE usp_ApplyFirstDebitDateToAll
(
@pi_UserIDObjectID,
@pi_TemplateOIDObjectID,
@pi_DateDATETIME
)
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED UPDATE PaymentTemplate
SET PaymentDate = @pi_Date
WHERE TemplateOID = @pi_TemplateOID
GO
Thanks for all your suggestions.

Try using ROWLOCK. UPDATE PaymentTemplate
SET PaymentDate = @pi_Date
FROM PaymentTemplate (ROWLOCK)
WHERE TemplateOID = @pi_TemplateOID Bambola.

Tried already, also tried exclusively locking the table at the start of any stored proc
prior to any updates/views within the PaymentTemplate table, but still seems to be an issue with
isolation levels within the transaction. Is there anyway of outputing from a storedproc what the current isolation level is?

"exclusively locking the table at the start of any stored proc" is in a way the opposite of ROWLOCK. it can cause more lock problems…
I wonder why does it take so long to update one row that it causes lock problems, and how could it cause deadlock problem. Do you have an index on the TemplateOID? Is there something else in that stored procedure except for what we see above?
Bambola.

Just for refernce… http://support.microsoft.com/default.aspx?scid=kb;en-us;295570
http://support.microsoft.com/default.aspx?scid=kb;EN-US;215520
In the Log, the stored procedure displayed is usp_CreatePaymentsFromTemplate. Can you post the code for the same? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Also please do not make duplicate posts. The duplicate post ishttp://sql-server-performance.com/forum/topic.asp?TOPIC_ID=1407 Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Apologies for double posting,
The stored proc is quite long, but the same problem happens in the proc I posted (hence why I posted the shorter of the two stored procs.) I am now currently looking at what locks are being taken out and held as a method of determining how the deadlock is occurring. I still think the issue is to do with the isolation within the DTC transaction, and ensuring that locks are released when not needed. (I know this might seem obvious but pinpointing the exact problem isnt proving to be easy). Thanks again for all replies

Take a look at this article.
http://www.sql-server-performance.com/deadlocks.asp Bambola.

Just for anyone who replied – thanks for your help. I have isolated the problem as being an actual deadlock within the DTC transaction.
We will resolve this by shortening what actually happens within this transaction – simplier said than done! Thanks again

]]>