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

2 minute read


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








在pg_dump中也有用到,看pg_dump 的说明:


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

           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事务生效

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。

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.  

Flag Counter

digoal’s 大量PostgreSQL文章入口