Sql Server Lock The Row The Read And Update Not Working In Python

I need to lock a row, read and then update a field of this row in SQL Server and Python, for a program that can run multiple process at the same time, to avoid a race condition. I'

Solution 1:

Because the first two statements are not modifying data, you need the UPDLOCK hint, otherwise it will not hold the lock til the end of the transaction, irrespective of any other hints, including XLOCK.

But you could just do it all in a single statement, here no UPDLOCK is needed, and no explicit transaction (the statement runs in a transaction anyway)

UPDATE TOP (1) q
    SET BQ_status ='Loading'
    OUTPUT inserted.Id, inserted.GCS_path, inserted.file_first_upload
FROM dbo.[Queue] AS q WITH (SERIALIZABLE, ROWLOCK)
    WHERE q.[BQ_status] ISNULLand q.upload_date ISNOTNULL;

While it is true that SERIALIZABLE sometimes only take a shared S lock, this is only true for either SELECT, or non-modified joined tables in DML statements.

In this instance, since Queue is the table being modified, a U lock is taken while finding the right row, this forbids U locks from other transactions, which would be blocked waiting (a transaction doing just SELECT would not be blocked). Then, when a row is about to be modified, the U lock is upgraded to X, and is modified.

So there is no fear of race conditions here, nor is the UPDLOCK hint necessary either.