sql server - SQL Threadsafe UPDATE TOP 1 for FIFO Queue

Question

I have a table of invoices being prepared, and then ready for printing.

[STATUS] column is Draft, Print, Printing, Printed

I need to get the ID of the first (FIFO) record to be printed, and change the record status. The operation must be threadsafe so that another process does not select the same InvoiceID

Can I do this (looks atomic to me, but maybe not ...):

1:

WITH CTE AS
(
    SELECT TOP(1) [InvoiceID], [Status]
    FROM    INVOICES
    WHERE   [Status] = 'Print'
    ORDER BY [PrintRequestedDate], [InvoiceID] 
)
UPDATE CTE
SET [Status] = 'Printing'
    , @InvoiceID = [InvoiceID]

... perform operations using @InvoiceID ...

UPDATE  INVOICES
SET [Status] = 'Printed'
WHERE   [InvoiceID] = @InvoiceID

or must I use this (for the first statement)

2:

UPDATE INVOICES
SET    [Status] = 'Printing'
    , @InvoiceID = [InvoiceID]
WHERE  [InvoiceID] = 
(
    SELECT TOP 1 [InvoiceID]
    FROM    INVOICES WITH (UPDLOCK)
    WHERE   [Status] = 'Print'
    ORDER BY [PrintRequestedDate], [InvoiceID] 
)

... perform operations using @InvoiceID ... etc.

(I cannot hold a transaction open from changing status to "Printing" until the end of the process, i.e. when status is finally changed to "Printed").

EDIT:

In case it matters the DB is READ_COMMITTED_SNAPSHOT

I can hold a transaction for both UPDATE STATUS to "Printing" AND get the ID. But I cannot continue to keep transaction open all the way through to changing the status to "Printed". This is an SSRS report, and it makes several different queries to SQL to get various bits of the invoice, and it might crash/whatever, leaving the transaction open.

@Gordon Linoff "If you want a queue" The FIFO sequence is not critical, I would just like invoices that are requested first to be printed first ... "more or less" (don't want any unnecessary complexity ...)

@Martin Smith "looks like a usual table as queue requirement" - yes, exactly that, thanks for the very useful link.

SOLUTION:

The solution I am adopting is from comments:

@lad2025 pointed me to SQL Server Process Queue Race Condition which uses WITH (ROWLOCK, READPAST, UPDLOCK) and @MartinSmith explained what the Isolation issue is and pointed me at Using tables as Queues - which talks about exactly what I am trying to do.

I have not grasped why UPDATE TOP 1 is safe, and UPDATE MyTable SET xxx = yyy WHERE MyColumn = (SELECT TOP 1 SomeColumn FROM SomeTable ORDER BY AnotherColumn) (without Isolation Hints) is not, and I ought to educate myself, but I'm happy just to put the isolation hints in my code and get on with something else :)

Thanks for all the help.

Answer 1

My concern would be duplicate [InvoiceID]
Multiple print requests for the same [InvoiceID]

On the first update ONE row gets set [Status] = 'Printing'

On the second update all [InvoiceID] rows get set [Status] = 'Printed'
This would even set rows with status = 'draft'

Maybe that is what you want

Another process could pick up the same [InvoiceID] before the set [Status] = 'Print'

So some duplicates will print and some will not

I go with comments on use the update lock

This is non-deterministic but you could just take top (1) and skip the order by. You will tend to get the most recent row but it is not guaranteed. If you clear the queue then you get em all.

This demonstrates you can lose 'draft' = 1

declare @invID int; 
declare @T table (iden int identity primary key, invID int, status tinyint);
insert into @T values (1, 2), (5, 1), (3, 1), (4, 1), (4, 2), (2, 1), (1, 1), (5, 2), (5, 2);
declare @iden int;
select * from @t order by iden;

declare @rowcount int = 1; 
while (@ROWCOUNT > 0)
    begin
        update top (1) t 
        set t.status = 3, @invID = t.invID,  @iden = t.iden
        from @t t 
        where t.status = '2';
        set @rowcount = @@ROWCOUNT;
        if(@rowcount > 0)
            begin 
                select @invID, @iden;
                -- do stuff  
                update t 
                set t.status = 4
                from @t t
                where t.invID = @invID; -- t.iden = @iden;
                select * from @T order by iden;
            end
    end