Possibility of error if script is executed at same time.


#1

Ok I will explain this, and if code is needed I will post it.

Basically I have 2 tables that keep track of sales.

a sales table- keeps track of overall sales information such as the customers name, date of sale, and the total cost of the sale. It has an auto_increment field called id. Each sale has one and only one row.

a sales_items table- Keeps track of sale detail. It keeps track of what items were ordered, their price, and quantity sold. It then has a field called sale_id that links back to the specific row in the sales table. Each item in a sale is a seperate row. There can be an infinate number of rows for each sale.

The process I do for adding the sale is as follows.

Insert each item into the sales_items table with all the correct information except sale_id=-1 because it will be updated later.

Then after all the sales items are inserted, a row is added to the sale table with all the overall sale information.

right after the insert to the sales table. I call the function mysql_insert_id() to get the id of that sale.

Then right after that I update each of the sales_items WHERE the sale_id=-1 to the value of mysql_insert_id().

This is all executed in one motion. (The sales_items are not slowly added as they are known, they are all known before hand and then added all at once in one execution of the script)

So my question is, could their ever be data corrupition such as sales_items getting the wrong sale_id because the script is executed at the same time by 2 or more people?


#2

I believe that if you are using InnDB aso you can lock the tables while you are doing all this you shouldn’t have a problem. One question though - why do the items first and then the overall sales table and go back? Why not do the overall sales table first, get the ID then load full details table with all the data?


#3

I am not sure exactly why I did it the way I did, it was probably easier to get totals for the overall sale details. I will probably re-do the way a sale gets added.