PostgreSQL并发控制（MVCC, 事务，事务隔离级别）

2019独角兽企业重金招聘Python工程师标准>>>

基于PostgreSQL9.4

9.3中文文档：http://58.58.27.50:8079/doc/html/9.3.1_zh/mvcc.html

9.4中文文档：http://www.postgresql.org/docs/9.4/static/mvcc.html

  本文描述PostgreSQL数据库系统在多个会话试图同时访问同一数据时的表现。并发控制的目标是为所有会话提供高效的访问，同时还要维护严格的数据完整性。每个数据库应用开发人员都应该熟悉本话题。

PostgreSQL的MVCC与锁

   PostgreSQL为开发者提供了丰富的对数据并发访问进行管理的工具。在内部，PostgreSQL利用多版本并发控制(MVCC)来维护数据的一致性。这就意味着当检索数据时，每个事务看到的都只是一小段时间之前的数据快照(一个数据库版本)，而不是数据的当前状态。这样，如果对每个数据库会话进行事务隔离，就可以避免一个事务看到其它并发事务的更新而导致不一致的数据。MVCC通过避开传统数据库系统锁定的方法，最大限度地减少锁竞争以允许合理的多用户环境中的性能。

   使用MVCC与使用锁定模式相比较的优缺点：

   在MVCC里，对检索(读)数据的锁请求与写数据的锁请求不冲突，所以读不会阻塞写，而写也从不阻塞读。甚至当通过创新的序列化快照隔离(SSI)级别提供事务隔离的严格等级时，PostgreSQL维持这样的保证。

  在PostgreSQL里也有表和行级别的锁定机制，用于给那些无法轻松接受MVCC行为的应用。 不过，恰当地使用MVCC总会提供比锁更好的性能。另外，由应用定义的咨询锁提供了一个获得不依赖于单独事务的锁的机制。

   MVCC的实现方法有两种：

   1.写新数据时，把旧数据移到一个单独的地方，如回滚段中，其他人读数据时，从回滚段中把旧的数据读出来；

   2.写数据时，旧数据不删除，而是把新数据插入。

   PostgreSQL数据库使用第二种方法，而Oracle数据库和MySQL中的innodb引擎使用的是第一种方法。

   与racle数据库和MySQL中的innodb引擎相比较，PostgreSQL的MVCC实现方式的优缺点如下。

   优点：

   1.事务回滚可以立即完成，无论事务进行了多少操作；

   2.数据可以进行很多更新，不必像Oracle和MySQL的Innodb引擎那样需要经常保证回滚段不会被用完，也不会像oracle数据库那样经常遇到“ORA-1555”错误的困扰；

   缺点：

   1.旧版本数据需要清理。PostgreSQL清理旧版本的命令成为Vacuum；

   2.旧版本的数据会导致查询更慢一些，因为旧版本的数据存在于数据文件中，查询时需要扫描更多的数据块。   

   (本段转自《PostgreSQL修炼之道》)

一、事务与事务id

  事务是单个逻辑工作单元执行的一系列操作。必须具有：原子性、一致性、隔离性和持久性（ACID）。

   在Postgres中，每一个事务都会得到一个被称作为 XID 的事务ID。这里说的事务不仅仅是被BEGIN - COMMIT包裹的一组语句，还包括单条的insert、update或者delete语句。当一个事务开始时，PostgreSQL递增XID，然后把它赋给这个事务。PostgreSQL还在系统里的每一行记录上都存储了事务相关的信息，这被用来判断某一行记录对于当前事务是否可见。

       举个例子，当你插入一行记录时，PostgreSQL会把当前事务的XID存储在这一行中并称之为 xmin 。只有那些‘已提交的而且 xmin’比当前事务的XID小的记录对当前事务才是可见的。这意味着，你可以开始一个新事务然后插入一行记录，直到你提交（ COMMIT ）之前，你插入的这行记录对其他事务永远都是不可见的。等到提交以后，其他后创建的新事务就可以看到这行新记录了，因为他们满足了 xmin < XID 条件，而且创建哪一行记录的事务也已经完成。

     对于 DELETE 和 UPDATE 来说，机制也是类似的，但不同的是对于它们PostgreSQL使用叫做 xmax 的值来判断数据的可见性。这幅图展示了在两个并发的插入/读取数据的事务中，MVCC在事务隔离方面是怎么起作用的。

    在下面的图中，假设我们先执行了这个建表语句：

