Skip to main content
edited body
Source Link
John L.
  • 245
  • 1
  • 9

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:

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

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?

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?

Source Link
John L.
  • 245
  • 1
  • 9

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:

  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?