March 2, 2009 at 5:34 am
Hi All,
I have setup a deadlock trace on some of our severs which is picking up SQL server deadlocks. the process works fine, but the problem I am now having is that the trace is telling me that a stored procedure is causing blocking, but I want to be able to see the exact code which is causing the deadlock.
I setup a profiler deadlock trace, deadlock chain and deadlock graph trace, but although I can see traces on profiler, but it doesnt actually show me the exact code which is running, I know I can setup a SQL statement trace on profiler, but the problem I have wioth profiler is that I dont want to be bombarded with sql statements since its a production box that runs lots of SQL commands.
Is there a way out for this ?
March 2, 2009 at 5:56 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here if you want help fixing the deadlock.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2009 at 6:18 am
The causing tables can be known from the deadlock details recorded by trace flage 1204. Then you can isolate the queries that are using that table.
March 3, 2009 at 7:38 am
Thanks for the posts guys, from the sql log details below, I know the 2 stored procedures that are suspect, I just need to know what objects/operations they are working on at the time of failure.
01/03/2009 12:00:55,Logon,Unknown,Error: 18456 State: 8.
01/03/2009 10:30:23,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x514364D8 Mode: U SPID:121 BatchID:0 ECID:0 TaskProxy:(0x4DC60378) Value:0x3f9a5c40 Cost:(0/0)
01/03/2009 10:30:23,spid4s,Unknown,Victim Resource Owner:
01/03/2009 10:30:23,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/03/2009 10:30:23,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x4FE04250 Mode: X SPID:98 BatchID:0 ECID:0 TaskProxy:(0x322B8378) Value:0x39f9dd20 Cost:(0/220)
01/03/2009 10:30:23,spid4s,Unknown,Requested By:
01/03/2009 10:30:23,spid4s,Unknown,Input Buf: Language Event: EXEC spProcessTransaction
01/03/2009 10:30:23,spid4s,Unknown,SPID: 121 ECID: 0 Statement Type: UPDATE Line #: 503
01/03/2009 10:30:23,spid4s,Unknown,Owner:0x2F8CA8A0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:0 XactLockInfo: 0x514364FC
01/03/2009 10:30:23,spid4s,Unknown,Grant List 0:
01/03/2009 10:30:23,spid4s,Unknown,KEY: 11:72057594480778480 (423177f7411f) CleanCnt:2 Mode:U Flags: 0x0
01/03/2009 10:30:23,spid4s,Unknown,Node:2
01/03/2009 10:30:23,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/03/2009 10:30:23,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x514364D8 Mode: U SPID:121 BatchID:0 ECID:0 TaskProxy:(0x4DC60378) Value:0x3f9a5c40 Cost:(0/0)
01/03/2009 10:30:23,spid4s,Unknown,Requested By:
01/03/2009 10:30:23,spid4s,Unknown,Input Buf: Language Event: spExportTransactionDetails
01/03/2009 10:30:23,spid4s,Unknown,SPID: 98 ECID: 0 Statement Type: DELETE Line #: 22
01/03/2009 10:30:23,spid4s,Unknown,Owner:0x2F5BA860 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:98 ECID:0 XactLockInfo: 0x4FE04274
01/03/2009 10:30:23,spid4s,Unknown,Grant List 1:
01/03/2009 10:30:23,spid4s,Unknown,KEY: 11:72057594080657408 (7400c3e8c05d) CleanCnt:3 Mode:X Flags: 0x0
March 3, 2009 at 7:59 am
First command must be an update statement under this(spProcessTransaction) SP and it should be at line#503(Let experts comment about line#)
EXEC spProcessTransaction
UPDATE Line #: 503
Second command must be an delete statement under this(spExportTransactionDetails) SP and it should be at line#22(Let experts comment about line#)
EXEC spExportTransactionDetails
DELETE Line #: 22
MJ
March 3, 2009 at 8:13 am
Thanks for the reply, so what you saying is that sp_helptext needs to be used and the line number traced.
Is it possible to setup a trace that only tracks those 2 Sp's and the statements they run and nothing else. I have found that using the Like statement within profiler doesnt particularly work well.
March 3, 2009 at 10:00 am
Dean Jones (3/3/2009)
Thanks for the posts guys, from the sql log details below
That's the results from 1204. Any chance you could use traceflag 1222 instead, as it produces a lot more detailed output with far more info and far easier to decipher.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2009 at 1:33 am
its a bit tough to analyse this using the graph. Put Trace flag 1222 ON which gives you a c more clear picture with the SP's and the TSQL that's causing deadlock to occur and post it, we would be able to help you out.
Ill try to analyze this anyway
its the dead lock between SPID's 121 and 98
Dean Jones (3/3/2009)
01/03/2009 12:00:55,Logon,Unknown,Error: 18456 State: 8.
01/03/2009 10:30:23,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x514364D8 Mode: U SPID:121 BatchID:0 ECID:0 TaskProxy:(0x4DC60378) Value:0x3f9a5c40 Cost:(0/0)
01/03/2009 10:30:23,spid4s,Unknown,Victim Resource Owner:
01/03/2009 10:30:23,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/03/2009 10:30:23,spid4s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x4FE04250 Mode: X SPID:98 BatchID:0 ECID:0 TaskProxy:(0x322B8378) Value:0x39f9dd20 Cost:(0/220)
01/03/2009 10:30:23,spid4s,Unknown,SPID: 121 ECID: 0 Statement Type: UPDATE Line #: 503
SPID 121 executing UPDATE statement
execute DBCC INPUTBUFFER (SPID) and view the statement at that session
01/03/2009 10:30:23,spid4s,Unknown,Owner:0x2F8CA8A0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:121 ECID:0 XactLockInfo: 0x514364FC
01/03/2009 10:30:23,spid4s,Unknown,Grant List 0:
01/03/2009 10:30:23,spid4s,Unknown,KEY: 11:72057594480778480 (423177f7411f) CleanCnt:2 Mode:U Flags: 0x0
01/03/2009 10:30:23,spid4s,Unknown,Node:2
01/03/2009 10:30:23,spid4s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values..
SPID 121 is asking for update lock on the resource(KEY: 11:72057594480778480 ). but look at your TSQL, its not updating any of the values i guess.
01/03/2009 10:30:23,spid4s,Unknown,SPID: 98 ECID: 0 Statement Type: DELETE Line #: 22
01/03/2009 10:30:23,spid4s,Unknown,Owner:0x2F5BA860 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:98 ECID:0 XactLockInfo: 0x4FE04274
01/03/2009 10:30:23,spid4s,Unknown,Grant List 1:
01/03/2009 10:30:23,spid4s,Unknown,KEY: 11:72057594080657408 (7400c3e8c05d) CleanCnt:3 Mode:X Flags: 0x0
SPID 98 has already got an exclusive lock on that resource(KEY:11:72057594080657408 )
SPID 121 and 98 are blocking each other
March 19, 2009 at 9:51 am
Thnaks guys, I have setup a test system where I was able to replicate the problem.
Transaction 1: Random Deleted
Transaction 2: Random Updates
Transaction 3: Random Updates/Selects
In the end, it ended up starting a deadlocking, I have improved the DML statements with the use of indexes on columns where it bases the deleted/updates on, they seem to run faster now, but what I wanted to know is that, will this sole solution resolve the problem.
March 19, 2009 at 10:21 am
Dean Jones (3/19/2009)
In the end, it ended up starting a deadlocking, I have improved the DML statements with the use of indexes on columns
That's the way to go. Proper indexing.
what I wanted to know is that, will this sole solution resolve the problem.
Well, cun't say that, you should keep monitoring locking/blocking activity now and then and also your application performance and identify any bottlenecks.
April 6, 2009 at 4:37 am
Hi
Just to give you guys an update, we are still experiencing other deadlocking problems, I have read that try/catch blocks can help resolve deadlocking problems, is this true.
Also, what else can be done to resolve the deadlocking problem ?
April 6, 2009 at 4:46 am
Dean Jones (4/6/2009)
HiJust to give you guys an update, we are still experiencing other deadlocking problems, I have read that try/catch blocks can help resolve deadlocking problems, is this true.
Also, what else can be done to resolve the deadlocking problem ?
Keep Transactions short
Using low transaction isolation levels
maintaining proper indexes
access objects in the same order
avoiding user interactions
but i thought u resoved the issue are you still having deadlocks?
April 6, 2009 at 5:00 am
Krishna Potlakayala (4/6/2009)
Dean Jones (4/6/2009)
HiJust to give you guys an update, we are still experiencing other deadlocking problems, I have read that try/catch blocks can help resolve deadlocking problems, is this true.
Also, what else can be done to resolve the deadlocking problem ?
Keep Transactions short
Using low transaction isolation levels
maintaining proper indexes
access objects in the same order
avoiding user interactions
but i thought u resoved the issue are you still having deadlocks?
Looks like after sorting out one, a new one springs up.
With regards to [Using low transaction isolation levels]
How can this be doen with Delete and Updates, i thought it was mainly reads.
, I dont quite understand this.
[avoiding user interactions] in all my cases, these were standard scheduled procedures.
April 6, 2009 at 6:02 am
Dean Jones (4/6/2009)
With regards to [Using low transaction isolation levels]How can this be doen with Delete and Updates, i thought it was mainly reads.
Read uncommitted is only for selects (and is often a bad idea anyway). The higher isolation levels affect the length of time and the extent of locks taken by selects. Updates and deletes always take locks and always hold them to the end of the transaction.
, I dont quite understand this.
Say you have two procedures that both update (or insert, or delete) Table1 and Table2. You want, as far as possible, to operate on teh tables in the same order in both procedures. So either both update (insert, delete) Table1 first then Table2 (or the other way around). This is to avoid the classic deadlock scenario where one procedure has a lock on Table1 and wants one on Table2 and the other has a lock on Table2 and wants one on Table1.
Doing this may not completely prevent deadlocks, but it does remove one major cause.
Also, what else can be done to resolve the deadlocking problem ?
Near-impossible to say without a lot more info. Please enable traceflag 1222 and post the deadlock graphs here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 6:22 am
Dean Jones (4/6/2009)
Krishna Potlakayala (4/6/2009)
Dean Jones (4/6/2009)
How can this be doen with Delete and Updates, i thought it was mainly reads.
You cannot use lower isolation levels with Insert,update and delete they always need exclusive locks on the records.
I have given you general idea of how you can minimize dead locks. did you perfmon your deadlocks? SQL Server locks: number of deadlocks/sec?
How are you saying you have deadlocks? I man did you trace it?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply