CODEWITHSUNDEEP
mysqltransactions
Bhavesh
Bhavesh

Reputation: 4677

Inserting data into a child table based on master table primary key values

I'm currently working with an e-commerce shopping site. It's quite unnecessary to describe that users can submit their orders from anywhere at any time.

I have two tables in MySql database order and order_details (and there are others too) to maintain the order details that we can easily imagine therefore I think, I need not describe the whole structure of both of the tables.

The order table basically contains the fields like order_id which an auto-incremented primary key, order_date, order_status and some others that we don't need in this context.

The order_details table basically contains the fields like order_id which a foreign key of the order table, product_name, product_price etc.

When a customer submits an order, the first entry which is to be made is in the order table and based on the order_id in this table, the details of this order is inserted into the order_details table.

Inserting into the order_details table requires an appropriate order_id to be fetched from the order table (which is auto-incremented and therefore, we can not have it in advance). I'm currently fetching this order_id from the order table something like this.

select max(order_id) from order;

This SQL statement gives me the latest order_id from the order table and using this value, I can insert the details of the particular order into the order_details table.

Now, let's assume that I'm currently submitting an order containing some products in the shopping cart. My order is inserted into the order table successfully but before inserting into the order_details (and before retrieving the max order_id from the order table), this process is switched away by the operating system scheduler on the server for some reasons to execute another customer's order and the processing of that another customer's order is fully completed (inserted into both of the tables, the order and the order_details).

After completing this order processing (that another customer's order), my order which is still pending starts executing which was switched away before retrieving the max order_id from the order table. When the above SQL statement is executed, it will fetch the max order_id from the order table which is relevant to that another customer's order and not relevant to mine resulting the transaction in an inconsistent state!

What I want to ask is that how to correctly fetch an appropriate order_id from the order table which is always relevant to a particular order and that guarantees that no transaction ends with an inconsistent state at any time. What's the correct way to do so?

Upvotes: 0

Views: 1549

Answers (1)

eggyal
eggyal

Reputation: 125845

MySQL's LAST_INSERT_ID() function will return the auto-incremented value from the first successful insert of the last INSERT operation on the current connection.

Your MySQL API will usually provide some wrapper around this function call, e.g. mysql_insert_id() in PHP.

Upvotes: 1

Related Questions