In this article we will touch base 3 ways of doing optimistic locking i.e. Using
ADO.NET dataset, SQL Server Timestamp check and old / new value check. So we
will start this article with a small introduction to concurrency, discuss the 5
concurrency problems and then get down to the actual meat to understand how to
implement optimistic locking using the 3 methodologies.
This is a small Ebook for all my .NET friends which covers topics like
WCF,WPF,WWF,Ajax,Core .NET,SQL etc you can download the same from
here or else you can catch me on
my daily free training @ from
here
In multi-user environment it's possible that multiple users can update the same
record at the same time causing confusion between users. This issue is termed as
concurrency.
Concurrency problems can be solved by implementing proper "Locking strategy".
Locks prevent action on a resource to be performed when some other resource is
already performing some action on it.
There are 4 kinds of major problems caused because of concurrency, below
table shows the details of the same.
| Problems | Short description | Explanation |
| Dirty reads | "Dirty Read" occurs when one transaction is reading a record, which is part of a half, finished work of other transaction. | - User A and user B are seeing value as "5".
- User B changes the value "5" to "2".
- User A is still seeing the value as "5"....Dirty read has happened.
|
| Unrepeatable read | In every data read if you get different values then it's an "Unrepeatable Read" problem. | - User A is seeing value as "5".
- User B changes the value"5" to "2".
- User A refreshes see values "5", he is surprised....unrepeatable read has happened.
|
| Phantom rows | If "UPDATE" and "DELETE" SQL statements does not affect the data then it can be "Phantom Rows" problem. | - User A updates all value "5' to "2".
- User B inserts a new record with value "2".
- User A selects all record with value "2' if all the values have changed, he is surprised to still find value "2" records.....Phantom rows have been inserted.
|
| Lost updates | "Lost Updates" are scenario where one updates which is successfully written to database is overwritten with other updates of other transaction. | - User A updates all value form "5" to "2".
- User B comes and updates all "2" values to "5".
- User A has lost all his updates.
|
By using optimistic or pessimistic locking, the further coming article
discusses the same.
![lock.JPG]()
As the name suggests "optimistic" it assumes that multiple transaction work
without affecting each other. In other words no locks are enforced while doing
optimistic locking. The transaction just verifies that no other transaction has
modified the data. In case of modification the transaction is rolled back.
You can implement optimistic locking by numerous ways but the fundamental to
implement optimistic locking remains same. It's a 5 step process as shown
below:-
- Record the current timestamp.
- Start changing the values.
- Before updating check whether anyone else has changed the values by checking
the old time stamp and new time stamp.
- If it's not equal rollback or else commit.
![1.JPG]()
There are 3 primary ways by which we can implement optimistic locking in
.NET:-
- Datasets: - Dataset by default implement optimistic locking. They do
a check of old values and new values before updating
- Timestamp Data type: - Create a timestamp data type in your table
and while updating check if old timestamp is equal to new timestamp.
- Check old and new value: - Fetch the values, do the changes and
while doing the final updates check if the old value and current values in
database are equal. If they are not equal then rollback or else commit the
values.
As said in the previous section dataset handles optimistic concurrency by
itself. Below is a simple snapshot where we held the debug point on Adapter's
update function and then changed the value from the SQL Server. When we ran the
"update" function by removing the break point it threw "Concurrency" exception
error as shown below.
![2.JPG]()
If you run the profiler at the back end you can see it fires the update
statement checking of the current values and the old values are same.
exec sp_executesql N'UPDATE [tbl_items] SET [AuthorName] = @p1 WHERE (([Id] = @p2) AND ((@p3 = 1 AND [ItemName] IS NULL) OR ([ItemName] = @p4)) AND ((@p5 = 1 AND [Type] IS NULL)
OR ([Type] = @p6)) AND ((@p7 = 1 AND [AuthorName] IS NULL) OR ([AuthorName] = @p8)) AND ((@p9 = 1 AND [Vendor] IS NULL) OR ([Vendor] = @p10)))',N'@p1 nvarchar(11),@p2 int,@p3
int,@p4 nvarchar(4),@p5 int,@p6 int,@p7 int,@p8 nvarchar(18),@p9 int,@p10 nvarchar(2)',@p1=N'this is new',@p2=2,@p3=0,@p4=N'1001',@p5=0,@p6=3,@p7=0,@p8=N'This is Old
Author',@p9=0,@p10=N'kk'
In this scenario we were trying to change the field value "AuthorName" to
"This is new" but while updating it makes a check with the old value "This is
old author". Below is the downsized code snippet of the above SQL which shows
the comparison with old value.
,@p8=N'This is Old Author'
The other way of doing optimistic locking is by using 'TimeStamp' data type
of SQL Server. Time stamp automatically generates a unique binary number every
time you update the SQL Server data. Time stamp data types are for versioning
your record updates.
![3.JPG]()
To implement optimistic locking we first fetch the old 'TimeStamp' value and
when we are trying to update we check if the old time stamp is equal to the
current time stamp as shown in the below code snippet.
update tbl_items set itemname=@itemname where CurrentTimestamp=@OldTimeStamp
We then check if any updates has happened, in case updates has not happened
then we raise a serious error '16' using SQL Server 'raiserror' as shown in the
below code snippet.
if(@@rowcount=0)
begin
raiserror('Hello some else changed the value',16,10)
end
If any concurrency violation takes place you should see the error propagated
when you call 'ExecuteNonQuery' to the client side as shown in the below figure.
![4.JPG]()
Many times we would like to check concurrency on only certain fields and omit
fields like identity etc. For those kind of scenarios we can check the old value
and the new value of the updated fields as shown in the below code snippet.
update tbl_items set itemname=@itemname where itemname=@OldItemNameValue
Below is the source code which demonstrates optimistic locking using dataset and
time stamp
Download the source code from top of this article.