在 SQL Server 中，如何以类似于 Oracle 的“SELECT FOR UPDATE WAIT"的方式锁定单行? [英] In SQL Server, how can I lock a single row in a way similar to Oracle's "SELECT FOR UPDATE WAIT"?

问题描述

我有一个程序可以连接到 Oracle 数据库并对其执行操作.我现在想调整该程序以支持 SQL Server 数据库.

I have a program that connects to an Oracle database and performs operations on it. I now want to adapt that program to also support an SQL Server database.

在 Oracle 版本中，我使用SELECT FOR UPDATE WAIT"来锁定我需要的特定行.我在更新基于 SELECT 的结果的情况下使用它，其他会话绝对不能同时修改它，因此他们必须先手动锁定它.该系统很容易受到试图同时访问相同数据的会话的影响.

In the Oracle version, I use "SELECT FOR UPDATE WAIT" to lock specific rows I need. I use it in situations where the update is based on the result of the SELECT and other sessions can absolutely not modify it simultaneously, so they must manually lock it first. The system is highly subject to sessions trying to access the same data at the same time.

例如:
两个用户尝试获取数据库中优先级最高的行，将其标记为忙碌，对其执行操作，然后再次将其标记为可用以备后用.在 Oracle 中，逻辑基本上是这样的:

For example:
Two users try to fetch the row in the database with the highest priority, mark it as busy, performs operations on it, and mark it as available again for later use. In Oracle, the logic would go basically like this:

BEGIN TRANSACTION;
SELECT ITEM_ID FROM TABLE_ITEM WHERE ITEM_PRIORITY > 10 AND ITEM_CATEGORY = 'CT1'
    ITEM_STATUS = 'available' AND ROWNUM = 1 FOR UPDATE WAIT 5;
UPDATE [locked item_id] SET ITEM_STATUS = 'unavailable';
COMMIT TRANSACTION;

请注意，查询是在我的代码中动态构建的.另请注意，当先前最有利的行被标记为不可用时，第二个用户将自动选择下一个，依此类推.此外，在不同类别上工作的不同用户将不必等待彼此的锁被释放.最坏的情况下，5秒后会返回错误并取消操作.

Note that the queries are built dynamically in my code. Also note that when the previously most favorable row is marked as unavailable, the second user will automatically go for the next one and so on. Furthermore, different users working on different categories will not have to wait for each other's locks to be released. Worst comes to worst, after 5 seconds, an error would be returned and the operation would be cancelled.

最后，问题是:如何在 SQL Server 中实现相同的结果?我一直在研究锁定提示，理论上，它们似乎应该起作用.但是，阻止其他锁的唯一锁是UPDLOCK"和XLOCK"，它们都只在表级别工作.
那些在行级别起作用的锁提示都是共享锁，也不能满足我的需求(两个用户可以同时锁定同一行，都将其标记为不可用并对相应项执行冗余操作).

So finally, the question is: how do I achieve the same results in SQL Server? I have been looking at locking hints which, in theory, seem like they should work. However, the only locks that prevents other locks are "UPDLOCK" AND "XLOCK" which both only work at a table level.
Those locking hints that do work at a row level are all shared locks, which also do not satisfy my needs (both users could lock the same row at the same time, both mark it as unavailable and perform redundant operations on the corresponding item).

有些人似乎添加了一个修改时间"列，以便会话可以验证他们是修改它的人，但这听起来像是会有很多冗余和不必要的访问.

Some people seem to add a "time modified" column so sessions can verify that they are the ones who modified it, but this sounds like there would be a lot of redundant and unnecessary accesses.

推荐答案

在 SQL Server 中有锁定提示，但它们不像您提供的 Oracle 示例那样跨越它们的语句.在 SQL Server 中执行此操作的方法是在包含要执行的语句的事务上设置隔离级别.请参阅此 MSDN 页面，但总体结构如下所示:>

In SQL Server there are locking hints but they do not span their statements like the Oracle example you provided. The way to do it in SQL Server is to set an isolation level on the transaction that contains the statements that you want to execute. See this MSDN page but the general structure would look something like:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    select * from ...

    update ...

COMMIT TRANSACTION;

SERIALIZABLE 是最高的隔离级别.请参阅其他选项的链接.来自 MSDN:

SERIALIZABLE is the highest isolation level. See the link for other options. From MSDN:

SERIALIZABLE 指定以下内容:

SERIALIZABLE Specifies the following:

语句无法读取已修改但尚未修改的数据由其他事务提交.

Statements cannot read data that has been modified but not yet committed by other transactions.

没有其他事务可以修改已被读取的数据当前事务直到当前事务完成.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

其他事务不能插入具有键值的新行落入当前任何语句读取的键范围内事务直到当前事务完成.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

这篇关于在 SQL Server 中，如何以类似于 Oracle 的“SELECT FOR UPDATE WAIT"的方式锁定单行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！