SQL Server Race Condition Question

Question

(Note: this is for MS SQL Server)

Say you have a table ABC with a primary key identity column, and a CODE column. We want every row in here to have a unique, sequentially-generated code (based on some typical check-digit formula).

Say you have another table DEF with only one row, which stores the next available CODE (imagine a simple autonumber).

I know logic like below would present a race condition, in which two users could end up with the same CODE:

1) Run a select query to grab next available code from DEF
2) Insert said code into table ABC
3) Increment the value in DEF so it's not re-used.

I know that, two users could get stuck at Step 1), and could end up with same CODE in the ABC table.

What is the best way to deal with this situation? I thought I could just wrap a "begin tran" / "commit tran" around this logic, but I don't think that worked. I had a stored procedure like this to test, but I didn't avoid the race condition when I ran from two different windows in MS:

begin tran

declare @x int

select   @x= nextcode FROM  def

waitfor delay '00:00:15'

update def set nextcode = nextcode + 1

select @x

commit tran

Can someone shed some light on this? I thought the transaction would prevent another user from being able to access my NextCodeTable until the first transaction completed, but I guess my understanding of transactions is flawed.

EDIT: I tried moving the wait to after the "update" statement, and I got two different codes... but I suspected that. I have the waitfor statement there to simulate a delay so the race condition can be easily seen. I think the key problem is my incorrect perception of how transactions work.

Answer 1

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

Answer 2

Late answer. You want to avoid a race condition...

"SQL Server Process Queue Race Condition"

Answer 3

You can set the column to a computed value that is persisted. This will take care of the race condition.

Persisted Computed Columns

NOTE

Using this method means you do not need to store the next code in a table. The code column becomes the reference point.

Implementation

Give the column the following properties under computed column specification.

Formula = dbo.GetNextCode()

Is Persisted = Yes

Create Function dbo.GetNextCode()
Returns VarChar(10)
As
Begin

    Declare @Return VarChar(10);
    Declare @MaxId Int

    Select @MaxId = Max(Id)
    From Table

    Select @Return = Code
    From Table
    Where Id = @MaxId;

    /* Generate New Code ... */

    Return @Return;

End

Answer 4

As other responders have mentioned, you can set the transaction isolation level to ensure that anything you 'read' using a SELECT statement cannot change within a transaction.

Alternatively, you could take out a lock specifically on the DEF table by adding the syntax WITH HOLDLOCK after the table name, e.g.,

SELECT nextcode FROM DEF WITH HOLDLOCK

It doesn't make much difference here, as your transaction is small, but it can be useful to take out locks for some SELECTs and not others within a transaction. It's a question of 'repeatability versus concurrency'.

A couple of relavant MS-SQL docs.

• Isolation levels
• Table hints

Answer 5

It's not a real race condition. It's more a common problem with concurrent transactions. One solution is to set a read lock on the table and therefor have a serialization in place.

Answer 6

This is actually a common problem in SQL databases and that is why most (all?) of them have some built in features to take care of this issue of obtaining a unique identifier. Here are some things to look into if you are using Mysql or Postgres. If you are using a different database I bet the provide something very similar.

A good example of this is postgres sequences which you can check out here:

Postgres Sequences

Mysql uses something called auto increments.

Mysql auto increment

Answer 7

Recap:

• You began a transaction. This doesn't actually "do" anything in and of itself, it modifies subsequent behavior
• You read data from a table. The default isolation level is Read Committed, so this select statement is not made part of the transaction.
• You then wait 15 seconds
• You then issue an update. With the declared transaction, this will generate a lock until the transaction is committed.
• You then commit the transaction, releasing the lock.

So, guessing you ran this simultaneously in two windows (A and B):

• A read the "next" value from table def, then went into wait mode
• B read the same "next" value from the table, then went into wait mode. (Since A only did a read, the transaction did not lock anything.)
• A then updated the table, and probably commited the change before B exited the wait state.
• B then updated the table, after A's write was committed.

Try putting the wait statement after the update, before the commit, and see what happens.