How to make this the sample SQL Atomic?

Question

I have an store procedure,

ALTER PROCEDURE [dbo].[InsertDealGallery] 
(
    @ImageID INT,
    @DealID INT
)
AS

    DECLARE @MaxOrder INT;
    SELECT @MaxOrder = MAX([Order]) + 1 FROM DealGallery WHERE DealID = @DealID

    IF (@MaxOrder IS NULL)
    BEGIN
        SET @MaxOrder = 1;
    END



   INSERT INTO [DealGallery]
                ([ImageID]
                ,[DealID]
                ,[Order])  
        VALUES
                (@ImageID
                ,@DealID
                ,@MaxOrder)

But I am fearing that this is not atomic because in the same time MaxOrder might remain same in concurrent thread. SO how to make this atomic?

Answer 1

Transactions is the feature to obtain atomic behavior. You know that a from ACID is for Atomic. But you have choiced a very bad design, the aggegration function can lock all rows, performance becomes very poor.

Edited

You should move to identitat data type. Also another techniques exists, like counter table, but easy way is identity.

To enclose code into a transaction you should to include 'Begin transaction' and 'commit' sentences.

Edited 2

This approach, with a counter for each Deal, will avoid lock all DealGallery's rows, only lock DealGalleryCounter row.

CREATE TABLE DealGalleryCounter 
  (DealID INT not null primary key,
   order int default 0
  );   -- Or add column to an existing deal table. 

ALTER PROCEDURE [dbo].[InsertDealGallery] 
(
    @ImageID INT,
    @DealID INT
)
AS BEGIN
    DECLARE @order int

    begin transaction
    set transaction isolation level serializable
    -- repeatable read is enough --

    select @order = order + 1
      from DealGalleryCounter 
     where @DealID = DealID

    if @order is null
      insert into DealGalleryCounter (DealID) values (@DealID)

    INSERT INTO dbo.DealGallery (ImageID, DealID, [Order])  
    VALUES ( @ImageID, @DealID, @order );

    update DealGallery 
       set DealGalleryCounter = @order
     where @DealID = DealID;

    commit;    --or check for errors and rollback
END