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:
- Read a value from a table (say Table 1 – Name/Value pair),
- Increment it by 1
- Update the value and
- 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.
- Same value being read by multiple threads
- Deadlock problem
What is the best way to address such an issue - Considering this is referred to in many different applications/services?