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 |
srinivas.k2005
Messages: 404 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 #674567 is a reply to message #674565] |
Fri, 01 February 2019 10:56 |
srinivas.k2005
Messages: 404 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 #674575 is a reply to message #674568] |
Mon, 04 February 2019 03:59 |
cookiemonster
Messages: 13925 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 |
srinivas.k2005
Messages: 404 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 |
cookiemonster
Messages: 13925 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 |
srinivas.k2005
Messages: 404 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 |
cookiemonster
Messages: 13925 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.
|
|
|
|
Goto Forum:
Current Time: Tue Jun 18 00:50:11 CDT 2024
|