Maintain a pure sequence


Let's use the example of maintaining an invoice number where no "holes" in the series is allowed.

First of all, use a surrogate key that doesn't matter if it is sequential or not as your primary key and put your generator on that via GeneratorLinks. If the user cancels the insert and there is a skipped value in this column it won't be a big deal. Call it the InvoiceID and call the other column the InvoiceNo.

Then, in a separate subsequent transaction (as a part of printing the invoice?), make it a simple guaranteed assignment/update of InvoiceNo from a generator. Make this transaction a special one configured with ServerAutoCommit=true, Isolation=tiCommitted, RecVersion=false and LockWait=true. Also make the update statement include WHERE InvoiceNo IS NULL so that it won't pick up the record if another quickly tries to operate on the same record. Check the RowsAffected property to make sure it actually performed the edit. This is a special combination that makes it bulletproof in a multi-user environment.

After it prints set it again to indicate that it has been printed in a similar manner.

ServerAutoCommit makes it so that once the client initiates it it will commit exactly as it happens on the server without having to wait for the client to tell it to commit. Thus removing a potential point of failure.

The combination of NoRecVersion and LockWait prevents a deadlock exception. This will make it wait until any pending transactions are actually finished with the record of interest before it reads it and makes the update. Otherwise, it would read the record, execute the update, before update event triggered, generator incremented, deadlock encountered, update cancelled, value in sequence lost.

I know this is brief but I hope it is enough to help solve this problem. It is critically important that you make it multi-user safe and this method I believe to be safe. Any other variations I doubt will hold up under a significant stress test.