0

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?

1

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.

1
  • 1
    The sample snippet you provided perfectly solves the scenario I mentioned in the question. But I guess it wouldn't be possible to apply this approach if the scenario required more complex checks such as joins or operated on a range of rows, right? I checked the UPDLOCK and HOLDLOCK pattern you mentioned but as far as I understand, it does not prevent a read from other transactions.
    – John L.
    Apr 15 '17 at 0:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.