1

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?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Ivelin
  • 25
  • 5
  • This has a lot do with concurrency levels, which are confusing, but may I suggest you switch in read committed isolation and put this in a transaction? – Nick.Mc Jan 31 '22 at 14:02
  • isnt this the default transaction level? also will this lead to one transaction waiting for the other because that was what i was trying to bypass. i wanted 2 sql statements to run side by side. – Ivelin Jan 31 '22 at 14:14

2 Answers2

1

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I ended up using parts from your answer and parts from David Browne. Thank you both so much. – Ivelin Feb 23 '22 at 07:15
1

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks. i will look into it. One question regarding my code. If i put a case when inside the update set wont that do the trick as sort of a second check so i ensure that the data is not tampered with and avoid using locks and other stuff? – Ivelin Jan 31 '22 at 16:35
  • No, you need locks to make this work. – David Browne - Microsoft Jan 31 '22 at 17:17
  • I ended up using parts from your answer and parts from Charlieface. Thank you both so much. – Ivelin Feb 23 '22 at 07:15