How to prevent same query result to multiple machines

[benjatado]

Hi,

I am working on a file processor that relies on the db to feed file records to different machines for processing.
The problem I am having is the same file record (row) is being sent to 2 different machines simultaneously.
In the db I have a "processing" column that flags the file record row as (Y/N). The app queries for file record rows = 'N' and updates the row's value to 'Y'.

However, since all machines can query the db, it's giving some the same file record (where processing = 'N') at once, canceling out the logic that prevents the machine from getting a file record that is "processing". It's as if the update is not fast enough or the row needs to be locked until the update/process is complete on it?

Is there a way to prevent the same record (row result) from being returned to more than one machine?

Thanks,

B

 

[Crowley16]

you could try housing your selects within a transaction and setting the transaction isolation level to read committed or higher.

that way, the particular record would be locked until the update has finished, assuming that's when you set the isProcessing flag.

Obviously, read up on transactions to be aware of the implications on your system.

--------------------
Procrastinate Now!

 

[benjatado]

Thanks!

However, I am experiencing some deadlocks now. I am trying to prevent these with READPAST, but I don't think this is helping?

Basically my query looks like this:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Begin Transaction
SELECT TOP 60  [ID]
FROM         Files with (readpast)
WHERE     Processing = 'N' 

UPDATE files set Processing = 'Y' where [ID] IN (SELECT TOP 60  [ID]
FROM         Files with (readpast)
WHERE     Processing = 'N' )
COMMIT
GO

It is selecting TOP 60 items and updating those items as "Processing". But I am getting a deadlock when I exec this at the same time.

Any ideas on why I may be getting deadlocks or is this standard? Also, if I put the first TOP 60 select statement as the parameters for the update, will this update the same items that were selected?

Thanks!!

 

[PDreyer]

If you don't use an order by then there is no guarantee you'll get the same set

No read lock will solve your problem
A read lock will not prevent other reads
and could get the same set

consider starting with an update
set the flag=1 -- for machine 1
you'll have an exclusive lock and no one else can read these

else have a look at the xlock hint