Deadlock - Key and RID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deadlock - Key and RID

Author

Topic

PrateekGupta01
Starting Member

2 Posts

Posted - 2008-04-13 : 02:27:12

Hi Guys

I am getting a deadlock in my DB almost daily now. The deadlock is generally between RID and KEY. Which confuses me more because all the operations that happen on this table are using the unqiue index only i.e. Each select/insert/update only affects one row.

Following is the output of 1204 trace.

Deadlock encountered .... Printing deadlock information
2008-04-02 15:39:08.92 spid4
2008-04-02 15:39:08.92 spid4 Wait-for graph
2008-04-02 15:39:08.92 spid4
2008-04-02 15:39:08.92 spid4 Node:1
2008-04-02 15:39:08.92 spid4 KEY: 5:1330103779:2 (2803833d27c3) CleanCnt:2 Mode: U Flags: 0x0
2008-04-02 15:39:08.92 spid4 Grant List 3::
2008-04-02 15:39:08.92 spid4 Owner:0x3629fee0 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:115 ECID:0
2008-04-02 15:39:08.92 spid4 SPID: 115 ECID: 0 Statement Type: DELETE Line #: 1
2008-04-02 15:39:08.92 spid4 Input Buf: RPC Event: sp_execute;1
2008-04-02 15:39:08.92 spid4 Requested By:
2008-04-02 15:39:08.92 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:108 ECID:0 Ec:(0x3432B508) Value:0x99806380 Cost:(0/0)
2008-04-02 15:39:08.92 spid4
2008-04-02 15:39:08.92 spid4 Node:2
2008-04-02 15:39:08.92 spid4 RID: 5:1:48452:0 CleanCnt:2 Mode: U Flags: 0x2
2008-04-02 15:39:08.92 spid4 Grant List 0::
2008-04-02 15:39:08.92 spid4 Owner:0x9943d100 Mode: S Flg:0x0 Ref:0 Life:02000000 SPID:108 ECID:0
2008-04-02 15:39:08.92 spid4 SPID: 108 ECID: 0 Statement Type: DELETE Line #: 1
2008-04-02 15:39:08.92 spid4 Input Buf: RPC Event: sp_prepexec;1
2008-04-02 15:39:08.92 spid4 Grant List 3::
2008-04-02 15:39:08.92 spid4 Requested By:
2008-04-02 15:39:08.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:115 ECID:0 Ec:(0x98ED1508) Value:0x94449fe0 Cost:(0/5C)
2008-04-02 15:39:08.92 spid4 Victim Resource Owner:
2008-04-02 15:39:08.92 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:108 ECID:0 Ec:(0x3432B508) Value:0x99806380 Cost:(0/0)

Please provide your inputs on what could be the cause of this.

Following is the schema for the table
create table USER_LOGIN_TABLE
(
BANK_ID char(9),
USER_ID char(65),
SESSION_ID char(32),
R_MOD_TIME datetime,
R_CRE_TIME datetime
)
go

create view LGIN as select * from USER_LOGIN_TABLE
go

create unique index IDX_LGIN
on USER_LOGIN_TABLE( bank_id, user_id )
go

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-13 : 15:51:53

Dup post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100851

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources