Why does MVCC require locking for DML statements

Question

In PostgreSQL, the MVCC concurrency control mechanism says that:

MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading

So, even for READ_COMMITTED, an UPDATE statement will lock the currently affected rows so no other transaction can modify them, until the current transaction commits or rolls back.

If a concurrent transaction issues an UPDATE on the locked rows, the second transaction will block until the first one releases it's locks.

1. Is this behavior trying to prevent the write-write conflicts?

2. Lost updates can still happen in READ_COMMITTED, as after the first transaction commits, the second one will overwrite the row (even if the database has changed between the UPDATE query start and the query end). So if lost updates are still possible, why does the second transaction have to wait? Couldn't the row-level snapshots be used to store the uncommitted transaction changes to avoid transactions having to wait for write-locks to be released?

Answer 1

The answer to the first question is Yes. No DBMS can support dirty writes; if two transactions T1 and T2 are concurrently executing and T2 overwrites an update from T1, then the system cannot handle the case where T1 subsequently issues a ROLLBACK since T2's update has already occurred.

To avoid dirty writes, the original definition for snapshot isolation was "first committer wins" - that is, conflicting writes would be allowed to happen, but only the first transaction to issue a COMMIT would be able to - all other conflicting transactions would have to ROLLBACK. But this programming model is somewhat problematic, if not wasteful, since a transaction might update a significant proportion of the database only to be refused the ability to COMMIT at the end. So, instead of "first committer wins" most DBMS systems that support MVCC implement "first updater wins" using fairly traditional two-phase locking.