c# - Gap-less sequence where multiple transactions with multiple tables are involved

Question

I have a requirement (by law) for a gap-less numbers on different tables. The IDs can have holes in them but not the sequences.

This is something I have to either solve in the C# code or in the database (Postgres, MS SQL and Oracle).

This is my problem:

Start transaction 1

Start transaction 2

Insert row on table "Portfolio" in transaction 1

Get next number in sequence for column Portfolio_Sequence (1)

Insert row on table "Document" in transaction 1

Get next number in sequence for column Document_Sequence (1)

Insert row on table "Portfolio" in transaction 2

Get next number in sequence for column Portfolio_Sequence (2)

Insert row on table "Document" in transaction 2

Get next number in sequence for column Document_Sequence (2)

Problem occurred in transaction 1

Rollback transaction 1

Commit transaction 2

Problem: Gap in sequence for both Portfolio_Sequence and Document_Sequence.

Note that this is very simplified and there is way more tables included in each of the transactions.

How can I deal with this?

I have seen suggestions where you "lock" the sequence until the transaction is either committed or rolled back, but this will be a huge halt for the system when it is this many tables involved and this complex long transactions.

Answer 1

As you have already seemed to conclude, gapless sequences simply do not scale. Either you run the risk of dropping values when a rollback occurs, or you have a serialization point that will prevent a multi-user, concurrent transaction system from scaling. You cannot have both.

My thought would be, what about a post processing action, where every day, you have a process that runs at close of business, checks for gaps, and renumbers anything that needs to be renumbered?

One final thought: I don't know your requirement, but, I know you said this is "required by law". Well, ask yourself, what did people do before there were computers? How would this "requirement" be met? Assuming you have a stack of blank forms that come preprinted with a "sequence" number in the upper right corner? And what happens if someone spilled coffee on that form? How was that handled? It seems you need a similar method to handle that in your system.

Hope that helps.