deadlock between select (shared) and update (intent exclusive)

I regularly have deadlocks on my sql-server 2000. Using the 1204 trace
I got the following info about the problem:
Wait-for graph
Node:1
PAG: 7:1:251381 CleanCnt:2 Mode: S Flags: 0x2
Grant List 0::
Owner:0x2c959e00 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61
ECID:3
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:72 ECID:0 Ec0x50871568)
Value:0x76375e60 Cost0/5580)
Node:2
PAG: 7:1:230822 CleanCnt:2 Mode: IX Flags: 0x2
Grant List 3::
Owner:0x4bba49e0 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:72
ECID:0
SPID: 72 ECID: 0 Statement Type: INSERT Line #: 1
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:3 Ec0x2D8EA0C0)
Value:0x75df1780 Cost0/0)
As I understand it, one statement owns an IX-lock and requests another
one while another statement owns a shared-lock and requests another
one. I know, I should always access tables in the same order but it's
too late for this now.
How can I tell the select statement to read the last commited data and
not to lock anything? IMHO we did not give any lock-hints with our
statements so the default lock levels should be used. Does it make
sense that a select blocks an update?Update: it is not a select and an UPDATE but a select count and an
insert.|||
> As I understand it, one statement owns an IX-lock and requests another
> one while another statement owns a shared-lock and requests another
> one. I know, I should always access tables in the same order but it's
> too late for this now.
> How can I tell the select statement to read the last commited data and
> not to lock anything? IMHO we did not give any lock-hints with our
> statements so the default lock levels should be used. Does it make
> sense that a select blocks an update?
>
I do not pretend to understand your locking situation.
And although I thought in the past that a select should Not be partner
in a deadlock. This proved to be wrong.
My situation.
Update transaction (standard isolation), two updates on one single row.
The select was a very simple select which resulted in a single row of a
single table.
The combination could result in a deadlock.
The probable cause of 'my' problem.
Both updates used different where clauses, which resulted in the same row,
but
resulted in different locking situations.
In this situation you can NOT tel to read the last commited data, because
that is
locked at the moment. In SQL-server 2005 you can opt for snapshot isolation,
where the last commited data is read. So with snapshot isolation reads do
not block
write and writes do not block reads.
Be carefull with snapshot isolation because this does not implement
serializability.
Good luck with your situation,
If you have more information please post it here,
If you have more questions please post it here.
ben brugman|||mhuhn.de@.gmail.com,
This feature has been implemented in SQL Server 2005 (Snapshot Isolation).
If you are using 2000 and do not want to change the order in which you
access your tables, consider using a table_hint in your "select" statement,
specifically ROWLOCK based on the info you posted (the lock seems to be at
the page level). See BOL for more info.
AMB
"mhuhn.de@.gmail.com" wrote:

> Update: it is not a select and an UPDATE but a select count and an
> insert.
>|||Thanks for answering. Anyway, 2005 is not an option because our
solution is already used from lots of customers. Do you think a ROWLOCK
makes sense if I do a select count? If the where-clause in the select
count includes the updated row, I'll have the same problem, right?
Furthermore, it will slow down my selects!?|||As I wrote in my other mail :
I do not pretend to understand your locking situation.
But I doubt very much that a ROWLOCK in the select will
solve the problem. The select (without a rowlock) is allready
waiting for another process to finish, this waiting can (I think)
not be solved by using a ROWLOCK, the rowlock will
(probably) prevent the other process on locking on the read
process.
A (row)lock in the update might claim enough resources that
the select is not capable of applying a lock which can stop the
update. So the update can finish after which the select can finish.
ben brugman
<mhuhn.de@.gmail.com> wrote in message
news:1147960900.879319.169740@.j55g2000cwa.googlegroups.com...
> Thanks for answering. Anyway, 2005 is not an option because our
> solution is already used from lots of customers. Do you think a ROWLOCK
> makes sense if I do a select count? If the where-clause in the select
> count includes the updated row, I'll have the same problem, right?
> Furthermore, it will slow down my selects!?
>