4

I have a table, sys_QueueJob, storing Queue logic data.

I thought that having an update with a return would be enough... However, now I am not sure if this is 100% safe.

How can I be sure that no matter an many parallel requests will not return the same ID?

UPDATE sys_QueueJob
SET ExecutionStartedOn = GETDATE()
OUTPUT DELETED.Id as Result
WHERE Id = (select top 1 x.Id
            from sys_QueueJob x with (rowlock, updlock, readpast)
                        where x.ExecutionFinishedOn is null
                            AND (
                                x.ExecutionStartedOn is null
                                OR x.ExecutionStartedOn < DATEADD(HOUR, -1, GETDATE())
                               )
                        order by x.CreatedOn asc)
0

2 Answers 2

6

queueing

Like I talk about in this post, a reliable way to process queues is to use a query like this:

WITH 
    q AS
(
    SELECT TOP (1) 
        x.* 
    FROM sys_QueueJob x WITH (ROWLOCK, UPDLOCK, READPAST)
    WHERE x.ExecutionFinishedOn IS NULL
    AND 
    (
          x.ExecutionStartedOn IS NULL 
       OR x.ExecutionStartedOn < DATEADD(HOUR, -1, GETDATE())
    )
    ORDER BY 
        x.CreatedOn ASC
        /*You may need to also order by Id if CreatedOn isn't unique*/
)
UPDATE 
    q
SET 
    ExecutionStartedOn = GETDATE()
OUTPUT 
    Deleted.Id as Result;

Of course, the bigger challenge for most queue queries is indexing them to make work easy to distribute. Usually, a filtered index that excludes finished work (ExecutionFinishedOn), and keys on filtering/sorting elements is sufficient (ExecutionStartedOn, CreatedOn).

In your case, you're looking both for items that haven't been started, and items that were started over an hour ago. It may make more sense to split these into two "workers" that look for each disposition independently, or adding some logic to go look for rows that started over an hour ago if no rows are found that haven't been started yet.

comparing techniques

The reason I've used a common table expression rather than an update with a subquery is to avoid a query plan with multiple locking calls to the table.

Take a look at this example, which uses a small helper view called dbo.WhatsUpLocks to summarize locks held by a session. I'm also using Extended Events to observe locks acquired and released on my table and related objects (indexes, default constraints, etc.) when the update query runs.

first technique

BEGIN TRANSACTION;
    DECLARE
        @id integer, 
        @reputation integer;

    WITH 
        q4 AS
    (
        SELECT TOP (1) 
            fq.*
        FROM dbo.four_queue AS fq WITH(READPAST, ROWLOCK, UPDLOCK)
        WHERE fq.in_process = 0
        ORDER BY 
            fq.id
    )
    UPDATE q4
    SET 
        q4.in_process = 1,
        q4.start_date = SYSDATETIME(),
        @id = q4.id,
        @reputation = q4.reputation
    FROM q4;
    
    SELECT
        wul.*
    FROM dbo.WhatsUpLocks(@@SPID) AS wul;
ROLLBACK;
GO 

Here's the query plan, with a single read reference to the table:

NUTS

Here are the locks that were taken and released during query execution:

NUTS

And here are the locks that remain before the transaction is rolled back:

NUTS

second technique

Let's compare that with your initial attempt, applied to the table setup in the post I linked earlier.

BEGIN TRANSACTION;
    DECLARE
        @id integer, 
        @reputation integer;

    UPDATE 
        q4
    SET
        q4.in_process = 1,
        q4.start_date = SYSDATETIME(),
        @id = q4.id,
        @reputation = q4.reputation
    FROM dbo.four_queue AS q4
    WHERE q4.id = 
    (
        SELECT TOP (1) 
            x.id
        FROM dbo.four_queue AS x WITH (ROWLOCK, UPDLOCK, READPAST)
        WHERE x.in_process = 0
        ORDER BY 
            x.id
    );

    SELECT
        wul.*
    FROM dbo.WhatsUpLocks(@@SPID) AS wul;
ROLLBACK;
GO 

Here's the query plan, which now has two read references to the table:

NUTS

Here are the locks that were taken and released during query execution:

NUTS

And here are the locks that remain before the transaction is rolled back:

NUTS

differences

The locks acquired and released during query execution are far different. There are way fewer in the common table expression than in the update with subquery, largely because of the single read and update reference in the query plan.

There's only a slight difference in the remaining locks before the query is rolled back. The bottom row in my result shows an IX lock on pages, and yours shows UIX locks on pages.

How much these differences add up depends on concurrency while processing queues, but you might as well do it the right way so you don't have to worry about it falling apart later.

As a final note, I don't want you to come away from this thinking that common table expressions are magickal. They are not. You could get the same results with a derived table or a created view that produces a single result to update, without the use of a subquery. In this case, I find the common table expression makes the query more understandable. That is all.

0
1

I believe that (testing will prove) using

CREATE TABLE BLAH (
ID INT IDENTITY(1,1) PRIMARY KEY,
StartTime DATETIME,
EndTime DATETIME,
FinishedTime DATETIME,
TH INT,
CreatedTime DATETIME DEFAULT GETDATE()
)

INSERT INTO Blah (TH)
SELECT TOp 100 (TH)
FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) TH
        FROM sys.objects a
    ) a1

BEGIN TRANSACTION 
UPDATE table1 
  SET StartTime = GETDATE() 
 OUTPUT DELETED.ID  
 FROM BLAH table1
    JOIN (  SELECT TOP 1 ID 
            FROM BLAH table2
            WHERE StartTime IS NULL
            ORDER BY CreatedTime ASC) Selector
 ON Selector.ID = table1.ID
 WHERE StartTime is null 
COMMIT --TRANSACTION

will LOCK the table for UPDATE during that transaction, so you capture the unprocessed ID and can proceed to process that row - while another call captures the next.

if you read https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16 this will help see how stuff is managed in SQL.

Seems to me that you are reprocessing records with StartTime set that haven't finished in an hour? might need to tweak the where clause to include that. Or have the process processing the ID update starttime to NULL when it crashes... will automatically be available for selection again.

In fact, running the create, insert, and update (without commit) and running the update again (without commit), outputs 1 then 2 - don't forget to COMMIT twice to close open transactions after testing.

1
  • 2
    You may want to read up on what can happen to queries using read committed under high concurrency. It isn't pretty, especially for processes like this. The Read Committed Isolation Level Commented Jan 24 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.