Let two transactions running the above code concurrently, named tx1 and tx2 respectively.
Both tx1 and tx2 will attempt first to run
SELECT * FROM PRODUCT WHERE ID = 1 AND STATUS = "AVAILABLE" FOR UPDATE;
This query means also acquiring the lock on the row from the result set. Naturally, only one transaction may acquire it, so the other will have to wait. For example, if tx1 acquires the lock, tx2 will wait until tx1 commits and releases the lock. Then, tx2 will proceed and commit too, since it simply sets the quantity to 0. As a result, the above approach won't work as desired, because in the end, both users will receive a success message about their purchase. If you would like to keep this approach though, instead of directly setting the quantity to zero is to reduce it by 1, and have a constraint on the column that states that its value cannot be dropped below 0. In this manner, tx2 will fail due to constraint violation (remember the C of the ACID - Consistency) and roll back, thus second user will receive a message that his/her purchase failed.
Alternatively, if you don't want to implement extra constraints for this particular example and simply set the quantity directly, you can employ optimistic locking, which uses transparently row versioning and is also cheaper in terms of performance. So for the product with id 1 (let's call it P), tx1 generates a new version of P (P1) and tx2 at the same time generates another new version of P (P2). When either of tx1 and tx2 attempts to commit, the system will notice that there are new uncommitted versions of P, the P1 and P2 and will have to approve only one of them. As a result, P1 may be selected as the accepted new version of P, thus tx1 succeeds and purchase is successful, and P2 is rejected, thus tx2 is aborted and rolled back, and second user receives a failure message about his/her purchase. Note also that the opposite can happen too, i.e. P2 accepted and P1 rejected.
Finally, regarding optimistic and pessimistic locking consider the following:
- Optimistic locking is preferred if application developer estimates that conflicts are not bound to happen frequently
- Pessimistic locking to be employed otherwise if you don't want to have frequent failures due to frequent concurrent updates.
Personally, I would choose pessimistic locking but fixing the above transaction, since for quantities greater than 1, optimistic locking would still cause one transaction to abort, while both could be served successfully using pessimistic, at the cost of performance, slightly.
While still above transaction is going on, other users can still see the PRODUCT available as it is still not committed and they may also
proceed to PAY.
Regarding this, it may have to do with the refresh-rate of user's screen. Even if the user is able to proceed to pay (stale data on the front-end), back-end should validate that the user is eligible to do the purchase and if yes, proceed with the purchase (all of this in a single transaction)
Also, if the sufficient quantity of product is available, taking a lock on the product row may stop other users trying to buy the same product at same time.
That is the point of pessimistic locking, the performance drop is the major impact of this, but all transactions can be served provided that quantity does not reach 0. In optimistic locking, only 1 would succeed and others would fail, prompting user to try the purchase again.
