根据rowversion值更新记录? [英] Update record based on rowversion value?

问题描述

我最近实现了SQL rowversion以防止系统中的并发问题.更新表中的单行时，我在where子句中使用rowversion.到目前为止，我已经测试过了，似乎是一个不错的解决方案.现在，我正在寻找一种在系统中实现此功能的简便方法.当用户想要更新记录时，将运行以下SP:

I have recently implemented SQL rowversion to prevent concurrency issues in my system. I use rowversion in where clause when updating single rows in the tables. So far I have tested and seems like a good solution. Now I'm looking for an easy way to implement this feature in my system. Here is SP that runs when user wants to update the record:

CREATE PROCEDURE [dbo].[UpdateBuilding]
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @OriginalRowVersion ROWVERSION
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN
        UPDATE dbo.Building
        SET Status = @Status,
            Name = @Name,
            Code = @Code,
            ActionDt = CURRENT_TIMESTAMP
        WHERE RowVersion = @OriginalRowVersion
        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('Buildingwith code %s was modified or deleted by another user.', 16, 1, @Code);
        END;
    END;

如果要在上面执行SP，则需要传递必需的参数.这就是我在SQL Management Studio中调用SP的方式:

If I want to execute SP above I would need to pass required parameters. This is how I call SP in SQL Management Studio:

EXEC UpdateBuilding
    @Status = 1,
    @Name = "Rockefeller Center",
    @Code = 436,
    @OriginalRowVersion = 0x0000000000006955;

现在，我开始研究如何在我的系统中使用ColdFusion与Datatbase进行通信.这是有关如何在CF 2016中执行此过程的示例:

Now I started looking how to implement this in my system where I use ColdFusion to communicate with Datatbase. Here is example on how this procedure will be executed with CF 2016:

<cfstoredproc procedure="UpdateBuilding" datasource="#dsn#">
    <cfprocparam dbvarname="@Status" value="#trim(arguments.status)#" cfsqltype="cf_sql_bit" />
    <cfprocparam dbvarname="@Code" value="#trim(arguments.code)#" cfsqltype="cf_sql_char" maxlength="2" null="#!len(trim(arguments.code))#" />
    <cfprocparam dbvarname="@Name" value="#trim(arguments.name)#" cfsqltype="cf_sql_varchar" maxlength="50" null="#!len(trim(arguments.name))#" />
    <cfprocresult name="Result"/>
</cfstoredproc>

您可以看到所有值都与用户在表单中提交的参数一起传递.但是，基于PK值(在我的情况下为代码"列)进行更新非常简单.现在，我具有二进制值，这使一切变得更加复杂.首先，我使用JSON将数据发送到客户端.在JSON对象中发送rowversion时，需要将该值转换为binary，然后在用户提交表单时转换回该值.我想知道是否有更好的方法来实现这一目标?理想情况下，我什至不发送rowversion值到用户端.我将其保留在后端，一旦用户基于PK提交表单拉行版本值，然后调用存储过程.如果有人知道处理这种情况的好方法，请告诉我.我以前没有用过rowversion，这对我来说是新的.

You can see that all values are passed with arguments that user submits in the form. However, updating based on PK value (Code column in my case) was pretty simple. Now I have binary value and that makes everything more complicated. First I use JSON to send the data to client side. Sending rowversion in JSON object would require converting that value to binary and then converting back when user submits the form. I'm wondering if there is better way to achieve this? Ideally I would not even send rowversion value to the user side. I woul keep that on the back end and once user submits the form pull row version value based on PK then call stored procedure. If anyone knows good way to handle this kind of situations please let me know. I have not used rowversion before and this is new to me.

推荐答案

我使用类似的方法，其中有一个名为version的列，其类型为int.我通过任何读取操作将其传递给客户端.如果客户端更新了一条记录，则它必须发回正在更新的记录的版本，并且更新将增加版本号.但是，我的方法设置了ColdFusion锁而不是DB锁.这是一些简化的逻辑:

I use a similar approach where I have a column named version of type int. I pass that to the client in any read operation. If the client updates a record, it must send back the version of the record being updating, and the update will increment the version number. However, my approach sets a ColdFusion lock instead of a DB lock. Here's some simplified logic:

function updateRecord (
    required numeric recordID,
    required struct updateData,
    required numeric version) {

    lock name="#arguments.recordID#" type="exclusive" timeout="1" throwontimeout=true {
        qRecord = queryExecute() // get the record
        if (qRecord.recordCount != 1) {
            throw();
        }
        if (qRecord.version != arguments.version) {
            throw();
        }
        // do the update using arguments.updateData
    }

}

与此有关的一个问题是，群集中的其他节点将不知道该命名锁.您将不得不想出另一种将代码段锁定到集群中其他请求的方法.如该线程中所述，有多种方法可以实现:

One issue with this is that other nodes in a cluster would not be aware of the named lock. You would have to come up with another way of locking that section of code to other requests across the cluster. There are ways to do it, as described in this thread:

https://dev.lucee.org/t/distributed-lock -management/1004

如果这是一个问题，我确定还有其他解决方案可用.

And if this is an issue, I'm sure there are other solutions available.

这篇关于根据rowversion值更新记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！