Attempting to lock a row in SQL with a 'Lock' column

Question

I'm trying to lock a row in a database, with a guid that identifies the holder of the lock, and an expiry date that specifies how long the lock is valid for (in case the process crashes, and fails to release the lock). Here's what I managed to come up with so far:

UPDATE Foo
SET
    Lock = @Lock,
    LockExpiry = DATEADD(MILLISECOND, @Duration, GETDATE())
WHERE Id = (
    SELECT TOP 1 Id
    FROM Foo
    WHERE (Lock IS NULL OR LockExpiry < GETDATE())
    ORDER BY LockExpiry
);

SELECT TOP 1
FROM Foo
WHERE (Lock = @Lock AND LockExpiry > GETDATE())

However, I've added some logging to the project, and it appears that the locks are sometimes overwritten by other processes. I have a suspicion there could be a race condition here:

Thread A selects the top Foo waiting to be updated, and gets Foo #3
Thread B selects the top Foo waiting to be updated, and gets Foo #3
Thread B sets Foo #3's lock id to some guid 'xxxx'
Thread B selects the top Foo that it holds a lock on, and returns Foo #3
Thread A sets Foo #3's lock id to some guid 'yyyy'
Thread A selects the top Foo that it holds a lock on, and returns Foo #3

How can I tell if this is happening? And if it is possible, how can I prevent it?

Edit

Also, is there an easier way of doing this? I need to lock the row, and have the lock persist across multiple transactions, possibly even multiple connections.

Murphy's law: if something may fail, it will fail. If there exists locking mechanisms is for a reason, because you can't relay on data in one row to ensure locking. — Gusman, Aug 10 '17 at 23:09
https://stackoverflow.com/questions/9502273/in-sql-server-how-can-i-lock-a-single-row-in-a-way-similar-to-oracles-select — Gusman, Aug 10 '17 at 23:12
@Gusman I'm using EntityFramework's `ExecuteSqlCommandAsync`, which automatically wraps the statement in a `BEGIN TRANSACTION` and `END TRANSACTION`. Can I call `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;` from within a transaction? — Andrew Williamson, Aug 10 '17 at 23:17
From here: https://msdn.microsoft.com/en-us/library/dn220055(v=vs.113).aspx " If there isn't an existing local transaction a new transaction will be used to execute the command" So, you can first create the transaction and then execute the query. — Gusman, Aug 10 '17 at 23:26
Ok, just testing it out now. If it works, I'll vote to close as duplicate. — Andrew Williamson, Aug 10 '17 at 23:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/151658/discussion-between-andrew-williamson-and-gusman). — Andrew Williamson, Aug 10 '17 at 23:55

score 0 · Accepted Answer · answered Aug 11 '17 at 01:46

I've managed to resolve this by making the UPDATE statement do a check for the same conditions as the select:

-- Only update the first row, because the WHERE clause can now match multiple
UPDATE TOP (1) Foo
SET
    Lock = @Lock,
    LockExpiry = DATEADD(MILLISECOND, @Duration, GETDATE())
-- Make sure that the check for the lock and the update are part of the same operation
WHERE (Lock IS NULL OR LockExpiry < GETDATE())
AND Id IN (
    -- The first Foo may have been locked by another thread by the time the UPDATE
    -- statement runs, so return multiple entries to try
    SELECT TOP 10 Id
    FROM Foo
    WHERE (Lock IS NULL OR LockExpiry < GETDATE())
    ORDER BY LockExpiry
);

SELECT TOP 1
FROM Foo
WHERE (Lock = @Lock AND LockExpiry > GETDATE())

Note that this can return false negatives, i.e. if eleven threads try to acquire a Foo at the same time, one of them might not succeed, even though there are enough Foos available at the time.

Also, this solution now means that we won't necessarily get the oldest outstanding Foo, but we will get an old one, and there's a good chance it will be the oldest.

Attempting to lock a row in SQL with a 'Lock' column

1 Answers1