Sql-server – Using XLOCK In SELECT Statements

lockingsql server

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:

  1. Read balance
  2. If customer has enough money, deduct the price of the product from the balance

So without XLOCK:

  1. T1(Transaction1) reads $40.
  2. T2 reads $40.
  3. T1 updates it to $20.
  4. 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?

Best Answer

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 >= 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.

Related Question