I need to generate 7-digit, unique, random numeric quasi-serial number. I'll generate these serial numbers (SN) within a buffered transaction that may or may not be finalized. So, the SN generated won't be written to disk right away. Therefore, I can't use the usual primary key routine most of use. I want to find an efficient system that won't add any (!) processing time to the transaction overall.

I can keep a table of just serial numbers generated and not reuse any that are in that table. However, it's possible that a number will be generated but not used.

I could, when I finalize the transaction, delete the unused SN record.

These sorts of transactions aren't frequent, but I'd like to be as efficient as possible. Plus, I was just sort of interested in the complexity using randomly generated numbers in addition to generating them in buffered transactions adds to using the usual PK routines.

TIA for any thoughts.