What is Multiversion Concurrency Control (MVCC) and who supports it? [closed]

Question

Recently Jeff has posted regarding his trouble with database deadlocks related to reading. Multiversion Concurrency Control (MVCC) claims to solve this problem. What is it, and what databases support it?

updated: these support it (which others?)

• oracle
• postgresql

Answer 1

Oracle has had an excellent multi version control system in place since very long(at least since oracle 8.0)

Following should help.

1. User A starts a transaction and is updating 1000 rows with some value At Time T1
2. User B reads the same 1000 rows at time T2.
3. User A updates row 543 with value Y (original value X)
4. User B reaches row 543 and finds that a transaction is in operation since Time T1.
5. The database returns the unmodified record from the Logs. The returned value is the value that was committed at the time less than or equal to T2.
6. If the record could not be retreived from the redo logs it means the database is not setup appropriately. There needs to be more space allocated to the logs.
7. This way the read consitency is achieved. The returned results are always the same with respect to the start time of transaction. So within a transaction the read consistency is achieved.

I have tried to explain in the simplest terms possible...there is a lot to multiversioning in databases.

Answer 2

PostgreSQL's Multi-Version Concurrency Control

As well as this article which features diagrams of how MVCC works when issuing INSERT, UPDATE, and DELETE statements.