Pessimistic vs Optimistic Concurrency Control

So today we are going to talk about database / storage layer strategies to deal with conflict

Pessimistic Concurrency Control

• This strategy assumes that conflicts between transactions are likely and prevents them by locking data early

• When a transactions reads/modifies data it acquires a lock on that resource

• Other transactions that need the same data must wait until the lock is released

• This is a good strategy for write heavy workloads

Optimistic Concurrency Control

• This strategy assumes that conflicts between transactions are rare

• Transactions don’t acquire locks on the objects

• Each transaction records a version / timestamp

• At commit time system validates whether any transaction has changed the data or not

• If no conflict commit is executed successfully

• If conflict is found then current transaction is rolled back and restarted

• This is a good strategy for read heavy workloads

Assume that 2 transactions which are updating the credits info of the user is 2708 start at the same time. Lets assume transaction 1 is able to commit the changes to the db a few seconds before transaction 2.

Now if transaction 2 during the time of commit goes and checks the record version it will mismatch, since it started the run with version 1 of the record but after transaction 1’s commit the version has updated.

Then the transaction 2 will roll back and start again with the new version of the record

and thats how data will be updated