Deadlock occured?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

Recent Posts

Popular Topics

Home

Home » SQL Server 7,2000 » Administration » Deadlock occured?

Add to briefcase

14 posts, Page 1 of 2

1 2 »»

Deadlock occured?

Expand / Collapse

Author

Message

Posted Thursday, December 04, 2008 10:11 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:57 PM
Points: 491, Visits: 1,554

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 Sad

Sad

0x28C27548) Value:0x59196d40 Cost Sad

Sad

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 Sad

Sad

0x1B04B540) Value:0x7d4173a0 Cost Sad

Sad

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 Sad

Sad

0x1B04B540) Value:0x7d4173a0 Cost Sad

Sad

0/3134)

Post #613895

Posted Thursday, December 04, 2008 10:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044

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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #613929

Posted Thursday, December 04, 2008 11:51 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:57 PM
Points: 491, Visits: 1,554

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

Post #613975

Posted Thursday, December 04, 2008 12:05 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 10,057, Visits: 11,087

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #613985

Posted Thursday, December 04, 2008 12:28 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:57 PM
Points: 491, Visits: 1,554

how can we know in which database the dead lock has occured from the information I got from error log?

Post #614011

Posted Thursday, December 04, 2008 12:53 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044

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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #614028

Posted Thursday, December 04, 2008 2:37 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:57 PM
Points: 491, Visits: 1,554

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?

Post #614118

Posted Thursday, December 04, 2008 4:01 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:57 PM
Points: 491, Visits: 1,554

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.

Post #614166

Posted Friday, December 05, 2008 12:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 30,270, Visits: 23,044

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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #614287

Posted Friday, December 05, 2008 1:03 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 9:57 PM
Points: 491, Visits: 1,554

is there any script to get an alret whenever a deadlock occurs?

Post #614293

« Prev Topic | Next Topic »

Add to briefcase

14 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse