我们正在将现有Java应用程序从Oracle DB迁移到PostgreSQL，最近我们注意到PostgreSQL出现以下“意外”行为：
会话#1：

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

会话#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中，一切都按预期运行：
会话#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

会话#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;

我已经阅读了PSQLException: current transaction is aborted, commands ignored until end of transaction block主题，并了解到保留以前Oracle行为的唯一方法（即在尝试锁定行失败后保持事务活动）是使用保存点。好的，到目前为止一切顺利，下面的代码至少在Supplier<T> supplier仅执行DB操作的情况下执行预期的操作（我确实理解在执行由保存点备份的更复杂操作时，数据库和持久性上下文之间存在差异的风险）：

@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中实现保存点可能会导致严重的性能问题，例如：

• Why we spent the last month eliminating PostgreSQL subtransactions
• PostgreSQL Subtransactions and performance
• PostgreSQL Subtransactions Considered Harmful

然而，这些博客文章中没有一个真正提供关于哪些保存点模式是安全的，哪些是不安全的信息，所以我的问题是：
在PostgreSQL中使用以下保存点模式是否安全：

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

我确实看到XID增长是不可能避免的，但是我不确定它对性能的影响，其他陷阱呢？