Alternative to Max(ID) in complex primary key

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 (Subsidiar开发者_运维知识库y, 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)'

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:

• Subsidiary;
• NextInvoiceNumber.

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

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 table command 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:

• Open an transaction and make sure your tables are InnoDB.
• Lock the subsiduary with an SELECT .. FOR UPDATE command. This can be done using LockMode.UPGRADE in NHibernate.
• Find the max id using max(..) function and do the insert
• Commit the transaction

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).