Why MVCC requires locking for DML statements

Question

In PostgreSQL, the MVCC concurrency control mechanism says that:

MVCC locks received for requests (reads) do not conflict with locks obtained for writing data, and therefore reading never blocks writing and writing never blocks reading

So, even for READ_COMMITTED , the UPDATE statement will lock the current affected lines so that no other transaction can modify them, before the current transaction commits or rolls back.

If a parallel transaction issues UPDATE in locked rows, the second transaction will be blocked until the first of them blocks it.

• Is this behavior trying to prevent write and write conflicts ?

• Lost updates can still occur in READ_COMMITTED, because after the first transaction the second one will overwrite the row (even if the database has changed between the start of the UPDATE query and the end of the query). Therefore, if lost updates are still possible, why should the second transaction wait? Could you not use row-level snapshots to store changes to pending transactions to avoid transactions that are waiting for release of write locks?

Answer 1

The answer to the first question is yes. No DBMS can support dirty recording; if two transactions T1 and T2 are simultaneously running, and T2 overwrites the update from T1, then the system cannot handle the case when T1 subsequently issues ROLLBACK, since the update of T2 has already occurred.

To avoid dirty recording, the initial definition of snapshot isolation was the “first gain from the commit” —that is, conflicting records could be resolved, but only the first transaction to issue COMMIT could — all other conflicting transactions would have to ROLLBACK. But this programming model is somewhat problematic, if not wasteful, because a transaction can update a significant part of the database just to give up COMMIT at the end. Thus, instead of the “first committer winning”, most DBMS systems that support MVCC implement the “first update winner” using a fairly traditional two-phase lock.