Geek asks and answers

Global temporary tables - row locking + concurrency issue

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

I am currently using global temp tables to store records matching certain criteria. I also do not want threads to overlap records for processing.

How to do this (overlap prevention)?

Thank!

+3
source share
3 answers

If on 11g I would use SELECT ... FOR UPDATE SKIP LOCKED.

Advanced Queuing , . dequeue ( , ) , dequeue , .

+5

, :

/?

Advanced Queuing SKIP LOCKED Adam.

, , STATE, , . :

UPDATE your_table 
   SET state='P'
 WHERE STATE IS NULL 
   AND rownum = 1
RETURNING id INTO :id;

. , .

, .

?

, Oracle ( ). , . , ( temp ).

, , . , , .

+2

The easiest way would be to use DBMS_SCHEDULER to schedule a job for each row that you want to process. You must pass the key to a permanent table to identify the row you want to process, or to put the full row in the arguments for the job, since the temporary contents of the table are not displayed in different sessions. The number of parallel jobs is controlled by a resource manager, mainly a limited number of processors.

Why do you want to process line by line? Setting operations is performed in most cases much faster.

0
source

More articles:


All Articles