Sql Interview Question #6: What are pessimistic and optimistic locks?


Sql Interview Question

What are pessimistic and optimistic locks?

Solution

Optimistic locking is a strategy where you read a record, take note of a version number and check that the version hasn’t changed before you write the record back. If the record is changed (i.e. a different version to yours), then you abort the transaction and the user can re-start the transaction with the new data and update appropriately.

Pessimistic locking is when you lock the record for your exclusive use until you have finished with it. There are 4 levels of locking in the pessimistic isolation levels from lowest to highest. They are: read uncommitted, read committed, repeatable read, and serializable. At the serializable level (the highest locking and isolation level) it has much better integrity than optimistic locking but requires you to be careful with your application design to avoid deadlocks.

 

Thanks for reading this article,

Next steps :

  1. Share this with your colleagues because Sharing is Learning
  2. Comment below if you need any assistance