Home » SQL & PL/SQL » SQL & PL/SQL » Account number selection from 10 parallel session (Oracle 11gR2)
Account number selection from 10 parallel session [message #674563] Fri, 01 February 2019 10:22 Go to next message
srinivas.k2005
Messages: 401
Registered: August 2006
Senior Member
Hi,

I have a requirement to generate 9 digit account number uniquely, cannot use the sequence as we have issues with it in Golden Gate sequence replication and Sequence without cache also may lose the number during any crash, we are not allowed to miss any digits.

So I decided to keep the pre-populated account numbers in a table as batches and populate in regular frequency and make it available for sessions.

We have 4 JVM triggers 10 threads(Parallel 10 threads, it can go to 100 parallel session also in prod) or more to fetch the account number from a pre-populated table, I need to control it in such a way that, the thread or session picks the account number and other thread does not pick the same and make sure to pick the next immediate number.

I started a sample as below


create table test_for_lock ( a number);
insert into test_for_lock values(1);
insert into test_for_lock values(2);
insert into test_for_lock values(3);
insert into test_for_lock values(4);
insert into test_for_lock values(5);
insert into test_for_lock values(6);
commit;

I thought I will use below in each Java thread call which will be a session, to use the number 

select min(a) from test_for_lock for update skip locked ;

Above throws errors but was checking how to log the min number and get it marked so that my other session does not touch that

Tried below, but as the min number will be the same for all sessions other session shows a blank record

select min(a) from test_for_lock where a = (select min(a) from  test_for_lock)
for update skip locked ;

After java gets the account number it inserts to the main table and then I am still thinking of deleting or marking some flag to that account as used 


Thanks
SRK
Re: Account number selection from 10 parallel session [message #674565 is a reply to message #674563] Fri, 01 February 2019 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Below is NOT a rhetorical scenario.
Assume a session requests & gets a new unique 9 digit account number & it is marked as "used".
A problem occurs with subsequent account creation & processing hit a fatal error which results in DML ROLLBACK.
How do you plan on going back and marking this account number as "available"?

IMO, the requirements devolve into a single threaded bottleneck which WILL negatively impact performance
Re: Account number selection from 10 parallel session [message #674567 is a reply to message #674565] Fri, 01 February 2019 10:56 Go to previous messageGo to next message
srinivas.k2005
Messages: 401
Registered: August 2006
Senior Member
Hi BlackSwan,

Sorry if I have not understood you correctly.

Assume a thread/ session triggers from JAVA :

It runs a lock on a single record for the pre-populated table, fetches the min number and keeps the record locked for other sessions
Insert the account number into the main table
Updates the pre-populated table with the flag as used
Then Commit

If DML rollback happens the account number will be still available for other sessions as the minimum account number.

Agree with you on the performance impact, as it locks a single record and shared connection pool will be busy stuck with it until commit is done, is there any solution with collections or any new feature which can suffice this requirement

Thanks,
SRK
Re: Account number selection from 10 parallel session [message #674568 is a reply to message #674567] Fri, 01 February 2019 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
please post working code that demonstrates how to lock & unlock a single row.
also demonstrate how this single row lock is respected by other sessions.
Re: Account number selection from 10 parallel session [message #674575 is a reply to message #674568] Mon, 04 February 2019 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I keep getting the feeling that you've massively missed the point of these forums BS. If the OP knew how to do that he wouldn't be posting here.

@srinivas - something like this should work:
DECLARE

  row_locked EXCEPTION;
  PRAGMA EXCEPTION_INIT(row_locked, -54);

  l          test_for_lock.a%TYPE;
  
BEGIN
  
  <<lp>>
  FOR rec IN (SELECT a FROM test_for_lock ORDER BY a) LOOP
    
    BEGIN
      
      SELECT a INTO l
      FROM test_for_lock
      WHERE a = rec.a
      FOR UPDATE NOWAIT;
      
      DELETE FROM test_for_lock WHERE a = rec.a;
      
      EXIT lp;
      
    EXCEPTION WHEN row_locked THEN
      NULL;
    END;
    
  END LOOP;
  
  dbms_output.put_line('LOCKED ROW '||l);
  
END;

If you make test_for_lock an index organized table then the performance shouldn't be too bad. I stuck 32 million rows in a table and was getting response times of < 0.1 seconds
Re: Account number selection from 10 parallel session [message #674589 is a reply to message #674575] Mon, 04 February 2019 08:09 Go to previous messageGo to next message
srinivas.k2005
Messages: 401
Registered: August 2006
Senior Member
Thanks Cookiemaster.

I modified to a function to replicate the multi-thread scenario, what i observed was when i trigger 100 sessions i get only 67 session numbers and could not trace the 33 sessions when i have an update in the function and get 100 sessions if it is a delete case. Not sure what is the difference between delete and update, i have the complete case below with scripts.
Can you help to understand the difference.



---Standard tables

drop table log_table;

create table log_table(a varchar2(1000), dt timestamp default SYSTIMESTAMP); 

alter table  log_table add  sess_id number;

DROP TABLE bsb_acct_pre_populated;

CREATE TABLE bsb_acct_pre_populated
(bsb_account_no  NUMBER(9)     NOT NULL,
 used_flag       VARCHAR2(6)   DEFAULT 'N' CONSTRAINT check_used_flag  CHECK (used_flag IN ('Y','N')),
 created_date    DATE          DEFAULT SYSDATE,
 created_user    VARCHAR2(100) DEFAULT USER,
 updated_date    DATE          DEFAULT SYSDATE,
 updated_user    VARCHAR2(100) DEFAULT USER,
 CONSTRAINT pk_bsb_account_no PRIMARY KEY (bsb_account_no) 
)
ORGANIZATION INDEX;


---Populate with records

begin
FOR i in 1..1000000 LOOP
INSERT INTO bsb_acct_pre_populated(bsb_account_no)
values (i);
commit;
end loop;
end;
/

---Function with update

CREATE OR REPLACE FUNCTION fn_get_bsb_number_upd RETURN VARCHAR2
IS
  row_locked        EXCEPTION;
  PRAGMA            EXCEPTION_INIT(row_locked, -54);
  v_bsb_account_no  bsb_acct_pre_populated.bsb_account_no%TYPE;
  v_err             VARCHAR2(1000);
  
 /***********************************************************
 Description
 
 ************************************************************/ 
BEGIN
  
  <<lp>>
  FOR rec IN (SELECT bsb_account_no 
              FROM   bsb_acct_pre_populated 
              WHERE  used_flag = 'N' 
              ORDER BY bsb_account_no
             ) 
  LOOP
    
    BEGIN
    
    insert into log_table(a,sess_id) values ('BEFORE SELECT'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));
      
      SELECT bsb_account_no 
      INTO   v_bsb_account_no
      FROM   bsb_acct_pre_populated
      WHERE  bsb_account_no = rec.bsb_account_no
      FOR UPDATE NOWAIT;
      
       insert into log_table(a,sess_id) values ('AFTER SELECT'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));
      
    UPDATE bsb_acct_pre_populated
      SET    used_flag = 'Y'
            ,updated_date = SYSDATE
            ,updated_user = USER
      WHERE bsb_account_no = rec.bsb_account_no;   
      

      
      insert into log_table(a,sess_id) values ('DELETE'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));       
      
      EXIT lp;
      
    EXCEPTION 
    WHEN row_locked THEN
      NULL;
    WHEN OTHERS THEN
    ROLLBACK;
    v_err := SQLCODE||','||SQLERRM;
    insert into log_table(a,sess_id) values ('IN'||v_bsb_account_no||','||v_err,Sys_Context('USERENV', 'SESSIONID'));
    commit;
    END;
    
  END LOOP;
  
  COMMIT;
  
  RETURN TO_CHAR(v_bsb_account_no, 'FM000000000');
  
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err := SQLCODE||','||SQLERRM;
    insert into log_table(a) values ('IN'||v_bsb_account_no||','||v_err);
    commit;
END fn_get_bsb_number_upd;
/



---Function with delete

CREATE OR REPLACE FUNCTION fn_get_bsb_number_del RETURN VARCHAR2
IS
  row_locked        EXCEPTION;
  PRAGMA            EXCEPTION_INIT(row_locked, -54);
  v_bsb_account_no  bsb_acct_pre_populated.bsb_account_no%TYPE;
  v_err             VARCHAR2(1000);
  
 /***********************************************************
 Description
 
 ************************************************************/ 
BEGIN
  
  <<lp>>
  FOR rec IN (SELECT bsb_account_no 
              FROM   bsb_acct_pre_populated 
              WHERE  used_flag = 'N' 
              ORDER BY bsb_account_no
             ) 
  LOOP
    
    BEGIN
    
    insert into log_table(a,sess_id) values ('BEFORE SELECT'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));
      
      SELECT bsb_account_no 
      INTO   v_bsb_account_no
      FROM   bsb_acct_pre_populated
      WHERE  bsb_account_no = rec.bsb_account_no
      FOR UPDATE NOWAIT;
      
       insert into log_table(a,sess_id) values ('AFTER SELECT'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));
      
     
       DELETE FROM bsb_acct_pre_populated
      WHERE bsb_account_no = rec.bsb_account_no; 
      
      insert into log_table(a,sess_id) values ('DELETE'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));       
      
      EXIT lp;
      
    EXCEPTION 
    WHEN row_locked THEN
      NULL;
    WHEN OTHERS THEN
    ROLLBACK;
    v_err := SQLCODE||','||SQLERRM;
    insert into log_table(a,sess_id) values ('IN'||v_bsb_account_no||','||v_err,Sys_Context('USERENV', 'SESSIONID'));
    commit;
    END;
    
  END LOOP;
  
  COMMIT;
  
  RETURN TO_CHAR(v_bsb_account_no, 'FM000000000');
  
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err := SQLCODE||','||SQLERRM;
    insert into log_table(a) values ('IN'||v_bsb_account_no||','||v_err);
    commit;
