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

问题描述

假设，我将要使用ASP.NET和SQL Server 2005启动一个项目。我必须为这个应用程序设计并发性要求。我计划在每个表中添加一个TimeStamp列。在更新表时，我将检查TimeStamp列是否与所选的相同。

这种方法是否足够？

感谢

Lijo

解决方案

首先，你在问题中描述的是我的意见 最佳方式 对于MS SQL作为数据库的ASP.NET应用程序。数据库中没有锁定。

如何阅读一些答案，这在术语上有一个误解。我们都意味着使用Microsoft SQL Server 2008或更高版本来保存数据库。如果在MS SQL Server 2008文档中打开主题rowversion（Transact-SQL），您会发现以下内容：

timestamp 是
rowversion 数据类型的同义词，并且受到
数据类型同义词的行为的约束。 ...
timestamp 语法已被弃用
此功能将在
未来版本的Microsoft SQL
服务器中删除，避免使用此功能
新开发工作，并计划
修改当前使用
此功能的应用程序。

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

当我读到你的问题，我读TimeStamp作为列名键入 rowversion 数据。我个人更喜欢名称​​ RowUpdateTimeStamp 。在AzManDB（请参阅Microsoft授权管理器与存储为数据库）我可以看到这样的名称。有时也使用 ChildUpdateTimeStamp 来跟踪分层的 RowUpdateTimeStamp 结构（相对于触发器）。

我在上一个项目中实现了这种方法，并且非常高兴。通常，您可以执行以下操作：

1. 将 RowUpdateTimeStam p列添加到数据库的每个表， rowversion （在Microsoft SQL Management Studio中将显示为时间戳，这是相同的）。


2. 将结果发送到客户端的查询，以便与主数据一起发送额外的 RowVersion 值。如果您有一个使用JOINT的SELECT，您应该从两个表中发送最大 RowUpdateTimeStamp 值的 RowVersion 值，例如

  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 
从dbo.Software AS s 
 INNER JOIN dbo.Manufacturer AS m ON s。 Manufacturer_Id = m.Id 
  

或进行如下的数据投放

  SELECT s.Id AS Id 
，s.Name AS软件名
，m.Name AS制造商名称
，CASE WHEN，s.RowUpdateTimeStamp> m.RowUpdateTimeStamp 
 THEN CAST（s.RowUpdateTimeStamp AS bigint）
 ELSE CAST（m.RowUpdateTimeStamp AS bigint）
 END AS RowUpdateTimeStamp 
来自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）这样的函数，相应的结构可能如下所示：

 （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 
  

3. 所有已断开连接的客户端（网络客户端）不仅接收并保存一些数据行，还接收并保存RowVersion （键入 ulong
   
4. 在尝试修改断开的客户端的数据时，客户端应向服务器发送与原始数据对应的 RowVersion 。 spSoftwareUpdate 存储过程可能类似于

  CREATE PROCEDURE dbo.spSoftwareUpdate 
 @Id int，
 @SoftwareName varchar（100）
 @originalRowUpdateTimeStamp bigint  - 用于乐观并发机制
 @NewRowUpdateTimeStamp bigint OUTPUT 
 AS 
 BEGIN 
  - 添加了SET NOCOUNT ON以防止来自
的额外结果集 - 干扰SELECT语句。 
  -  ExecuteNonQuery（）返回-1，但它不是一个错误
  - 一个应该测试@NewRowUpdateTimeStamp 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 的高级选项中使用乐观并发（如使用乐观并发 。

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

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

我无法在此处写入整个代码，很多例子在互联网上。我想只重复一次，在我看来，基于 rowversion 的使用乐观并发是 大多数ASP.NET方案的最佳方式 。

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?

Please advice.

Thanks

Lijo

解决方案

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.

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:

"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."

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.

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. 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

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

3. All disconnected clients (web clients) receive and hold not only some rows of data, but RowVersion (type ulong) of the data row.
4. 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

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.

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.

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.

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屋！