Concurrency handling of Sql transactrion

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:

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

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

More Related Contents:

Leave a Comment Cancel reply