Sql事务的并发处理 [英] Concurrency handling of Sql transactrion

问题描述

假设，我即将开始一个使用 ASP.NET 和 SQL Server 2005 的项目.我必须为这个应用程序设计并发需求.我打算在每个表中添加一个时间戳列.在更新表格时，我将检查 TimeStamp 列是否与选择时相同.

Suppose, I am about to start a project using ASP.NET and SQL Server 2005. I have to design the concurrency requirement for this application. I am planning to add a TimeStamp column in each table. While updating the tables I will check that the TimeStamp column is same, as it was selected.

这种方法就足够了吗?或者这种方法在任何情况下都有什么缺点?

Will this approach be suffice? Or is there any shortcomings for this approach under any circumstances?

请指教.

谢谢

利乔

推荐答案

首先，您在问题中描述的方式在我看来是最好的方式以 MS SQL 作为数据库的 ASP.NET 应用程序.数据库中没有锁定.它非常适合永久断开连接的客户端，例如网络客户端.

First of all the way which you describe in your question is in my opinion the best way for ASP.NET application with MS SQL as a database. There is no locking in the database. It is perfect with permanently disconnected clients like web clients.

如何从一些答案中读取，术语中存在误解.我们都指的是使用 Microsoft SQL Server 2008 或更高版本来保存数据库.如果您在 MS SQL Server 2008 文档中打开主题rowversion (Transact-SQL)"，您会发现以下内容:

How one can read from some answers, there is a misunderstanding in the terminology. We all mean using Microsoft SQL Server 2008 or higher to hold the database. If you open in the MS SQL Server 2008 documentation the topic "rowversion (Transact-SQL)" you will find following:

"时间戳是rowversion 数据类型并受数据类型同义词的行为." ...时间戳 语法已弃用.此功能将在Microsoft SQL 的未来版本服务器.避免在以下情况下使用此功能新的开发工作，并计划修改当前使用的应用程序这个功能."

"timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonym." … "The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

所以 timestamp 数据类型是 MS SQL 的 rowversion 数据类型的同义词.它拥有 64 位计数器，该计数器存在于每个数据库内部，可以看作 @@DBTS.修改数据库的一张表中的一行后，计数器会增加.

So timestamp data type is the synonym for the rowversion data type for MS SQL. It holds 64-bit the counter which exists internally in every database and can be seen as @@DBTS. After a modification of one row in one table of the database, the counter will be incremented.

当我阅读您的问题时，我将TimeStamp"读为 rowversion 数据类型的列名.我个人更喜欢名称 RowUpdateTimeStamp.在 AzManDB 中(参见 Microsoft 授权管理器，商店为 DB)我可以看到这样的名称.有时也使用 ChildUpdateTimeStamp 来跟踪分层的 RowUpdateTimeStamp 结构(关于触发器).

As I read your question I read "TimeStamp" as a column name of the type rowversion data. I personally prefer the name RowUpdateTimeStamp. In AzManDB (see Microsoft Authorization Manager with the Store as DB) I could see such name. Sometimes were used also ChildUpdateTimeStamp to trace hierarchical RowUpdateTimeStamp structures (with respect of triggers).

我在上一个项目中实施了这种方法并且非常高兴.通常您会执行以下操作:

I implemented this approach in my last project and be very happy. Generally you do following:

1. 将 RowUpdateTimeStamp 列添加到类型为 rowversion 的数据库的每个表中(它将在 Microsoft SQL Management Studio 中显示为 timestamp>，这是一样的).
2. 您应该构造所有用于将结果发送到客户端的 SQL SELECT 查询，以便将附加的 RowVersion 值与主要数据一起发送.如果您有一个带有 JOINT 的 SELECT，您应该从两个表中发送最大 RowUpdateTimeStamp 值的 RowVersion，例如

1. Add RowUpdateTimeStamp column to every table of you database with the type rowversion (it will be seen in the Microsoft SQL Management Studio as timestamp, which is the same).
2. You should construct all you SQL SELECT Queries for sending results to the client so, that you send additional RowVersion value together with the main data. If you have a SELECT with JOINTs, you should send RowVersion of the maximum RowUpdateTimeStamp value from both tables like

SELECT s.Id AS Id
    ,s.Name AS SoftwareName
    ,m.Name AS ManufacturerName
    ,CASE WHEN s.RowUpdateTimeStamp > m.RowUpdateTimeStamp
          THEN s.RowUpdateTimeStamp 
          ELSE m.RowUpdateTimeStamp 
     END AS RowUpdateTimeStamp 
FROM dbo.Software AS s
    INNER JOIN dbo.Manufacturer AS m ON s.Manufacturer_Id=m.Id

或者像下面这样进行数据转换

Or make a data casting like following

SELECT s.Id AS Id
    ,s.Name AS SoftwareName
    ,m.Name AS ManufacturerName
    ,CASE WHEN s.RowUpdateTimeStamp > m.RowUpdateTimeStamp
          THEN CAST(s.RowUpdateTimeStamp AS bigint)
          ELSE CAST(m.RowUpdateTimeStamp AS bigint)
     END AS RowUpdateTimeStamp 
FROM dbo.Software AS s
    INNER JOIN dbo.Manufacturer AS m ON s.Manufacturer_Id=m.Id

