database 数据库：什么是多版本并发控制 (MVCC)，谁支持它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database: What is Multiversion Concurrency Control (MVCC) and who supports it?
提问by Mark Harrison
Recently Jeff has postedregarding 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?
最近，Jeff发布了关于他在与读取相关的数据库死锁方面遇到的麻烦。 多版本并发控制 (MVCC)声称可以解决这个问题。它是什么，哪些数据库支持它？
updated: these support it (which others?)
更新：这些支持它（还有哪些？）
- oracle
- postgresql
- 甲骨文
- PostgreSQL
采纳答案by Krantz
Oracle has had an excellent multi version control system in place since very long(at least since oracle 8.0)
很久以来（至少从 oracle 8.0 开始），Oracle 就拥有一个出色的多版本控制系统
Following should help.
以下应该有所帮助。
- User A starts a transaction and is updating 1000 rows with some value At Time T1
- User B reads the same 1000 rows at time T2.
- User A updates row 543 with value Y (original value X)
- User B reaches row 543 and finds that a transaction is in operation since Time T1.
- 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.
- 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.
- 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.
- 用户 A 启动一个事务并在 T1 时间用某个值更新 1000 行
- 用户 B 在时间 T2 读取相同的 1000 行。
- 用户 A 用值 Y（原始值 X）更新第 543 行
- 用户 B 到达第 543 行并发现事务自时间 T1 起正在运行。
- 数据库从日志中返回未修改的记录。返回值是在小于或等于 T2 的时间提交的值。
- 如果无法从重做日志中检索记录，则意味着数据库设置不正确。需要为日志分配更多空间。
- 这样就实现了读取一致性。返回的结果对于事务的开始时间总是相同的。因此，在事务中实现了读取一致性。
I have tried to explain in the simplest terms possible...there is a lot to multiversioning in databases.
我试图用最简单的术语来解释……数据库中的多版本处理有很多。
回答by thelsdj
PostgreSQL's Multi-Version Concurrency Control
As well as this articlewhich features diagrams of how MVCC works when issuing INSERT, UPDATE, and DELETE statements.
以及这篇文章，其中展示了在发出 INSERT、UPDATE 和 DELETE 语句时 MVCC 如何工作的图表。
回答by John Greeley
The following have an implementation of MVCC:
以下是MVCC的实现：
SQL Server 2005 (Non-default, SET READ_COMMITTED_SNAPSHOT ON)
SQL Server 2005（非默认，SET READ_COMMITTED_SNAPSHOT ON）
Oracle (since version 8)
Oracle（从版本 8 开始）
MySQL 5 (only with InnoDB tables)
MySQL 5（仅适用于 InnoDB 表）
PostgreSQL
PostgreSQL
Firebird
火鸟
Informix
Informix
I'm pretty sure Sybase and IBM DB2 Mainframe/LUW do not have an implementation of MVCC
我很确定 Sybase 和 IBM DB2 Mainframe/LUW 没有 MVCC 的实现
回答by lit
XtremeDatadbX supports MVCC.
XtremeDatadbX 支持 MVCC。
In addition, dbX can make use of SQL primitives implemented in FPGA hardware.
此外，dbX 可以利用在 FPGA 硬件中实现的 SQL 原语。
回答by Osama Al-Maadeed
Firebird does it, they call it MGA (Multi Generational Architecture).
Firebird 做到了，他们称之为 MGA（多代架构）。
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.
Oracle 覆盖数据本身，并为其他会话使用回滚段/撤消表空间并回滚。
回答by OlivierH
SAP HANA also uses MVCC. SAP HANA is a full In-Memory Computing System, so MVCC costs for select is very low... :)
SAP HANA 也使用 MVCC。SAP HANA 是一个完整的内存计算系统，因此选择的 MVCC 成本非常低...... :)
回答by Ted Kenney
McObject announced in 11/09 that it has added an optional MVCC transaction manager to its eXtremeDB embedded database:
McObject 在 11/09 宣布它已经在其 eXtremeDB 嵌入式数据库中添加了一个可选的 MVCC 事务管理器：
http://www.mcobject.com/november9/2009
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.
eXtremeDB 最初是作为内存数据库系统 (IMDS) 开发的，现在提供混合（内存/磁盘）存储、高可用性、64 位支持等版本。
回答by Ted Kenney
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:
在这篇由 McObject 的联合创始人兼 CEO 在 RTC 杂志上撰写的文章中，对 MVCC 有一个很好的解释——带有图表——以及 eXtremeDB 的一些性能数据：
http://www.rtcmagazine.com/articles/view/101612
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.
显然，随着应用程序扩展到包括在多个 CPU 内核上执行的许多任务，MVCC 越来越有益。
回答by Neall
Here is a link to the PostgreSQL doc page on MVCC. The choice quote (emphasis mine):
这是MVCC 上 PostgreSQL 文档页面的链接。选择报价（强调我的）：
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.
使用并发控制而不是锁定的 MVCC 模型的主要优点是，在 MVCC 中，为查询（读取）数据而获取的锁与为写入数据而获取的锁不冲突，因此读取从不阻塞写入，写入从不阻塞读取。
This is why Jeff was so confounded by his deadlocks. A read should never be able to cause them.
这就是杰夫对他的僵局如此困惑的原因。阅读应该永远不会导致它们。
回答by DrPizza
SQL Server 2005 and up offer MVCC as an option; it isn't the default, however. MS calls it snapshot isolation, if memory serves.
SQL Server 2005 及更高版本提供 MVCC 作为选项；然而，它不是默认值。如果有内存的话，MS 称之为快照隔离。
