PostgreSQL serializable read only deferrable事务的用法背景

背景

在开始讲serializable read only deferrable用法前，需要先了解一下serializable隔离级别。

https://wiki.postgresql.org/wiki/Serializable

http://www.postgresql.org/docs/9.5/static/transaction-iso.html#XACT-SERIALIZABLE

《PostgreSQL SERIALIZABLE ISOLATION LEVEL introduce》

serializable是最高的隔离级别，模拟并行事务的串行处理，当不能实现串行一致时，后提交的会话需要回滚，因此serializable事务相互之间是有干扰的。

PostgreSQL在三个地方介绍到了serializable的deferrable用法。

有些时候，用户可能会需要串行一致的视角执行SQL来统计一些数据的报告，这些SQL可能需要运行非常长的时间，但是只需要只读操作。对于运行非常长时间的串行事务，即使是只读的，也可能和之前已经开启并正在执行的其他串行事务产生干扰，从而导致其他串行事务需要回滚。

因此对于这类长时间运行的只读串行事务，我们可以使用一个deferrable模式，这个模式是等待其他的串行事务结束或确保其他正在执行的串行事务不会与当前的串行事务冲突，然后才开始执行只读SQL。

这个时间是不可控的。

但是好处是，当你运行非常长的串行只读事务中的SQL时，不会再感染其他人执行的串行事务了，你爱跑多长时间都行。

在pg_dump中也有用到，看pg_dump 的说明：

(因为pg_dump通常需要长时间运行，使用–serializable-deferrable是指从串行视角来备份数据库，从而得到串行一致的备份数据)

pg_dump默认是使用repeatable read隔离级别来备份的。

       --serializable-deferrable  
           Use a serializable transaction for the dump, to ensure that the snapshot used is consistent with later database states;   
	   but do this by waiting for a point in the transaction stream at which no anomalies can be present,  
           so that there isn't a risk of the dump failing or causing other transactions to roll back with a serialization_failure.   
	   See Chapter 13, Concurrency Control, in the documentation for more information about transaction  
           isolation and concurrency control.  
  
           This option is not beneficial for a dump which is intended only for disaster recovery.   
	   It could be useful for a dump used to load a copy of the database for reporting or other read-only load sharing while the original  
           database continues to be updated. Without it the dump may reflect a state which is not consistent with any serial execution of the transactions eventually committed.   
	   For example, if batch processing techniques are used,  
           a batch may show as closed in the dump without all of the items which are in the batch appearing.  
  
           This option will make no difference if there are no read-write transactions active when pg_dump is started.   
	   If read-write transactions are active, the start of the dump may be delayed for an indeterminate length of time.  
           Once running, performance with or without the switch is the same.  

set transaction语法也支持设置deferrable属性

deferrable值对read only serializable事务生效

http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY.   
When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot,   
after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure.   
This mode is well suited for long-running reports or backups.  

还有一个参数控制默认的serializable read only事务是否为deferrable。

http://www.postgresql.org/docs/9.5/static/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-DEFERRABLE

default_transaction_deferrable (boolean)  
When running at the serializable isolation level, a deferrable read-only SQL transaction may be delayed before it is allowed to proceed.   
However, once it begins executing it does not incur any of the overhead required to ensure serializability;   
so serialization code will have no reason to force it to abort because of concurrent updates, making this option suitable for long-running read-only transactions.  
  
This parameter controls the default deferrable status of each new transaction.   
It currently has no effect on read-write transactions or those operating at isolation levels lower than serializable. The default is off.  

digoal’s 大量PostgreSQL文章入口