<p>Recently Jeff has <a href="http://blog.codinghorror.com/deadlocked/" rel="noreferrer">posted</a> regarding his trouble with database deadlocks related to reading.  <a href="https://en.m.wikipedia.org/wiki/Multiversion_concurrency_control" rel="noreferrer">Multiversion Concurrency Control (MVCC)</a> claims to solve this problem.  What is it, and what databases support it?</p>

<p>updated: these support it (which others?)</p>

<ul>
<li>oracle</li>
<li>postgresql  </li>
</ul>

[{'answer_id': 27504, 'author': 'thelsdj', 'author_id': 163, 'author_profile': 'https://Stackoverflow.com/users/163', 'pm_score': 3, 'selected': False, 'text': '<p><a href="http://www.onlamp.com/pub/a/onlamp/2001/05/25/postgresql_mvcc.html" rel="nofollow noreferrer">PostgreSQL\'s Multi-Version Concurrency Control</a></p>\n\n<p>As well as <a href="https://vladmihalcea.com/how-does-mvcc-multi-version-concurrency-control-work/" rel="nofollow noreferrer">this article</a> which features diagrams of how MVCC works when issuing INSERT, UPDATE, and DELETE statements.</p>\n'}, {'answer_id': 27541, 'author': 'Krantz', 'author_id': 2528, 'author_profile': 'https://Stackoverflow.com/users/2528', 'pm_score': 6, 'selected': True, 'text': '<p>Oracle has had an excellent multi version control system in place since very long(at least since oracle 8.0)</p>\n\n<p>Following should help.</p>\n\n<ol>\n<li>User A starts a transaction and is updating 1000 rows with some value At Time T1</li>\n<li>User B reads the same 1000 rows at time T2.</li>\n<li>User A updates row 543 with value Y (original value X)</li>\n<li>User B reaches row 543 and finds that a transaction is in operation since Time T1.</li>\n<li>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.</li>\n<li>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.</li>\n<li>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.</li>\n</ol>\n\n<p>I have tried to explain in the simplest terms possible...there is a lot to multiversioning in databases.</p>\n'}, {'answer_id': 27783, 'author': 'Neall', 'author_id': 619, 'author_profile': 'https://Stackoverflow.com/users/619', 'pm_score': 1, 'selected': False, 'text': '<p>Here is a <a href="http://www.postgresql.org/docs/8.3/static/mvcc-intro.html" rel="nofollow noreferrer">link to the PostgreSQL doc page on MVCC</a>. The choice quote (emphasis mine):</p>\n\n<blockquote>\n  <p>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 <strong>reading never blocks writing and writing never blocks reading</strong>.</p>\n</blockquote>\n\n<p>This is why Jeff was so confounded by his deadlocks. A read should never be able to cause them.</p>\n'}, {'answer_id': 29041, 'author': 'DrPizza', 'author_id': 2131, 'author_profile': 'https://Stackoverflow.com/users/2131', 'pm_score': 1, 'selected': False, 'text': "<p>SQL Server 2005 and up offer MVCC as an option; it isn't the default, however. MS calls it snapshot isolation, if memory serves.</p>\n"}, {'answer_id': 29049, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 1, 'selected': False, 'text': "<p>MVCC can also be implemented manually, by adding a version number column to your tables, and always doing inserts instead of updates.</p>\n\n<p>The cost of this is a much larger database, and slower selects since each one needs a subquery to find the latest record.</p>\n\n<p>It's an excellent solution for systems that require 100% auditing for all changes.</p>\n"}, {'answer_id': 245734, 'author': 'Seun Osewa', 'author_id': 6475, 'author_profile': 'https://Stackoverflow.com/users/6475', 'pm_score': 1, 'selected': False, 'text': '<p>MySQL also uses MVCC by default if you use InnoDB tables:\n<a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html</a></p>\n'}, {'answer_id': 245941, 'author': 'Osama Al-Maadeed', 'author_id': 25544, 'author_profile': 'https://Stackoverflow.com/users/25544', 'pm_score': 2, 'selected': False, 'text': '<p>Firebird does it, they call it MGA (Multi Generational Architecture).</p>\n\n<p>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).</p>\n\n<p>Oracle overwrites the data itself, and uses a rollback segments/undo tablespaces for other sessions and to rollback.</p>\n'}, {'answer_id': 952122, 'author': 'John Greeley', 'author_id': 62209, 'author_profile': 'https://Stackoverflow.com/users/62209', 'pm_score': 3, 'selected': False, 'text': '<p>The following have an implementation of MVCC:</p>\n\n<p>SQL Server 2005 (Non-default, <code>SET READ_COMMITTED_SNAPSHOT ON</code>)</p>\n\n<ul>\n<li><a href="http://msdn.microsoft.com/en-us/library/ms345124.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms345124.aspx</a></li>\n</ul>\n\n<p>Oracle (since version 8)</p>\n\n<p>MySQL 5 (only with InnoDB tables)</p>\n\n<p>PostgreSQL</p>\n\n<p>Firebird</p>\n\n<p>Informix</p>\n\n<p>I\'m pretty sure Sybase and IBM DB2 Mainframe/LUW do not have an implementation of MVCC</p>\n'}, {'answer_id': 2873281, 'author': 'Ciaran De Buitlear', 'author_id': 346050, 'author_profile': 'https://Stackoverflow.com/users/346050', 'pm_score': 0, 'selected': False, 'text': '<p>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.  </p>\n'}, {'answer_id': 3376580, 'author': 'Ted Kenney', 'author_id': 407264, 'author_profile': 'https://Stackoverflow.com/users/407264', 'pm_score': 1, 'selected': False, 'text': '<p>McObject announced in 11/09 that it has added an optional MVCC transaction manager to its eXtremeDB embedded database:</p>\n\n<p><a href="http://www.mcobject.com/november9/2009" rel="nofollow noreferrer">http://www.mcobject.com/november9/2009</a></p>\n\n<p>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.</p>\n'}, {'answer_id': 3376593, 'author': 'Ted Kenney', 'author_id': 407264, 'author_profile': 'https://Stackoverflow.com/users/407264', 'pm_score': 1, 'selected': False, 'text': '<p>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:</p>\n\n<p><a href="http://www.rtcmagazine.com/articles/view/101612" rel="nofollow noreferrer">http://www.rtcmagazine.com/articles/view/101612</a></p>\n\n<p>Clearly MVCC is increasingly beneficial as an application scales to include many tasks executing on multiple CPU cores. </p>\n'}, {'answer_id': 4187297, 'author': 'lit', 'author_id': 447901, 'author_profile': 'https://Stackoverflow.com/users/447901', 'pm_score': 2, 'selected': False, 'text': '<p><a href="http://www.xtremedata.com/" rel="nofollow"><strong>XtremeData</strong></a> dbX supports MVCC.</p>\n\n<p>In addition, dbX can make use of SQL primitives implemented in FPGA hardware.</p>\n'}, {'answer_id': 5778359, 'author': 'chris polzer', 'author_id': 508709, 'author_profile': 'https://Stackoverflow.com/users/508709', 'pm_score': 0, 'selected': False, 'text': '<p><a href="http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html" rel="nofollow">Berkeley DB</a> also supports MVCC. </p>\n\n<p>And when <a href="http://dev.mysql.com/doc/refman/4.1/en/bdb-storage-engine.html" rel="nofollow">BDB storage engine</a> is used in MySQL, MySQL also supports MVCC.</p>\n\n<p>Berkeley DB is a very powerful, customizable fully ACID conform DBMS. It supports several different methods for indexing, master-slave replication, can be used as a pure key value store with it\'s own dynamic API or queried with SQL if wanted. Worth taking a look at.</p>\n\n<p>Another document oriented DBMS embracing MVCC would be <a href="http://guide.couchdb.org/index.html" rel="nofollow">CouchDB</a>. MVCC here also is a big plus for the built in peer-to-peer replication.</p>\n'}, {'answer_id': 9421258, 'author': 'Sean Timm', 'author_id': 321466, 'author_profile': 'https://Stackoverflow.com/users/321466', 'pm_score': 0, 'selected': False, 'text': '<p>From <a href="http://vschart.com/list/multiversion-concurrency-control/" rel="nofollow">http://vschart.com/list/multiversion-concurrency-control/</a></p>\n\n<p>Couchbase,\nOrientDB,\nCouchDB,\nPostgreSQL,\nProject Voldemort,\nBigTable,\nPercona Server,\nHyperGraphDB,\nDrizzle,\nCloudant,\nIBM DB2,\nInterSystems Caché,\nInterBase</p>\n'}, {'answer_id': 12383861, 'author': 'OlivierH', 'author_id': 1665088, 'author_profile': 'https://Stackoverflow.com/users/1665088', 'pm_score': 2, 'selected': False, 'text': '<p>SAP HANA also uses MVCC.\nSAP HANA is a full In-Memory Computing System, so MVCC costs for select is very low... :)</p>\n'}]
['https://Stackoverflow.com/questions/27499', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/116/']