How to synchronize threads in Oracle?

user_2635209 0
Viewed 259

I have the following procedure

    procedure MyProc is
        n number;   
    begin
        -- Query A
        select count(*) into n from SomeTable where Column1 = 0;
        if n = 0 then 
            insert into SomeTable (Column1, Column2) values (0, 'some data');
        else        
            update SomeTable set Column2 = 'some other data' where Column1 = 0;
        end if;

        commit;
    end;

This procedure is run by several jobs in several threads :

    for i in 1..10      
    loop
            Jobname := dbms_scheduler.generate_job_name('JobName');            
            JobAction := 'begin MyProc; end;';                                                               
            dbms_scheduler.create_job(job_name => Jobname, job_type => 'PLSQL_BLOCK', job_action => JobAction, enabled => true);        
    end loop;

The goal is to create only one row in the table SomeTable and all the other jobs will update the same row...

When all the jobs are finished, I notice that sometimes several rows are created instead of only one.

I understood that whenever the Query A is executed, because of row locks, it will see only the rows of the table that were committed before the query started... hence some other jobs don't see the change...

Is there anyway to solve that please ?

In .Net there is a concept of Monitor.Enter & Monitor.Exit that makes all the other threads wait until a resource is released...

Can anyone help please ?

Thanks

plsql oracle
2 Answers

You could use a dbms_lock. Something like this:

procedure MyProc is
    n number;   
    h varchar2(200);
    r number;
begin
    -- Query A
    dbms_lock.allocate_unique('MyLock', h);
    r := dbms_lock.request(h, dbms_lock.x_mode); 

    select count(*) into n from SomeTable where Column1 = 0;
    if n = 0 then 
        insert into SomeTable (Column1, Column2) values (0, 'some data');
    else        
        update SomeTable set Column2 = 'some other data' where Column1 = 0;
    end if;

    commit;
    dbms_lock.release(h);
end;
user_3820090

You should take a look at transaction isolation level. Your key is which data will recover the first query running the select count. If you don't specify an ISOLATION LEVEL for your transaction, then by default is READ COMMITED, that allows PHANTOMS. The read committed transaction isolation level is the Oracle default. With this setting, each query can see only data committed before the query, not the transaction, began. Oracle queries do not read dirty, or uncommitted, data; however, it does not prevent other transaction from modifying data read by a query. Thus, it is possible that other transactions can change data between executions of the query. Any transaction that executes a given query more than once can experience non-repeatable reads or phantoms.

Other option is SERIALIZABLE. This transaction isolation level is not supported with distributed transactions. With the serializable transaction isolation level, only data that was committed at the start of the transaction plus those made by the transaction itself through INSERTs, UPDATEs and DELETEs can be accessed by a query.

Imagine this scenario

  • Job 1 enters and do the query. Imagine it gets 0, therefore enters by the if insert. then insert a row.
  • Job 2 enters at almost the same time, the counter is 0 if the job 1 did not commit yet.
  • Job 3 enters at almost the same time as job 2, the counter is 0 if the job 1 did not commit yet.

http://www.dba-oracle.com/t_oracle_isolation_level.htm https://docs.oracle.com/cd/E25054_01/server.1111/e25789/consist.htm#BABEAFAH

In your specific case, perhaps a solution might be using a CURSOR for the select using SELECT FOR UPDATE. It will lock the rows affected by the CURSOR. Until the cursor is close, the row won't be released.

user_137555380
Related