Global Temporary Tables - locking rows + Concurrency question

Question

I have a list of 100 entries that I want to process with multiple threads. Each thread will take up to 20 entries to process.

I'm currently using global temp tables to store the entries that meet certain criteria -- I also do not want threads to overlap entries to process.

How do I do this (preventing the overlap)?

Thanks!

Answer 1

If on 11g, I'd use the SELECT ... FOR UPDATE SKIP LOCKED.

If on a previous version, I'd use Advanced Queuing to populate a queue with the primary key values of the entries to be processed, and have your threads dequeue those keys to process those records. Because the dequeue can (but doesn't have to be, if memory serves) within the processing transactional scope, the dequeue commits or rolls back with the processing, and no two threads can get the same records to process.