In SQL Server, how can I lock a single row in a way similar to Oracle's "SELECT FOR UPDATE WAIT"?

Question

I have a program that connects to an Oracle database and performs operations on it. I now want to adapt that program to also support an SQL Server database.

In the Oracle version, I use "SELECT FOR UPDATE WAIT" to lock specific rows I need. I use it in situations where the update is based on the result of the SELECT and other sessions can absolutely not modify it simultaneously, so they must manually lock it first. The system is highly subject to sessions trying to access the same data at the same time.

For example:
Two users try to fetch the row in the database with the highest priority, mark it as busy, performs operations on it, and mark it as available again for later use. In Oracle, the logic would go basically like this:

BEGIN TRANSACTION;
SELECT ITEM_ID FROM TABLE_ITEM WHERE ITEM_PRIORITY > 10 AND ITEM_CATEGORY = 'CT1'
    ITEM_STATUS = 'available' AND ROWNUM = 1 FOR UPDATE WAIT 5;
UPDATE [locked item_id] SET ITEM_STATUS = 'unavailable';
COMMIT TRANSACTION;

Note that the queries are built dynamically in my code. Also note that when the previously most favorable row is marked as unavailable, the second user will automatically go for the next one and so on. Furthermore, different users working on different categories will not have to wait for each other's locks to be released. Worst comes to worst, after 5 seconds, an error would be returned and the operation would be cancelled.

So finally, the question is: how do I achieve the same results in SQL Server? I have been looking at locking hints which, in theory, seem like they should work. However, the only locks that prevents other locks are "UPDLOCK" AND "XLOCK" which both only work at a table level.
Those locking hints that do work at a row level are all shared locks, which also do not satisfy my needs (both users could lock the same row at the same time, both mark it as unavailable and perform redundant operations on the corresponding item).

Some people seem to add a "time modified" column so sessions can verify that they are the ones who modified it, but this sounds like there would be a lot of redundant and unnecessary accesses.

Solution 1

In SQL Server there are locking hints but they do not span their statements like the Oracle example you provided. The way to do it in SQL Server is to set an isolation level on the transaction that contains the statements that you want to execute. See this MSDN page but the general structure would look something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    select * from ...

    update ...

COMMIT TRANSACTION;

SERIALIZABLE is the highest isolation level. See the link for other options. From MSDN:

SERIALIZABLE Specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Solution 2

You're probably looking forwith (updlock, holdlock). This will make a select grab an exclusive lock, which is required for updates, instead of a shared lock. The holdlock hint tells SQL Server to keep the lock until the transaction ends.

FROM TABLE_ITEM with (updlock, holdlock)

Solution 3

As documentation sayed:

XLOCK

Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

So solution is using WITH(XLOCK, ROWLOCK):

BEGIN TRANSACTION;

SELECT ITEM_ID
FROM TABLE_ITEM
WITH(XLOCK, ROWLOCK)
WHERE ITEM_PRIORITY > 10 AND ITEM_CATEGORY = 'CT1' AND ITEM_STATUS = 'available' AND ROWNUM = 1;

UPDATE [locked item_id] SET ITEM_STATUS = 'unavailable';

COMMIT TRANSACTION;

Solution 4

Have you tried WITH (ROWLOCK)?

BEGIN TRAN

   UPDATE your_table WITH (ROWLOCK)
   SET your_field = a_value
   WHERE <a predicate>

COMMIT TRAN

View more solutions

Comments

[Paradoxyde]

ROWLOCK does prevent two users from modifying the same row, but it does not, however, prevent two users from locking that same row.

[Paradoxyde]

It's good to know, but it seems that Serializable will prevent reads only if the data has been modified, so it will not prevent locks until that moment.

[Paul Sasik]

@Paradoxyde: That's not my understanding. Per the 2nd item of the spec (see my edit above) the row is locked as soon as it's read until the end of the transaction. What part of the spec are you getting your question from?

[Andomar]

This acquires a shared lock and later an exclusive lock, so it's vulnerable to deadlocks

[Paradoxyde]

I tried it locally to confirm. No transactions can modify the data, though obtaining a lock in a similar way would not count as a modification. In that case, it would be possible for two sessions to acquire a lock on the same row and then take turns modifying it.

[Paradoxyde]

I agree that this is the type of lock that I am looking for, however, as I stated in the question, updlock affects the entire table, whilst I am looking to affect only a single row.

[Paul Sasik]

@Paradoxyde: According to this: No other transactions can modify data that has been read by the current transaction until the current transaction completes. modification by any other means should not be possible until the tran that read the row first finishes...

[Paradoxyde]

You must have misunderstood my previous comment. What I said/meant is that while other transactions can not modify the data (as stated in the docs in the part you quoted), they can still obtain locks for it. That means that two sessions could lock the same row at the same time, and one would wait while the first one set its item as 'unavailable'. When the first session would then release the row, the second session would also update the row to set it as 'unavailable'. This results in two sessions believing they took ownership of the item.

[Paul Sasik]

@Paradoxyde: Ah, ok! The way to get around that is to set some kind of flag in your update statement that will be read as the first step in your transaction. If the flag is set, then the 2nd tran should just return w/o any other action. You could use a timestamp or some sort of val. Depends on your datamodel.

[Paradoxyde]

I was really hoping not having to resort to a flag, as it sounds that it would add unnecessary accesses; however, it seems like there is no better option for SQL Server. Thanks for the help!

[Paul Sasik]

How to avoid a flag in SQL Server in this kind of scenario would be a good follow up question. I'd be interested to see if there were any responses. I personally have no idea.

[Paradoxyde]

Well, after fooling a bit more with it I found a potential solution, although I will admit it feels cheap and could be unreliable in some cases (rowlocks are not always honored by SQL Server). What I did is set my transaction isolation level to read committed, and begin my transaction. Before doing my select to get my most favorable row's index, I do an update with the same search parameters and update nothing (set item_name = item_name for example). This effectively locks my row until the end of the transaction. Any other transaction accessing this table can then specify readpast or nolock.

[Andomar]

updlock changes the type of lock (exclusive vs shared) but not what is locked. To lock the entire table, specify with (tablock), or to lock a row, specify with (rowlock)

[Biscuit Coder]

@Paradoxyde If two people lock the same row, will it be a deadlock. Will the row update be successful ?

[SamuraiJack]

what would be the performance implications? Do you think this could cause deadlocks?