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 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.
- T2 reads $40.
- T1 updates it to $20.
- T2 updates it to $20.
But there should be $0 left in the account.
Is there a way to prevent this without the use of XLOCK? What are the alternatives?