What is Lock?
Lock is a technique that assures data integrity and atomicity when multiple processes/applications access database concurrently. There are different levels of locking, but from this post, we will focus on transactional locking, especially Optimistic Lock and Pessimistic Lock for concurrency control.
Optimistic Lock
Optimistic Lock is a strategy to manage your data using a special value. The value can be a version number, timestamp, checksums, etc.
When a user tries to write a data, it checks if the special value hasn’t been changed after it was read. If the value has been updated by other user, the transaction rolls back and start over. Optimistic Lock does not lock the transaction, so it is recommended to be used in environments where the cost of rolling back the transaction is lower than the cost of locking data.
Pessimistic Lock
Pessimistic Lock uses an exclusive lock which makes only one user/session to be able to use data at the same time.
The user who first accessed in a transaction will lock the record until the transaction is finished. The other users will be on hold, and when the first user releases the lock, the subsequent user gets its chance to access.
Pessimistic Lock can provide a better integrity than Optimistic Lock, but it is harder to manage the lock. In worst scenarios, if it fails to manage the lock, application can encounter issues like Deadlocks. Therefore, it is recommended to use pessimistic lock in environments where it has high contention for data and rolling back transactions is expensive.
Reference
- https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking
- https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15
- https://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/TransactionJTA_Overview-Pessimistic_and_optimistic_locking.html
- https://medium.com/@recepinancc/til-9-optimistic-vs-pessimistic-locking-79a349b76dc8
Will the Guru 
Leave a comment