deadlock when reading most currently inserted records

From: RJ (RJ_at_PostOnly.com)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 08:03:20 -0500

I have pair of batch processes continually and concurrently accessing the
same table. One is doing inserts of "unprocessed" records, the other is
selecting these freshly inserted "unprocessed" records. Immediately after
each select, the records in the result set are updated as "processed", using
an identity column as the key, and will be excluded from subsequent selects.

I periodically get deadlocks, so I restarted SQLserver with /T1204 to get
detailed information in the SQL log. What the log appears to show is the
select obtaining a shared lock, then the insert obtaining an IX lock at the
page level?. The select is using the default isolation level or Read
Commited, so I assumed it would return records that the insert has already
commited and "un-locked". This seems like it would be true if it were not
for lock promotion. Am I understanding the log correctly? Is there any
way to deal with this scenario, where the reads are "chasing" the inserts,
to avoid deadlocking? I've read what I find, but don't think that select
with (nolock) is going to solve the problem without causing other problems.
Thanks for any ideas.

Node 2 info below : PAG: 21:1:112 CleanCnt:1
Mode: IX Flags: 0x2

Log excerpt ~ 30 lines
Deadlock encountered .... Printing deadlock information
2004-09-21 14:56:59.74 spid4
2004-09-21 14:56:59.74 spid4 Wait-for graph
2004-09-21 14:56:59.74 spid4
2004-09-21 14:56:59.74 spid4 Node:1
2004-09-21 14:56:59.74 spid4 Port: 0x42bf0100 Xid Slot: 0, EC:
0x430a3588, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-09-21 14:56:59.74 spid4 SPID: 53 ECID: 0 Statement Type: SELECT
Line #: 31
2004-09-21 14:56:59.74 spid4 Input Buf: RPC Event:
SelectTransactionRecords;1
2004-09-21 14:56:59.74 spid4 Coordinator: EC = 0x430a3588, SPID: 53,
ECID: 0, Not Blocking
2004-09-21 14:56:59.74 spid4 Consumer List::
2004-09-21 14:56:59.74 spid4 Consumer: Xid Slot: 0, EC = 0x430a3588,
SPID: 53, ECID: 0, Not Blocking
2004-09-21 14:56:59.74 spid4 Producer List::
2004-09-21 14:56:59.74 spid4 Producer: Xid Slot: 1, EC = 0x6286c098,
SPID: 53, ECID: 1, Blocking
2004-09-21 14:56:59.74 spid4
2004-09-21 14:56:59.74 spid4 Node:2
2004-09-21 14:56:59.74 spid4 PAG: 21:1:112 CleanCnt:1
Mode: IX Flags: 0x2
2004-09-21 14:56:59.74 spid4 Grant List 2::
2004-09-21 14:56:59.74 spid4 Owner:0x42be0b00 Mode: IX Flg:0x0
Ref:2 Life:02000000 SPID:55 ECID:0
2004-09-21 14:56:59.74 spid4 SPID: 55 ECID: 0 Statement Type: INSERT
Line #: 24
2004-09-21 14:56:59.74 spid4 Input Buf: RPC Event:
InsertHostDataToTable;1
2004-09-21 14:56:59.74 spid4 Requested By:
2004-09-21 14:56:59.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:53 ECID:1 Ec:(0x6286C098) Value:0x42bca040 Cost:(0/0)
2004-09-21 14:56:59.74 spid4
2004-09-21 14:56:59.74 spid4 Node:3
2004-09-21 14:56:59.74 spid4 PAG: 21:1:19902 CleanCnt:1
Mode: S Flags: 0x2
2004-09-21 14:56:59.74 spid4 Grant List 0::
2004-09-21 14:56:59.74 spid4 Owner:0x42be1ce0 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:53 ECID:0
2004-09-21 14:56:59.74 spid4 Requested By:
2004-09-21 14:56:59.74 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:55 ECID:0 Ec:(0x60E9F590) Value:0x42bda320 Cost:(0/210)

Relevant Pages