PostgreSQL中特定Savepoint模式的安全性及性能风险问询

问题现象 #

我们正将现有Java应用从Oracle数据库迁移至PostgreSQL，近期发现PostgreSQL存在如下意外行为：

PostgreSQL中的行为 #

session#1：

db=> begin;
BEGIN
db=> select r_object_id from dm_sysobject_s where r_object_id='08000000800027d6' for update;
   r_object_id    
------------------
 08000000800027d6

session#2：

db=> begin;
BEGIN
db=> select r_object_id from dm_sysobject_s where r_object_id='08000000800027d6' for update nowait;
ERROR:  could not obtain lock on row in relation "dm_sysobject_s"
db=> select r_object_id from dm_sysobject_s where r_object_id='08000000800027d6' for update nowait;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Oracle中的行为（符合预期） #

session#1：

SQL> set autocommit off;
SQL> select r_object_id from dm_sysobject_s where r_object_id='0800012d80000122' for update;

R_OBJECT_ID
----------------
0800012d80000122

session#2：

SQL> set autocommit off;
SQL> select r_object_id from dm_sysobject_s where r_object_id='0800012d80000122' for update nowait;
select r_object_id from dm_sysobject_s where r_object_id='0800012d80000122' for update nowait
                        *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> select r_object_id from dm_sysobject_s where r_object_id='0800012d80000122' for update nowait;
select r_object_id from dm_sysobject_s where r_object_id='0800012d80000122' for update nowait
                        *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> commit;

现有解决方案 #

查阅资料后得知，在数据库层保留Oracle原有行为（锁行失败后保持事务活跃）的唯一方式是使用Savepoint，为此编写了如下Java代码：

@Override
public <T> T withSavepoint(SessionImplementor session, Supplier<T> supplier) {
    return session.doReturningWork(connection -> {
        DatabaseMetaData metaData = connection.getMetaData();
        if (!metaData.supportsSavepoints()) {
            return supplier.get();
        }
        boolean success = false;
        Savepoint savepoint = null;
        try {
            savepoint = connection.setSavepoint();
            T result = supplier.get();
            success = true;
            return result;
        } finally {
            if (savepoint != null) {
                if (!success) {
                    connection.rollback(savepoint);
                }
                connection.releaseSavepoint(savepoint);
            }
        }
    });
}

疑问 #

经研究发现PostgreSQL的Savepoint实现可能引发严重性能问题，但相关内容未明确哪些模式安全。现问询：

1. 如下Savepoint模式在PostgreSQL中是否安全？

savepoint s1;
select id from tbl where id=? for update nowait;
rollback to/release s1;

2. 已知无法避免XID增长，但不确定其性能影响，还有哪些潜在陷阱？

内容的提问来源于stack exchange，提问作者Andrey B. Panfilov