SQL Server filter rows which are being selected in other transactions-CodePudding

i have a couple of jobs Update from select queries e.g

UPDATE TABLE_X
SET "stopFlag" = 1
OUTPUT 
    INSERTED."RowID" AS "rowID"
WHERE "RowID" IN (
    SELECT TOP 50
        "RowID"
    FROM
        TABLE_X
    WHERE
        stopFlag=0
)

Currently i was thinking that the update cannot conflict with another update but as i see the logs of my database tables it seems that 2 different jobs executed for a single row resulting in messed up data. My question is. Is this a proper way to filter the rows from being selected. If it is then what am i missing?

CodePudding user response：

A transaction is not necessary here, as every statement runs in an auto-commit transaction anyway.

You could up the isolation level to SERIALIZABLE, which may be more consistent, at the cost of more blocking. You could also add an UPDLOCK hint to the inner reference of Table_X.

But I think the best thing to do here will actually improve performance also: don't self-join the table, just update the derived table directly

UPDATE x
SET stopFlag = 1
OUTPUT 
    inserted.RowID AS rowID
FROM (
    SELECT TOP 50
        RowID,
        stopFlag
    FROM
        TABLE_X
    WHERE
        stopFlag = 0
) x;

An UPDLOCK is automatically taken on any rows read from the table reference which is being updated, so that is not necessary.

CodePudding user response：

If you want the statements to run concurrently, but mark and return disjoint rows, use READPAST. You can even introduce ordering guarantees, eg:

UPDATE TABLE_X
SET "stopFlag" = 1
OUTPUT 
    INSERTED."RowID" AS "rowID"
WHERE "RowID" IN (
    SELECT TOP 50
        "RowID"
    FROM
        TABLE_X with (rowlock, updlock, readpast)
    WHERE
        stopFlag=0
    ORDER BY "RowID"
)

See generally Using tables as Queues.