Is using XLOCK (Exclusive Lock) in SELECT statements considered bad practice?
Let's assume the simple scenario where a customer's account balance is $40. Two concurrent 20$$20 puchase requests arrive. Transaction includes:
- Read balance
- If customer has enough money, deduct the price of the product from the balance
So without XLOCK:
- T1(Transaction1) reads 40$$40.
- T2 reads 40$$40.
- T1 updates it to 20$$20.
- T2 updates it to 20$$20.
But there should be 0$$0 left in the account.
Is there a way to prevent this without the use of XLOCK? What are the alternatives?