0

I am designing html form where users can make new PO request and save into MySQL database. PO ID will be auto generated by retrieving the last PO number from table, then increment the PO number and concatenate it with various string and date information to get a unique PO ID.

The form will show this newly generated PO ID to users before the save button is clicked (each user will assume that they are given the latest PO ID from the system).

My problem is when 2 or more users are viewing the form at the same time, they both get the same PO ID from the database before saving. Current solution: the fastest user clicking the save button will get the correct PO ID, while the rest will get error message saying the PO ID already exists, they need to refresh the page to get the next PO ID.

Kindly advise me the best practice to solve this case? am I allowed to pessimistically lock the entire Purchase Order table, wait for one user to complete the insert transaction before releasing the entire table, during this transaction what will happen to other users when they try to open the PO Input form and trying to get the latest PO number from the table?

I need to apply the best practice solution to other similar business processes (Issuing delivery notes, invoices, new customer ID registration, etc). Thank you.

Daedalus Lee
  • 63
  • 10
  • 2
    How much research have you done on this topic on SO, I think a similar question was asked once or twice... http://stackoverflow.com/questions/2364273/how-to-make-sure-there-is-no-race-condition-in-mysql-database-when-incrementing http://stackoverflow.com/questions/22561241/how-to-avoid-race-condition-in-mysql http://stackoverflow.com/questions/264807/mysql-insert-race-condition http://stackoverflow.com/questions/13980443/race-condition-in-mysql-select-sql http://stackoverflow.com/questions/795237/mysql-race-conditions – mickmackusa Feb 22 '17 at 03:16
  • 2
    http://stackoverflow.com/questions/13926075/how-to-properly-avoid-mysql-race-conditions http://stackoverflow.com/questions/2805041/insert-a-row-and-avoiding-race-condition-php-mysql – mickmackusa Feb 22 '17 at 03:16
  • more to the point, I'd say don't create the unique ID until everything form related is totally complete. Then allow the database to handle the unique ID by using its auto-increment functionality. As for the concatenated ID string, you can provide that to the user, but maybe you can keep all of those pieces in separate columns in the database. So when you query in the future, you can explode the concatID into its critical parts and match the necessary columns. – mickmackusa Feb 22 '17 at 03:22
  • Sorry I have tried to research the topic but couldn't get to type the correct keyword to filter the result. I was having trouble in explaining my situation in the correct terms (english and database term). Thank you very much for the resources @mickmackusa – Daedalus Lee Feb 22 '17 at 03:26
  • Also have a look into 1NF compliance: https://en.wikipedia.org/wiki/First_normal_form – mickmackusa Feb 22 '17 at 03:27

0 Answers0