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

score 1 · Accepted Answer · answered Sep 24 '14 at 21:27

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)

answered Sep 24 '14 at 21:27

James Baxter

1,237
9
17

This appears to work, but I'm getting some exceptions, likely dead locks. I'll debug a bit and figure things out further. – ohmusama Sep 24 '14 at 21:40
`Failed to access Queue: Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.` Is this normal? – ohmusama Sep 24 '14 at 21:52
okay modified it to `serializable` (from `repeatable read`) and it now doesn't appear to be dead locking, thanks. – ohmusama Sep 24 '14 at 22:02

execute stored procedure atomicly

1 Answers1