How growing up with siblings can help you better understand concurrency problems while working with database(while shopping on e-commerce, have you ever experienced that some items from your cart go out of stock or missing).
Let’s define our problem statement before moving forward
We all do ecommerce shopping and sometimes we experience that some of the items in the cart go missing or they say out of stock when we click for payment option.
Before diving straight into solve this problem, let’s do a story time segment of this article(and justify why I wrote that title for the article)
So I grew up with an elder sister and one of the demerits (LOL) when you have siblings is sometimes you have to share few food items with them. One day my parents got me and my sister good amount of chocolates for Christmas and they told us to divide and share with each other. I immediately ate all my chocolates and when my sister was out with her friends, I eat one chocolate from her share which she kept in a secret box. She came home and immediately complaint saying someone ate one chocolate from her box. She remembered what was the count of chocolates in her box before she left home to meet her friends. She counted after she came home and saw that difference and came to the conclusion.
You all must be wondering how can this incident help all of us to understand concurrency better when working with database. Let me explain
You are shopping on e-commerce website named XYZ and there is only one Ipad remaining. Now imagine there is another guy named Collin who is also trying to buy same Ipad at same time.
Let’s understand from database end what is happening. You made a fetch/read request for ipad, ipad was there in database so you got the response, you decided to buy and made a update/buy request to Database. Same sequence will happen with Collin as well.
Now imagine you and Collin both clicked make payment button at approximately same time. Due to network delay, Collin’s payment request went before you by 100milliseconds so technically he bought first and then your request came . so question is now should database allow this request? Absolutely not because you have only one Ipad in stock. Since your request came 100milliseconds late, you will be get out of stock message. (I hope now you know how sometimes you get out of stock message for few items)
Careful reader will ask this question that Sumit but how database handles this situation like you and Collin both got response from Database that Ipad is available but how database reached the conclusion that your request was not valid though you both clicked make payment button approximately at same time? You remember how my sister came to conclusion that one of the chocolate was missing from her box by comparing count before she left home and after she came home. Now imagine this read request/get ipad information request came with a column named version = 1 . so you and Collin both got version as 1 when you both fetched information from database. Like I said Collin’s buy ipad request came 100ms earlier than yours so database updated version for this ipad record as 2 in database. When your buy ipad request came, it came with version as 1( why sumit 1? when you fetched what version you got?1 right?). Now database sees that record version is 2 and your request version is 1 just like my sister concluded that someone ate chocolate from her box. Similarly, database also see that I have version 2 record and request is coming for version 1 so that means this is stale request and I cannot allow it to update database record since it was made on stale premise.
This is called MVCC(Multi version concurrency control) concept in database which deals with isolation during concurrent transactions happening in the system.
I hope you enjoyed the article 😁✌🏻😉. Do share with your friends.
