How two phase locking prevents lost updates

Two phase locking is an old method of ensuring serializable transactions using locks. A common issue with non-serializable isolation in the fact of concurrent writes is the lost update problem.

Here’s an example of a lost update, lets assume:

• You have a record in a database with column x that has a value of 5
• There are two independent processes that will perform a read-modify-write on that record
• The two processes read the current value at the same time. Assuming readers don’t block readers, they’ll read the same value
• Process A will attempt to read the current value of 5 and increment it by 2
• Process B will attempt to read the current value of 5 and increment it by 5
• Process A writes first and the new value of x is 7
• Process B writes last and the new value of x is 10. The update from process A is considered lost / as if it never happened!

Some databases will detect a write conflict like this and raise an error and others may just proceed without a hiccup. In either case, there’s a potential conflict that may need to be resolved depending on the expectations of a client. If this is a bank account, a lost update is a huge problem. If this is a view counter, maybe it’s not as big of a problem.

(Conservative) Two phase locking

There’s two types of locks in two phase locking (2PL): shared locks and exclusive locks. Exclusive locks block both reads and writes. Only shared locks can be shared. In snapshot isolation levels, exclusive locks from writers only block other writers – readers can still read records that are exclusively locked. With serializable, writers can block readers.

The reason 2PL is able to guarantee serializability is by splitting it locking behavior into two phases – one before actual query execution and the other after. Prior to actually executing the query, the query planner figures out what locks it needs to obtain. If a transaction only does a read, only a shared lock is acquired for that record. If, however, a transaction does a read of a record followed by a write to the same record, the shared lock is “upgraded” to an exclusive lock.

Once all the locks are obtained (lock point), the locks are held until execution is complete. If you have two concurrent transactions, the first one with an exclusive lock to reach the lock point will block the other. This is what ensures serial execution.

The reason this is sometimes referred to as conservative 2PL is that it obtains all the locks upfront regardless of whether or not it needs to.