Concurrency handling

Question

Hi,

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

Answer 1

I'm not sure that concurrency should be handled in the database like this. The database itself should be able manage isolation and transactional behavior, but the threading behavior ought to be done in code.

Answer 2

You should really study about transactions, which are part of the SQL language.

Answer 3

In SQL Server a recommended approach for type of situation is to create a column of type 'rowversion' and use that to check if any of the fields in that row have changed.

SQL Server guarantees that if any value in the row changes (or a new row is inserted) it's rowversion column will automatically updated to different value. Letting the database handle this for you is much more reliable than trying to do it yourself.

In your update statements you simply need to add a where clause to check that the rowversion value is the same as it was when you first retrieved the row. If not, someone else has changed the row (ie: it's dirty)

Also, from that page:

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.

Answer 4

rowversion suggestion is correct I would say but its disappointing to see that timestamp will be deprecated soon. Some of my OLD applications are using this for different reasons then checking concurrency.

Answer 5

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 *RowUpdateTimeStam*p 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

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
    -- @originalRowUpdateTimeStamp used for optimistic concurrency mechanism
    @Id int,
    @SoftwareName varchar(100),
    @originalRowUpdateTimeStamp bigint,
    @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  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.

Answer 6

Great Explanation..Thanks.

Some more questions

1)Which is the best method to store the RowUpdateTimeStamp value in aspx page? [In my scenario session variable is not allowed]. Is it hidden field?

2) Why should I check joining tables also? If I am updating only one table (though multiple tables are used for select) I can take the RowUpdateTimeStamp of that table alone, right? If I am going for multiple update statements for multiple tables, I need to take the max of RowUpdateTimeStamp . Is it correct?

3) Suppose I am displaying a list of software manufacturer details in a grid view. The gridview has editable text boxes. User does a bulk update. Which is the best method to store the RowUpdateTimeStamp value in aspx page?

Thanks

Lijo