END fn_get_bsb_number_del;
/

----Span multiple sessions -- 100 sessions for update

declare
l_jobname       varchar2(255);
BEGIN

For i in 1..100 loop

 l_jobname := dbms_scheduler.generate_job_name;
      
      dbms_scheduler.create_job
      (
        job_name            => l_jobname,
        job_type            => 'PLSQL_BLOCK',
        job_action          => 'Declare
                                a varchar2(9);
                                begin
                                a := fn_get_bsb_number_upd;
                                end;',
        comments            => 'Background process submitted',
        enabled             => true
      );
END loop;      
END;   

--I run below to check if all 100 threads used one number, but it gives me 67 or 70 only 

select count(1) from bsb_acct_pre_populated where used_flag = 'Y';

70

---I rollback all flag to N

update bsb_acct_pre_populated
set used_flag = 'N'
where used_flag = 'Y'

commit;

----Span multiple sessions -- 100 sessions for delete

declare
l_jobname       varchar2(255);
BEGIN

For i in 1..100 loop

 l_jobname := dbms_scheduler.generate_job_name;
      
      dbms_scheduler.create_job
      (
        job_name            => l_jobname,
        job_type            => 'PLSQL_BLOCK',
        job_action          => 'Declare
                                a varchar2(9);
                                begin
                                a := fn_get_bsb_number_del;
                                end;',
        comments            => 'Background process submitted',
        enabled             => true
      );
