Optimistic Concurrency in SQL Server

In one of our earlier post, we had talked about using ORA_ROWSCN to implement optimistic concurrency in the case of Oracle. In this post, we will cover how to implement this in the case of SQL Server. Read that previous blog post to see what optimistic concurrency is and when it is used. Optimistic concurrency in SQL Server can be implemented in two ways:

Optimistic Concurrency with Values:  When using optimistic concurrency with values, as the name suggests, the current values for the row (for each column) are compared to the older values from when the row was last fetched.   If any of the values have changed,  that indicates that some other session has made modifications to the row.  This is noted as a “no data exception” which the client application must trap and refresh the cursor and to let the end user decide whether they wish to perform modifications based on the new values.

Optimistic Concurrency with row versioning:  This is a more common approach of implementing optimistic concurrency in the case of SQL Server.  In this scheme,  we use a versioning scheme in the schema by adding a versioning column to the tables.  One can chose to either use just an integer column to implement optimistic concurrency with row versioning or one can choose to use the timestamp data-type.  The timestamp data-type has nothing to do with date or time – it is a 18 digit binary number that indicates the sequence of modifications made to the record.  Every database on an instance has a global current timestamp value which can be obtained by @@DBTS.  Think of it as a running counter for a database.  So, if there is a table that has a column called say opt_version_id that has a data-type of timestamp, any time a modification is made to this record, the value from @@DBTS is used to update this value in that row and the value is automatically incremented to the next value by SQL Server.  Let’s see this using an example:

CREATE TABLE dbo.DECIPHER_TEST
(
COL1                INT    IDENTITY NOT NULL,
COL2                INT             NOT NULL,
OPT_VERSION_ID        TIMESTAMP     NOT NULL,
CONSTRAINT PK_DECIPHER_TEST PRIMARY KEY (COL1)
)
GO
/*Let us create some data – we will populate only COL2*/
INSERT INTO dbo.DECIPHER_TEST (COL2) VALUES (10)
INSERT INTO dbo.DECIPHER_TEST (COL2) VALUES (20)
INSERT INTO dbo.DECIPHER_TEST (COL2) VALUES (30)

/*Check the data now*/

select * from dbo.DECIPHER_TEST

COL1        COL2        OPT_VERSION_ID
———– ———– ——————
1           10          0x00000000000007D1
2           20          0x00000000000007D2
3           30          0x00000000000007D3

As you can see from above, SQL Server automatically populated the opt_version_id column.  Let’s look at what is the value for @@DBTS right now:

select @@DBTS

——————
0x00000000000007D3

So, as you can see, it is the current value that it used for the column.  If another record is inserted, it will be the next higher up value.  Now, let’s see how this can be used to automatically enforce the optimistic concurrency – we will talk about this using T-SQL as an example and then will cover what goes on when using this using ADO or ADO.Net:

Session 1:  Suppose that Session 1 read the current value into a variable and is going to use that for doing the updates.  Say till time T1, it has executed these commands:

declare @x varbinary(18)
select @x = OPT_VERSION_ID from dbo.DECIPHER_TEST where COL1 = 1

print @x

0x00000000000007D1

Then, in another session, say at time T2, another session goes ahead and updates the record:

Session 2:

update dbo.DECIPHER_TEST set COL2 = 1000 where COL1 = 1

Now, say at time T3, Session one tries to make the update using the value that it read before:

Session 1:

update dbo.DECIPHER_TEST
SET COL2 = 100
WHERE COL1 = 1
AND OPT_VERSION_ID = @x

if (@@rowcount = 0)
begin
select ‘some other session updated the value.  Updated value is:’,  OPT_VERSION_ID
from dbo.DECIPHER_TEST
where COL1 =1
end

You will get:

OPT_VERSION_ID
——————————————————– ——————
some other session updated the value.  Updated value is: 0x00000000000007D4

This is because the other session already went in and update the record and SQL Server automatically updated this counter.  As a result, Session 1 got a no data found exception.  This concurrency methodology is used in those scenarios where there is a very little chance of other sessions updating the row in the interval between a read and a write i.e. the cost of occasional rollbacks due to clashes is not that big and a re-read and a re-try can be done and is acceptable to the business.

The advantage of using optimistic concurrency with row versioning is that the engine does not have to compare all the column’s before and after values – all it needs to use is the timestamp data-type column value.  In ADO, one can specify adLockOptimistic for the LockType…one thing to note is that if the table does not have a column with a timestamp data-type and the application has specified optimistic concurrency with row versioning, the cursor defaults to values based optimistic concurrency control instead.  And obviously, one table can have only one timestamp data-type column.  In future posts, we will discuss pessimistic concurrency model and multivalue concurrency models.