What’s difference between “Optimistic” and “Pessimistic” locking?
In optimistic locking there is no locking actually. It only checks if the old values are changed, in case they are changed it means somebody has changed the data, so it raises exception.
In pessimistic locking you actually lock the record, depending on the type of lock no other process can make modifications to the record.
How many ways are there to implement optimistic locking in ADO.NET?
Following are the ways to implement optimistic locking using ADO.NET:-
- When we call “Update” method of Data Adapter it handles locking internally. If the Dataset values are not matching with current data in Database, it raises concurrency exception error. We can easily trap this error using Try. Catch block and raise appropriate error message to the user.
- Define a Date time stamp field in the table. When actually you are firing the UPDATE SQL statements, compare the current timestamp with one existing in the database. Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This I the best practice used by industries for locking.
- Check for original values stored in SQL SERVER and actual changed values. In stored procedure check before updating that the old data is same as the current Example in the below shown SQL before updating field1 we check that is the old field1 value same. If not then some one else has updated and necessary action has to be taken.
Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures. For more details of how to implementing locking in SQL SERVER read “What are different locks in SQL SERVER?” in SQL SERVER chapter.
How can do pessimistic locking?
Pessimistic locking is done by using transaction isolation levels like read committed, read uncommitted, repeatable read and serializable.
See following video on Dataset is a disconnect while datareader is connected: -
Click and see here for more training on ADO.NET.