如何以独占方式锁定阻止 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屋!