主页 / dba / 问题 / 307008
Accepted
CSharpie
Asked: 2022-02-05 02:13:29 +0800 CST

不返回记录的查询挂在锁上

  • 772

我们注意到这种奇怪的情况，即不返回记录的查询将在指定时等待锁定WITH(UPDLOCK)。

查询很简单

SELECT primaryKey FROM someTable WITH(UPDLOCK) 
WHERE columA = 'A' 
      AND columnB BETWEEN 6 AND 8 
      AND columnC != 'C' ;

运行此查询的系统使用隔离级别read uncommitted，我们无法更改。

有没有一种方法，例如提示，不会让这个等待，但如果它会产生结果，仍然会锁定记录？

我注意到索引使用在其中起作用，因为其他查询没有这种行为。但遗憾的是，定义索引也不在可能的范围内。

我想出的唯一解决方案是IF EXISTS在执行实际查询之前在其中抛出一个，但由于竞争条件，它仍然可能发生。

类似的问题已在此处得到解答：为什么 UPDLOCK 会导致 SELECTs 挂起（锁定）？

如果没有索引来定位要锁定的行，所有测试的行都将被锁定，并且对符合条件的行的锁定将一直保持到事务完成。

但我不认为那里提供的答案适用于我的问题版本，因为查询不会返回行来“限定”。

遗憾的是，我无法访问该系统。我们只能对某些表运行查询。

目的是锁定和读取记录（如果存在）并最终更新它。如果丢失，请插入。

UPDLOCK之所以使用它，是因为在我的理解中，当锁定未提交的读取隔离时，这是正确的。

该表有一个主键，但未在 where 子句中使用。

sql-server locking

1 个回答

  1. Best Answer

    没有直接的方法可以做你想做的事：在读取未提交的隔离中读取行，只U对与给定谓词匹配的行加锁。

    UPDLOCK工作原理

    当您指定 时UPDLOCK，SQL ServerU在行或页级别获取锁。如果需要一个表锁，SQL Server 会X取而代之的是一个锁。

    无论是否找到任何匹配的行，表和页锁都会保留到事务结束。使用行级锁时，SQL Server 总是在测试之前U对行进行锁，以查看它是否匹配条件。

    在特殊情况下，测试行发现与同一数据访问运算符中的谓词不匹配（例如扫描或查找），当前行上的锁在获取下一行上的锁U之前被释放。U否则，U锁会像往常一样保持到事务结束。

    这是对 SQL Server 如何实现UPDLOCK提示的描述。我确实理解文档中并不清楚，或者您希望它如何表现。然而，事情就是这样。

    解决方法

    没有完美的方法来实现您想要的，但有几个部分解决方案。

    第一个是WITH (UPDLOCK, READPAST, ROWLOCK)：

    SELECT 
    ST.PrimaryKey
FROM dbo.SomeTable AS ST 
    WITH (UPDLOCK, READPAST, ROWLOCK) 
WHERE
    ST.ColumnA = 'A' 
    AND ST.ColumnB BETWEEN 6 AND 8 
    AND ST.ColumnC != 'C';

    如果要测试的行具有不兼容的行级锁，这将跳过阻塞。如果在页或表级别持有不兼容的锁，查询仍将阻塞。

    第二种解决方法是在会话隔离级别读取，然后U通过加入主键来锁定单独的数据访问：

    SELECT 
    CA.PrimaryKey 
FROM dbo.SomeTable AS ST -- No hints here
CROSS APPLY 
(
    SELECT TOP (1) 
        ST2.PrimaryKey
    FROM dbo.SomeTable AS ST2 
        WITH (UPDLOCK, FORCESEEK)
    WHERE
        ST2.PrimaryKey = ST.PrimaryKey
) AS CA
WHERE
    ST.ColumnA = 'A' 
    AND ST.ColumnB BETWEEN 6 AND 8 
    AND ST.ColumnC != 'C';

    重要的是要完全遵循那里的模式，包括顶部。如果正确实施，此方法在实践中应该非常可靠。

    上插

    我不得不提到这个问题似乎是一个 upsert 模式。UPDLOCK单独服用是不够的。

    您还需要围绕 upsert 的每个部分的事务，以及SERIALIZABLE确保任何不存在的行在执行插入之前继续不存在的提示。更新锁只能对存在的行进行。请参阅Michael J Swart 的SQL Server UPSERT 模式和反模式。

    当然，当您使用SERIALIZABLE语义时，您的阻塞机会可能会大大增加。

    • 7

相关问题