Dealock Statement Type SELECT

Hi guys,
I have two stored procs involved. One of them doesn't have a single
modification statement in there nor it calls any other stored procs.
Objects involved in the deadlock are clustered and non- clustered inexes
of the same table. Statement types are SELECT and DELETE.
I guess, my question is, how can a stored proc not making any database
chages get involved in a deadlock?
Below is the trace from the log file:
Wait-for graph
Node: 1
KEY: 8:2135066742:1 (20006e2f6030) CleanCnt:2 Mode: U Flags: 0x0
Grant List 0::
Grant List 1::
Owner: 0x459ae620 Mode: S Flg: 0x0 Ref: 1 Life: 00000000 SPID:
121 ECID: 0
SPID: 121 ECID: 0 Statement Type: SELECT Line #: 171
Input Buf: RPC Event: WF_GetJobProperties; 1 --NOT A SINGLE MOD
STATEMENT IN THIS PROC!!!
Requested By:
ResType: LockOwner Stype:'OR' Mode: X SPID: 90 ECID: 0 Ec :( 0xCC0BB570)
Value: 0x1f9485e0 Cost :( 0/3CB0)
Node: 2
KEY: 8:2135066742:2 (c900b92bd60a) CleanCnt: 2 Mode: X Flags: 0x0
Grant List 0::
Owner: 0x47875c40 Mode: X Flg: 0x0 Ref: 0 Life: 02000000 SPID: 90
ECID: 0
SPID: 90 ECID: 0 Statement Type: DELETE Line #: 52
Input Buf: RPC Event: WF_UpdateRepository; 1
Requested By:
ResType: LockOwner Stype:'OR' Mode: S SPID: 121 ECID:0 Ec :( 0x4CA11570)
Value: 0x843a74c0 Cost :( 0/0)
Victim Resource Owner:
ResType: LockOwner Stype:'OR' Mode: S SPID: 121 ECID: 0 Ec :(
0x4CA11570) Value: 0x843a74c0 Cost :( 0/0)
Your help is greatly appreciated.
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***Take a look at this msdn article (SQL Server technical bulletin - How
to resolve a deadlock) http://support.microsoft.com/?kbid=832524|||"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:erVENq6cGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> I have two stored procs involved. One of them doesn't have a single
> modification statement in there nor it calls any other stored procs.
> Objects involved in the deadlock are clustered and non- clustered inexes
> of the same table. Statement types are SELECT and DELETE.
> I guess, my question is, how can a stored proc not making any database
> chages get involved in a deadlock?
>
SELECT takes a shared lock 'S' on every key or row it reads. You might read
an index key, locate a row of interest, and then go read that row. You
might place a S lock on the index key, then attempt to place a S lock on the
data row. But a concurrent UPDATE might own an X lock on the data row, and
be attempting to take an X lock on the index key that the SELECT has already
read and locked. Deadlock.
In SQL Server 2005 READ CONCURRENT SNAPSHOT ISOLATION eliminates the need
for the SELECT statement to take S locks in the first place. Instead they
read from the version store to get a consistent view of the database.
David