What's the best practice or well known methods to implement sequence numbers for business entities such as invoices, purchase orders, job numbers, etc? I want to be able to save the latest value in the database and be able to set it programatically. Is it OK to use a table for this purpose that has a SEQUENCE_NAME, SEQUENCE_NUMBER tuple? I know some databases have a first class sequence type but others (eg, MySQL) do not so it's not something I want to rely on. If a table is used to hold these sequences what is the right way to get and increment them in a synchronized fashion to ensure no data inconsistencies arise?
A:
If being provider-agnostic is your primary goal then using a sequence table like you proposed is probably the most vanilla approach. You can use row locking, either with explicit locks or Select...For Update depending on your provider, to serialize access to the table.
BenV
2010-04-14 03:25:25