我应该使用哪些锁提示（T-SQL）？ [英] Which lock hints should I use (T-SQL)?

问题描述

我要实现一个原子事务，如下所示：

  BEGIN TRAN A 
 
 SELECT id 
 FROM Inventory 
 WITH（???）
 WHERE material_id = 25 AND quantity> 10 
 
 / * 
使用库存记录处理一些事情，
最终写入一些更新，这些更新取决于特定库存记录具有足够数量的
大于10）。 
 * / 
 
 COMMIT TRAN A 
  

问题是还有其他发生从我们的库存消耗数量的交易，因此在选择记录和将更新写入事务A的时间之间，该记录可能会变为无效选择，因为其数量可能已降低到WHERE子句中的阈值以下。

因此，问题是我应该在WITH子句中使用什么锁定提示，以防止在完成更新并提交事务之前更改所选的库存记录？ / p>

编辑：
所以感谢John，一个好的解决方案似乎是将事务隔离级别设置为REPEATABLE READ。这将确保没有其他事务可以修改当前事务读取的数据，直到当前事务完成。

解决方案

您可以设置事务隔离级别，而不是使用查询提示。

以下来自联机丛书的参考资料提供了每个不同隔离

http ：//msdn.microsoft.com/en-us/library/ms173763.aspx

这里是解释各种类型的锁定行为的好文章SQL Server并提供示例。

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

I want to implement an atomic transaction like the following:

BEGIN TRAN A

SELECT id
FROM Inventory
WITH (???)
WHERE material_id = 25 AND quantity > 10

/*
Process some things using the inventory record and
eventually write some updates that are dependent on the fact that
that specific inventory record had sufficient quantity (greater than 10).
*/

COMMIT TRAN A

The problem is that there are other transactions happening that consume quantity from our inventory, so between the time that the record is selected and the updates are written in transaction A that record could become an invalid selection because it's quantity might have been lowered below the threshold in the WHERE clause.

So the question is what locking hints should I use in the WITH clause to prevent the selected inventory record from being changed before I finish my updates and commit the transaction?

EDIT: So thanks to John, a good solution seems to be to set the transaction isolation level to REPEATABLE READ. This will will make sure that "no other transactions can modify data that has been read by the current transaction until the current transaction completes."

解决方案

You may actually be better off setting the transaction isolation level rather than using a query hint.

The following reference from Books Online provides details of each of the different Isolation levels.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Here is good article that explains the various types of locking behaviour in SQL Server and provides examples too.

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

这篇关于我应该使用哪些锁提示（T-SQL）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！