How to insert into Orders table and Order Items table in MySQL by NodeJS

Question

I have a table called: Orders (Id, FK_Customer_Id, Comments, PaymentMethod, DeliverTime), which is used to store order information of a online retailer website, and also a table: Order_Items (Id, FK_Order_Id, FK_Product_Id, Quantity), to store the details of an order.

once the user submit an order, I need to save the information to the two table, but I don't know how to solve the concurrency problem.

Because, I should first insert into Orders table, then get the Order_Id and insert this Id to Order_Items table. I try to use "SELECT LAST_INSERT_ID() AS OrderId". But what if I insert into order and before I can get the Id, someone else insert another Order? Then the above statement will get the Id of the new inserted order, which is not my order anymore.

Please help me and let me know how to do it, or if I design the order/orderItem in a wrong way.

P.S. I m using angular 2 as front-end and node.js to create APIs and MySQL as DB.

Thank you in advance.

Answer 1

I would separate the operations. This will prevent any unexpected "race" affects with inserting records.

So, first, insert the order.

Then, second, confirm that the insert query was successful by asking for the "insert id" with something like mysql_insert_id() or whatever the node.js equivalent is.

Then, third, once you have that id, you can safely insert the details of the order.