Using XLOCK In SELECT Statements 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?
I would consider it to be a bad practice unless you're 100% sure that it won't limit your effective concurrency. For example, I use TABLOCK all the time when I know that I'll be the only one accessing the table on a development database.

One technique that can help with the hypothetical issue that you called out is to write the transaction to use a single query instead of multiple queries. SQL Server will take a lock at the appropriate level (depends on the table structure) and I think that things will work out. Consider the following query:

BEGIN TRANSACTION

UPDATE Balance_table
SET balance = balance - 20
WHERE customer_id = ????
and balance &gt;= 20;

COMMIT TRANSACTION;


For the default isolation level that query should prevent the situation that you called out from occurring.

For more complicated examples, you could consider using sp_getapplock to lock a resource at the account level. You could also try a UPDLOCK and HOLDLOCK pattern.