复杂主键中 Max(ID) 的替代方案
我正在制作一个发票系统，支持多个子公司，每个子公司都有自己的一组发票号码，因此我有一个主键为（子公司，InvoiceNo）的表
我不能使用MySQL自动增量字段，因为这样它就会不断增加所有子公司的相同数量。
我不想为每个子公司制作单独的表格，因为将根据需要添加新的子公司...
我目前正在使用“Select Max (ID)Where Subsidiary = X”，从我的表格中并根据添加发票这。
我使用的是nHibernate，并且Invoice插入位于InvoiceItem插入之前，因此如果Invoice插入失败，InvoiceItem将不会被执行。但我会捕获异常，重新检索 Max(ID) 并重试。
这种方法有什么问题呢？如果有的话，有什么替代方案吗？
询问的原因是因为我读了这个问题的答案之一： Nhibernate Criteria: 'select max(id )'
I am making an invoicing system, with the support for multiple subsidaries which each have their own set of invoice numbers, therefore i have a table with a primary key of (Subsidiary, InvoiceNo)
I cannot use MySQL auto increment field, as then it will be constantly incrementing the same count for all subsidaries.
I don't want to make seperate tables for each subsidiary as there will be new subsidaries added as need be...
I am currently using "Select Max (ID) Where Subsidiary = X", from my table and adding the invoice according to this.
I am using nHibernate, and the Invoice insert, comes before the InvoiceItem insert, therefore if Invoice insert fails, InvoiceItem will not be carried out. But instead i will catch the exception, re-retrieve the Max(ID) and try again.
What is the problem with this approach? And if any, what is an alternative?
The reson for asking is because i read one of the answers on this question: Nhibernate Criteria: 'select max(id)'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在生成主键时使用这是一个非常糟糕的主意。我的建议如下：
不要赋予主键商业含义（合成键）；
使用辅助机制生成发票编号。
这将使您的生活变得更加轻松。用于生成发票号码的机制可以是一个类似于以下内容的表：
这会将内部编号与数据库的工作方式分开。
有了这样的机制，您将能够再次使用自动增量字段，甚至更好的是 GUID。
一些阅读材料的链接：
http://fabiomaulo.blogspot.com /2008/12/identity-never-ending-story.html
http://nhforge.org/blogs/nhibernate/archive/2009/02/09/nh2-1-0-new-generators.aspx
This is a very bad idea to use when generating primary keys. My advise is as follows:
Do not give primary keys a business meaning (synthetic keys);
Use a secondary mechanism for generating the invoice numbers.
This will make your life a lot easier. The mechanism for generating invoice numbers can then e.g. be a table that looks something like:
This will separate the internal numbering from how the database works.
With such a mechanism, you will be able to use auto increment fields again, or even better, GUID's.
Some links with reading material:
http://fabiomaulo.blogspot.com/2008/12/identity-never-ending-story.html
http://nhforge.org/blogs/nhibernate/archive/2009/02/09/nh2-1-0-new-generators.aspx
正如您所说，这种方法的问题是多个会话可能会尝试插入相同的发票 ID。您会遇到唯一约束违规，必须重试，也可能会失败，等等。
我通过在创建新发票期间锁定子公司来解决此类问题。但是，不要锁定表，(a) 如果您使用 InnoDB，则默认情况下
lock table命令会出现问题 提交事务。 (b) 没有理由不能同时添加两个不同子公司的发票，因为它们具有不同的独立发票号码。在你的情况下我会做的是：
SELECT .. FOR UPDATE命令锁定子公司。这可以使用LockMode.UPGRADE 来完成在 NHibernate 中。这将序列化一个子公司的所有发票插入（即，只有一个会话可以一次执行此类插入，任何第二次尝试都将等到第一次完成或已回滚）但这就是您想要的。您不希望发票号码中出现漏洞（例如，如果您插入发票 ID 3485 然后失败，则有发票 3484 和 3486，但没有 3485）。
As you say, the problem with this approach is multiple sessions might try and insert the same invoice ID. You get a unique constraint violation, have to try again, that might fail as well, and so on.
I solve such problems by locking the subsiduary during the creation of new invoices. However, don't lock the table, (a) if you are using InnoDB there are problems that a
lock tablecommand by default will commit the transaction. (b) There is no reason why invoices for two different subsiduaries shouldn't be added at the same time as they have different independent invoice numbers.What I would do in your situation is:
SELECT .. FOR UPDATEcommand. This can be done usingLockMode.UPGRADEin NHibernate.This serializes all invoice inserts for one subsiduary (i.e. only one session can do such an insert at once, any second attempt will wait until the first is complete or has rolled back) but that's what you want. You don't want holes in your invoice numbers (e.g. if you insert invoice id 3485 and then it fails, then there are invoices 3484 and 3486 but no 3485).