解决PostgreSQL授予权限时的tuple concurrently updated错误

问题现象 #

动态创建数据库和模式时偶尔出现以下错误：

无法应用数据库权限。
io.vertx.core.impl.NoStackTraceThrowable: Error granting permission.
io.vertx.pgclient.PgException:
ERROR: tuple concurrently updated (XX000)

环境信息：

• PostgreSQL 14.6（Homebrew）
• vertx-pg-client 4.3.8
• 仅运行单个服务实例

问题根源 #

1. 无效的 Advisory Lock：当前代码中ADVISORY_LOCK用随机数生成锁ID，每次调用生成的锁ID都不一样，完全起不到互斥作用，没法阻止同一数据库/角色/模式的并发授权操作。
2. 未检查锁获取结果：就算锁没拿到，代码还是继续执行后续授权语句，导致多个操作同时修改PostgreSQL系统表（比如pg_namespace、pg_class），触发并发更新错误。
3. SQL语法问题：GRANT_PERMISSION3末尾缺分号，可能引发语句执行异常，间接导致事务内的并发问题。

修复方案 #

1. 生成基于资源标识的固定锁ID #

把锁ID和数据库名、角色名、模式名绑定，确保同一资源的操作共用同一个锁ID，实现真正的互斥。可以用PostgreSQL内置哈希函数生成锁ID：

private static final String ADVISORY_LOCK = "SELECT pg_try_advisory_lock("
        + "pg_catalog.hash_text('<db-name>')::bigint, "
        + "pg_catalog.hash_text('<role-name>')::bigint, "
        + "pg_catalog.hash_text('<schema-name>')::bigint"
        + ")";

也可以在Java端计算这三个字符串的组合哈希，生成固定long值传入锁函数，避免SQL层面的哈希计算。

2. 检查锁获取状态 #

执行锁查询后必须检查是否成功获取锁，没拿到就回滚事务终止操作：

.query(updateQueryString(ADVISORY_LOCK, databaseName, userName, schemaName)).execute()
.compose(res1 -> {
    // 检查是否获取到锁
    Boolean lockAcquired = res1.iterator().next().getBoolean(0);
    if (!lockAcquired) {
        // 未获取锁，回滚事务并返回失败
        return tx.rollback().compose(v -> Future.failedFuture("Failed to acquire advisory lock"));
    }
    // 获取锁成功，继续执行后续授权
    return conn.query(updateQueryString(GRANT_PERMISSION1, databaseName, userName)).execute();
})

3. 修正SQL语法错误 #

给GRANT_PERMISSION3补充末尾的分号：

private static final String GRANT_PERMISSION3 = "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema-name> TO <role-name>;";

4. 优化锁的释放逻辑 #

推荐用事务级 Advisory Lock（pg_advisory_xact_lock），它会在事务提交或回滚时自动释放，不用手动处理，简化代码：

private static final String ADVISORY_LOCK = "SELECT pg_advisory_xact_lock("
        + "pg_catalog.hash_text('<db-name>')::bigint, "
        + "pg_catalog.hash_text('<role-name>')::bigint, "
        + "pg_catalog.hash_text('<schema-name>')::bigint"
        + ")";

用事务级锁时无需检查锁获取结果（函数会阻塞直到拿到锁），如果需要非阻塞逻辑，仍可使用pg_try_advisory_xact_lock并检查结果。

修改后的完整代码示例 #

private static final String ERR_PERMISSION_GRANT_ERROR_MESSAGE = "Error granting permission. ";
private static final String ADVISORY_LOCK = "SELECT pg_advisory_xact_lock("
        + "pg_catalog.hash_text('<db-name>')::bigint, "
        + "pg_catalog.hash_text('<role-name>')::bigint, "
        + "pg_catalog.hash_text('<schema-name>')::bigint"
        + ")";  
private static final String CREATE_USER = "CREATE ROLE <role-name> LOGIN PASSWORD <pwd>;";
private static final String GRANT_PERMISSION1 = "GRANT CREATE, CONNECT ON DATABASE <db-name> TO <role-name>;";
private static final String GRANT_PERMISSION2 = "GRANT USAGE ON SCHEMA <schema-name> TO <role-name>;";
private static final String GRANT_PERMISSION3 = "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema-name> TO <role-name>;";
private static final String GRANT_PERMISSION5 = "ALTER DEFAULT PRIVILEGES IN SCHEMA <schema-name> GRANT ALL ON SEQUENCES TO <role-name>;";


private static Promise<Boolean> grantDatabase(PgPool pool, String databaseName, String userName, String schemaName,
        Vertx vertx) {
    Promise<Boolean> promise = Promise.promise();

    pool.getConnection()
            .onSuccess(conn -> {
                conn.begin().compose(tx -> conn
                        .query(updateQueryString(ADVISORY_LOCK, databaseName, userName, schemaName)).execute()
                        .compose(res1 -> conn.query(updateQueryString(GRANT_PERMISSION1, databaseName, userName)).execute())
                        .compose(res2 -> conn.query(updateQueryString(GRANT_PERMISSION2, schemaName, userName)).execute())
                        .compose(res3 -> conn.query(updateQueryString(GRANT_PERMISSION3, schemaName, userName)).execute())
                        .compose(res4 -> conn.query(updateQueryString(GRANT_PERMISSION5, schemaName, userName)).execute())
                        .compose(res5 -> tx.commit()))
                        .eventually(v -> conn.close())
                        .onSuccess(v -> promise.complete(Boolean.TRUE))
                        .onFailure(err -> promise.fail(ERR_PERMISSION_GRANT_ERROR_MESSAGE + err.getMessage()));
            })
            .onFailure(err -> promise.fail("Failed to get database connection: " + err.getMessage()));
    return promise;
}

总结 #

核心问题是原代码的随机锁ID无法实现互斥，导致同一资源的授权操作并发修改PostgreSQL系统表。通过绑定资源标识生成固定锁ID，确保同一资源的操作串行执行，就能彻底解决"tuple concurrently updated"错误。

内容的提问来源于stack exchange，提问作者BigBug