This article talks about 6 ways of doing locking in .NET. It starts with concurrency problems and then discusses about 3 ways of doing optimistic locking. As optimistic locking does not solve the concurrency issues from roots, it introduces pessimistic locking. It then moves ahead to explain how isolation levels can help us implement pessimistic locking. Each isolation level is explained with sample demonstration to make concepts clearer.

This is a small Ebook for all my .NET friends which covers topics like WCF,WPF,WWF,Ajax,Core .NET,SQL, Entity framework, Design patterns , Agile etc you can download the same from hereor else you can catch me on my daily free training @ from here

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.

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.

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.

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.

If you run the profiler at the back end you can see it fires the updatestatement checking of the current values and the old values are same.

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.

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 andwhen 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.

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.

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.

The other locks are pretty straight forward; the update lock is confusing because of its hybrid nature. Many times before we update we read the record. So during read the lock is shared and while actually updating we would like to have an exclusive lock. Update locks are more of transient locks.

Isolation levels are features of RDBMS software, in other words they fundamental really belong to SQL Server and not to Ado.NET, EF or LINQ. Said and done that you can always set the transaction isolation level from any of these components.

Some important Key points about read committed:-
• It’s the default transaction isolation level for SQL Server.
• Its reads only committed data. In other words any uncommitted data is not read and blocked until the commit happens. Below figure explains the same in more detail. You can see the update

If you want the see above things practically do the following:-
• Open 2 Query windows fire an update transaction but do not commit.

• In the second window try firing select query it will show a blocked query asshown in the below figure.

Yes, read uncommitted is opposite to read committed. When you set thetransaction isolation level to read uncommitted, uncommitted data is also read.Some important key points for read committed:-• Uncommitted is see so dirty read possible.• No locks held.• Useful when locking is not important and more important is concurrency andthroughput.If you want to test the same, fire the below SQL statement which is doing anupdate and roll back. The roll back happens after 20 seconds halt. Within thattime if you fire a select query you will get the uncommitted data and after 20seconds you will see the old data this committed data is rolled back.

By setting isolation level to repeatable read no one can read and update the data. Some key points about repeatable read isolation level are as follows:-

• Only committed data is read when repeatable transaction isolation level isset for select queries.• When you select a record using repeatable read no one other transaction canupdate the record. , but selects are possible.• If repeatable transaction is set in update query, until the transactionfinishes no one can read or update the same.• When select and update query is set to repeatable read other transaction caninsert new records. In other words phantom rows are possible.

If you want to test this isolation level, fire the below syntax and try firingselect and update queries they will be blocked and after 50 seconds you shouldsee the data.

This is the highest level of isolation level; in this other transactions cannot update, select and insert records.

Some key points for serializable transaction are:-• No other transaction can insert, update, delete or select when isolation levelis serializable.• Lot of blockings but all concurrency issues are solved.

Subscription Package for fundamentals and Interview preparation

which consist of .NET, C#, ASP.NET, SQL Server, WCF, Design Patterns, SilverLight, LINQ, SharePoint, Azure videos read details of subscription Also want to inquire more then call us on +91-22-66752917/mail us on questpond@questpond.com. Simply click to .