(Design Idea) Best Practise to reserve record for multi-process workflow engine

Question

I'm designing multiple process to query job from database.

Each job wake up once per minute to query task and send to workflow system.

I need advice about which best way to mask record and query it and not duplicate with other process.

Answer 1

In Oracle, depending on version:

10g and down -- use Advanced Queuing. Have your job dequeue the keys that you've had enqueued.

11g and up -- if you don't want the hassle of queuing, you can use the SKIP LOCKED clause and have your job SELECT FOR UPDATE the task it's to work on; think of it as queuing without having to make PL/SQL calls.

Answer 2

assuming you have control over the database design, wouldn't you just include flag of some kind? You could just have a yes/no kind of flag, which might be too basic (?). Alternatively a more detailed approach that recorded the identity of the process which "has" the Record / Job. This would give you a richer set of data and a more flexible application.