Object Locking in Relational Database Transactions – Part 2

Jun 16, 2021 by Robert Gravelle

Relational database systems (RDBMS) employ various locking strategies to enforce transaction ACID properties when modifying (e.g., UPDATING or DELETING) table records. On occasion, deadlock may occur when two concurrent transactions cannot make progress because each one is waiting for the other to release the lock. In Part 1 of this series, we we established what Object Locking is in Relational Databases, the different types of locks, and deadlocking. In today’s follow-up, we’ll be comparing the pros and cons of Pessimistic and Optimistic locking.

With Pessimistic Locking, a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time. In situations where most transactions simply read the resource and never update it, an exclusive lock may be overkill as it leads to more lock contention (deadlocks). Thinking back to the banking example from Part 1, the account would be locked as soon as it was accessed in a transaction. Any attempt to use the account in other transactions while it was locked would either result in the other process being delayed until the account lock was released, or that the process transaction would be cancelled and rolled back to the previous state.

Using optimistic locking, a resource is not actually locked when it is first is accessed by a transaction. Instead, the pristine state of the resource is persisted. Other transactions are still able to access to the resource, making the possibility of conflicting changes a known risk. At commit time, when the resource is about to be updated in persistent storage, the state of the resource is read from storage again and compared to the state that was saved when the resource was first accessed in the transaction. If the two states differ, that means that a conflicting update was made, so the transaction is rolled back. In our banking example, the amount of the account would be saved when it’s first accessed. If the transaction changed the account amount, the amount would be read from the store again just before the amount was about to be updated. If the amount had changed since the transaction began, the transaction would fail itself, otherwise the new amount would stand and be saved.

Now that we’ve covered what both types of locking are, the question becomes which to use. In most cases, Optimistic Locking is more efficient and offers higher performance. Meanwhile, Pessimistic Locking provides better integrity on the data, BUT, management of the lock is harder as there is a greater chance of encountering deadlocks. When choosing between pessimistic and optimistic locking, consider the following three guidelines:

• Pessimistic locking is useful if there are a lot of updates and relatively high chances of users trying to update data concurrently.
• Pessimistic locking is also more appropriate in applications that contain small tables that are frequently updated. In the case of these such “hotspots”, conflicts are so likely that optimistic locking wastes effort in rolling back conflicting transactions.
• Optimistic locking is useful when the possibility for conflicts is very low, i.e., there are many records but relatively few users, or very few updates and mostly read operations.

In this blog, we compared Pessimistic versus Optimistic locking. In the next installment, we’ll be exploring strategies for recovering from a deadlock situation.

Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.