Multi-Version Concurrency Control (MVCC)

Multi-Version Concurrency Control (MVCC) is a sophisticated and widely used method for implementing optimistic concurrency control. It is the default concurrency method in many modern databases like PostgreSQL, Oracle, and InnoDB (the default MySQL storage engine). The core idea of MVCC is to avoid the traditional locking of data items when a read operation is performed. Instead of overwriting old data with new data, MVCC maintains multiple 'versions' of each data item in the database. Each version is typically marked with the timestamp or transaction ID of the transaction that created it. When a transaction starts, it is given a snapshot of the database at that point in time. When this transaction reads a data item, the DBMS provides it with the most recent version of that item that existed when the transaction began. This means that a reading transaction will never be blocked by a writing transaction, and it will always see a consistent view of the database. This is often summarized as 'readers don't block writers, and writers don't block readers'. When a transaction wants to write (update or delete) a data item, it creates a new version of that item, again marked with its transaction ID. A conflict only occurs if two transactions try to write to the same data item concurrently. MVCC provides excellent read performance and high concurrency, making it very effective for read-heavy workloads.