Oracle Concurrency Control with Delphi7

Question

We are faced with the problem of accessing Oracle rows in stages, the table schema is

myTable(UniqueNum,flag)

UniqueNum is a field where the number is not repeated, and a flag is a field that has a default value of F.

My steps in programming:

• Extract UniqueNum where flag F.

  select min(UniquaNum) 
    from myTable 
   where flag='F';
  

• Perform an operation.

Some operations (Communication on comport)

3. Set the flag to T.

   Update myTable 
      set flag='T' 
    where flag='F' 
      and UniqueNum= 'UN'
   

I developed an exe (Delphi-7) that performs these steps. The problem occurs when exe runs on multiple computers at the same time. Many exe get the same UniqueNum (step 1), but only one of these exe can complete all 3 steps.

Let's say PC1 selects 13and performs step 2, and at this time PC2 performs STEP 1, then it will also select 13. Then my step 3 will crash for PC2.

, - , ( )?

- , concurrency? Delphi #?

Answer 1

1 ".. nowait". , .

Answer 2

, , , - , 2, . 11 , for update skip locked. 9i, . :

UniqueNum, (UniqueNum is a field where number does not repeat - , ) , min(), order by UniqueNum.

next_min_number select min() from. ( min UniqueNum), , . , UniqueNum.

create or replace function next_min_number 
 return number
 is
   l_res number;
   row_locked exception;
   pragma exception_init(row_locked, -54);
 begin
    for i in (select un
                from t1
               where flag = 'F' /*of course this value can be passed as parameter */
               order by un ) 
    loop
      begin
        -- trying to lock the row
        select un
          into l_res
          from t1
         where un = i.un
         for update nowait;
         /*row of interest is locked, exit the loop*/
         exit;
       exception 
         when row_locked then null;
         when no_data_found then null;         
       end;     
    end loop;
    return l_res;
 end;

. F Flag      , Flag F, .

2: - `NULL ', .

:

:

create table t1(
  un number,
  flag varchar2(1)
);

insert into t1
  select level
       , 'F'
    from dual
   connect by level <= 3;

 commit;

select *
  from t1

       UN FLAG
---------- ----
         1 F   
         2 F   
         3 F 

№ 1:

 set serveroutput on;
 var min_num number;
 -- your program
 begin
     :min_num := next_min_number;
     dbms_output.put_line('Current min number: ' || to_char(:min_num));
     dbms_output.put_line('Updating...');
    end;
  /
Current min number: 1                                                           
Updating...                                                                     
PL/SQL procedure successfully completed.

№ 2

set serveroutput on;
var min_num number;
 -- your program
begin
    :min_num := next_min_number;
    dbms_output.put_line('Current min number: ' || to_char(:min_num));
    dbms_output.put_line('Updating...');
  end;
 /
Current min number: 2                                                           
Updating...                                                                     
PL/SQL procedure successfully completed

, .

.

# 1

 update t1
    set flag = 'T'
  where flag = 'F'
    and un = :min_num;

1 row updated.

commit;

# 2

 update t1
    set flag = 'T'
  where flag = 'F'
    and un = :min_num;

1 row updated.

commit;

:

select *
  from t1;

        UN FLAG
---------- ----
         1 T   
         2 T   
         3 F   

3 rows selected.

Answer 3

Oracle - .

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

SELECT customers_seq.nextval from DUAL;

, 1 . Select , .

,

function GiveNextID():integer;

.

As far as I remember, you could put it in the table definition as the default value for the column.