Tuesday, November 4, 2003 05:33 PM
Let's start with pessimistic locking - the simpler of the 2.
Pessimistic locking is an easy way to ensure that nobody else touches your row while you are accessing it. Quite simply, other people will block until you are finished. The problem with this type of locking is that it is very restrictive because it blocks all others trying to access a locked row. Plus, locks are expensive. Overall, this reduces the performance of an application.
Optimistic locking is based on a premise that most of the time, nobody else needs to access your row so why use expensive database locks it in the first place? Optimistic locking never locks any rows but instead is able to detect instances where 2 people modified the same row at the same time (by use of time stamps on the data). In rare cases where this happens, optimistic locking algorithms can choose to abort the transaction and start all over. Since this happens rarely, there is an overall gain in performance.