blocks|key|821364|text|Oracle在很久以前就有了一个优秀的多版本控制系统(至少从oracle+8.0开始)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|821365|以下几点应该会有帮助。|821366|用户A启动事务，并在时间上用某个值更新1000行T1|ordered-list-item|821367|用户B在时间上读取相同的1000行T2。|821368|用户A用值Y(原始值X)更新行543|821369|用户B到达第543行，并发现事务自时间T1开始运行。|821370|数据库从日志中返回未经修改的记录。返回的值是在时小于或等于T2时提交的值。|821371|如果无法从重做日志中撤回记录，则意味着数据库没有正确设置。需要为日志分配更多的空间。|821372|这样就实现了读取的一致性。对于事务的开始时间，返回的结果总是相同的。因此，在事务中，读取一致性得到了实现。|821373|我试图用最简单的术语来解释possible...there对于数据库中的多版本控制是非常重要的。|821374|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|10|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|11|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|12|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|13|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|14|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|15|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|V|$]]

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

Following should help.

<ol>
<li>User A starts a transaction and is updating 1000 rows with some value At Time T1</li>
<li>User B reads the same 1000 rows at time T2.</li>
<li>User A updates row 543 with value Y (original value X)</li>
<li>User B reaches row 543 and finds that a transaction is in operation since Time T1.</li>
<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>
<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>
<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>
</ol>

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

blocks|key|1108401|text|PostgreSQL的多版本并发控制|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1108402|以及这篇文章，它提供了MVCC在发出INSERT、UPDATE和DELETE语句时如何工作的图表。|1108403|entityMap|0|LINK|mutability|MUTABLE|url|http://www.onlamp.com/pub/a/onlamp/2001/05/25/postgresql_mvcc.html|1|https://vladmihalcea.com/how-does-mvcc-multi-version-concurrency-control-work/^0|0|I|0|0|2|4|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

<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>

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.

blocks|key|109127|text|以下是MVCC的实现：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|109128|Server+2005+(非默认值，SET+READ_COMMITTED_SNAPSHOT+ON)|offset|length|style|CODE|109129|http://msdn.microsoft.com/en-us/library/ms345124.aspx|unordered-list-item|109130|Oracle+(自第8版起)|109131|MySQL+5(只包含InnoDB表)|109132|PostgreSQL|109133|火鸟|109134|Informix|109135|我很确定Sybase和IBM+/LUW没有DB2的实现|109136|entityMap|0|LINK|mutability|MUTABLE|url^0|0|I|U|0|0|1H|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@$D|15|E|16|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|K|3|L|5|6|7|1B|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1C|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1E|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1F|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1G|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|I]]]]

The following have an implementation of MVCC:

SQL Server 2005 (Non-default, <code>SET READ_COMMITTED_SNAPSHOT ON</code>)

<ul>
<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>
</ul>

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

blocks|key|109077|text|Firebird这样做了，他们称它为MGA+(多代架构)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|109078|它们保持原始版本不变，并添加一个只有使用它的会话才能看到的新版本，当提交时，旧版本被禁用，更新版本为每个人启用(文件堆叠数据，需要定期清理)。|109079|Oracle重写数据本身，并为其他会话和回滚使用回滚段/撤消表空间。|109080|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|1108669|text|XtremeData+dbX支持MVCC。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1108670|此外，dbX还可以利用在FPGA硬件上实现的SQL原语。|1108671|entityMap|0|LINK|mutability|MUTABLE|url|http://www.xtremedata.com/^0|0|A|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

<a href="http://www.xtremedata.com/" rel="nofollow">XtremeData</a> dbX supports MVCC.

In addition, dbX can make use of SQL primitives implemented in FPGA hardware.

blocks|key|1108805|text|也使用MVCC。SAP是一个全内存计算系统，因此MVCC的选择成本非常低.:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1108806|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

SAP HANA also uses MVCC.
SAP HANA is a full In-Memory Computing System, so MVCC costs for select is very low... :)

blocks|key|817298|text|这是一个链接到MVCC上的PostgreSQL文档页。。选择引文(重点是我的)：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|817299|使用并发控制的MVCC模型而不是锁定的主要优点是，在为查询(读取)数据而获取的MVCC锁中，数据不与为写入数据而获得的锁冲突，因此读取从不阻止写入和写入从不阻止读取。|blockquote|style|BOLD|817300|这就是为什么杰夫被他的死锁弄得如此困惑的原因。读一读永远不应该导致它们。|817301|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/8.3/static/mvcc-intro.html^0|4|N|0|0|1T|H|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@$A|X|B|Y|G|H]]|9|@]|C|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

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):

