optimistic and pessimistic locking

[ramareddy_dotne]

what is the difference between optimistic locking and pessimistic locking in ado.net

[npasma]

Optimistic vs. Pessimistic Locking

OK Eddy, here goes:

Locking is a method used to protect records that will be accessed by multiple users so that concurrency errors do not occur (when multiple users change records near simultaneously resulting in inconsistencies).
"Locking" refers to preventing access in some way to the file in order to protect it while a user is making an update.

Optimistic locking is better to use when the likelihood of a update conflict is low. This is usually the case when the normal action is adding a record, like in an order entry system. Pessimistic locking is used when the likelihood of such a conflict is high.

Pessimistic locking anticipates contention for the same record, preventing users from selecting a record for editing when another user has already done so. This is often done by relying on the database itself. Most relational databases use this method, only each may use a different standard for the "granularity" considered when making a lock. As an example, SQL Server 2000 locks single rows, while others may lock the entire page or table containing the record to be changed. A drawback is that this type of locking requires that you remain connected to the database the whole time, which can be a bit much to ask. Also, this type of locking can back up on users waiting to access a given record.

Optimistic locking allows multiple users to access the same record for edits, counting on minimal conflicts over data. The "locking" happens after the user tries to save changes on top of someone else's changes. The program logic checks to see if the record has been changed since you opened it. If it has, an error is thrown and the update is rolled back. If no changes are detected, the record is saved as planned.

As you can tell, there are advantages and disadvantages to both. :-|
So the fun part is deciding which approach best suits your needs. :)

Hope this helps! Good luck!

[npasma]

P.S.- If I just gave you an answer to a homework problem, you owe me $20. :evil: :p :cheesy: