Edit: My question is, "Why does my first code example work?" Please read on...
Edit1: There is no doubt that a unique constraint is the correct way to ensure duplicates don't happen. This is a given. However, sometime we need to know that we're attempting a duplicate entry. Further, this post goes beyond merely handling duplicates.
This is potentially a repeat of probably over 100 questions on SO. I've read an endless array of confusions and contradictions about the simple questions of atomic updates, locks, and concurrency.
I see that blogs and experts disagree widely on these points. Here I provide test code based on the various solutions people have advised, indicate the results, state my views, and invite your comment.
Context: I'm running SQL Server 2008 Express SP2.
I created the following test table:
create table dbo.Temp (Col int)
The table deliberately has no constraints on it as we want to test the SQL code ideas, not constraints.
I ran the following concurrently in 2 and then 3 query windows:
declare @i int
set @i = 0
while @i < 5000 begin
set @i = @i + 1
update dbo.temp set Col = (SELECT Col from dbo.Temp) + 1
end
I did not use any explicit locking, as can be seen. All DB settings are default. I checked the value of Col and it was the desired number: 25,000. Nothing missed.
Since SQL Server is ACID, the "A" tells us that a single statement is executed atomically. Therefore, based on the above, we could agree with those who say that locks are not needed for a simple update as above.
Next, I ran the following concurrently in 3 query windows:
while @i < 5000 begin
set @i = @i + 1
insert into dbo.temp select @i where not exists
(select 1 from dbo.temp where Col = @i)
end
The results were not correct, despite the f