python - SQL Server 锁定行读取和更新在 Python 中不起作用

问题描述

对于可以同时运行多个进程的程序，我需要在 SQL Server 和 Python 中锁定一行，读取然后更新该行的一个字段，以避免竞争条件。

我正在使用以下代码，但在 Python 中它会更新一个 Id（即 3411）并返回另一个 Id（即 3071）。如果我直接在 SSMS 中运行相同的 SQL 代码，它会更新并返回相同的 Id。

import pyodbc

def read_and_update_files_queue(self):
    conn = pyodbc.connect('Driver={SQL Server};'
                    'Server='+self.server+';'
                    'Database='+self.database+';'
                    'Trusted_Connection=yes;')
    cursor = conn.cursor()
    sql_query=f'''
                BEGIN TRANSACTION;
                    DECLARE @Temp_ID AS INT
                    SELECT TOP 1 @Temp_ID=Id 
                    FROM dbo.[Queue] 
                    WITH(XLOCK, ROWLOCK)
                    WHERE [BQ_status] IS NULL and upload_date IS NOT NULL

                    Select TOP 1 
                    Id,GCS_path,file_first_upload 
                    FROM dbo.[Queue]
                    WITH(XLOCK, ROWLOCK) 
                    WHERE Id=@Temp_ID


                    UPDATE dbo.[Queue] SET BQ_status='Loading' 
                    WHERE Id=@Temp_ID

                COMMIT TRANSACTION;
                '''
    cursor.execute(sql_query)
    cursor.commit()

怎么了？代码基于此：在 SQL Server 中，如何以类似于 Oracle 的“SELECT FOR UPDATE WAIT”的方式锁定单行？谢谢！！

标签： pythonsql-serverconcurrencylocking

因为前两个语句没有修改数据，所以你需要UPDLOCK提示，否则它不会持有锁直到事务结束，不管任何其他提示，包括XLOCK.

但是您可以在一个语句中完成所有操作，这里不需要UPDLOCK，也不需要显式事务（该语句无论如何都在事务中运行）

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] IS NULL and q.upload_date IS NOT NULL;

虽然SERIALIZABLE有时只使用共享S锁是正确的，但这仅适用SELECT于 DML 语句中的任何一个或未修改的连接表。

在这种情况下，由于Queue正在修改表，U因此在找到正确的行时会获取锁，这会禁止U其他事务的锁，这些事务将被阻塞等待（正在执行的事务SELECT不会被阻塞）。然后，当一行即将被修改时，U锁升级为X，并被修改。

所以这里不用担心竞争条件，也不UPDLOCK需要提示。

python - SQL Server 锁定行读取和更新在 Python 中不起作用

问题描述

解决方案

推荐阅读