December 4, 2008 at 10:11 am
hi,
I got an deadlock. plz advice me what to do?
2008-12-04 08:56:56.41 spid4
Deadlock encountered .... Printing deadlock information
2008-12-04 08:56:56.41 spid4
2008-12-04 08:56:56.41 spid4 Wait-for graph
2008-12-04 08:56:56.41 spid4
2008-12-04 08:56:56.41 spid4 Node:1
2008-12-04 08:56:56.41 spid4 PAG: 9:1:220801 CleanCnt:2 Mode: IX Flags: 0x2
2008-12-04 08:56:56.41 spid4 Grant List 2::
2008-12-04 08:56:56.41 spid4 Owner:0x540060a0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:85 ECID:0
2008-12-04 08:56:56.41 spid4 SPID: 85 ECID: 0 Statement Type: INSERT Line #: 123
2008-12-04 08:56:56.41 spid4 Input Buf: RPC Event: dbo.abc_Activity_Inquire;1
2008-12-04 08:56:56.41 spid4 Requested By:
2008-12-04 08:56:56.41 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:68 ECID:0 Ec:(0x28C27548) Value:0x59196d40 Cost:(0/1D5F4)
2008-12-04 08:56:56.41 spid4
2008-12-04 08:56:56.41 spid4 Node:2
2008-12-04 08:56:56.41 spid4 PAG: 9:1:221153 CleanCnt:2 Mode: X Flags: 0x2
2008-12-04 08:56:56.41 spid4 Grant List 2::
2008-12-04 08:56:56.41 spid4 Owner:0x67979360 Mode: X Flg:0x0 Ref:3 Life:02000000 SPID:68 ECID:0
2008-12-04 08:56:56.41 spid4 SPID: 68 ECID: 0 Statement Type: INSERT Line #: 1
2008-12-04 08:56:56.41 spid4 Input Buf: RPC Event: dbo.abc_Activity_BeginTransaction;1
2008-12-04 08:56:56.41 spid4 Requested By:
2008-12-04 08:56:56.41 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:85 ECID:0 Ec:(0x1B04B540) Value:0x7d4173a0 Cost:(0/3134)
2008-12-04 08:56:56.41 spid4 Victim Resource Owner:
2008-12-04 08:56:56.41 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:85 ECID:0 Ec:(0x1B04B540) Value:0x7d4173a0 Cost:(0/3134)
December 4, 2008 at 10:46 am
Take a look at line 123 of the procedure abc_Activity_Inquire. There's an insert statement there that's one half of the deadlock.
The other half is a piece of dynamic sQL somewhere in the proc abc_Activity_BeginTransaction. No way to tell where in there though.
To find the tables that were involved in the deadlock requires some work, as they are page locks.
Please run the following.
DBCC TRACEON (3604) -- output to console
DBCC PAGE(9,1,220801) -- print the page's header info
DBCC PAGE(9,1,221153)
DBCC TRACEOFF (3604)
Somewhere in the page header info there'll be an objectid. Find that for each of the outputs and use the OBJECT_NAME function to get the name of the object. That'll give you the two tables involved in the deadlock.
As for fixing it, depends on the code.
Make sure objects are always accessed in the same order
Make sure the queries are as optimal as possible
Make sure the indexes support the queries.
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
December 4, 2008 at 11:51 am
thanks Gail,
Here is the output after running the DBCC commands
PAGE HEADER:
------------
Page @0x1CE04000
----------------
m_pageId = (1:221153) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1775657719 m_indexId = 0 m_prevPage = (1:221152)
m_nextPage = (1:221154) pminlen = 611 m_slotCnt = 11
m_freeCnt = 471 m_freeData = 7699 m_reservedCnt = 0
m_lsn = (115148:58581:7) m_xactReserved = 0 m_xdesId = (0:69018247)
m_ghostRecCnt = 0 m_tornBits = 3285233
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:218376) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After executing SELECT OBJECT_NAME(1775657719 ) giving me the result as:
no column name
NULL
So, how can I get the table names that are involved in the deadlock?
Thanks for your help
December 4, 2008 at 12:05 pm
Make sure you do Select Object_name(object_id) in the database you have the deadlock in. It returns NULL when you execute in a database other than the one the object is in.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 12:28 pm
how can we know in which database the dead lock has occured from the information I got from error log?
December 4, 2008 at 12:53 pm
From the deadlock graph, you can see it occurred in the database with an id of 9 (that's why 9 was the first parameter to DBCC Page - DBCC PAGE(Database ID, File ID, Page No)).
SELECT DB_Name(9)
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
December 4, 2008 at 2:37 pm
thank you very much...
The procedure abc_Activity_Inquire is in different database whose dbid is 12. the dbid =9 is a different database, which does not have the procedure abc_Activity_Inquire .
is that possible that the dead lock happened in different database 12 which not mentioned in the dead lock graph?
December 4, 2008 at 4:01 pm
Hi Gail,
here, are the two stored procedures involved in the deadlock OR the two tables with object ids
select object_name(1224065284)
select object_name(1775657719) were involved in the deadlock?
I checked fragmentation of the two tables using DBCC SHOWCONTIG. I got the below results, which shows that there is not much fragmentation(Logical Scan Fragmentation 5.41%) .Plz correct me if I am wrong
DBCC SHOWCONTIG scanning 'ABC' table...
Table: 'ABC' (1224065284); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 740
- Extents Scanned..............................: 102
- Extent Switches..............................: 131
- Avg. Pages per Extent........................: 7.3
- Scan Density [Best Count:Actual Count].......: 70.45% [93:132]
- Logical Scan Fragmentation ..................: 5.41%
- Extent Scan Fragmentation ...................: 21.57%
- Avg. Bytes Free per Page.....................: 1040.8
- Avg. Page Density (full).....................: 87.14%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'XYZ' table...
Table: 'XYZ' (1775657719); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 676
- Extents Scanned..............................: 93
- Extent Switches..............................: 121
- Avg. Pages per Extent........................: 7.3
- Scan Density [Best Count:Actual Count].......: 69.67% [85:122]
- Logical Scan Fragmentation ..................: 4.29%
- Extent Scan Fragmentation ...................: 18.28%
- Avg. Bytes Free per Page.....................: 1094.9
- Avg. Page Density (full).....................: 86.47%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Plz clarify me.
December 5, 2008 at 12:40 am
madhu.arda (12/4/2008)
is that possible that the dead lock happened in different database 12 which not mentioned in the dead lock graph?
The dead lock resources were on the two tables in DB 9. There's no chance of a mistake there. It's possible that the procs are in a different DB and are calling across databases, without seeing them, can't say
here, are the two stored procedures involved in the deadlock OR the two tables with object ids
select object_name(1224065284)
select object_name(1775657719) were involved in the deadlock?
I don't understand what you're asking.
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
December 5, 2008 at 1:03 am
is there any script to get an alret whenever a deadlock occurs?
December 5, 2008 at 5:06 pm
can I have a sample script to generate a trigger when a dead lock is occured?
December 5, 2008 at 5:14 pm
Dead lock occured again. I have couple of questions:
1.In this dead lock, SPID 90 is automatically killed by SQL Server right?
2.what is the dead lock duration ?(Here, I can see it started and ended at the same time 10:00:03.46). how long the deadlock was there?
Plz advice...immediate steps to take,inorder to avoid deadlocks
Deadlock encountered .... Printing deadlock information
2008-12-05 10:00:03.46 spid4
2008-12-05 10:00:03.46 spid4 Wait-for graph
2008-12-05 10:00:03.46 spid4
2008-12-05 10:00:03.46 spid4 Node:1
2008-12-05 10:00:03.46 spid4 PAG: 9:1:240565 CleanCnt:2 Mode: X Flags: 0x2
2008-12-05 10:00:03.46 spid4 Grant List 3::
2008-12-05 10:00:03.46 spid4 Owner:0x67575760 Mode: X Flg:0x0 Ref:25 Life:02000000 SPID:91 ECID:0
2008-12-05 10:00:03.46 spid4 SPID: 91 ECID: 0 Statement Type: INSERT Line #: 1
2008-12-05 10:00:03.46 spid4 Input Buf: RPC Event: dbo.abcsp_Activity_BeginTransaction;1
2008-12-05 10:00:03.46 spid4 Requested By:
2008-12-05 10:00:03.46 spid4 ResType:LockOwner Stype:'OR' Mode: IU SPID:96 ECID:0 Ec:(0x67A01540) Value:0x65b9cc00 Cost:(0/F2E48)
2008-12-05 10:00:03.46 spid4
2008-12-05 10:00:03.46 spid4 Node:2
2008-12-05 10:00:03.46 spid4 PAG: 9:1:239420 CleanCnt:2 Mode: IX Flags: 0x2
2008-12-05 10:00:03.46 spid4 Grant List 1::
2008-12-05 10:00:03.46 spid4 Owner:0x66193260 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:90 ECID:0
2008-12-05 10:00:03.46 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE Line #: 27
2008-12-05 10:00:03.46 spid4 Input Buf: RPC Event: dbo.abcsp_Activity_BeginTransaction;1
2008-12-05 10:00:03.46 spid4 Requested By:
2008-12-05 10:00:03.46 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:91 ECID:0 Ec:(0x31931548) Value:0x65b9c4e0 Cost:(0/41558)
2008-12-05 10:00:03.46 spid4 Victim Resource Owner:
2008-12-05 10:00:03.46 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:91 ECID:0 Ec:(0x31931548) Value:0x65b9c4e0 Cost:(0/41558)
December 6, 2008 at 1:09 am
[font="Verdana"]You have two locks. One is an eXclusive(X) lock for the insert and the second an Intent eXclusive(IX) for the update. These lock types are incompatible( Search BOL: LOCK COMPATIBILITY) with each other. Find the following extract:
An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention to update only some of the rows rather than all of them. Other transactions that want to read or update some of the rows are also permitted providing they are not the same rows being updated by other transactions.
Your scenario has two SPs updating two different tables. I can only take a guess and say that, there might be a relationship constraint that the insert or the update is trying to change while the other is accessing it causing a deadlock.
Reference:
Deadlock:
http://support.microsoft.com/kb/323630
http://support.microsoft.com/kb/832524
http://support.microsoft.com/support/kb/articles/Q169/9/60.ASP
http://www.sqlservercentral.com/articles/Administering/exploringdeadlocks/616/
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
Scripts:
http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/31535/
Regards,[/font]
December 6, 2008 at 1:12 am
madhu.arda (12/5/2008)
Dead lock occured again. I have couple of questions:1.In this dead lock, SPID 90 is automatically killed by SQL Server right?
Spid 90 wasn't even involved. The deadlock was between Spids 91 and 96. From the last line of the graph you can see that 91 was the victim
2.what is the dead lock duration ?(Here, I can see it started and ended at the same time 10:00:03.46). how long the deadlock was there?
They're usually very quick. Under a second. The deadlock detector will pick up deadlock conditions and resolve them almost as soon as they occur
Plz advice...immediate steps to take,inorder to avoid deadlocks
I told you earlier.
As for fixing it, depends on the code.
Make sure objects are always accessed in the same order
Make sure the queries are as optimal as possible
Make sure the indexes support the queries.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply