在哪里存储另一个表列的计数器？
我有一个数据库要求，它与您在大多数数据库介绍中看到的标准发票系统略有不同。我的发票系统有多个不相关的“办公室”。因此，每个办事处各自的发票号码都不同。实际发票号码与主键明显不同。
例如，办公室 A 和 B 可能都有不同的发票 #1005。
是否有为另一张表存储计数器的最佳实践？
create table Offices (
Id integer primary key,
InvoiceCounter integer not null,
Name varchar(32) not null
);
create table Invoices (
Id int primary key,
InvoiceNumber int not null,
Comment varchar(512) not null
-- other columns...
);
这是我在创建新发票时想到的算法：
- 获取 Office 的当前 InvoiceCounter。
- 加一并与发票一起插入。
- 使用新的 InvoiceCounter 编号更新 Office。
这有道理吗？
I have a database requirement that is a slight twist on the standard invoicing system you'd see in most DB introductions. My invoicing system has multiple "offices" that are unrelated. Therefore, respective invoice numbers are different per-office. The actual invoice number is obviously different than the primary key.
For example, Offices A and B could both have different invoice #1005.
Is there a best practice to storing a counter for another table?
create table Offices (
Id integer primary key,
InvoiceCounter integer not null,
Name varchar(32) not null
);
create table Invoices (
Id int primary key,
InvoiceNumber int not null,
Comment varchar(512) not null
-- other columns...
);
This is the algorithm that I thought of when creating a new invoice:
- Get the current InvoiceCounter for the Office.
- Increment by one and insert with the invoice.
- Update the Office with the new InvoiceCounter number.
Does this make sense?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要关心的一件事是此后可能出现的竞争条件。例如，人们尝试获取下一个 Inv#，他们都得到 1006。
为了避免这种情况，请在计算下一个数字时使用 sp_getapplock 进行阻止
这是一个好主意的原因是事务通常不会阻止读取。例如，T1 读取 1005，但在写入 1006 之前，T2 也读取 1005。当 T1 完成事务时，T2 继续写入 1006。如果您的唯一密钥正确，这将导致您的事务失败。
One thing you need to concern yourself with is the potential for race conditions since. E.g. to people try to get the next Inv# and they both get 1006 back.
To avoid this use sp_getapplock to block while you're calculating the next number
The reason why this is a good idea is because Transactions don't typically block reads. e.g. T1 reads 1005 but before it writes 1006, T2 also reads 1005. When T1 finishes the transaction T2 goes ahead and also write 1006. This will cause your transaction to fail if your unique key is correct.
我认为如果可以的话，你想尽量不要明确阻止。仅将其作为最后的手段。如果由于同一办公室的两笔交易提取相同的发票号码而导致交易失败，您是否不能重试交易？失败的交易只需要重试获取给定办公室的下一个可用发票号码。
这确实假设您有一个包含 Office# 和 Invoice# 的唯一密钥。
I think you want to try not to explicitly block if you can. Only do that as a last resort. Can't you just retry the transaction if a transaction fails due to two transactions for the same office pulling the same invoice number? The failing transaction just needs to retry getting the next available invoice number for the given office.
This does assume you have a unique key which contains the office# and invoice#.