MySQL get next unique value without auto increment

Question

I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100.

WorkorderID    SiteID    SiteWorkorderNum
     1           1            100
     2           1            101
     3           2            100

WorkorderID is the auto increment field.
SiteID and SiteWorkorderNum are both set as a unique constraint.

Whenever a new work order is about to be inserted for a specific site, the application checks for the max(SiteWorkorderNum) for the siteid of whoever is logged in and returns that value to the insert statement. The problem I want to avoid is if two users of the same site both enter a new work order at the exact same time.

I have a couple of possible solutions but I want to see if there is a better way as each has it's drawbacks, but one I'm sure will be better than the other, and of course I'm open to new ideas.

1) Lock the table to guarantee no other users retrieve a SiteWorkorderNum value until after we have retrieved and inserted our values (however let's pretend we have thousands of users trying to enter work orders every minute, wouldn't the table locks slow things down?)

2) Don't lock the table, and retrieve the next available SiteWorkorderNum and try to insert into the database, if I receive a unique constraint error, catch the error and try again until I'm successful. (this might keep the table freed up, but again pretending we have thousands of users at the same site, would the multiple database calls be a little inefficient?)

Am I being too paranoid about how either of these solutions 'could' effect performance? Of course I don't have thousands of users, but I'm wanting to apply best practice in this design in case I ever work on a project which does have high traffic.

score 0 · Answer 1 · answered Jun 09 '12 at 06:14

One option is to use transactions. If you do the SELECT MAX(SiteWorkorderNum) ... WHERE SiteID=... together with the resulting insert, then everything should work. The only problem is that your transactions might fail, which could be bad for the user experience.

The other scheme which I've used before is to have an allocation table with SiteId and NextWorkorderNum columns. Just do UPDATE SiteWorkorderAlloc SET @Num=NextWorkorderNum, NextWorkorderNum=NextWorkorderNum+1 WHERE SiteId=.... Then grab @Num to use as your workorder number in a subsequent insert. It works fine, the only minor drawback being that if there is a crash between getting the workorder number and the insert, that workorder number is lost (never used).

MySQL get next unique value without auto increment

1 Answers1