What's the difference between optimistic and pessimistic locking?

Locking mechanisms in DBMSDatabase Management System are essential for managing concurrent access to shared resources in multi-user environments. Optimistic locking allows multiple transactions to proceed simultaneously, checking for conflicts only during the update phase. If conflicts arise, the affected transaction is rolled back and can be retried. In contrast, pessimistic locking aims to prevent conflicts by acquiring locks before performing operations, allowing only one transaction to access and modify a resource. The choice between optimistic and pessimistic locking depends on the frequency of conflicts and the desired level of concurrency in the application.

Let's delve into the details of optimistic and pessimistic locking to gain a better understanding of their mechanisms and implications.

Optimistic locking

It is a method of locking transactions that allows all users to enter transactions simultaneously. However, in a conflict where multiple users commit transactions at the same time, if one user successfully commits a transaction, it will reject all other conflicting requests. Users whose transactions are denied are notified about the conflict, prompting them to reevaluate their data and retry their transactions accordingly.

Now let's review the illustration below for a better understanding.

Explanation

In the given example, we observe that both User 1 and User 2 send transaction requests for the same record simultaneously. However, while User 1's transaction is successfully updated on the other side, User 2's committed transaction is rejected. Consequently, in order to retry the transaction, User 2 must first read the current data and then create a new transaction based on that information.

Pessimistic locking 

It is a locking approach in which the transaction is controlled when a conflict occurs between two or more users while simultaneously updating records in the database. In that case, just one person can update the data, while for all other users, the database can be accessed but cannot be modified.

Let's explore an example for better understanding.

Explanation

In the example above, we have two users, User 1 and User 2. User 1 initiates a transaction request, which is promptly updated successfully. Meanwhile, User 2 is restricted to accessing the database to read the record. After User 1's transaction is completed, User 2 gains permission to proceed with their own transaction.