SQL counter increment concurrency issues - The select/update issue

Question

This is more of a knowledge sharing post.

Recently in one of my projects, I came across a issue which is pretty common, but never really gave it a thought till I faced it. There are number of solutions available, but somehow I did not find THE ONE I was looking for, which I am going to share in this post. I am sure many of you already know about the below solution, but for those who don't this can definitely be a life saver. :-)

The Problem:

Recently, I was working on a windows service. This service was supposed to insert some records into a table that long existed and being used by several other old services. There is a column (say “ID”) which holds an integer value. The logic written (probably years ago) to get the Integer to insert into this table is stated below:

The logic:

1. Read a value from a table (say Table 1 – Name/Value pair),
2. Increment it by 1
3. Update the value and
4. Finally get the new value[/highlight].

Code

SELECT @value = [Value] from [dbo].[Table1] WHERE [Name] = @Name;
UPDATE [dbo].[ Table1] SET [Value] = @value +1  WHERE [Name] = @Name;
SELECT @value = @value+1;

The technology used has Major Concurrency issues.

1. Same value being read by multiple threads
2. Deadlock problem

What is the best way to address such an issue - Considering this is referred to in many different applications/services?

Answer 1

The Resolution

The obvious thought would be get rid of this piece and rewrite this. But there are times, particularly when you work on areas like this where certain logic, tables, codes are already getting referenced by several other programs, it is not an easy call.

My goal was to attack the root of the problem and get rid of it. If I can do that there are supposed to be no real impacts. The output of the proc would be the same, so to the outside world nothing actually changed. :-)

So, I did some minor tweaks to the above statements to ensure that every thread updates the value and reads the updated value. No more conflicts, no more dirty reads.

Updated Code

DECLARE @table table(
    Value int NOT NULL
);

-- Update the value and output the same into a local table variable
UPDATE [dbo].[Table1] SET [Value] = [Value] + 1  OUTPUT inserted.[Value] INTO @table
WHERE [Name] = @Name

-- Now read the value from the local table variable
SELECT Value FROM @table;

For more information on output clause check https://msdn.microsoft.com/en-us/library/ms177564(v=sql.90).aspx

I ran some tests with a parallel.foreach (20,000 times). My test table had two entries -

1. Name = First and Value = 1 - After 20,000 updates the value should be 20,001
2. Name = Second, Value = 100 - After 20,000 updates the value should be 20,100

If you check the attachment, you will find out the difference in the numbers.

Deadlock issues are hard to reproduce, but the problem with multiple threads reading the same value in clearly evident from the test result.

Any thoughts on this is most welcome.

As I said earlier, many of you would already know this solution, but for those who don't this can definitely save some time. :-)