END loop;      
END;   

--I run below to check if all 100 threads used one number, this gives me 100 

select count(1) from bsb_acct_pre_populated where used_flag = 'Y';

100

I have a log table which i am not able to interpret why update misses number and not the delete

[Updated on: Mon, 04 February 2019 08:26]

Report message to a moderator

Re: Account number selection from 10 parallel session [message #674590 is a reply to message #674589] Mon, 04 February 2019 08:30 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why did you replace NOWAIT in my code with SKIP LOCKED?

Nowait causes -54 to be thrown if the row is locked and my code is built on the assumption that will happen.
The whole point of Skip Locked, on the other hand, is to avoid locked rows, and so avoid -54.

If two sessions run that code at the same time then the following will happen:
session 1 gets value 1 from the for loop
session 2 gets value 2 from the for loop
session 1 runs the select that locks the row
session 2 runs the select that locks the row, but because session 1 has just locked it it finds nothing and throws a no_data_found
session 1 completes and returns value 1
session 2 ends up in the when others exception handler and then returns null.

Also the insert into log table should be in a seperate procedure that's an autonomous_transaction.
Re: Account number selection from 10 parallel session [message #674592 is a reply to message #674590] Mon, 04 February 2019 09:09 Go to previous messageGo to next message
srinivas.k2005
Messages: 401
Registered: August 2006
Senior Member
Why did you replace NOWAIT in my code with SKIP LOCKED?
--Sorry i used NOWAIT in my code, it was copy paste issue as i was trying it with SKIP LOCKED. Below worked sample is with NOWAIT, updated the below code.

Nowait causes -54 to be thrown if the row is locked and my code is built on the assumption that will happen.
The whole point of Skip Locked, on the other hand, is to avoid locked rows, and so avoid -54.

If two sessions run that code at the same time then the following will happen:
session 1 gets value 1 from the for loop
session 2 gets value 2 from the for loop
----I assume you meant it as value 1 if it was valued 2 it would go smoothly for session 2
session 1 runs the select that locks the row
---Locks the row with value 1
session 2 runs the select that locks the row, but because session 1 has just locked it finds nothing and throws a no_data_found
---Try to lock the row with value 1, as it is already locked it throws error
session 1 completes and returns value 1
session 2 ends up in the when others exception handler and then returns null.
--After it goes to when others it insert to log table and continues the loop to fetch next number is my understanding, hence did not understand how number can skip or the thread can go waste.

Also the insert into log table should be in a separate procedure that's an autonomous_transaction.
---Sure, below was just to test i will make sure to use autonomous transactions
Re: Account number selection from 10 parallel session [message #674595 is a reply to message #674592] Mon, 04 February 2019 10:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
The problem is probably read consistency/ read committed
If you run a query that selects all the rows in table, and while the query is running some other session updates one of the rows the original query ignores the change.

The fact that you're committing where you are doesn't help.

Starting with no numbers used
session 1 runs for loop - will return all rows
session 1 selects and locks row 1
session 1 updates row 1
session 2 runs for loop - will return all rows - session 1 has updated row 1, but it's not committed yet so session 2 ignores that.
session 1 commits;
session 2 selects and locks row 1 - it can because session 1 has released the lock.

You don't get this with delete because when session 2 tries to lock row 1 it finds it's no longer there and goes to the exception handler.
You can avoid the issue by checking the flag when you do the select for update.

And you need to use autonomous_transaction now. This code relies on transactions working a certain way, your logging relies on constant commits, which makes the problems with your code occur more often.

Suggested revised code:
CREATE OR REPLACE FUNCTION fn_get_bsb_number_upd RETURN VARCHAR2
IS
  row_locked        EXCEPTION;
  PRAGMA            EXCEPTION_INIT(row_locked, -54);
  v_bsb_account_no  bsb_acct_pre_populated.bsb_account_no%TYPE;
  v_err             VARCHAR2(1000);
  v_used_flag       bsb_acct_pre_populated.used_flag%TYPE;
  
 /***********************************************************
 Description
 
 ************************************************************/ 
BEGIN

  --Loop over all potentially unused rows (some may get used while this is running)  
  <<lp>>
  FOR rec IN (SELECT bsb_account_no 
              FROM   bsb_acct_pre_populated 
              WHERE  used_flag = 'N' 
              ORDER BY bsb_account_no
             ) 
  LOOP
    
    BEGIN
    
      logging_procedure ('BEFORE SELECT'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));
      
      SELECT bsb_account_no,
             used_flag
      INTO   v_bsb_account_no,
             v_used_flag
      FROM   bsb_acct_pre_populated
      WHERE  bsb_account_no = rec.bsb_account_no
      AND used_flag = 'N' --only want it if it's not used
      FOR UPDATE NOWAIT;

      logging_procedure ('AFTER SELECT'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));
      
      UPDATE bsb_acct_pre_populated
      SET    used_flag = 'Y'
            ,updated_date = SYSDATE
            ,updated_user = USER
      WHERE bsb_account_no = rec.bsb_account_no;   

      logging_procedure ('UPDATE'||rec.bsb_account_no,Sys_Context('USERENV', 'SESSIONID'));       
      
      EXIT lp;
      
    EXCEPTION 
    WHEN row_locked THEN
      --someone else has got it
      NULL;
    WHEN no_data_found THEN
      --used_flag is not N or been deleted in the meantime
      NULL;
    END;
      
  END LOOP;
  
--  COMMIT;   DO NOT COMMIT IN HERE, this whole process only works if this only gets committed when the parent transaction does
  
  RETURN TO_CHAR(v_bsb_account_no, 'FM000000000');
  
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK; DON'T ROLLBACK EITHER, let the parent transaction do that
logging_procedure (SQLERRM,Sys_Context('USERENV', 'SESSIONID'));       
--v_err := SQLCODE||','||SQLERRM; --SQlerrm contains SQLCODE, so doing that is pointless

    --commit; NEVER, EVER, COMMIT IN A WHEN OTHERS - it's a great way to make a mess of your data
END fn_get_bsb_number_upd;
/

I post that code, however - I wouldn't do the update, if you've got a lot of values it'll cause ever increasing performance problems as the code as to work it's way past all the used values to get to the unused ones. Set the first 500,000 rows to used and see how long it takes.

Use delete instead - then it'll only ever have to look at the first few rows.

If you really absolutely have to have details about when a sequence was used and who by - insert the data into a different table.
Re: Account number selection from 10 parallel session [message #674597 is a reply to message #674595] Mon, 04 February 2019 10:16 Go to previous message
srinivas.k2005
Messages: 401
Registered: August 2006
Senior Member
Thanks, Cookiemonster, now I understood. Yes, there is a requirement to keep track of the used sequence, I will go with insert the data into a different table.
Previous Topic: table export to excel
Next Topic: Need help to Schedule Job to Purge dba_audit_trails along with fga_audit_trail
Goto Forum:
  


Current Time: Mon Sep 28 09:40:53 CDT 2020