涉及多个表的多个事务的无间隙序列 [英] Gap-less sequence where multiple transactions with multiple tables are involved

问题描述

我有一个要求(根据法律)在不同的桌子上有一个无间隙的数字.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.

希望有所帮助.

这篇关于涉及多个表的多个事务的无间隙序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！