问题描述
我有一个要求(根据法律)在不同的桌子上有一个无间隙的数字.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 的序列有差距.
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.
我该如何处理?
我已经看到一些建议，您可以在事务提交或回滚之前锁定"序列，但是当涉及这么多表和复杂的长事务时，这对系统来说将是一个巨大的停顿.
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.
希望有所帮助.
这篇关于涉及多个表的多个事务的无间隙序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！