execute stored procedure atomicly

Question

I have created a "queue" of sorts in sql, and I want to be able to set an item as invisible to semi-simulate an azure like queue (instead of deleting it immediately in the event the worker fails to process it, it will appear automatically in the queue again for another worker to fetch).

As per recommendation from this SO: Is T-SQL Stored Procedure Execution 'atomic'?

I wrapped Begin Tran and Commit around my spDeQueue procedure, but I'm still running into duplicate pulls from my test agents. (They are all trying to empty a queue of 10 simultaneously and I'm getting duplicate reads, which I shouldn't)

This is my sproc

ALTER PROCEDURE [dbo].[spDeQueue]
    @invisibleDuration int = 30,
    @priority int = null
AS
BEGIN   

    begin tran
        declare @now datetime = GETDATE()

        -- get the queue item
        declare @id int =
        (
            select top 1
                [Id]
            from 
                [Queue]
            where
                ([InvisibleUntil] is NULL or [InvisibleUntil] <= @now)
                and (@priority is NULL or [Priority] = @priority)
            order by
                [Priority],
                [Created]
        )

        -- set the invisible and viewed count
        update
            [Queue]
        set 
            [InvisibleUntil] = DATEADD(second, @invisibleDuration, @now),
            [Viewed] = [Viewed] + 1
        where
            [Id] = @id

        -- fetch the entire item
        select
            *
        from
            [Queue]
        where
            [Id] = @id
    commit

    return 0
END

What should I do to ensure this acts atomicly, to prevent duplicate dequeues.

Thanks

Answer 1

Your transaction (ie statements between 'begin trans' and 'commit') is atomic in the sense that either all the statements will be committed to the database, or none of them.

It appears you have transactions mixed up with synchronization / mutual exclusive execution.

Have a read into transaction isolation levels which should help enforce sequential execution - repeatable read might do the trick. http://en.wikipedia.org/wiki/Isolation_(database_systems)