In the previous post, we tried to understand transactions and concurrency in SQL Server. We also looked into the problems occurring with concurrent transactions from multiple session instances. To understand it in layman's terms an example is when many users are trying to access the same data set of the same object, some for reading the data, some for manipulating the data, etc at the same time. These concurrent reads write operations can lead to data inconsistency and unexpected results. We also dived into Optimistic and Pessimistic Approaches to understand ways to tackle the concurrency problems. Optimistic Concurrency is implemented using the row versioning technique and it doesn't block concurrent read and write requests from multiple session instances. However do note that in the Optimistic approach, the write operation will still block other write operations. This post is to understand and implement row version data types in SQL Server. ' Rowversion is auto-generated unique binary numbers within the database and is generally used to version-stamp rows in tables. Each database has its counter which gets incremented when any manipulation operations are performed on a table that has a rowversion column. A table can have only one rowversion column and with every insertion or updation of the rows in the table, the incremented database rowversion value is inserted in the rowversion column. Hence rowversion columns should not be used as keys. Even if the actual value of the attribute of the table entity is not changed, the counter will still increment the rowversion column due to the update operation. To understand, supposing you have a row in the table with a column having a value of 2 and you perform the update operation on that row and again assign the value 2 to that column, the rowversion counter will still be incremented. To check the current value of the counter, we can use the below select query, which returns the current counter value.

select @@DBTS;

Create a table with rowversion column

create database TestDB;
use TestDB;
create table products(
    id int identity(1,1) primary key,
    product_id int,
    category_id int,
    price decimal(6,2),
    last_changed rowversion not null --column for rowversion
);

select @@DBTS as 'Before insert in products table';

insert into products(product_id, category_id, price) values (1, 1, 109.23), (2, 1, 450.78), (1, 2, 890.99);

select * from products; -- returns all the rows in table

select @@DBTS as 'After insert in products table';

The above image depicts the counter value before the start of the insert and after the insert operations, which incremented with the 3 inserts we made after creating the table. At the start, the counter value was 0x00000000000007D0 and after 3 insert operations, the counter current value is 0x00000000000007D3.

Now we will demonstrate update operations, in both cases wherein we change the column value of one row and not of the column in another row, to see if the rowversion value gets updated.

begin transaction;
select @@DBTS;
select * from products;

update products set price = 100 where id = 1; -- update which actually change the price from 109.23 to 100,
update products set price = 450.78 where id = 2; -- update which sets same price as before;

select * from products;
select @@DBTS;
commit transaction;

As you can see in the above image, we had a rowversion counter value of 0x00000000000007D3 before any update operation. We had two select statements in the transaction to show last_changed column values before and after update statements. Before the update operation, the last_changed value of the rows were 0x00000000000007D1 and 0x00000000000007D2. After the update operation last_changed value which is rowversion counter also updated to 0x00000000000007D4 and 0x00000000000007D5. The rowversion counter i.e. our last_changed column value updated even if there was no actual change in our price during the update.

Using ROWVERSION in SQL Server for Concurrency

In this part, we will see how to implement and use the rowversion of the table for concurrent requests. We will use the same table we created above. Now the scenario is that in the transaction - 1 we will be performing the update on our product table having the id value 1, but after simulating time delay depicting some other work. During that time transaction - 2 will also update the same product having id value 1 and commit the transaction. After transaction 2 commits, and after completing complex work in the transaction - 1, we will again try to update our product with the same id. Below is a demonstration of the above case.

-- transaction - 1

begin transaction;
declare @last_changed rowversion;

select @last_changed = last_changed 
from products
where id = 1; -- returning last_changed value in @last_changed variable.

waitfor delay '00:00:20'; -- simulate time delay to depeict work process.

update products
set price = 50
where id = 1 
and last_changed = @last_changed; -- update statement runnig after transation 2 updates the price to something else.

if @@ROWCOUNT == 0 
begin
    if not exists(select 1 from products where id = 1)
    begin
        print 'The data was deleted by other session. Try again with updated records.';
        rollback transaction;
    end
    else 
    begin
        print 'The data row was updated by some other sessions. Try again with updated records.'
        rollback transaction;
    end
end
else 
begin
    commit transaction;
end

In another tab or session of the management, studio try to run the below update query transaction

begin transaction

update products 
set price = 70 
where id = 1;

commit transaction

When we execute the above two scripts in different tabs or sessions of management studio, the transaction - 2 commits and closes and the transaction - 1 rollback's and we get the message which we directed to print. It shows that since the row is updated by another session, transaction - 1 fails to update the row as it was holding an older value of the rowversion in variable and when transaction - 2 updates the same row, the rowversion counter auto-incremented. Hence while updating the row in the transaction - 1 we don't find the row and it print's the instructed statement as output and rollbacks. We can leverage the rowversion counter as used in the above illustration to solve concurrent transaction problems.