CREATE TABLE numbers (value int);

虽然 xmin 和 xmax 的值在日常使用中都是被隐藏的，但是你可以直接请求他们，Postgres会高兴的把值给你：

SELECT *, xmin, xmax FROM numbers;

获取当前事务的XID也很简单：

SELECT txid_current();

二、事务的操作命令

  http://58.58.27.50:8079/doc/html/9.3.1_zh/sql-set-transaction.html

BEGIN;  --开启事务

--事务隔离级别，定义多个事务时间的隔离级别
BEGIN TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE];  --一次启动事务并指定事务隔离级别
BEGIN;
TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE];  --先启动事务，再设置事务隔离级别


--预备事务，使得事务分阶段可以提交
PREPARE TRANSACTION 'foobar';
......
COMMIT PREPARE TRANSACTION 'foobar';
ROLLBACK PREPARE TRANSACTION 'foobar';


--保存点savepoint,可以支持事务的部分回滚
insert into lyy values(1,'nn');
savepoint svp1;
insert into lyy values(2,'ff');
rollback to savepoint svp1;
--此时提交的话，第二个insert未被插入，但是第一个插入成功。


END; 或者 COMMIT;  --结束并提交事务
或者 ROLLBACK;  --结束并回滚事务

三、事务隔离级别

  SQL标准定义了四个级别的事务隔离。最严格的是串行化，它是通过标准来定义的，也就是说， 保证一组可序列化事务的并发执行以产生同样顺序依次运行它们的同一效果。 其他三个层次是通过现象术语被定义，导致并发事务之间的相互作用，这不应该发生在每个级别中。

  各个级别不希望发生的现象是：

  脏读：一个事务读取了另一个并行的未提交事务写入的数据。(包括insert，update，delete)

  不可重复读：一个事务A重新读取前面读取过的数据，发现该数据已经被另一个已提交事务B修改（这个事务B的提交是在事务A第一次读之后发生的）。(同一行，update)

  幻读：一个事务重新执行一个查询，返回一套符合查询条件的行，发现这些行因为其它最近提交的事务而发生了改变。(结果集，insert、delete)

  这四种隔离级别和对应的行为在表Table1里描述。

  Table1 标准SQL事务隔离级别

   隔离级别      脏读     不可重复读   幻读

         读未提交      可能       可能      可能

   读已提交      不可能       可能      可能

         可重复读      不可能      不可能    可能

         可串行化      不可能      不可能    不可能

  在PostgreSQL里，你可以请求四种可能的事务隔离级别中的任意一种。但是在内部， 实际上只有三种独立的隔离级别，分别对应读已提交，可重复读和可串行化。如果你选择了读未提交的级别， 实际上你用的是读已提交，在重复读的PostgreSQL执行时，幻读是不可能的， 所以实际的隔离级别可能比你选择的更严格。这是 SQL 标准允许的：四种隔离级别只定义了哪种现像不能发生， 但是没有定义那种现像一定发生。PostgreSQL只提供三种隔离级别的原因是：这是把标准的隔离级别与多版本并发控制架构映射相关的唯一合理方法。可用的隔离级别的行为在下面小节里描述。

   要设置一个事务的隔离级别，使用SET TRANSACTION命令。

   特别注意: 一些PostgreSQL数据类型和函数关于事务行为有特定的规则。 尤其是，序列变化（因此列数通过serial声明）对于所有其他的事务是立即可见的， 如果事务改变终止，则不进行回退。参见Section 9.16和Section 8.1.4。

事务隔离级别详解

