Deadlocks Involving Locks

Question

Post reply

Deadlocks Involving Locks

lux-254120

Valued Member

Points: 66
More actions
August 22, 2005 at 7:44 am

#90566

Hi all,
The below is error log from my MS SQLSEVER
Deadlock encountered .... Printing deadlock information
2005-08-22 18:03:07.34 spid4
2005-08-22 18:03:07.34 spid4     Wait-for graph
2005-08-22 18:03:07.34 spid4
2005-08-22 18:03:07.34 spid4     Node:1
2005-08-22 18:03:07.34 spid4     PAG: 6:1:16932567              CleanCnt:1 Mode: X Flags: 0x0
2005-08-22 18:03:07.34 spid4      Grant List 3::
2005-08-22 18:03:07.34 spid4        Owner:0x4d877a00 Mode: X        Flg:0x0 Ref:1 Life:00000000 SPID:56 ECID:0
2005-08-22 18:03:07.34 spid4        SPID: 56 ECID: 0 Statement Type: DBCC Line #: 1
2005-08-22 18:03:07.34 spid4        Input Buf: Language Event: DBCC SHRINKDATABASE ('in_cdr')
2005-08-22 18:03:07.34 spid4      Requested By:
2005-08-22 18:03:07.34 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec0x597C1548) Value:0x44e67ca0 Cost0/0)
2005-08-22 18:03:07.34 spid4
2005-08-22 18:03:07.34 spid4     Node:2
2005-08-22 18:03:07.34 spid4     TAB: 6:5575058 []              CleanCnt:1 Mode: IX Flags: 0x0
2005-08-22 18:03:07.34 spid4      Grant List 2::
2005-08-22 18:03:07.34 spid4        Owner:0x6a38c0a0 Mode: IS       Flg:0x0 Ref:1 Life:00000000 SPID:52 ECID:0
2005-08-22 18:03:07.34 spid4        SPID: 52 ECID: 0 Statement Type: SELECT Line #: 105
2005-08-22 18:03:07.34 spid4        Input Buf: Language Event: vanX_callhistory '667786', '08/01/2005', '8/22/2005', 'SCRATCH','Pross','23.0.80.10'
2005-08-22 18:03:07.34 spid4      Grant List 3::
2005-08-22 18:03:07.34 spid4      Requested By:
2005-08-22 18:03:07.34 spid4        ResType:LockOwner Stype:'OR' Mode: X SPID:56 ECID:0 Ec0x72EE3510) Value:0x42bdc6e0 Cost1/0)
2005-08-22 18:03:07.34 spid4     Victim Resource Owner:
2005-08-22 18:03:07.34 spid4      ResType:LockOwner Stype:'OR' Mode: X SPID:56 ECID:0 Ec0x72EE3510) Value:0x42bdc6e0 Cost1/0)
2005-08-22 18:55:24.90 spid4
Someone can help please?
How would we solve this issue?
Many thanks,
Lux

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

xblondel SSC Eights! Points: 818 More actions · Answer 1

Hi!

You have two competing transactions, their Input Buffers being:

1. DBCC SHRINKDATABASE ('in_cdr') [SPID 56]

2. vanX_callhistory '667786', '08/01/2005', '8/22/2005', 'SCRATCH','Pross','23.0.80.10' [SPID 52]

It seems like both transactions are trying to convert an intent-read lock to a write lock in file number 6 (which must be a table, according to the table lock hold by transaction 2). It is a conversion deadlock, where no transaction would succeed, because of the locks already held by the other one.

You should rework your vanX_callhistory procedure, using lock hints, to take an update or an exclusive lock first place at the beginning of the transaction. Post its source code if you are confused.

Bye,

Xavier

PS: Nothing to do with the topic, but I think we'd better talk about "promotion" deadlocks than "conversion" deadlocks (as K. Delaney does, for instance), since "promotion" bears the idea of going to a stronger lock. Well, nevermind!

lux-254120 Valued Member Points: 66 More actions · Answer 2

Many thanks Xavier.

Yet i stiil wonder Y I've been using this procedure for long.

Why this issue just happend?

Or Do we have another background process?

Thanks,

xblondel SSC Eights! Points: 818 More actions · Answer 3

Hi!

Your deadlock is clearly between dbcc shrinkdatabase and vanX_callhistory; no other background process is involved.

A special situation is needed for a conflict to arise, it will not necessary happen. Furthermore, I presume you are not shrinking your database all day long, which reduces the probability of a conflict!

Anyway, you can also modify your procedure to handle deadlocks (if @@ERROR = 1205, then you have a deadlock - loop and try your operation again.)

K. Delaney's "Inside SQL Server 2000", chapter 14, is worth reading, especially pp. 775-779 about deadlocks and the way to handle them (by the way, the whole book is worth reading!)

Bye,

Xavier

lux-254120 Valued Member Points: 66 More actions · Answer 4

thanks in advance my freind.

Hope this would help me.

Cheers,

Lux