How to handle concurrent access in mysql?

Question

If a shopping site only has 1 product available and 2 people try to buy it at the same time, who will get the product? How will the server prioritize the user."curious about flash sale on amazon,flip kart". which algorithm?

Answer 1

Followings are what could happen behind the scene and using any modern application framework it is pretty easy to achieve.

I'm assuming that your scenario is:

• Two users logged into your system say they are U1 and U2
• Select a product
• There is only one product is available
• They both clicked Add to Cart/Buy Now/Checkout
• One user will be served and another user will be notified that the product is not available anymore

Suppose T1 and T2 are the nanosecond representation of the time when they clicked on the checkout button. The chances that T1 and T2 are equals to each other is very low but there is a possibility.

In your case, the web-server will serve both the requests generated by the users in two different threads, TH1 and TH2. It is highly unlikely since there are hundreds of users at any given time present in your system but not impossible that the TH1 and TH2 get served by two different core of the CPU, assuming you have more than one core.

Hence both of the TH1 and TH2 will try to get a hold onto your Product.

Now you need to have/introduce two attributes (think as MySQL columns) to your PRODUCT: VERSION and CHECKED_OUT.

Both the TH1 and TH2 will start their own transaction at the same time, say TR1 and TR2, assuming you have InnoDB as your database engine.

Both of the TR1 and TR2 will:

• Read your PRODUCT from the database table along with the VERSION and CHECKED_OUT: {id: 1, version: 0, checked_out: 0, ...} and transfer it to the server.
• In the server, both of the TR1 and TR2 will increase the VERSION value which was read earlier and execute an Update statement stating that UPDATE PRODUCT SET CHECKED_OUT = 1, VERSION = 1 WHERE ID = 1 AND VERSION = 0
• DB will lock the row, execute the UPDATE and return the number of the modified row in a sequential fashion since the UPDATE should be executed within a single thread. Note here, this thread is DB's own thread, not the TR1 and TR2.
• Here if I assume TR1, i.e., TH1 was served before TR2, i.e., TH2 by DB's UPDATE thread then business logic that is behind TR1 will get that the number of rows updated is equaled to 1, whereas that of TR2 will get 0.
• Which in turn imply that U1 get to check out the product whereas U2 will be notified with a nice apologizing message.