Concurrency Issues: SQL Counter increment problem - The select/update issue

• sourav.2007.ghosh

  SSC Veteran

  Points: 269

  More actions

  October 16, 2015 at 11:58 am

  #399201

  Hi All,

  This is more of a knowledge sharing topic. 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].

  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

  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.

  Solution:

  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.

  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;

  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 welcome. As I said earlier, many of you would already know this solution, but for those who don't this can definitely be a life saver. 🙂