Thread Safe InnoDB Insert

[barneyf]

I am looking for suggestions on implementing a thread safe way to insert a new row into a WorkOrder table.  The table will be InnoDB with an AutoIncrement UID primary key. But the WorkOrder field is based on 4 alpha-numeric characters (format specified by the user) and will be unique for each row.  The field is alpha-numeric, so I can’t use a simple AutoIncrement integer value.  The rate of inserts will be very low, about 20 per day.  Queries to the table will be several thousand per day.

 

Since I must read the latest WorkOrder field in order to create the next one, I have to prevent concurrent threads from reading the same last WorkOrder value at the same time and generating duplicate WorkOrder values.  Can I use the InnoDB row locking to prevent duplicates, do I lock the whole table, or is there a better way (ie a PHP analog to JSP synchronized) to ensure thread safety?

 

Thanks,

Barney

[fenway]

You can try to use SELECT ... FOR UPDATE...  see this.

[barneyf]

Looks good.  Since I want to base the new work order number on the last one stored in the table, I guess the basic process (excluding error handling) is:

 

Table: WorkOrders
Fields: id     AutoIncrement integer & primary key
         wo    User defined work order UUID


SET AUTOCOMMIT = 0
BEGIN
SELECT * FROM WorkOrders ORDER BY id DESC LIMIT 1 FOR UPDATE
   /* extract wo value from data set, increment, and assign to a variable $next_wo */
INSERT INTO WorkOrders (wo) VALUES ('$next_wo')
COMMIT