Conversion deadlocks
-
Hi *,
Two AddTime sproc are tyring to do an UPDATE that leads to a deadlocks, is this a conversion deadlock situation? I have done some reading in SQL but I am generally new to this topic, any of your feedback is valueable.
Conversion deadlocks ONLY happens on a shared resource that two or more nodes want to update at the same time i.e moving from shared to (intent exclusive and then) exclusive lock state? Isn't the MODE: Range-s-u ought to take care of the deadlock situation with the update in this range mode - why does deadlock occur :whistling?
Deadlock encountered .... Printing deadlock information
2005-09-1107:12:42.40 spid4
2005-09-1107:12:42.40 spid4 Wait-for graph
2005-09-1107:12:42.40 spid4
2005-09-1107:12:42.40 spid4 Node:1
2005-09-1107:12:42.40 spid4 KEY: 7:1700253162:6 (9a0d62ba578d) CleanCnt:2 Mode: Range-S-U Flags: 0x0
2005-09-1107:12:42.40 spid4 Grant List 0::
2005-09-1107:12:42.40 spid4 Grant List 2::
2005-09-1107:12:42.40 spid4 Owner:0x5436ed40 Mode: Range-S-S Flg:0x0 Ref:1 Life:02000000 SPID:114 ECID:0
2005-09-1107:12:42.40 spid4 SPID: 114 ECID: 0 Statement Type: UPDATE Line #: 350
2005-09-1107:12:42.40 spid4 Input Buf: RPC Event: AddTime;1
2005-09-1107:12:42.40 spid4 Requested By:
2005-09-1107:12:42.40 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec
0x78E4B558) Value:0x7fd8bc0 Cost0/A00)2005-09-1107:12:42.40 spid4
2005-09-1107:12:42.40 spid4 Node:2
2005-09-1107:12:42.40 spid4 KEY: 7:1700253162:6 (9a0d62ba578d) CleanCnt:2 Mode: Range-S-U Flags: 0x0
2005-09-1107:12:42.40 spid4 Grant List 0::
2005-09-1107:12:42.40 spid4 Owner:0x6536e1e0 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:98 ECID:0
2005-09-1107:12:42.40 spid4 SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 350
2005-09-1107:12:42.40 spid4 Input Buf: RPC Event: AddTime;1
2005-09-1107:12:42.40 spid4 Grant List 2::
2005-09-1107:12:42.40 spid4 Requested By:
2005-09-1107:12:42.40 spid4 ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:114 ECID:0 Ec
0x6B84B558) Value:0x66d17360 Cost0/A00)2005-09-1107:12:42.40 spid4 Victim Resource Owner:
2005-09-1107:12:42.40 spid4 ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:114 ECID:0 Ec
0x6B84B558) Value:0x66d17360 Cost0/A00)Thanks
-
- what kind of transaction are you using ?
- what kind of connection isolation level are you using ?
- what kind of lock-mechanism is active when the sp is being executed ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
On the Key 7:1700253162:6,
SPID 114 has Range_S_S lock (shared serializable range scan), and intents to acquire Range_S_U (shared serializable update scan)
SPID 98 has Range_S_U lock, and intents to acquire X lock.
However Range_S_U (114 tries to get) is not compatible with Range_S_U lock (98 owns) . So SPID 114 can not get it and has to wait SPID 98 to finish;
X lock (98 tries to get) is not comaptible with Range_S_S lock (114 owns) . So SPID 98 can not get it and has to wait SPID 114 to finish.
So deadlock occurs
According to BOL, key-range locks are used only when transaction is on serializable isolation level. Can you try to use read committed isolation level?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply