1

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

Community
  • 1
  • 1
Michal Ciechan
  • 13,492
  • 11
  • 76
  • 118

2 Answers2

3

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

Pieter van Ginkel
  • 29,160
  • 8
  • 71
  • 111
  • I would still have locking problems, on that table. Although I might implement in the future to ease the implementation of PreLoadEvent security checking. – Michal Ciechan Nov 05 '10 at 12:07
  • Your locking problems would be seriously mitigated because you're only locking a single record. To have a consistent MAX(ID), you would theoretically have to lock the entire table because what you want to lock is that you want that the `SELECT MAX(ID) WHERE Subsidiary = ?` changes. There is a significant difference. Also, with locking the subsidiary table, you're doing something "strange" because the subsidiary table has nothing to do with the invoices table. It's just a locking strategy but you're not doing anything with the data. – Pieter van Ginkel Nov 05 '10 at 12:49
1

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

Adrian Smith
  • 17,236
  • 11
  • 71
  • 93
  • This looks like exactly something i am looking for, Could you clear up the lockign process a bit more. Would i need to select * from invoices where subsidiary = x for update, and then another query on the same session to get the max id? – Michal Ciechan Nov 05 '10 at 11:37
  • 1
    You'd need a table `subsiduary` where you have one row per subsiduary. It'd be that row that you would lock, to show that an exclusive operation was going on for the subsiduary. `START TRANSACTION`, then `SELECT id FROM subsiduary WHERE subsiduary_id=? FOR UPDATE`, then `SELECT MAX(id) FROM invoice ...`, `INSERT...`, `COMMIT`. – Adrian Smith Nov 05 '10 at 13:29
  • So when you lock the subsidiary row in the subsidiary table it won't let any new records be inserted that have this row as a foreign key constraint? Is that correct? – Michal Ciechan Nov 05 '10 at 13:48
  • Not quite; if you lock the row in the subsidiary table then nobody else can lock that particular row until you commit or rollback. What you then do (e.g. update that row, do something else, select max invoice id then insert a new invoice) is up to you. – Adrian Smith Nov 05 '10 at 13:54