将RowUpdateTimeStamp保存为bigint，对应C#的ulong数据类型.如果您从多个表中创建 OUTER JOINT 或 JOINT，则来自所有表的构造 MAX(RowUpdateTimeStamp) 会显得稍微复杂一些.因为 MS SQL 不支持像 MAX(a,b,c,d,e) 这样的函数，相应的构造可能如下所示:

to hold RowUpdateTimeStamp as bigint, which corresponds ulong data type of C#. If you makes OUTER JOINTs or JOINTs from many tables, the construct MAX(RowUpdateTimeStamp) from all tables will be seen a little more complex. Because MS SQL don't support function like MAX(a,b,c,d,e) the corresponding construct could looks like following:

(SELECT MAX(rv)
 FROM (SELECT table1.RowUpdateTimeStamp AS rv
      UNION ALL SELECT table2.RowUpdateTimeStamp
      UNION ALL SELECT table3.RowUpdateTimeStamp
      UNION ALL SELECT table4.RowUpdateTimeStamp
      UNION ALL SELECT table5.RowUpdateTimeStamp) AS maxrv) AS RowUpdateTimeStamp

1. 所有断开连接的客户端(网络客户端)不仅接收和保存一些数据行，而且接收和保存数据行的RowVersion(类型ulong).
2. 在尝试修改断开连接的客户端的数据时，您的客户端应将与原始数据对应的 RowVersion 发送到服务器.spSoftwareUpdate 存储过程可能看起来像

1. All disconnected clients (web clients) receive and hold not only some rows of data, but RowVersion (type ulong) of the data row.
2. In one try to modify data from the disconnected client, you client should send the RowVersion corresponds to the original data to server. The spSoftwareUpdate stored procedure could look like

CREATE PROCEDURE dbo.spSoftwareUpdate
    @Id int,
    @SoftwareName varchar(100),
    @originalRowUpdateTimeStamp bigint, -- used for optimistic concurrency mechanism
    @NewRowUpdateTimeStamp bigint OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- ExecuteNonQuery() returns -1, but it is not an error
    -- one should test @NewRowUpdateTimeStamp for DBNull
    SET NOCOUNT ON;

    UPDATE dbo.Software
    SET Name = @SoftwareName
    WHERE Id = @Id AND RowUpdateTimeStamp <= @originalRowUpdateTimeStamp

    SET @NewRowUpdateTimeStamp = (SELECT RowUpdateTimeStamp
                                  FROM dbo.Software
                                  WHERE (@@ROWCOUNT > 0) AND (Id = @Id));
END

dbo.spSoftwareDelete 存储过程的代码看起来是一样的.如果你不开启NOCOUNT，你会在很多场景中自动生成DBConcurrencyException.Visual Studio 为您提供了使用乐观并发的可能性，例如 TableAdapter 或 DataAdapter 的高级选项中的使用乐观并发"复选框.

Code of dbo.spSoftwareDelete stored procedure look like the same. If you don’t switch on NOCOUNT, you can produce DBConcurrencyException automatically generated in a lot on scenarios. Visual Studio gives you possibilities to use optimistic concurrency like "Use optimistic concurrency" checkbox in Advanced Options of the TableAdapter or DataAdapter.

如果您查看 dbo.spSoftwareUpdate 存储过程，您会发现，我在 WHERE 中使用 RowUpdateTimeStamp <= @originalRowUpdateTimeStamp 而不是 RowUpdateTimeStamp = @originalRowUpdateTimeStamp.我这样做是因为，具有客户端的 @originalRowUpdateTimeStamp 的值通常被构造为 MAX(RowUpdateTimeStamp) 来自多个表.所以它可以是 RowUpdateTimeStamp <@originalRowUpdateTimeStamp.您应该使用严格相等 = 并在此处复制与 SELECT 语句中使用的相同的复杂 JOIN 语句，或者像我一样使用 <= 构造并保持完全相同的安全性之前.

If you look at dbo.spSoftwareUpdate stored procedure carful you will find, that I use RowUpdateTimeStamp <= @originalRowUpdateTimeStamp in WHERE instead of RowUpdateTimeStamp = @originalRowUpdateTimeStamp. I do so because, the value of @originalRowUpdateTimeStamp which has the client typically are constructed as a MAX(RowUpdateTimeStamp) from more as one tables. So it can be that RowUpdateTimeStamp < @originalRowUpdateTimeStamp. Either you should use strict equality = and reproduce here the same complex JOIN statement as you used in SELECT statement or use <= construct like me and stay exact the same safe as before.

顺便说一句，我们可以基于 RowUpdateTimeStamp 为 ETag 构造非常好的值，它可以在 HTTP 标头中与数据一起发送到客户端.使用 ETag，您可以在客户端实现智能数据缓存.

By the way, one can construct very good value for ETag based on RowUpdateTimeStamp which can sent in HTTP header to the client together with data. With the ETag you can implement intelligent data caching on the client side.

我不能在这里写完整的代码，但你可以在互联网上找到很多例子.我只想再重复一次，在我看来，基于 rowversion 的使用乐观并发是大多数 ASP.NET 场景的最佳方法.

I can’t write whole code here, but you can find a lot of examples in Internet. I want only repeat one more time that in my opinion usage optimistic concurrency based on rowversion is the best way for the most of ASP.NET scenarios.

这篇关于Sql事务的并发处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！