I have a requirement to generate gapless invoice and receipt number in our application. so i have currently used the below approach.
a. created table with a column to hold the invoice and receipt number sequence value.
b. whenever transaction gets succeed. i will lock the table which holds the invoice number and receipt number inorder to avoid the sequential number slipages.
Issue
1. since the application belongs to online payment through portal by customers, when concurrent users trying to pay and while generating receipt number's, i am facing with "resource busy time out" message frequently. Here i noticed when user1 locks the table to access the receipt number value and session is not committed or rollback and another session user2 trying to access the same resource, in this scenario i am facing this error.
Frequency of encountering this error is low, but customer was telling us this error is show stopper and affects normal business.
Is there any alternative solution or method can be applied to overcome this problem?
Current SQL used in application
cursor <cursor name> is.
select <column_name>
into <variable>
from <table name>
for update of wait 5
update <table name> set <column name> = value + 1
where current of <cursor name>