<blockquote>
 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.
</blockquote>

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

blocks|key|108991|text|Server+2005和更高版本提供了MVCC选项；但是，它不是默认选项。如果内存正常，MS称之为快照隔离。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|108992|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|109035|text|还可以手动实现MVCC，方法是向表中添加版本号列，并始终执行插入而不是更新。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|109036|这样做的成本要大得多，而且选择速度较慢，因为每个数据库都需要一个子查询来查找最新的记录。|109037|对于需要对所有更改进行100%25审计的系统来说，这是一个很好的解决方案。|109038|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|1108493|text|如果使用MySQL表：http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html，则默认情况下也使用MVCC。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1108494|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html^0|B|1V|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

MySQL also uses MVCC by default if you use InnoDB tables:
<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>

blocks|key|109172|text|McObject在2009年11月宣布，它在其eXtremeDB嵌入式数据库中添加了一个可选的MVCC事务管理器：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|109173|http://www.mcobject.com/november9/2009|offset|length|109174|eXtremeDB最初是作为内存数据库系统(IMDS)开发的，现在有了混合存储(内存/磁盘存储)、高可用性、64位支持等版本。|109175|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|12|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

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

<a href="http://www.mcobject.com/november9/2009" rel="nofollow noreferrer">http://www.mcobject.com/november9/2009</a>

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.

blocks|key|109203|text|MVCC有一个很好的解释--用图表--以及McObject的联合创始人兼首席执行官在RTC杂志上写的eXtremeDB的一些性能数字：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|109204|http://www.rtcmagazine.com/articles/view/101612|offset|length|109205|显然，作为一个应用程序规模，MVCC越来越有益于包含在多个CPU核上执行的许多任务。|109206|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1B|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

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:

<a href="http://www.rtcmagazine.com/articles/view/101612" rel="nofollow noreferrer">http://www.rtcmagazine.com/articles/view/101612</a>

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

blocks|key|109158|text|DB2+Version9.7有一个经过许可的Post除法版本，并在其中加上。这意味着这个特性(在正确的模式下)支持这个特性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|109159|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1108730|text|伯克利数据库还支持MVCC。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1108731|当BDB存储引擎在MySQL中使用时，MySQL也支持MVCC。|1108732|Berkeley+DB是一个非常强大的、可定制的完全符合ACID的DBMS。它支持几种不同的索引方法，主从复制，可以使用它自己的动态API作为纯密钥值存储，或者根据需要使用SQL查询。值得一看。|1108733|另一个面向文档的DBMS包括MVCC将是CouchDB。这里的MVCC对于内置的对等复制也是一个很大的好处。|1108734|entityMap|0|LINK|mutability|MUTABLE|url|http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html|1|http://dev.mysql.com/doc/refman/4.1/en/bdb-storage-engine.html|2|http://guide.couchdb.org/index.html^0|0|6|0|0|1|7|1|0|0|K|7|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|J|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]|R|$5|M|N|O|C|$P|S]]|T|$5|M|N|O|C|$P|U]]]]

<a href="http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html" rel="nofollow">Berkeley DB</a> also supports MVCC. 

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.

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.

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.

blocks|key|1108749|text|来自http://vschart.com/list/multiversion-concurrency-control/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1108750|库奇基地，OrientDB，CouchDB，PostgreSQL，Voldemort项目，BigTable，Percona，HyperGraphDB，微雨，Cloudant，IBM+DB2，InterSystems+Caché，InterBase|1108751|entityMap|0|LINK|mutability|MUTABLE|url|http://vschart.com/list/multiversion-concurrency-control/^0|2|1L|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

From <a href="http://vschart.com/list/multiversion-concurrency-control/" rel="nofollow">http://vschart.com/list/multiversion-concurrency-control/</a>

Couchbase,
OrientDB,
CouchDB,
PostgreSQL,
Project Voldemort,
BigTable,
Percona Server,
HyperGraphDB,
Drizzle,
Cloudant,
IBM DB2,
InterSystems Caché,
InterBase

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?

updated: these support it (which others?)

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

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

最近，Jeff对与读取相关的数据库死锁的问题进行了处理。声称解决了这个问题。它是什么，什么数据库支持它？更新:这些支持它(哪些其他？)甲骨文postgresql

问什么是多版本并发控制(MVCC)，谁支持它？
EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问什么是多版本并发控制(MVCC)，谁支持它？EN

回答 15

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问什么是多版本并发控制(MVCC)，谁支持它？
EN