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.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…