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.