SQL Server Race Status Question

Question

(Note: this is for MS SQL Server)

Say you have an ABC table with a primary key identification column and a CODE column. We want each line to have a unique, sequentially generated code (based on some typical check digit formula).

Say you have another DEF table with only one row that stores the next available code (imagine a simple auto-dial number).

I know that logic like the one below represents a race condition in which two users can have 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 not re-used. 

I know that two users can get stuck in step 1) and can end up with the same code in the ABC table.

What is the best way to deal with this situation? I thought I could just turn on “start trance” / “make a transition” around this logic, but I don’t think it worked. I had a stored procedure like this one to check, but I did not escape the race condition when I was running 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 this transaction would prevent another user from accessing my NextCodeTable until the first transaction completes, but I believe that my understanding of transactions is erroneous.

EDIT: I tried moving the wait after the “update” statement, and I got two different codes ... but I suspected that. I have a waitfor command to simulate a delay, so race status can be easily seen. I think the key issue is a misconception about how transactions work.

Answer 1

Set transaction isolation level to Serializable.
At lower isolation levels, other transactions may read data in a row that is being read (but not yet modified) in that transaction. Thus, two transactions can indeed read the same value. With very low isolation (Read Uncommitted), other transactions may even read data after they have been changed (but before they are committed) ...

View SQL Server isolation levels here

So, the bottom line is that the isolation level is a critical part here to control what level of access to other transactions is included in this.

Note. From the link , about Serializable
Statements cannot read data that has been changed but has not yet been committed by other transactions.
This is due to the fact that locks are placed when the line changes, and not when Begin Trans occurs. Thus, everything you have done can allow another transaction to read the old value until the moment you change it. Therefore, I would change the logic to change it in the same expression that you read it, thereby simultaneously blocking it.

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

Answer 2

As other respondents noted, you can set the transaction isolation level to make sure that everything you read with the SELECT statement cannot be changed in the transaction.

Alternatively, you can release the lock specifically in the DEF table by adding the WITH HOLDLOCK syntax after the table name, for example,

 SELECT nextcode FROM DEF WITH HOLDLOCK 

It is not so important here, since your transaction is small, but it may be useful to remove locks for some SELECTs, and not others in the transaction. This is a question of "repeatability versus concurrency".

Several relevant MS-SQL documents.

• Isolation levels
• Table hints

Answer 3

Late answer. Do you want to avoid the race condition ...

SQL Server Queue Queue State Condition

Answer 4

Summary:

• You have started a transaction. It actually doesn’t do anything by itself; it changes subsequent behavior.
• You are reading data from a table. The default isolation level is Read Committed, so this select statement is not part of the transaction.
• Then you wait 15 seconds.
• Then you send the update. With a declared transaction, this will result in a lock until the transaction is completed.
• Then you complete the transaction, releasing the lock.

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

• read the "next" value from the def table, then go to standby
• B read the same “next” value from the table and then go into standby mode. (Since A just read, the transaction did not lock anything.)
• And then he updated the table and probably made the change before B exited the idle state. Then
• B updated the table after the record was recorded.

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

Answer 5

This is not a real race condition. This is a more common concurrency issue. One solution is to set the read lock on the table and therefore serialize in place.

Answer 6

You can set the column for the calculated value that is saved. This will take care of the race condition.

Saved computed columns

Note

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

Implementation

Give the column the following properties in the computed column specification.

Formula = dbo.GetNextCode ()

Saved = 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 7

This is actually a common problem in SQL databases, and that is why most (all?) Of them have some built-in functions to take care of this problem of obtaining a unique identifier. Here are some things to consider if you are using Mysql or Postgres. If you use a different database, I am sure you will get something very similar.

A good example of this are postgres sequences, which you can check here:

Subsequent sequences

Mysql uses what is called auto-increment.

Mysql auto increment