What is ADO.Net Locking

ADO.Net Locking

How to Concurrency Handling in ado.net ?

In a multiuser environment when multiple users attempt to access and modify data from database at the same time, we should implement some controls to prevent one user's modifications from adversely affecting modifications from simultaneous users. This system of controling is known as Concurrency Handling or Locking. This is a technique that allows you to detect and resolve conflicts that arise out of two concurrent requests to the same data source.

We can handling concurrency in two ways: optimistic concurrency and pessimistic concurrency.

What is Pessimistic locking ?

Pessimistic locking means that when a user opens a record to update it, a lock is granted. That means a user can't open a record for editing if another user has already opened it. He can only view the data until the other user has finished the update and released the lock. You can perform this locking when the chance of a conflict between users are very high, but it may involve users have to wait for a long time to access a particular record in some situations.

What is Optimistic locking ?

Optimistic locking means that a record is opened for updating by multiple users. Record gets locked only while updating the record to Database. When you save the record to the database a check is made to see whether any other user has updated it in the meantime and if any chenges happend the whole update is rolled back and an error is thrown. You can perform this locking when the chance of a conflict between users is very low. This type of locking is the most preferred way of locking for the web application.