(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.