问题描述
我有对不同的表的间隙少数的要求（由法律）。该ID可以有破洞，但不是序列。
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.
这是我必须要么解决了C＃代码或数据库（Postgres的，MS SQL和Oracle 。）
This is something I have to either solve in the C# code or in the database (Postgres, MS SQL and Oracle).
这是我的问题：
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
问题：差距顺序为 Portfolio_Sequence 和 Document_Sequence 。
请注意，这是非常简单的并且有包含在每个交易方式更多的表。
Note that this is very simplified and there is way more tables included in each of the transactions.
我该如何面对呢？
我见过的建议，你锁定的顺序，直到事务提交或回滚，但是这将是当它是这么多表参与这种复杂的长期交易系统的一个巨大的暂停。
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.
推荐答案
正如你似乎已经得出结论，无间隙的序列根本就没有形成规模。要么你放弃运行值的风险，当发生回滚，或者你有一个序列点，以防止多用户并发交易系统结垢。你不能兼得。
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.
希望有所帮助。
这篇关于其中，有多个表的多个交易涉及无间隙序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！