Centralized database: multiple consumers

Question

I have one table with starting data and multiple consumer applications working on different machines in local network.

Please suggest how do I can orginize them to take each record from this database only once, so there will no be any situation where 2 or more apps taken the same record from the table.

I see 2 possible ways to resolve it, but not sure:

1. One more app that would receive requests from consumer apps and give them unique non-processed records,
2. Implement some stored procedure in database...

Please advise..

Answer 1

To retrieve the row, create a stored procedure that:

• starts a transaction
• selects and locks a row that is available
• updates a column of the row to mark it unavailable for others
• commits the transaction
• passes the row to the client

To update the row, create a stored procedure that:

• begins a transaction
• updates the row (optionally check the rowversion)
• updates the availability of the row
• commits the transaction

Extra's:

• you might want to add a date/time to the row so you'll know when a row was locked
• if a row has been locked for a long time (longer than you expected) you could free the lock and let someone else select the row. However, to prevent the previous owner from updating (he doesn't know he lost the lock) you should have a rowversion column in the table.
• you could keep the (un)available status in a separate table that might make it easier to find available rows.