Is this sql update guaranteed to be atomic?

Question

I have the following sql:

UPDATE Customer SET Count=1 WHERE ID=1 AND Count=0
SELECT @@ROWCOUNT

I need to know if this is guaranteed to be atomic.

If 2 users try this simultaneously, will only one succeed and get a return value of 1? Do I need to use a transaction or something else in order to guarantee this?

The goal is to get a unique 'Count' for the customer. Collisions in this system will almost never happen, so I am not concerned with the performance if a user has to query again (and again) to get a unique Count.

EDIT:

The goal is to not use a transaction if it is not needed. Also this logic is ran very infrequently (up to 100 per day), so I wanted to keep it as simple as possible.

I think you might have simplified that SQL too much to correspond with your last paragraph. What is the purpose of having a unique "Count"? Is it intended that it only have a value of 0 or 1? — Andrew Morton, Jun 27 '16 at 17:24
It is getting a unique number for each order by the customer. This will be the user's reference number for the order, so every customer starts at 0 for the first order, and 1 for their second order, , then 2 etc. — David, Jun 27 '16 at 17:44
Possible duplicate of [Avoiding concurrency problems with MAX+1 integer in SQL Server 2008... making own IDENTITY value](http://stackoverflow.com/questions/8956044/avoiding-concurrency-problems-with-max1-integer-in-sql-server-2008-making-ow) — Andrew Morton, Jun 27 '16 at 17:55

score 0 · Answer 1 · answered Jun 27 '16 at 17:30

0

It may depend on the sql server you are using. However for most, the answer is yes. I guess you are implementing a lock.

answered Jun 27 '16 at 17:30

Nuri Tasdemir

9,720
3
42
67

score 0 · Accepted Answer · answered Jun 28 '16 at 18:36

Using SQL SERVER (v 11.0.6020) that this is indeed an atomic operation as best as I can determine.

I wrote some test stored procedures to try to test this logic:

-- Attempt to update a Customer row with a new Count, returns 
-- The current count (used as customer order number) and a bit
-- which determines success or failure.  If @Success is 0, re-run
-- the query and try again.
CREATE PROCEDURE [dbo].[sp_TestUpdate]
(
  @Count INT OUTPUT,
  @Success BIT OUTPUT
) 
AS
BEGIN
   DECLARE @NextCount INT 

   SELECT @Count=Count FROM Customer WHERE ID=1
   SET @NextCount = @Count + 1
   UPDATE Customer SET Count=@NextCount WHERE ID=1 AND Count=@Count
   SET @Success=@@ROWCOUNT
END

And:

-- Loop (many times) trying to get a number and insert in into another
-- table.  Execute this loop concurrently in several different windows
-- using SMSS.
CREATE PROCEDURE [dbo].[sp_TestLoop]
AS
BEGIN
   DECLARE @Iterations INT
   DECLARE @Counter INT
   DECLARE @Count INT
   DECLARE @Success BIT
   SET @Iterations = 40000
   SET @Counter = 0
   WHILE (@Counter < @Iterations)
   BEGIN
      SET @Counter = @Counter + 1
      EXEC sp_TestUpdate @Count = @Count OUTPUT , @Success = @Success OUTPUT
      IF (@Success=1)
      BEGIN
          INSERT INTO TestImage (ImageNumber) VALUES (@Count)
      END
   END
END

This code ran, creating unique sequential ImageNumber values in the TestImage table. This proves that the above SQL update call is indeed atomic. Neither function guaranteed the updates were done, but they did guarantee that no duplicates were created, and no numbers were skipped.

Is this sql update guaranteed to be atomic?

2 Answers2