complex locking and sequence generation

Hi,

I need to generate sequences that are sequential , even with multiple users.

that is to say , I cannot allow any gaps in the sequence , caused by a user not committing a transaction.

That would appear to rule out oracle generated sequences.

my sequence consists of:

yyyy-mm-dd- rev

where revision is a file root index (A-Z), there would not be more than 10 file generations , in a day, due to the nature of the application , but it may be that upto 10 staff generate a root index a day.

my key index key would be:

 PRODFIL-yyyy-mm-dd-rev

basically I have the following sql

begin lock table bs_product_file_details IN SHARE MODE; insert into BS_PRODUCT_FILE_DETAILS
(PRDFILMASTER_KEY,REVISION)

values ('PRF0EMWOT2Q1',
(select decode (max(trunc(born)) ,null,'A',
(decode(

max(revision),null,'A','Z','BAD',CHR(LTRIM(ASCII(substr(max(revision),1,1)))+1 ))))
from BS_PRODUCT_FILE_DETAILS where deleted=0 and trunc(born)=trunc(sysdate))
);
end;

what i am attempting to do is the following:

1. ensure no other user can generate a key at the same time
2. find the date from the table, where the date is the same as todays date.
3. if there is no date , set the revision to "A"
4. if there is a date for today, get the current file revision and increment it by one. 5, if the latest revision for today is already "Z" cause a "value too large error"
5. insert the new details into the table and commit.

the whole process, should take no longer than a few seconds, as the user cannot interrupt the transaction, however in the unlikely event that 2 or more users should generate a new file revision at exactly the same time, I need to trap it.

The only other way of trapping it I can think of , would be to take the date , convert it to a string add the revision onto the end, and then store it into the database as a unique index, that would then cause a constraint error , if 2 users tried to generate a revision at exactly hte same time.

any ideas

Steve
] Received on Sun May 28 2006 - 03:44:36 CDT