子事务及其性能
PostgreSQL中的子事务及其性能
最近，在排查PostgreSQL性能问题的时候，我遇到了两次子事务的问题。因此我觉得这是一个不错的话题，另起一篇文章。
什么是子事务
每个人都知道事务。在PostgreSQL中，它是在自动提交的模式下运行的，你必须使用BEGIN或start transaction显式地启动一个涵盖多个语句的事务，然后用END或COMMIT关闭它。如果使用ROLLBACK中止事务（或在没有提交的情况下结束数据库会话），事务内完成的所有工作都会被撤消。
现在，子事务允许回滚事务中完成的部分工作。使用标准SQL语句在事务内部启动子事务：
SAVEPOINT name;"name"是子事务的标识符（没有单引号！）。你不能在SQL中提交子事务(它会自动与包含它的事务一起提交)，但是可以用如下语句回滚：
ROLLBACK TO SAVEPOINT name;子事务用法
子事务在较长的事务中很有用。在PostgreSQL中，事务中的任何错误都会中断事务
test=> BEGIN;
BEGIN
test=*> SELECT 'Some work is done';
?column?
-------------------
Some work is done
(1 row)
test=*> SELECT 12 / (factorial(0) - 1);
ERROR: division by zero
test=!> SELECT 'try to do more work';
ERROR: current transaction is aborted, commands ignored until end of transaction block
test=!> COMMIT;
ROLLBACK对于执行大量工作的事务，这是非常恼人的，因为这意味着到目前为止完成的所有工作都丢失了。子事务可以帮助您从这种情况中脱身
test=> BEGIN;
BEGIN
test=*> SELECT 'Some work is done';
?column?
-------------------
Some work is done
(1 row)
test=*> SAVEPOINT a;
SAVEPOINT
test=*> SELECT 12 / (factorial(0) - 1);
ERROR: division by zero
test=!> ROLLBACK TO SAVEPOINT a;
ROLLBACK
test=*> SELECT 'try to do more work';
?column?
---------------------
try to do more work
(1 row)
test=*> COMMIT;
COMMIT请注意，ROLLBACK TO SAVEPOINT在回滚旧事务时将启动另一个子事务，称为a。
PL/pgSQL中的子事务
即使从未使用过SAVEPOINT语句，也可能已经遇到过子事务。换成用 PL/pgSQL 编写，上一节的代码如下所示：
BEGIN
PERFORM 'Some work is done';
BEGIN -- a block inside a block
PERFORM 12 / (factorial(0) - 1);
EXCEPTION
WHEN division_by_zero THEN
NULL; -- ignore the error
END;
PERFORM 'try to do more work';
END;每当你进入一个带有EXCEPTION子句的块时，一个新的子事务就会启动。子事务在离开代码块时提交，在进入异常处理程序时回滚。
与其他库的兼容性
许多其他数据库以不同的方式处理事务内部的错误。它们不中止整个事务，而是只回滚导致错误的语句，使事务本身仍处于活跃状态。
当从这样的数据库迁移或移植到PostgreSQL时，你可能会试图将每个语句包装在子事务中，以模拟上述行为。
PostgreSQL JDBC驱动程序有一个连接参数autosave，你可以设置为always，这样会在每个语句之前设置savepoint，在失败的时候可以回滚。
如下所示，这种诱人的技术会导致严重的性能问题。
性能测试样例
为了演示过度使用子事务的问题，我创建了一个表
CREATE UNLOGGED TABLE contend (
id integer PRIMARY KEY,
val integer NOT NULL
)
WITH (fillfactor='50');
INSERT INTO contend (id, val)
SELECT i, 0
FROM generate_series(1, 10000) AS i;
VACUUM (ANALYZE) contend;表很小，并且是UNLOGGED，同时有个较小的fillfactor，以尽可能地减少IO，这样，我便可以更好地观察子事务的影响。
我准备使用pgbench，PostgreSQL自带的基准测试工具，运行下面自定义SQL脚本
BEGIN;
PREPARE sel(integer) AS
SELECT count(*)
FROM contend
WHERE id BETWEEN $1 AND $1 + 100;
PREPARE upd(integer) AS
UPDATE contend SET val = val + 1
WHERE id IN ($1, $1 + 10, $1 + 20, $1 + 30);
SAVEPOINT a;
\set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);
SAVEPOINT a;
\set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);
...
SAVEPOINT a;
\set rnd random(1,990)
EXECUTE sel(10 * :rnd + :client_id + 1);
EXECUTE upd(10 * :rnd + :client_id);
DEALLOCATE ALL;
COMMIT;第一项测试设置60个保存点，第二项测试设置90个保存点。同时使用预备语句来最小化查询解析的开销。
pgbench会将:client id替换为数据库会话中唯一的数字。因此，只要不超过10个客户端，每个客户端的update不会与其他客户端冲突，但他们会查询对方的元组。
运行测试
因为我的机器有8个核，所以我会使用6个并发，并运行10分钟。假如要perf top能看到有意义的信息，需要安装PostgreSQL debugging symbols。在生产系统上特别建议这样做。
Test 1 (60 subtransactions)
pgbench -f subtrans.sql -n -c 6 -T 600
transaction type: subtrans.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 600 s
number of transactions actually processed: 100434
latency average = 35.846 ms
tps = 167.382164 (including connections establishing)
tps = 167.383187 (excluding connections establishing)这是测试运行时，perf top --no-children --call-graph=fp --dsos=/usr/pgsql-12/bin/postgres看到的结果
+ 1.86% [.] tbm_iterate
+ 1.77% [.] hash_search_with_hash_value
1.75% [.] AllocSetAlloc
+ 1.36% [.] pg_qsort
+ 1.12% [.] base_yyparse
+ 1.10% [.] TransactionIdIsCurrentTransactionId
+ 0.96% [.] heap_hot_search_buffer
+ 0.96% [.] LWLockAttemptLock
+ 0.85% [.] HeapTupleSatisfiesVisibility
+ 0.82% [.] heap_page_prune
+ 0.81% [.] ExecInterpExpr
+ 0.80% [.] SearchCatCache1
+ 0.79% [.] BitmapHeapNext
+ 0.64% [.] LWLockRelease
+ 0.62% [.] MemoryContextAllocZeroAligned
+ 0.55% [.] _bt_checkkeys
0.54% [.] hash_any
+ 0.52% [.] _bt_compare
0.51% [.] ExecScanTest 2 (90 subtransactions)
pgbench -f subtrans.sql -n -c 6 -T 600
transaction type: subtrans.sql
scaling factor: 1
query mode: simple
number of clients: 6
number of threads: 1
duration: 600 s
number of transactions actually processed: 41400
latency average = 86.965 ms
tps = 68.993634 (including connections establishing)
tps = 68.993993 (excluding connections establishing)这是perf top --no-children --call-graph=fp --dsos=/usr/pgsql-12/bin/postgres看到的
+ 10.59% [.] LWLockAttemptLock
+ 7.12% [.] LWLockRelease
+ 2.70% [.] LWLockAcquire
+ 2.40% [.] SimpleLruReadPage_ReadOnly
+ 1.30% [.] TransactionIdIsCurrentTransactionId
+ 1.26% [.] tbm_iterate
+ 1.22% [.] hash_search_with_hash_value
+ 1.08% [.] AllocSetAlloc
+ 0.77% [.] heap_hot_search_buffer
+ 0.72% [.] pg_qsort
+ 0.72% [.] base_yyparse
+ 0.66% [.] SubTransGetParent
+ 0.62% [.] HeapTupleSatisfiesVisibility
+ 0.54% [.] ExecInterpExpr
+ 0.51% [.] SearchCatCache1即使我们考虑到测试2中的事务更长，与测试1相比，这仍然有60%的性能下降。
子事务的实现
要理解发生了什么，我们必须理解事务和子事务是如何实现的。
每当事务或子事务修改数据时，都会为其分配事务ID。PostgreSQL在提交日志中跟踪这些事务id，这些事务ID被持久化到数据目录的pg_xact子目录中。
但是，事务和子事务之间有一些区别：
1.每个子事务都有一个包含事务或子事务(父事务)。2.提交子事务不需要WAL刷新。3.每个数据库会话只能有一个事务，但可以有多个子事务
哪个(子)事务是给定子事务的父事务的信息被持久化到数据目录的pg_subtrans子目录中。由于该信息在包含该事务的事务结束时就会过时，因此不必在关闭或崩溃时保存该数据。
子事务和可见性
行版本(元组)在PostgreSQL中的可见性是由xmin和xmax系统列决定的，它们包含创建和销毁事务的事务ID。如果存储的事务ID是子事务的ID，PostgreSQL还必须查询包含的（子）事务的状态，以确定事务ID是否有效。
为了确定语句可以看到哪些元组，PostgreSQL在语句(或事务)开始时获取数据库的快照。这样的快照包括：
1.最大事务ID：从该事务开始的所有内容都是不可见的2.在获取快照时处于活动状态的事务和子事务列表3.当前(子)事务中最早可见命令的命令号command number
通过查找进程数组process array来初始化快照，该进程数组存储在共享内存中并包含有关所有当前正在运行的后端的信息。当然，这包含后端的当前事务 ID，并且每个会话最多可容纳 64 个未中止的子事务。如果有超过 64 个这样的子事务，则快照被标记为suboverflowed。
解释测试结果
一个被标记为suboverflowed快照并没有包括可见性判断需要的所有数据，因此PostgreSQL有时不得不求助于pg_subtrans。这些页面被缓存在共享缓冲区中，但是您可以看到在perf输出的结果中，看到SimpleLruReadPage_ReadOnly位于输出结果前面。其他事务必须更新pg_subtrans来注册子事务，您可以在perf输出中看到它们如何争夺lightweight locks。
诊断子事务过多的问题
除了观察perf top之外，还有其他一些症状指向这个问题
1.工作负载在运行单线程时表现良好，但有多个并发数据库会话中运行时表现糟糕2.你会经常在pg_stat_activity中看到SubtransSLRU的等待事件（老版本是SubtransControlLock）3.如果你使用pg_export_snapshot()函数导出一个快照，在pg_snapshots子目录下的结果文件，会包含"sof:1"的行，用于表明子事务数组已经溢出了
小结
子事务是个利器，但你要谨慎使用。如果需要并发，单个事务内不要超过64个子事务。本文中显示的诊断应该可以帮助你确定是否遇到了类似问题。找到这个问题的原因可能很棘手。例如，您为SQL语句的每个结果行(可能在触发器中)调用的带有异常处理程序的函数启动一个新的子事务可能并不明显。
postgres=# begin;
BEGIN
postgres=*# select txid_current();
txid_current
--------------
1209675
(1 row)
postgres=*# insert into test values(1);
INSERT 0 1
postgres=*# savepoint a;
SAVEPOINT
postgres=*# insert into test values(2);
INSERT 0 1
postgres=*# savepoint b; ---做了两次savepoint
SAVEPOINT
postgres=*# savepoint c; ---做了两次savepoint，事务加1
SAVEPOINT
postgres=*# insert into test values(3);
INSERT 0 1
postgres=*# commit ;
COMMIT
postgres=# select id,xmin from test;
id | xmin
----+---------
1 | 1209675
2 | 1209676
3 | 1209678
(3 rows)
postgres=# begin;
BEGIN
postgres=*# select txid_current();
txid_current
--------------
1209679
(1 row)
postgres=*# insert into test values(4);
INSERT 0 1
postgres=*# savepoint a;
SAVEPOINT
postgres=*# insert into test values(5);
INSERT 0 1
postgres=*# savepoint b;
SAVEPOINT
postgres=*# savepoint c;
SAVEPOINT
postgres=*# insert into test values(6);
INSERT 0 1
postgres=*# rollback to savepoint a;
ROLLBACK
postgres=*# rollback to savepoint b;
ERROR: savepoint "b" does not exist
postgres=!# commit ;
ROLLBACK
postgres=# select * from test;
id
----
1
2
3
(3 rows)这个例子可以看到
1.每一个savepoint语句都会消耗一个事务ID，即使没有做写入操作2.子事务也可以"包含"子事务
实际案例
参照陈华军在此次大会上的分享：
之前同事遇到过一起实际案例，也是大量子事务带来的性能问题。源端OGG同步过来，最开始是6000条提交一次，也就意味着
start transaction;
insert into xxx
savepoint 1
insert into xxx
savepoint 2
insert into xxx
savepoint 3
...总共6000个savepoint，大量的SubtransControlLock锁的竞争。后续更改为50条就提交一次了之后，问题得以解决。
另外在JDBC中，有如下参数控制子事务
•autosave = String Specifies what the driver should do if a query fails. In autosave=always mode, JDBC driver sets a savepoint before each query, and rolls back to that savepoint in case of failure. In autosave=never mode (default), no savepoint dance is made ever. In autosave=conservative mode, savepoint is set for each query, however the rollback is done only for rare cases like ‘cached statement cannot change return type’ or ‘statement XXX is not valid’ so JDBC driver rollsback and retries The default is never•cleanupSavepoints = boolean Determines if the SAVEPOINT created in autosave mode is released prior to the statement. This is done to avoid running out of shared buffers on the server in the case where 1000’s of queries are performed. The default is ‘false’W
在psql中，则是ON_ERROR_ROLLBACK。
所以，子事务能不用就不用吧。
原文作者：PostgreSQL学徒；如有侵权，请联系管理员删除。
