Multiple Instances of Application / Prevent SELECTing Same Row 1

[ResonantEcho]

I posted this earlier but not sure where it went. So... here I go again.

We have an application that runs every x minutes and looks for a row that is in a 'READY' status. A row represents a batch to be processed. We used to run one instance of the application. However, our volume has increased tremendously which requires us to run multiple instances to keep up. We noticed early on at times the same row (batch) would be grabbed by more than one instance causing the processed data to be duplicated.

Our current solution is to have each instance grab a different status (READY1, READY2, READY3, etc.). This is not ideal because we manually have to move them to these additional statuses.

The two queries within the application are as follows. The first query does a SELECT to see if there is a row in 'READY' status. If it finds one it immediatly does an UPDATE on that row and sets it to an 'ACTIV' status:

SELECT  TOP 1 columnid, column1, column2
FROM    table
WHERE   status = 'READY'

UPDATE  table
SET     status = 'ACTIV'
WHERE   columnid = <columnid>

What is the proper way to prevent multiple instances of an application from selecting the same row?

 

[SQLBill]

You might consider using the table hint WITH (ROWLOCK) to lock the row. If your SELECT and UPDATE are in the same transaction, you could make it WITH(HOLDLOCK, ROWLOCK) which will ensure the lock on the row is held until the transaction is complete.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[pjw001]

Not sure how easy it would be, but you could try updating the row first.
e.g.
UPDATE table
SET status = 'ACTIV'
WHERE columnid = the lowest column id where status = 'READY'

 

[genomon]

Paste this in the BOL Help search window and have a look at Isolation Levels (may be a fix): ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/016fb05e-a702-484b-bd2a-a6eabd0d76fd.htm

Beir bua agus beannacht!