Как синхронизировать выполнение процедуры в разных заданиях (сессиях)?

Question

Есть такая процедура:

create or replace procedure myproc is
    n number;   
begin
    -- Query A
    select count(*) into n from sometable where Col1 = 0;
    if n = 0 then 
        insert into sometable values (0, 'some data');
    else        
        update sometable set Col2 = 'some other data' where Col1 = 0;
    end if;
    commit;
end;

Запускаю её в задании, то есть она выполняется параллельно в различных сессиях:

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

Хочу добиться, чтобы вставить только одну строку в таблице SomeTable, а все остальные задания будут обновлять эту же строку. Это работает, но иногда замечаю, что когда все задания завершены, то создается несколько строк вместо одной.

Я понял, что всякий раз, когда выполняется запрос Query A, из-за блокировок строк, он будет видеть только те строки таблицы, которые были зафиксированы до начала запроса. Следовательно, другие задания не видят изменения.

Есть ли, несмотря на выше изложенное, какое-то решение?

В .Net существует концепция Monitor.Enter* и Monitor.Exit, которая заставляет все остальные потоки ждать, пока ресурс не будет отпущен.

_{Свободный перевод вопроса How to synchronize threads in Oracle? от участника @Thomas Carlton}

Answer 1

Для этого можно воспользоваться DBMS_LOCK. Измените процедуру как-то так:

create or replace procedure MyProc is
    n number;   
    h varchar2(200);
    r number;
begin
    dbms_lock.allocate_unique ('MyLock', h);
    --Query A
    r := dbms_lock.request (h, dbms_lock.x_mode); 
    select count(*) into n from SomeTable where Col1 = 0;
    if n = 0 then 
        insert into SomeTable (Col1, Col2) values (0, 'some data');
    else        
        update SomeTable set Col2 = 'some other data' where Col1 = 0;
    end if;
    commit;
    if dbms_lock.release (h) != 0 then null;
        /* do someting with error */ 
    end if;    
end;

_{Свободный перевод ответа от участника @hol}

Answer 2

Если возможно сделать колонку Col1 уникальной, то задача упростится:

alter table sometable modify (Col1 number unique)
/

Так при первой вставке будет заблокирована только одна запись. Если же первая вставка уже зафиксирована, то последующие должны быть игнорированны. Процедура будет выглядит так:

create or replace procedure MyProc (name varchar2) is
begin
    insert /*+ ignore_row_on_dupkey_index (SomeTable (Col1)) */ 
        into SomeTable values (0, 'creator:'||name);
    if sql%rowcount = 0 then          
        update SomeTable set Col2=Col2||'; changer:'||name
        where Col1 = 0;
    end if;     
end;
/

После выполнения 10 заданий, получим ожидаемый результат:

      COL1 COL2                                                        
---------- ------------------------------------------------------------
         0 creator:MYJOB4370; changer:MYJOB4388; changer:MYJOB4372;    
           changer:MYJOB4374; changer:MYJOB4380; changer:MYJOB4376;    
           changer:MYJOB4378; changer:MYJOB4382; changer:MYJOB4384;    
           changer:MYJOB4386

БЛОГ НА HUSL

Как синхронизировать выполнение процедуры в разных заданиях (сессиях)?

Ответы (2 шт):