读已提交隔离级别( BEGIN TRANSACTION ISOLATION LEVEl READ COMMITTED; )

   是PostgreSQL里的缺省隔离级别。当一个事务运行在这个隔离级别时: SELECT查询(没有FOR UPDATE/SHARE子句)只能看到查询开始之前已提交的数据而无法看到未提交的数据或者在查询执行期间其它事务已提交的数据 (仅读当时数据库快照)。不过，SELECT看得见其自身所在事务中前面更新执行结果，即使它们尚未提交。（注意：在同一个事务里两个相邻的SELECT命令可能看到不同的快照，因为其它事务会在第一个SELECT执行期间提交。）

     分析：要是同时有两个事务修改同一行数据会怎么样？这就是事务隔离级别（transaction isolation levels）登场的时候了。Postgres支持两个基本的模型来让你控制应该怎么处理这样的情况。默认情况下使用读已提交（READ COMMITTED），等待初始的事务完成后再读取行记录然后执行语句。如果在等待的过程中记录被修改了，它就从头再来一遍。举一个例子，当你执行一条带有WHERE子句的UPDATE时，WHERE子句会在最初的事务被提交后返回命中的记录结果，如果这时WHERE子句的条件仍然能得到满足的话，UPDATE才会被执行。在下面这个例子中，两个事务同时修改同一行记录，最初的UPDATE 语句导致第二个事务的WHERE不会返回任何记录，因此第二个事务根本没有修改到任何记录：

可重复读隔离级别(BEGIN TRANSACTION ISOLATION LEVEl REPEATABLE READ; )

  这个级别和读已提交级别是不一样的。重复读事务中的查询看到的只是事务开始时的快照， 而不是该事务内部当前查询开始时的快照，这样，同一个事务内部后面的SELECT命令总是看到同样的数据等，它们没有看到通过 自身事务开始之后提及的其他事务做出的改变。

  使用这个级别的应用必须准备好重试事务，因为串行化失败。

可串行化隔离级别：(BEGIN TRANSACTION ISOLATION LEVEl SERIALIZABLE; )

  可串行化级别提供最严格的事务隔离。这个级别为所有已提交事务模拟串行的事务执行， 就好像事务将被一个接着一个那样串行(而不是并行)的执行。不过，正如可重复读隔离级别一样， 使用这个级别的应用必须准备在串行化失败的时候重新启动事务。 事实上，该隔离级别和可重复读希望的完全一样，它只是监视这些条件，以所有事务的可能的序列不一致的（一次一个）的方式执行并行的可序列化事务执行的行为。 这种监测不引入任何阻止可重复读出现的行为，但有一些开销的监测，检测条件这可能会导致序列化异常 将触发序列化失败。

    分析：如果你需要更好的“两个事务同时修改同一行数据”控制这种行为，你可以把事务隔离级别设置为 可串行化（SERIALIZABLE） 。在这个策略下，上面的场景会直接失败，因为它遵循这样的规则：“如果我正在修改的行被其他事务修改过的话，就不再尝试”，同时 Postgres会返回这样的错误信息：由于并发修改导致无法进行串行访问 。捕获这个错误然后重试就是你的应用需要去做的事情了，或者不重试直接放弃也行，如果那样合理的话。

   特别注意：序列化事务隔离级别尚未被添加到热备复制目标中 （正如在Section 25.5中描述的）。 严格的隔离级别目前热备方式上支持可重复读。 当在主库上执行所有永久数据库写入可序列化事务中将确保所有的措施将最终达成一致， 运行在备库上的可重复读事务会看到一个过渡状态，与主库上的任何串行执行的可序列化事务不一致。

四、显式锁定

       转至另一篇博客：http://my.oschina.net/liuyuanyuangogo/blog/499460#OSC_h2_2

参考文章：

MVCC参考：

中文：http://www.zlovezl.cn/articles/postgresql-concurrency-with-mvcc/

英文：https://devcenter.heroku.com/articles/postgresql-concurrency

PostgreSQL事务处理机制：

http://blog.chinaunix.net/uid-20726500-id-4040024.html

转载于:https://my.oschina.net/liuyuanyuangogo/blog/497929