Pessimistic Locking vs Optimistic Locking

locking one of the most important mechanism to keep data sane and not get corrupted. (make it “Isolation” complaint, “I” from ACID)

Pessimistic Locking

means take the lock on the data being read, and then execute a transaction. 2 types -

shared lock ( SELECT ….. FROM TABLE FOR SHARE ) - allow read on contented rows by multiple transactions

exclusive lock ( SELECT ….. FROM TABLE FOR UPDATE ) - lock the row from being read and modified

This is implement by database. This is usually used when rows are highly contented and inventory is fixed.

Optimistic Locking

means execute the transaction and then before commiting check for any issues and then commit.

this is implemented by application layer, usually by using adding an extra column version which each transaction updates as soon as any change is made to the row which so that it signifies other transactions that this row has already been modified (via changed version number) so rollback the transaction.

Claude conversation - https://claude.ai/share/97104cb0-2498-4927-b884-5cdf3384f8e4