RE: deadlock when reading most currently inserted records

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 06:27:02 -0700

Hi

Do yoou have a clustered index on that table, is it on the identity column?

I have found in similar scenarios, having one results in better behaviour as
one process always remains 'behind' the other.

Regards
Mike

"RJ" wrote:

> 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