PostgreSQLSQL高级技巧事务

pg的事务快照功能在9.2版本开始支持，允许事务共享它当时的snapshot给其他的事务使用。
SET TRANSACTION SNAPSHOT命令允许新的事务使用与一个现有事务相同的快照运行。已经存 在的事务必须已经把它的快照用pg_export_snapshot函数导出。该函数会返回一个快照标识符，SET TRANSACTION SNAPSHOT需要被给定一个快照标识符来指定要导入的快照。
需要注意的是：只有事务是SERIALIZABLE以及 repeatable read时，DEFERRABLE 事务属性才会有效。

例子：
建表：

bill@bill=>create table test (id int);  
CREATE TABLE
bill@bill=>insert into test values (1),(2); 
INSERT 0 2

–例1:事务隔离级别是repeatable read的。
session1:

bill@bill=>begin TRANSACTION ISOLATION LEVEL repeatable read;  
BEGIN
bill@bill=>SELECT pg_export_snapshot();
 pg_export_snapshot  
---------------------
 00000004-00000EAF-1
(1 row)

bill@bill=>insert into test values (3);
INSERT 0 1
bill@bill=>SELECT pg_export_snapshot(); 
 pg_export_snapshot  
---------------------
 00000004-00000EAF-2
(1 row)

bill@bill=>select * from txid_current();
 txid_current 
--------------
      8885549
(1 row)

bill@bill=>select * from txid_current_snapshot();  
 txid_current_snapshot 
-----------------------
 8885549:8885549:
(1 row)

session2(插入一条新数据并提交)：

bill@bill=>insert into test values (4); 
INSERT 0 1

session3(能查看到会话2插入的数据):

bill@bill=>select * from test;
 id 
----
  1
  2
  4
(3 rows)

session4 (导入s1的第一个snapshot, 因此看不到s2提交的数据) :

bill@bill=>begin TRANSACTION ISOLATION LEVEL repeatable read; 
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EAF-1';
SET
bill@bill=>select * from test;  
 id 
----
  1
  2
(2 rows)
bill@bill=>select * from txid_current(); 
 txid_current 
--------------
      8885552
(1 row)


bill@bill=>select * from txid_current_snapshot();
 txid_current_snapshot 
-----------------------
 8885549:8885549:
(1 row)

session5 (导入s1的第二个snapshot, 因此看不到s2提交的数据, 同时验证了看不到s1修改过的数据):

bill@bill=>begin TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EAF-2';
SET
bill@bill=>select * from test; 
 id 
----
  1
  2
(2 rows)

bill@bill=>select * from txid_current();  
 txid_current 
--------------
      8885553
(1 row)

bill@bill=>select * from txid_current_snapshot(); 
 txid_current_snapshot 
-----------------------
 8885549:8885549:
(1 row)

session1(提交)：

bill@bill=> commit;  
COMMIT  

session4 (s1提交后, 这个snapshot还存在, 只要还有导入了这个snapshot的事务存在着) :

bill@bill=>select * from test; 
 id 
----
  1
  2
(2 rows)

session5 (s1提交后, 这个snapshot还存在, 只要还有导入了这个snapshot的事务存在着) :

bill@bill=>select * from test; 
 id 
----
  1
  2
(2 rows)

–例2:
export的事务是read committed的, 与repeatable read不同的是, 这里在事务中多次执行pg_export_snapshot()将能明显的体现不同, repeatable read和serializable是看不到事务中其他事务提交的数据的, read committed则可以看到, 多次调用pg_export_snapshot()只需要在read committed 场景使用。

清理之前的数据：

bill@bill=>truncate test;
TRUNCATE TABLE
bill@bill=>vacuum ANALYZE test ;
VACUUM
bill@bill=>insert into test values (1),(2);  
INSERT 0 2

session1(注意是read commited级别):

bill@bill=>begin TRANSACTION ISOLATION LEVEL read committed; 
BEGIN
bill@bill=>SELECT pg_export_snapshot();  
 pg_export_snapshot  
---------------------
 00000004-00000EB8-1
(1 row)

bill@bill=>insert into test values (3);
INSERT 0 1
bill@bill=>SELECT pg_export_snapshot();
 pg_export_snapshot  
---------------------
 00000004-00000EB8-2
(1 row)

bill@bill=>select * from test;  
 id 
----
  1
  2
  3
(3 rows)

session2:

bill@bill=>select * from test ;
 id 
----
  1
  2
(2 rows)

session1:

bill@bill=>select * from test;  
 id 
----
  1
  2
  3
  4
(4 rows)

bill@bill=>SELECT pg_export_snapshot();  
 pg_export_snapshot  
---------------------
 00000004-00000EB8-3
(1 row)

session3:

bill@bill=>begin TRANSACTION ISOLATION LEVEL repeatable read; 
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EB8-1';
SET
bill@bill=>select * from test;  
 id 
----
  1
  2
(2 rows)

session4:

bill@bill=>begin TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EB8-2';
SET
bill@bill=>select * from test;  
 id 
----
  1
  2
(2 rows)

session5:

bill@bill=>begin TRANSACTION ISOLATION LEVEL repeatable read; 
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EB8-3';
SET
bill@bill=>SELECT * FROM TEST;  
 id 
----
  1
  2
  4
(3 rows)

session2:

bill@bill=>insert into test values (5); 
INSERT 0 1

session1:

bill@bill=>select * from test;  
 id 
----
  1
  2
  3
  4
  5
(5 rows)

会话3,4,5看到的结果与前面看到的结果一致 :
session3:

bill@bill=>begin TRANSACTION ISOLATION LEVEL repeatable read; 
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EB8-1';
SET
bill@bill=>select * from test;  
 id 
----
  1
  2
(2 rows)

session4:

bill@bill=>begin TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EB8-2';
SET
bill@bill=>select * from test;  
 id 
----
  1
  2
(2 rows)

session5:

bill@bill=>begin TRANSACTION ISOLATION LEVEL repeatable read; 
BEGIN
bill@bill=>SET TRANSACTION SNAPSHOT '00000004-00000EB8-3';
SET
bill@bill=>SELECT * FROM TEST;  
 id 
----
  1
  2
  4
(3 rows)

总结：
import事务快照时，其实只是把执行export事务的当时的txid_current_snapshot传递过来. 不会传递事务的隔离属性如(read committed或repeatable read或serializable)。
因此, 我们从例2看到共享事务snapshot的事务之间，除了存在自己修改的数据的差异之外，对于执行export的事务如果是read committed的，它看到的数据和执行import的事务看到的数据也是存在差异的。