2

I have the following scenario: a bank context, where I do not want a particular customer to complete a withdraw from his account, unless a previous withdraw is not committed.

In order to achieve this, I have created the following table

CREATE TABLE [dbo].[Locks](
        [CustomerID] [int] NOT NULL,
    CONSTRAINT [PK_Locks] PRIMARY KEY CLUSTERED 
    (
        [CustomerID] ASC
    )

Now, whenever a withdraw starts, I have the following code that essentially inserts the customer in this helper table (if he does not exist), and then locks the row for the duration of the transaction so that other withdraw do not occur before the first transaction is committed

BEGIN TRAN
IF NOT EXISTS (SELECT * FROM Locks WHERE CustomerID=@customerId) --if customerid does not exist, insert the row
BEGIN
    INSERT INTO Locks (CustomerID) 
    VALUES (@customerId)
END
SELECT CustomerID FROM Locks WITH (HOLDLOCK XLOCK ROWLOCK) WHERE CustomerID=@customerId --lock on row
--(check if customer has enough balance, then perform withdraw from customer account)
COMMIT

The above code seems to work in general with thousands of withdraws per day, but once every week or so, I do get a case where the lock did not work and the customer was found with negative balance because two withdraw operations happened at the same time.

Any ideas under which conditions the HOLDLOCK XLOCK ROWLOCK could fail to lock the transaction?

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
Themos Piperakis
  • 675
  • 8
  • 25

1 Answers1

4
IF NOT EXISTS (SELECT * FROM Locks WHERE CustomerID=@customerId) --if customerid does not exist, insert the row
BEGIN
    INSERT INTO Locks (CustomerID) 
    VALUES (@customerId)
END
SELECT CustomerID FROM Locks WITH (HOLDLOCK XLOCK ROWLOCK) WHERE CustomerID=@customerId --lock on row
--(check if customer has enough balance, then perform withdraw from customer account)

This is a race condition farm.

  • Two transactions can both run the SELECT, conclude there is no lock, and both proceed to insert the lock. With a PK constraint in place, one will fail.This is the happy case.
  • A transaction can come, run the SELECT, conclude there is a row and proceed to second SELECT. Meantime the row can be deleted. The SELECT WITH (lock hints) will find no row, but still proceed with the conclusion that it locked something (it didn't, there was no row to lock). This is a much worse case, it can result in (rolldrum, please!) negative balance.

These came up in like 10 seconds code inspection. I'm sure there are more (I did not even consider rollbacks...). Using rows as locks is an anti-pattern. Use applocks instead.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    Remus, any reason rowlocks are clearly inferior to applocks? Is this a well-known anti-pattern? I've been using rowlocks a few times and was content with the results. – usr Feb 05 '15 at 14:06
  • @usr with *rows* as a substitute for *locks* you are subject to whatever query processing decides is the *right* locking strategy. Consider lock escalation. The fact that you are hinting it to death `(HOLDLOCK, ROWLOCK)` in *hope* you got it right is a clear indication you are trying to force the engine's hand. App locks are much cleaner, since they are *lock*, what you actually want. – Remus Rusanu Feb 05 '15 at 14:16
  • @RemusRusanu this SP sounds exactly what I am looking for. But what about performance? Will it perform fast in a transactional environment with 100 transactions per second? I am a bit afraid of locking there – Themos Piperakis Feb 05 '15 at 16:10
  • @ThemosPiperakis app locks are exactly the same performance as data locks, they use the very same internals. So it should be much faster overall, considering that you no longer need to do any actual data access (SELECT). app locks also have *per session* semantics, if desired, but *those* you have to be careful to release them back, or they can leak in the app. Per transaction app locks behave just like data lock, they are released at commit/rollback. – Remus Rusanu Feb 05 '15 at 17:17
  • But how can one reliably use applocks without rowlocks? If I have a queue from which multiple workers claim rows to process, how can I request an applock over a row id without blocking that row first? I don't know the id in advance, I select the *next available* row. If I do so without a locking hint and try to get an applock on it, there will be a race of multiple clients doing that to the same row, so they all will have to contain a (horrible) loop that selects a next row, tries to claim an app lock and repeats if failed. `with(rowlock, updlock, readpast)` seems that much cleaner and faster? – GSerg Feb 05 '15 at 23:33
  • @Gserg [Using Tables as Queues](http://rusanu.com/2010/03/26/using-tables-as-queues/) is a different topic. I was very careful to say "row as locks'" not "rowlocks" in my answer. This discussion here is about implementing logical locks needed by the app ('Lock customer 123'), not about general data access strategies and locks required to access data. – Remus Rusanu Feb 06 '15 at 06:03
  • @RemusRusanu excellent answer and feedback. Will test it now and if all goes well I will release it into production. – Themos Piperakis Feb 06 '15 at 11:24