Mysql> INSERT INTO site_workorder_seq (SiteID) VALUES
> SiteWorkorderNum int not null auto_increment, Here is a sample loading into this table: mysql> DROP DATABASE david SiteWorkorderNum int not null auto_increment, I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site CREATE TABLE site_workorder_seq What is interesting about this situation can be solved using the MyISAM storage. (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?)Īm 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. 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.ġ) 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?)Ģ) 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.
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. 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. SiteID and SiteWorkorderNum are both set as a unique constraint. There are multiple 'sites', and each site has work order numbers starting at 100. I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization.
0 kommentar(er)
