如何执行行锁？ [英] How to perform a row lock?

问题描述

我要锁定一个记录，然后任何人不得更改该记录。当我释放锁，那么人们可能会更改记录。

I want to lock one record and then no one may make changes to that record. When I release the lock, then people may change the record.

在此期间，一个记录被锁定，我想向用户显示一个警告，记录被锁定，不允许更改。

In the meantime that a record is locked, I want to show the user a warning that the record is locked and that changes are not allowed.

我怎样才能做到这一点？

How can I do this?

我已经试过所有的IsolationLevel水平，但他们都没有我想要的行为。一些隔离级别等到锁被释放，然后做出改变。我不希望这样，因为更新不此刻记录被锁定允许的。

I've tried all the IsolationLevel levels, but none of them has the behavior I want. Some of the Isolation levels wait until the lock is released and then make a change. I don't want this, because updating is not allowed at the moment a record is locked.

我能做些什么来锁定记录，并拒绝所有的变化？

What can I do to lock a record and deny all changes?

我使用SQL Server 2008

I use SQL Server 2008

推荐答案

通过假设这是MS SQL服务器，你可能想 UPDLOCK ，可能与 ROWLOCK 合并（Table提示的）。我无法找到它描述了理论像样的文章，但这里是简单的例子：

With the assumption that this is MS SQL server, you probably want UPDLOCK, possibly combined with ROWLOCK (Table hints). I'm having trouble finding a decent article which describes the theory, but here is quick example:

SELECT id From mytable WITH (ROWLOCK, UPDLOCK) WHERE id = 1 

这语句将放置一个的update锁定该行的事务的持续时间（所以要注意的时候是非常重要的交易将结束）。由于更新锁是与（需要更新记录​​）排它锁不兼容，这将阻止任何人更新这个纪录直到事务已经结束。

This statement will place an update lock on the row for the duration of the transaction (so it is important to be aware of when the transaction will end). As update locks are incompatible with exclusive locks (required to update records), this will prevent anyone from updating this record until the transaction has ended.

请注意其他进程试图修改，直到事务完成这一纪录将被阻止，但将继续与他们要求的任何写操作一旦交易结束（除非他们超时或杀死了一个僵持的过程）。如果要防止这种情况那么你的其他进程需要才能使用额外的提示要么如果检测到不兼容的锁中止，或者如果它已经改变跳过记录。

Note that other processes attempting to modify this record will be blocked until the transaction completes, however will continue with whatever write operation they requested once the transaction has ended (unless they are timed out or killed off as a deadlocked process). If you wish to prevent this then your other processes need to use additional hints in order to either abort if an incompatible lock is detected, or skip the record if it has changed.

此外，您不应该在等待用户输入。如果这是你的意图，那么你应该添加某种被修改列到表来代替。

Also, You should not use this method to lock records while waiting for user input. If this is your intention then you should add some sort of "being modified" column to your table instead.

在SQL Server锁定机制实际上只适合用于保护数据完整性/防止死锁 - 交易一般应保持的尽可能短的并且当然应该不等待用户的输入被保持

The SQL server locking mechanisms are really only suited for use to preserve data integrity / preventing deadlocks - transactions should generally be kept as short as possible and should certainly not be maintained while waiting for user input.

这篇关于如何执行行锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！