Introduction

Locking protocols are used in database management systems as a means of concurrency control. When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.

Multiple transactions may request a lock on a data item simultaneously. Hence, we require a mechanism to manage the locking requests made by transactions and for our database to be consistent during such concurrent (requests) scenarios, we need to implement some kind of locking strategy on our databases. Locking approaches(strategy) can be broadly divided into:

Optimistic Locking

As the name suggests, the user is optimistic with his locking approach and assumes that no data inconsistency would take places and hence, users do not lock data when they read it. Now, when a user updates data, the system checks to see if another user changed the data after it was read. If another user updated the data, an error is raised and the transaction is roll backed and the DB transaction is started over.

So few important points to note in case of optimistic locking are as follows:

• Record is open for all user session/transaction(Row is not locked by user)
• The record changes are validated only when the record is committed.
• If one user successfully updates the record, the other users attempting to commit their concurrent updates are informed that a conflict exist
• Version Number & timestamp columns will be updated
• Preferred when read is high and write is less.
• Concurrent users can access without informing the users that others are also attempting to update the record, so performance is better.
• Preferred whenever there are few conflicts/updates, low contention for data, and where the cost of occasionally rolling back a transaction is lower than the cost of locking data when read.

Algorithm:

• Read a record and take a note of version number(date,timestamp).
• Check that version hasn't changed before we write back.
• When writing, we filter update based on version to make sure it is atomic.

In optimistic locking, when multiple users edit a record concurrently, after one user's changes are committed, the other users' changes are rejected and data conflicts must be saved and manually merged similar to how merge conflicts are resolved in GIT.

Pessimistic Locking

As the name suggests, the user is pessimistic with his locking approach and is always extra conscious before making any change, by preventing users from modifying data in a way that affects other users. After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it.

Few important points to note in case of pessimistic locking are as follows:

• Only that transaction can update the row and not anyone else(Row is locked)
• Used mostly in RDBMS
• Holds up resource, so that no other users(concurrent) gets access of the doc.
• Preferred when there are many updates/conflicts, high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.
• User needs to make sure deadlock is handled/ have lock timeout.
• Lock the record for exclusive use until we have finished with it.

In the diagram above, both Ram and Shyam will acquire a read (shared) lock on the account table row upon reading it.

Because both Ram and Shyam hold the read (shared) lock on the account record, neither of them can change it until one of them releases the read lock acquired.

A write operation requires a write (exclusive) lock acquisition, and read (shared) locks prevent write (exclusive) locks.

For this reason, Shyam's UPDATE blocks until Ram releases the exclusive lock he has acquired previously.

Comparison

Conclusion

Both pessimistic and optimistic locking are useful techniques. Pessimistic locking is suitable when the cost of retrying a transaction is very high or when contention is so large that many transactions would end up rolling back if optimistic locking were used.

On the other hand, optimistic locking works even across multiple database transactions since it doesn’t rely on locking physical records.

Thankyou for your time. Happy reading, and stay tuned for more such articles.