Get the first non-locked record.

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Tue, 20 Nov 2001 19:20:19 GMT
Message-ID: <3bfaab87.103696187@news.alt.net>

Imagine I have a table:

Item

---

Id (PRIMARY KEY)
Processing_Order (NOT NULL, UNIQUE)
Process (UNIQUE)

The table lists items that are processed in order.

More than one process will try to take an item at the same time. When a process gets an item, it places its name there, and deletes the record when the process finishes with the item.

The way to get the next item would be.

SELECT Id FROM Item WHERE Process IS NULL ORDER BY Processing_Order;

I'm assuming it should be SELECTed FOR UPDATE so as to get the record it actually thinks it has. But how does the second process skip the record the first one has currently LOCKED (if it didn't UPDATE it yet)? If I don't say NOWAIT it sits there, and if I do, it returns an error rather than going to the next record or returning NULL.

Brian Received on Tue Nov 20 2001 - 13:20:19 CST