What
DBMS maintains multiple physical versions of a single logical object in the database.
How
Each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.
Why
This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation (I in ACID) for each database session.
The main advantage of using the MVCC model of concurrency control rather than locking is that in 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.
Writers may still block other writers if they are writing the same object, since there is still a lock on the versions related to the database object.
Snapshot Isolation
SI is susceptible to the Write Skew Anomaly
Approach
- Timestamp Ordering Concurrency Control
- Assign txns timestamps that determine serial order.
- Optimistic Concurrency Control
- Three phase protocol
- Private workspace for new versions
- Two Phase Lock
- Txns acquire appropriate lock on physical version before they can read/write a logical tuple