Part 20 - Using ROWVERSION or TIMESTAMP to detect concurrency conflicts

Suggested Videos
Part 17 - Concurrency in Linq to SQL
Part 18 - Handling ChangeConflictException
Part 19 - UpdateCheck Property



This is continuation to Part 19. Please watch Part 19 before proceeding. In this video we will discuss how to use ROWVERSION or TIMESTAMP columns to detect concurrency conflicts in linq to sql. Let us understand this with an example. We will be using the following Accounts table in this demo.





By default LINQ to SQL uses all the columns of the table in the WHERE clause to detect concurrency conflicts. The query would look as shown below.

exec sp_executesql N'UPDATE [dbo].[Accounts]

SET [AccountBalance] = @p3

WHERE ([AccountNumber] = @p0) AND ([AccountName] = @p1)

AND ([AccountBalance] = @p2)',

N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 int',@p0=1,

@p1=N'John Mary',@p2=1000,@p3=1500

This is OK if we have a few columns in the table. In real time applications we may have tables with large number of columns. For example, what if the table has 30 columns. The WHERE clause would be huge and it can impact the performance of the application.

In situations like this we can use ROWVERSION or TIMESTAMP columns. Here are the steps

Step 1 : Add a Version column to the Accounts table. The datatype of the column must be either ROWVERSION or TIMESTAMP. The value for this column is automatically generated by the database if the row gets changed. So this column can alone be used to detect concurrency conflicts.

ALTER TABLE Accounts

ADD [Version] ROWVERSION

Step 2 : In Visual Studio, delete the Account Entity from the Sample.dbml file

Step 3 : In Server Explorer window in Visual Studio, right click on Accounts table and select "Refresh". 

Step 4 : Drag and  drop Accounts table on the Designer surface of Sample.dbml file. Notice that a Version Property is automatically added. Navigate to Sample.Designer.cs file and look at the code generated for this property. Notice that IsVersion & IsDbGenerated properties are set to true.

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_Version",

AutoSync = AutoSync.Always, DbType = "rowversion NOT NULL", CanBeNull = false,

IsDbGenerated = true, IsVersion = true, UpdateCheck = UpdateCheck.Never)]

public System.Data.Linq.Binary Version

{

    get

    {

        return this._Version;

    }

    set

    {

        if ((this._Version != value))

        {

            this.OnVersionChanging(value);

            this.SendPropertyChanging();

            this._Version = value;

            this.SendPropertyChanged("Version");

            this.OnVersionChanged();

        }

    }

}

Testing for concurrency conflicts : Let's now simulate the scenario of 2 users updating the same record at the same time. To do this

Step 1 : Throw a break point on the line where we call db.SubmitChanges(); in btnDeposit_Click() method.

Step 2 : Run the application in Debug mode, and click "Deposit $500" button. The execution should stop on SubmitChanges() method.

Step 3 : At this point open SQL Server Management Studio. 
   a) Execute the following SELECT query
       Select * from Accounts where AccountNumber = 1
   b) Notice the Value of Version column
   c) Execute the following UPDATE query
       Update Accounts set AccountBalance = AccountBalance - 300 
       Where AccountNumber = 1
   d) Now notice that the Value of Version column is automatically changed to a new value

Step 4 : Open SQL Profiler and run a new trace.

Step 5 : Now come back to Visual Studio and press F5 to continue the execution. Notice that "ChangeConflictException" is thrown as expected.

Step 6 : In SQL Profiler notice that the UPDATE query that is generated has used Version column in the WHERE clause to detect concurrency conflicts.

exec sp_executesql N'UPDATE [dbo].[Accounts]

SET [AccountBalance] = @p2

WHERE ([AccountNumber] = @p0) AND ([Version] = @p1)

SELECT [t1].[Version]

FROM [dbo].[Accounts] AS [t1]

WHERE ((@@ROWCOUNT) > 0) AND ([t1].[AccountNumber] = @p3)',

N'@p0 int,@p1 timestamp,@p2 int,@p3 int',

@p0=1,@p1=0x0000000000002715,@p2=1500,@p3=1