Deadlock on simple update on different rows
-
HI ,
I am facing a weird situation.
I have a LOCATION table >> SysID, Trans_no & Location ID are the primary keys.
SysID and Trans_no have foreign key constraint on another table.
I have a location update SP
Create Proc spLocationupdate
@SysID as char(14),
@Trans_no as int,
@Location ID as int,
@AddressID as int,
@nameID as int
As
Update LOCATION
set AddressID = @AddressID,
set nameID = @nameID
where
SysID = @SysID and
Trans_no = @Trans_no and
Location_ID = @Location_ID
IN a multi instanced service , two services call the location update SP simultaneously.
From the trace I found out that
below sps are executed simultaneously and one is a victim of deadlock.
spLocationupdate '14500000000000',1,1,1,1
spLocationupdate '24500000000000',1,1,1,1
I am not getting any ideas for solving , please help.
There are only insert and delete triggers on the LOCATION table.
-
If the services run under the .Net code then this is the cause as default isolation level for .Net transaction are serializable and that would cause deadlocks. Change isolation level in procedure to read commited.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Suresh thakns for ur reply ... but I don't have begin and commint tran in my SP. Also the code is VB 5.0, a VB 6.0 service is accessing it.
-
What about indexes?
It would be helpful to have clustered index on (SysID, Trans_no, Location_ID)
_____________
Code for TallyGenerator -
yea it is having a clustered index on (SysID, Trans_no, Location_ID).
-
Seems impossible.
Different SysID values are trying to access different segments of the table, so they cannot block each other.
Or you don't show full query.
Or there is another, third query.
_____________
Code for TallyGenerator -
In VB code this there is an object corresponding to this table and that object is part of a very big Object with lots of Objects( each table for each object ) in it.
The problem occurs when a save is performed on the big object, it fires the Save to all the child objects and update SPs are fired for each child object.
But it fails always in this particular SP, don't know why ?
-
See if you can get traceflag 1204 switched on. That outputs the details of the deadlock to the error log, including both nodes and what resource they were deadlocked on.
If you can get that, then post the deadlock graph here.
One thought... How fragmented is that clustered index?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
spid3...
spid3Wait-for graph
spid3
spid3RID: 12:1:71827:36 CleanCnt:1 Mode: X Flags: 0x2
spid3Node:1
spid3
spid3Owner:0x56e46c60 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185 ECID:0
spid3Grant List 1::
spid3Requested By:
spid3Input Buf: RPC Event: spWcLocationUpdate;1
spid3SPID: 185 ECID: 0 Statement Type: UPDATE Line #: 47
spid3
spid3ResType:LockOwner Stype:'OR' Mode: U SPID:183 ECID:0 Ec
0x680F9548) Value:0x43spid3ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0x6803D548) Value:0x56spid3Victim Resource Owner:
spid3ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec
0x6803D548) Value:0x56spid3Requested By:
spid3Input Buf: RPC Event: spWcLocationUpdate;1
spid3SPID: 183 ECID: 0 Statement Type: UPDATE Line #: 47
spid3Owner:0x6facf1c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:183 ECID:0
spid3Grant List 1::
spid3RID: 12:1:71828:20 CleanCnt:1 Mode: X Flags: 0x2
spid3Node:2
spid3...
spid3
spid3Grant List 1::
spid3RID: 12:1:71828:34 CleanCnt:1 Mode: X Flags: 0x2
spid3Node:2
spid3
spid3ResType:LockOwner Stype:'OR' Mode: U SPID:146 ECID:0 Ec
0x5BDAF548) Value:0x70spid3Requested By:
spid3Input Buf: RPC Event: spWcLocationUpdate;1
spid3SPID: 99 ECID: 0 Statement Type: UPDATE Line #: 47
spid3Owner:0x6face060 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:99 ECID:0
spid3Grant List 1::
spid3RID: 12:1:71829:6 CleanCnt:1 Mode: X Flags: 0x2
spid3Node:1
spid3
spid3Wait-for graph
spid3Input Buf: RPC Event: spWcLocationUpdate;1
spid3SPID: 146 ECID: 0 Statement Type: UPDATE Line #: 47
spid3Owner:0x42bc9760 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:146 ECID:0
spid3Victim Resource Owner:
spid3ResType:LockOwner Stype:'OR' Mode: U SPID:99 ECID:0 Ec
0x5787D548) Value:0x6b8spid3Requested By:
spid3ResType:LockOwner Stype:'OR' Mode: U SPID:99 ECID:0 Ec
0x5787D548) Value:0x6b8 -
The lock requests are on a RID. That's not possible if there's a clustered index on the table in question. RIDs (Row Identifiers) only exist in heaps.
Can you post the definition of the table, with all its indexes that is updated on line 47 of the procedure spWcLocationUpdate.
Is that the full procedure that you posted in the initial post?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What you can try, as a last resort, is to add WITH (ROWLOCK) to the update statement. I don't normally suggest hints as SQL usually does a good enough job without.
Try adding it and see if it solves the deadlock.
UPDATE LOCATION WITH (ROWLOCK) SET .....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply