Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel