在GaussDB/postgresql系数据库中的xact_rollback增长

by

在 PostgreSQL系数据库中(含GaussDB for OpenGauss)，pg_stat_database 视图中的 xact_rollback 计数器表示 事务回滚（rollback）的次数。当这个值增加时，意味着数据库中发生了事务回滚操作。今日有客户的数据库环境监控了该指标，提示回滚率过高，谈谈我的看法。

首先监控回滚率是个不太使用的指标，于其监控 xact_commit 和 xact_rollback 的绝对值，不如监控自上次检查以来这些值的变化。否则，你将无法及时发现潜在的问题：如果已经正常运行了好几个月，那么需要很长时间的异常行为才能明显改变该比率。 不如当多长时间内达到频繁的rollback分析。

常见原因：

原因是否增加 xact_rollback
显式 ROLLBACK✅ 是
错误导致自动回滚✅ 是
客户端断开连接✅ 是
ROLLBACK TO SAVEPOINT✅ 是（计入）
触发器/函数异常✅ 是
如果你发现 xact_rollback 增长过快，建议：
检查数据库日志中的错误信息；
分析应用逻辑是否频繁使用回滚；
优化约束检查、死锁处理等机制；
提高代码健壮性，避免不必要的异常抛出。

下面简单的测试 –环境gaussdb

anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271208 |            18
 gauss     |     1273563 |            10
(7 rows)

anbob=# select 1;
 ?column?
----------
        1
(1 row)

anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271210 |            18
 gauss     |     1273563 |            10
(7 rows)

anbob=# select * from tnotfound;
ERROR:  Relation "tnotfound" does not exist on anbob.
LINE 1: select * from tnotfound;
                      ^
anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271217 |            19 《《《《《《《
 gauss     |     1273569 |            10
(7 rows)

anbob=# create table t(id primary key);
ERROR:  syntax error at or near "primary"
LINE 1: create table t(id primary key);
                          ^
anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271218 |            19
 gauss     |     1273569 |            10
(7 rows)

anbob=# create table t(id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE
anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271226 |            20 《《《《《《《
 gauss     |     1273577 |            10
(7 rows)

anbob=# insert into t values(1);
INSERT 0 1
anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271228 |            20 《《《《《《《
 gauss     |     1273577 |            10
(7 rows)

anbob=# insert into t values(1);
ERROR:  Duplicate key value violates unique constraint "t_pkey".
DETAIL:  Key (id)=(1) already exists.
anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271229 |            21 《《《《《《《
 gauss     |     1273577 |            10
(7 rows)

anbob=# rollback;
NOTICE:  there is no transaction in progress
ROLLBACK
anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271236 |            22  《《《《《《《
 gauss     |     1273583 |            10
(7 rows)


anbob=# insert into t values(null);
ERROR:  The null value in column "id" violates the not-null constraint.
DETAIL:  Failing row contains (null).

anbob=# select datname,xact_commit,xact_rollback from pg_stat_database;
  datname  | xact_commit | xact_rollback
-----------+-------------+---------------
 anbob     |     1271430 |            23 《《《《《《《
 gauss     |     1273770 |            10
(7 rows)

分析错误

参数配置 log_min_error_statement 为error (default)

anbob=# show log_min_error_statement
anbob-# ;
 log_min_error_statement
-------------------------
 error

查找日志

$ grep -A 4  "\[BACKEND\] ERROR" gaussdb-2025-08-29_000000.log
...

2025-08-29 15:45:50.017 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742046 [BACKEND] ERROR:  Relation "pg_stat_databae" does not exist on anbob. at character 320
2025-08-29 15:45:50.017 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742046 [BACKEND] CAUSE:  Failed to recognize the relation.
2025-08-29 15:45:50.017 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742046 [BACKEND] ACTION:  Check whether the specified position has the relation.
2025-08-29 15:45:50.017 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742046 [BACKEND] STATEMENT:  select datname,round(100*(xact_rollback::numeric/(case when xact_commit > 0 then xact_commit else 1 end + xact_rollback)),2)||$$ %$$ rollback_ratio, round(100*(blks_hit::numeric/(case when blks_read>0 then blks_read else 1 end + blks_hit)),2)||$$ %$$ hit_ratio, blk_read_time, blk_write_time, conflicts, deadlocks from pg_stat_databae;
2025-08-29 15:45:52.697 gauss postgres localhost 281469869474304 0[0:0#0]  0 [INSTR] LOG:  clean statement thread start
--
2025-08-29 15:47:12.238 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742087 [BACKEND] ERROR:  Relation "tnotfound" does not exist on anbob. at character 15
2025-08-29 15:47:12.238 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742087 [BACKEND] CAUSE:  Failed to recognize the relation.
2025-08-29 15:47:12.238 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742087 [BACKEND] ACTION:  Check whether the specified position has the relation.
2025-08-29 15:47:12.238 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742087 [BACKEND] STATEMENT:  select * from tnotfound;
2025-08-29 15:47:17.283 gauss postgres localhost 281471150768640 0[0:0#0]  0 [BACKEND] LOG:  perf generate flamegraph report: flamegraph-2025-08-29_154711.html
--
2025-08-29 15:47:45.387 gauss anbob [local] 281469886317056 0[0:0#0]  0 [BACKEND] ERROR:  syntax error at or near "primary" at character 19
2025-08-29 15:47:45.387 gauss anbob [local] 281469886317056 0[0:0#0]  0 [BACKEND] CAUSE:  Syntax error happened.
2025-08-29 15:47:45.387 gauss anbob [local] 281469886317056 0[0:0#0]  0 [BACKEND] ACTION:  Check character string.
2025-08-29 15:47:45.387 gauss anbob [local] 281469886317056 0[0:0#0]  0 [BACKEND] STATEMENT:  create table t(id primary key);
2025-08-29 15:47:53.332 gauss postgres localhost 281469869474304 0[0:0#0]  0 [INSTR] LOG:  clean statement thread start
--
2025-08-29 15:48:53.177 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742112 [BACKEND] ERROR:  Duplicate key value violates unique constraint "t_pkey".
2025-08-29 15:48:53.177 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742112 [BACKEND] DETAIL:  Key (id)=(1) already exists.
2025-08-29 15:48:53.177 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742112 [BACKEND] CAUSE:  Duplicate key values exist.
2025-08-29 15:48:53.177 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742112 [BACKEND] ACTION:  Remove the unique restriction, or do not insert the same key.
2025-08-29 15:48:53.177 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742112 [BACKEND] STATEMENT:  insert into t values(1);
--
2025-08-29 16:18:15.404 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742661 [BACKEND] ERROR:  The null value in column "id" violates the not-null constraint.
2025-08-29 16:18:15.404 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742661 [BACKEND] DETAIL:  Failing row contains (null).
2025-08-29 16:18:15.404 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742661 [BACKEND] CAUSE:  Not-null constraint check fails.
2025-08-29 16:18:15.404 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742661 [BACKEND] ACTION:  Please check null data source.
2025-08-29 16:18:15.404 gauss anbob [local] 281469886317056 0[0:0#0]  1125899910742661 [BACKEND] STATEMENT:  insert into t values(null);

— over —