The current project im working on is a crowdfunding platform for real estate investing that allows investors to buy a fraction of a property as a form of investment and receive rental income from it.
Investors should have the possibility to put as many shares of a property as are available in a cart and proceed to payment.
I have an endpoint called /checkout that is responsible for processing an order, not before doing some verifications like comparing the investor's balance with the total price of the order (balance must be greater than the total price of the order to proceed).
One of the verifications I need to build is to check if there are enough shares available at the moment my front-end hits the /checkout endpoint and temporarily lock other purchese requests to avoid selling more shares than the property is set to have.
As an example: property 1 has 150 shares available for purchese. Investor 1 wants to buy 100 shares and investor 2 wants to buy 70 shares. If they hit the endpoint coincidentally at the same time, 170 shares should be subtracted out of 150 avalailable (the endpoint should throw an error in this case like "Property ran out of shares" or somenthing)
Is there a way to avoid problems in this operation? I was researching the LOCK attribute at GET Record function but I'm not sure how to implement it. Should i combine with a database transation function?
A simplifyed version of my database design looks like this:
