I'm making an invoicing system, using the support for multiple subsidaries which have their very own group of invoice amounts, therefore i've got a table having a primary key of (Subsidiary, InvoiceNo)

I am unable to use MySQL auto increment area, as then it will likely be constantly incrementing exactly the same count for those subsidaries.

I'd rather not make seperate tables for every subsidiary as you will see new subsidaries added as necessary...

I'm presently using "Choose Max (ID) Where Subsidiary = X", from the table and adding the invoice based on this.

I'm using nHibernate, and also the Invoice place, comes prior to the InvoiceItem place, if Invoice place fails, InvoiceItem won't be completed. But rather i'll catch the exception, re-retrieve the Max(ID) and check out again.

What's the trouble with this method? And when any, what's an alternate?

The reson for asking happens because i just read among the solutions about this question: Nhibernate Criteria: 'select max(id)'

This can be a very bad idea to make use of when producing primary secrets. My advise is the following:

  • Don't give primary secrets a company meaning (synthetic secrets)

  • Make use of a secondary mechanism for producing the invoice amounts.

This makes your existence a great deal simpler. The mechanism for producing invoice amounts may then e.g. be considered a table that appears something similar to:

  • Subsidiary
  • NextInvoiceNumber.

This can separate the interior numbering from the way the database works.

With your a mechanism, you'll have the ability to use auto increment fields again, as well as, GUID's.

Some links with reading through material:

http://fabiomaulo.blogspot.com/2008/12/identity-never-ending-story.html http://nhforge.org/blogs/nhibernate/archive/2009/02/09/nh2-1--new-machines.aspx

While you say, the issue with this particular approach is multiple periods might try to place exactly the same invoice ID. You receive a unique constraint breach, need to repeat the process, that may fail too, and so forth.

I solve such problems by securing the subsiduary throughout the development of new bills. However, don't lock the table, (a) if you work with InnoDB you will find issues that a lock table command automatically will commit the transaction. (b) There's pointless why bills for 2 different subsiduaries should not be added in simultaneously because they have different independent invoice amounts.

Things I would do in you are:

  • Open an transaction and make certain your tables are InnoDB.
  • Lock the subsiduary by having an SELECT .. FOR UPDATE command. You can do this using [cde] in NHibernate.
  • Discover the max id using max(..) function and perform the place
  • Commit the transaction

This serializes all invoice card inserts for just one subsiduary (i.e. just one session can perform this kind of place at the same time, any second attempt will hold back until the very first is complete or has folded back) but that is what you would like. You wouldn't want holes inside your invoice amounts (e.g. should you place invoice id 3485 after which it fails, then you will find bills 3484 and 3486 but no 3485).