Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 16 - Difference between optimistic and pessimistic concurrency control

Suggested Videos
Part 13 - Compiled queries in Linq to SQL
Part 14 - How to directly execute sql queries using Linq to SQL
Part 15 - Identity Cache in Linq to SQL



In this video we will discuss 
1. Why is concurrency control required
2. Difference between optimistic and pessimistic concurrency 



Why is concurrency control required
Concurrency control is required to prevent two users from trying to update the same data at the same time. It can also prevent one user from seeing out-of-date data while another user is updating the same data.

Let us understand what can happen if there is no concurrency control in place with an example. 

John and Mary has a joint account. At the moment the balance in the account is $1000. John and Mary visits different branches. John wants to deposit $500 and Mary wants to withdraw $500.

The following are the transactions at the bank branches without concurrency control
Difference between optimistic and pessimistic concurrency

At the end of both the transaction the account balance must be $1000 (1000 - 500 + 500), but the balance now is $1500 which is incorrect. This happened because 2 users updated the balance at the same time and since there is no concurrency control in place, the second update has overwritten the changes made by the first update. This is a concurrency related problem and is called as Lost updates problem. There are several other concurrency related problems which we will discuss in a later video session.

With the same example, let us now understand what can happen if there is some sort of concurrency control in place.

The following are the transactions at the bank branches with concurrency control in place
why is concurrency control needed

In this example, the account is locked while John is processing his transaction. The lock is released only after John's transaction is finished. After the lock has been released, Mary can proceed with her transaction. Since we have a concurrency control in place, we prevented 2 users from updating the balance at the same time which also prevented lost updates. So, the balance is updated correctly as expected.

There are 2 different concurrency control mechanisms
1. Pessimistic concurrency control 
2. Optimistic concurrency control

What is the difference between optimistic and pessimistic concurrency control
Pessimistic concurrency involves locking rows to prevent other users from modifying the same data at the same time. Until the lock is released by the lock owner, no other users will be able to access that data. Pessimistic locking can very easily lead to performance bottle necks in an application.

Optimistic concurrency does not involve locking rows when reading. Instead, this model checks if two users tried to update the same record at the same time. If that happens one user's changes are committed and the other user's changes are discarded and an exception will be thrown to notify the user.

We will discuss how Linq to SQL implements optimistic concurrency with an example in our next video.

LINQ to SQL Tutorial

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.