Database: What is Multiversion Concurrency Control (MVCC) and who supports it?

Question

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

updated: these support it (which others?)

• oracle
• postgresql

Answer 1

PostgreSQL's Multi-Version Concurrency Control

Correct link to the Coding Horror article

Answer 2

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 3

Here is a link to the PostgreSQL doc page on MVCC. The choice quote (emphasis mine):

The main advantage to 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.

This is why Jeff was so confounded by his deadlocks. A read should never be able to cause them.

Answer 4

SQL Server 2005 and up offer MVCC as an option; it isn't the default, however. MS calls it snapshot isolation, if memory serves.

Answer 5

MVCC can also be implemented manually, by adding a version number column to your tables, and always doing inserts instead of updates.

The cost of this is a much larger database, and slower selects since each one needs a subquery to find the latest record.

It's an excellent solution for systems that require 100% auditing for all changes.

Answer 6

MySQL also uses MVCC by default if you use InnoDB tables: http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html

Answer 7

Firebird does it, they call it MGA (Multi Generational Architecture).

They keep the original version intact, and add a new version that only the session using it can see, when committed the older version is disabled, and the newer version is enabled for everybody(the file piles-up with data and needs regular cleanup).

Oracle overwrites the data itself, and uses a rollback segments/undo tablespaces for other sessions and to rollback.

Answer 8

The following have an implementation of MVCC:

SQL Server 2005 (Non-default, SET READ_COMMITTED_SNAPSHOT ON)

• http://msdn.microsoft.com/en-us/library/ms345124.aspx

Oracle (since version 8)

MySQL 5 (only with InnoDB tables)

PostgreSQL

Firebird

Informix

I'm pretty sure Sybase and IBM DB2 Mainframe/LUW do not have an implementation of MVCC

Answer 9

DB2 version 9.7 has a licensed version of postgress plus in it. This means that this feature (in the right mode) supports this feature.

Answer 10

McObject announced in 11/09 that it has added an optional MVCC transaction manager to its eXtremeDB embedded database:

http://www.mcobject.com/november9/2009

eXtremeDB, originally developed as an in-memory database system (IMDS), is now available in editions with hybrid (in-memory/on-disk) storage, High Availability, 64-bit support and more.

Answer 11

There's a good explanation of MVCC -- with diagrams -- and some performance numbers for eXtremeDB in this article, written by McObject's co-founder and CEO, in RTC Magazine:

http://www.rtcmagazine.com/articles/view/101612

Clearly MVCC is increasingly beneficial as an application scales to include many tasks executing on multiple CPU cores.