在update语句中获取最新的rowversion / timestamp值 - Sql Server [英] Getting latest rowversion/timestamp value in update statement - Sql Server

问题描述

我使用rowversion列来处理乐观并发，并希望在更新时获得新的rowversion值，以便我的数据层具有最新值，并且可以执行另一个更新并获得并发异常（除非该记录已由其他人更新）。

I'm using rowversion columns for handling optimistic concurrency and want to get the new rowversion value back when I've done an update so that my data layer has the latest value and can perform another update with getting a concurrency exception (unless the record has been update by someone else).

我只是在做更新后在数据层中获取，但这不是很有效或完全可靠。

I was just doing a get in the data layer after doing an update but this wasn't very efficient or perfectly reliable.

对于下表：

CREATE TABLE PurchaseType
(
    PurchaseTypeCode nvarchar(20) NOT NULL PRIMARY KEY CLUSTERED (PurchaseTypeCode),
    Name nvarchar(50) NOT NULL,
    TS rowversion NOT NULL
)

我试过：

CREATE PROCEDURE PurchaseType_UpdateWithGet
@PurchaseTypeCode nvarchar(20),
@Name nvarchar(50),
@TS rowversion OUTPUT    
AS

UPDATE PurchaseType
SET Name = @Name
WHERE PurchaseTypeCode = @PurchaseTypeCode
 AND TS = @TS

SELECT @TS = TS FROM PurchaseType WHERE PurchaseTypeCode = @PurchaseTypeCode
GO

但不完全满意，因为可能没有从别人的更新中获得rowverion值。然后我在rowversion文档中遇到OUTPUT语句（http://msdn.microsoft.com/en-us/library/ms182776.aspx / http://msdn.microsoft.com/en-us/library/ms177564.aspx ）并尝试使用：

but wasn't entirely happy because of the possibility of not getting the rowverion value from someone else's update. Then I came across the OUTPUT statement in rowversion documentation (http://msdn.microsoft.com/en-us/library/ms182776.aspx / http://msdn.microsoft.com/en-us/library/ms177564.aspx) and tried this:

CREATE PROCEDURE PurchaseType_UpdateWithOutput
@PurchaseTypeCode nvarchar(20),
@Name nvarchar(50),
@TS rowversion OUTPUT    
AS

DECLARE @Output TABLE (TS BINARY(8))

UPDATE PurchaseType
SET Name = @Name
    OUTPUT inserted.TS into @Output
WHERE PurchaseTypeCode = @PurchaseTypeCode
    AND TS = @TS

SELECT TOP 1 @TS = TS FROM @Output
GO

这很好。在我的基本测试（只运行10000次调用和计时），OUTPUT选项需要大约40％的时间，但仍然少于半毫秒。我们的问题是，有没有人知道一个更好/更简单的方法来做到这一点？我的问题是，有没有任何可测量的时间与SET统计时间开。

This works well. In my very basic tests (just running 10000 calls and timing it) the OUTPUT option takes about 40% longer but still less than half a millisecond. Neither took any measurable time with SET STATISTICS TIME ON.

我曾经希望一个函数我可以使用类似于SCOPE_IDENTITY（）的标识列，但没有找到任何类似的。

I had hoped for a function I could use similar to SCOPE_IDENTITY() for identity columns but can't find anything like that. Anyone know if I'm missing something?

提前感谢。

推荐答案

您的问题是，在更新和选择其他人之间更新行。因此，当您在更新语句之后选择时，不会获得相同的值。这是一个不可重复读的问题。您应该使用锁定提示（UPDLOCK）与update语句。这个锁将允许读者（即共享锁），但会阻塞写者。所以你会在更新后得到相同的行。所以你的存储过程应该是 -

Your problem is that between update and select someone else updates the rows. So when you do select after your update statement you do not get the same values. This is a issue of non repeatable read. You should should use locking hint (UPDLOCK) with update statement. This lock will allow readers (that is shared locks) but will block writers. So you will get same rows after update. So your stored procedure should be -

CREATE PROCEDURE PurchaseType_UpdateWithGet
@PurchaseTypeCode nvarchar(20),
@Name nvarchar(50),
@TS rowversion OUTPUT    
AS

UPDATE PurchaseType with (updlock)
SET Name = @Name
WHERE PurchaseTypeCode = @PurchaseTypeCode
 AND TS = @TS

SELECT @TS = TS FROM PurchaseType WHERE PurchaseTypeCode = @PurchaseTypeCode
GO

这篇关于在update语句中获取最新的rowversion / timestamp值 - Sql Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！