SQL Server Race Condition Question

SQL Server Race Condition Question

Set the Transaction Isolation Level to Serializable.

At lower isolation levels, other transactions can read the data in a row that is read, (but not yet modified) in this transaction. So two transactions can indeed read the same value. At very low isolation (Read Uncommitted) other transactions can even read data after it's been modified (but before committed)...

Review details about SQL Server Isolation Levels here

So bottom line is that the Isolation level is crtitical piece here to control what level of access other transactions get into this one.

NOTE. From the link, about Serializable
Statements cannot read data that has been modified but not yet committed by other transactions.

This is because the locks are placed when the row is modified, not when the Begin Trans occurs, So what you have done may still allow another transaction to read the old value until the point where you modify it. So I would change the logic to modify it in the same statement as you read it, thereby putting the lock on it at the same time.

begin tran
declare @x int
update def set @x= nextcode, nextcode += 1
waitfor delay '00:00:15'
select @x
commit tran

Preventing Conditional INSERT/UPDATE Race Condition in MS-SQL

As alluded to in the articles I posted to your last question (Conditional INSERT/UPDATE Race Condition and “UPSERT” Race Condition With MERGE) using MERGE along with HOLDLOCK is thread safe, so your query would be:

MERGE tblWords WITH (HOLDLOCK) AS w
USING (VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId)) AS s (cl_Word, cl_WordLangCode, cl_SourceId)
    ON s.cl_Word = w.cl_Word
    AND s.cl_WordLangCode = w.cl_WordLangCode
WHEN NOT MATCHED THEN 
    INSERT (cl_Word, cl_WordLangCode, cl_SourceId)
    VALUES (s.cl_Word, s.cl_WordLangCode, s.cl_SourceId);

It also looks like this might be a stored procedure and you are using SELECT @cl_WordId to return the ID to the caller. This falls under one of Aaron Bertrand's bad habits to kick, instead you should use an output parameter, something like:

CREATE PROCEDURE dbo.SaveCLWord
        @cl_Word            VARCHAR(255), 
        @cl_WordLangCode    VARCHAR(255), 
        @cl_SourceId        INT,
        @cl_WordId          INT OUTPUT
AS
BEGIN

    MERGE tblWords WITH (HOLDLOCK) AS w
    USING (VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId)) AS s (cl_Word, cl_WordLangCode, cl_SourceId)
        ON s.cl_Word = w.cl_Word
        AND s.cl_WordLangCode = w.cl_WordLangCode
    WHEN NOT MATCHED THEN 
        INSERT (cl_Word, cl_WordLangCode, cl_SourceId)
        VALUES (s.cl_Word, s.cl_WordLangCode, s.cl_SourceId);

    SELECT  @cl_WordId = w.cl_WordId
    FROM    tblWords AS w
    WHERE   s.cl_Word = @cl_Word
    AND     s.cl_WordLangCode = @cl_WordLangCode;

END

---

ADDEDNUM

You can do this without MERGE as follows.

BEGIN TRAN

INSERT tblWords (cl_Word, cl_WordLangCode, cl_SourceId)
SELECT  @cl_Word, @cl_WordLangCode, @cl_SourceId
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    tblWords WITH (UPDLOCK, HOLDLOCK)
            WHERE   cl_Word = @cl_Word
            AND     l_WordLangCode = @cl_WordLangCode
        );

COMMIT TRAN;

SELECT  @cl_WordId = w.cl_WordId
FROM    tblWords AS w
WHERE   s.cl_Word = @cl_Word
AND     s.cl_WordLangCode = @cl_WordLangCode;

If you are not using merge because you are concerned about its bugs, or because in this case you don't actually do an UPDATE, so MERGE is overkill and an INSERT will suffice, then that is fair enough. But not using it because it is unfamiliar syntax is not the best reason, take the time to read about it, learn more, and add another string to your SQL bow.

---

EDIT

From online docs

HOLDLOCK

Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

So in your query, you have 6 statements:

-- STATETMENT 1
DECLARE @cl_WordId bigint = NULL

--STATEMENT 2
SELECT
  @cl_WordId = cl_WordId
FROM tblWords WITH (HOLDLOCK)
WHERE cl_Word = @cl_Word
AND cl_WordLangCode = @cl_WordLangCode

BEGIN

--STATEMENT 3
  IF (@cl_WordId IS NULL)
  BEGIN

    -- STATEMENT 4
    INSERT INTO tblWords (cl_Word, cl_WordLangCode, cl_SourceId)
      VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId)
    SET @cl_WordId = SCOPE_IDENTITY()

    --STATEMENT 5
    SELECT
      @cl_WordId
  END
  ELSE
  BEGIN

    -- STATEMENT 6
    SELECT
      @cl_WordId
  END
END

Since you don't have explicit transactions, each statement runs within its own implicit transaction, so concentrating on statement 2, this is equivalent to:

BEGIN TRAN

SELECT
  @cl_WordId = cl_WordId
FROM tblWords WITH (HOLDLOCK)
WHERE cl_Word = @cl_Word
AND cl_WordLangCode = @cl_WordLangCode

COMMIT TRAN

Therefore, since HOLDLOCK applies for the duration of the transaction in which it is used, the lock is released, the lock is released as soon as this code finishes, so by the time you have progressed to statement 3 and 4 another thread could have inserted to the table.

How to deal with race condition in case when it's possible to have multiple servers (and each of them can have multiple threads)

After reading the majority of the comments let's assume that you need a solution for a relational database.

The main thing that you need to guarantee is that the write operation at the end of your code only happens if the precondition is still valid (e.g. product.Quantity - requestedQuantity).

This precondition is evaluated at the application side in memory. But the application only sees a snapshot of the data at the moment, when database read happened: _database.GetProduct(); This might become obsolete as soon as someone else is updating the same data. If you want to avoid using SERIALIZABLE as a transaction isolation level (which has performance implications anyway), the application should detect at the moment of writing if the precondition is still valid. Or said differently, if the data is unchanged while it was working on it.

This can be done by using offline concurrency patterns: Either an optimistic offline lock or a pessimistic offline lock. Many ORM frameworks support these features by default.

SQL Server Process Queue Race Condition

Edit:

I googled to check my answer: "Processing Data Queues in SQL Server with READPAST and UPDLOCK". It's been years since I read about and played with this solution.

Original:

If you use the READPAST hint, then locked rows are skipped. You've used ROWLOCK so you should avoid lock escalation. You also need UPDLOCK, as I found out.

So process 1 locks 20 rows, process 2 will take the next 20, process 3 takes rows 41 to 60, etc

The update can also be written like this:

UPDATE TOP (20)
    foo
SET
    ProcessorID = @PROCID
FROM
    OrderTable foo WITH (ROWLOCK, READPAST, UPDLOCK)
WHERE
    ProcessorID = 0

Refresh, Oct 2011

This can be done more elegantly with the OUTPUT clause if you need a SELECT and an UPDATE in one go.

SQL Server update - is there a race condition?

If my_table_id is the primary key of your table (i.e. the primary key constraint is set), then indeed it is impossible that you would have 2 rows affected by that update statement.

Through locking, the database engine ensures that a row is updated by one process at a time. At no moment in time will there be two records with the same value for my_table_id, since the primary key constraint forbids such a situation.

Secondly, as you update the flag pending_flag to 1, the record will not match if you run the same update statement again, and so 0 records will have been updated.

can I use this command alone as a mechanism to guarantee that I don't process the record multiple times?

Yes! (with condition that you have my_table_id as the primary key or at least with a unique constraint).

---

Related Topics