deadlocks on updates

Question

Post reply

deadlocks on updates

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
June 5, 2006 at 1:15 pm

#91120

I have a case where I cannot find a solution and every hint is greatly appreciated.
I have the table bellow and the application which process 5 records at the time. This is the flow: Open transaction, Grab the first 5 records available, update their Owner, do some stuff with the text field affecting other tables in the database, then if all correct - update the lock_time and commit or rollback the transaction. I cannot change this logic.
There are 5-6 instances of this process running (5-6 processes will select 5 rows simultaneously). This situation works fast if there are about 10,000 rows in the table. The problem occurs when there are more then 100-150,000 rows in the tables - and I finally getting deadlocks.
Is there a way to prevent the deadlocking in this design?
Thanks a lot,mj
This is the statements which I use to update the records.
SET ROWCOUNT 5
UPDATE EXAMPLE
SET OWNER = 'ABC', LOCK_TIME = ?
WHERE (OWNER IS NULL OR LOCK_TIME < ?) Index:
CREATE INDEX [IX_EX_OWNER] ON [dbo].[EXAMPLE]([OWNER], [LOCK_TIME]) ON [PRIMARY]
This is the table:
CREATE TABLE [EXAMPLE]
([LOAD_SEQ] [smallint] NOT NULL ,
[OWNER] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCK_TIME] [bigint] NULL ,
[MSG] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

Have you tried using locking hints? I would test it with

UPDATE Example WITH(ROWLOCK, HOLDLOCK, READPAST)

This should tell each process to lock the five rows it is working with, hold the locks until the end of the transaction, and skip over rows locked by any other process.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 2

Yes, I did use the locking hints but I did not see any difference in the behavior.

Thanks a lot,

mj

vhanda SSCrazy Points: 2240 More actions · Answer 3

Have you tried using SQL profiler to attempt to capture the offending SQL statements?

Also, I would consider removing the existing composite index and adding separate ones for owner and lock_time. Composite indexes only work if the where clause in the query always uses the first column in the index. If you have the case where only the second column is referenced, the index is not used. This may lead to table scans and long lock times on updates.

Vik

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 4

Can we use READPAST for updates? I though it's only for select statements...

Thanks,mj

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

From BOL:

READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified.

Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.

Rodney West Grasshopper Points: 17 More actions · Answer 6

G'day,

Because you are using transactions, you can afford to be reasonably ruthless with the updates, if they fail, I'm assuming your app will pick up the missed records and reprocess them. In this case you could try setting a low lock_timeout value with 'SET LOCK_TIMEOUT 10', this would fail the current transaction if it can't get its requested locks within 10 milliseconds, and then move onto the next batch of 5 records.

Not an ideal situation obviously, indexing, application design and DB design should be smartly done too, but just a couple tricks to buy you some time.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 7

LOCK_TIMEOUT 10 did not work for me as if the first process times out, then the second process says - #1 is out and i need to quit... So, the whole application shuts down.

Also, I tried READ PAST on update and did not work - I do not know why BOL said that...

Thanks a lot,mj