如何以独占方式锁定阻止 CRUD 操作的行 [英] How to exclusively lock a row that prevent CRUD operation

问题描述

您好专家我如何在 sql server 中锁定一行，以防止 CRUD 操作甚至 SELECT.是否可以?可序列化隔离级别不会阻止 SELECT.谢谢

Hi expert how I can lock a row in sql server that prevent CRUD operation even SELECT. Is it Possible? Serializable Isolation level does not prevent SELECT. thanks

推荐答案

BEGIN TRAN

    SELECT 1
    FROM Table
    WITH (XLOCK, ROWLOCK)

COMMIT TRAN

这样就行了.

编辑

正如其他人所指出的，你不能锁定一行不被读取.我知道这样做的唯一方法如下:

As noted by others, you cannot lock a row to not be read. The only way I know of doing this is as follows:

WITH (UPDLOCK, TABLOCK)

这是假设在 SELECT 语句中从不使用 WITH (NOLOCK)(无论如何都应该避免).

And this is assuming that a WITH (NOLOCK) is never used in a SELECT statement (which should be avoided anyway).

我对此进行了测试，它会起作用，尽管 TABLOCK 仅应在极端情况下使用.当然，如果需要并发性，这是一个糟糕的解决方案，并且需要某种其他形式的锁定.一种方法是更新位列Available True/False"并仅读取可用 = True 的行.正如@gbn 建议的那样，READPAST 可以与此一起使用.

I tested this and it will work, although TABLOCK should only be used in extreme cases. Certainly if concurrency is required, it's a bad solution and some other form of locking would be needed. One way is to update a bit column "Available True/False" and only read rows where Available = True. As @gbn suggested, READPAST could be used with this.

这篇关于如何以独占方式锁定阻止 CRUD 操作的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！