Re: deadlock when reading most currently inserted records

From: Toby Herring (therring*_at_*teletrack.com)
Date: 09/22/04 

Date: Wed, 22 Sep 2004 10:49:28 -0400

We have this type of problem frequently on our system.

Our solution is to use ROWLOCK hints on all INSERT, UPDATE, and DELETE
statements to prevent the server from escalating the locks to full pages.

Depending on your overall system design, you may need to ROWLOCK or NOLOCK
your SELECT statements, as well. Just make sure your system is designed
around the dirty read issue if you're going to use NOLOCK. 

-- 
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life? 
http://secondlife.com/ss/?u=03e0e5b303c234bf08e80ee40119a65e
"RJ" <RJ@PostOnly.com> wrote in message 
news:OQOSLSKoEHA.2588@TK2MSFTNGP12.phx.gbl...
>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