Hi,
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.
How can we solve concurrency problems?
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.
What is Optimistic locking?
As the name suggests “optimistic” it assumes that multiple transaction will 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.
How does optimistic lock work?
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 rollbacks or else commit.
What are the different solutions by which we can implement optimistic locking?
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 commits the values.
Solution number 1: - Datasets
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 .
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'
Solution number 2:- Use timestamp data type
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.
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.
Solution number 3: - Check old values and new values
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
But it looks like by using optimistic locking concurrency problems are not really solved?
Yes, you said right. By using optimistic locking you only detect the concurrency problem. To solve concurrency issues from the roots
itself we need to use pessimistic locking. Optimistic is like prevention while pessimistic locking is actually the cure.
What is pessimistic locking?
Pessimistic locking assumes that concurrency / collision issues will happen so a lock is placed on the records and then data is updated.
How can we do pessimistic locking?
We can do pessimistic locking by specifying “IsolationLevel” in SQL Server stored procedures, ADO.NET level or by using transaction scope object.
What kind of locks can be acquired by using pessimistic locking?
There are 4 kinds of locks you can acquire Shared, Exclusive, Update and intent. The first two are actual locks while the other two
are hybrid locks and marker.
1. Shared Lock
2. Update Lock
3. Internet Lock(Demand Locks)
4. Schema Lock
5. Bulk Update Lock
ADO.NET
You can also specify transaction isolation level using “SqlTransaction” object in ADO.NET.
SqlTransaction objtransaction = objConnection.BeginTransaction(System.Data.IsolationLevel.Serializable);
SQL Server
You can also specify isolation level in TSQL using ‘SET TRANSACATION ISOLATION LEVEL’ as shown in the below code snippet.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Which transaction isolation level solves which problems from Concurrency?
Below is a chart which shows which transaction isolation level solves which problems of concurrency.
Read committed(S) Repeatable read(I) Serializable Read Uncommitted
Dirty reads Solves Solves Solves X
Lost updates X Solves Solves X
Non repeatable reads X Solves Solves X
Phantom rows X X Solves
Kavya Shree Mandapalli
Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator