PostgreSQL 14.5 PL/pgSQL函数并发执行时出现tuple concurrently updated错误

开发者阿泽

2026-6-20

并发场景下PL/pgSQL权限校验函数抛出tuple concurrently updated错误排查

我编写了一个用作权限校验中间件的PL/pgSQL函数，单例执行完全正常，但在并发测试场景（20个虚拟用户1分钟内发起350-400次请求）下，会随机抛出"original: error: tuple concurrently updated"错误。函数逻辑为：通过多表关联查询判断用户归属，返回1或0；若为1则执行另一查询并返回JSON数据，否则返回指定JSON。尝试在函数中添加commit语句后问题仍未解决，以下是相关代码及错误信息，请求协助排查。

PL/pgSQL函数代码

create or replace function getUserData() returns json as 
$body$
    declare 
        v_belongs_to_group json;
        v_has_permission_in_group json;
    begin
        if (
            select case when exists(
                SELECT u.email , utg.id, g.group_name, g.id 
                    FROM users as u
                    inner join public.users_to_groups as utg on utg.user_id = u.id
                    inner join "groups" as g on g.id = utg.group_id 
                    where u.email='email@email.com' 
                    and g.group_name ='necessary group'
                    and g.status_id = 1
                ) then cast(1 as integer) else cast(0 as integer) end
        ) = 1 then
                select 
                    jsonb_build_object(
                        'user_id', u.id
                    )
                    from users as u 
                    inner join users_to_groups utg on utg.user_id=u.id 
                    inner join "groups" g on g.id = utg.group_id
                    inner join users_to_permissions utp on utp.user_group_id = utg.id 
                    inner join permissions p on p.id =utp.permission_id 
                    where u.email= 'email@email.com' 
                    and g.group_name = 'another Group' 
                    and p."name" = 'some permission'
                    and g.status_id = 1
                    into v_has_permission_in_group;
                return to_json(v_has_permission_in_group);
            else
                v_belongs_to_group = jsonb_build_object('v_belongs_to_group',false);
                return to_json(v_belongs_to_group);
        end if;
    commit;
end; 
$body$ 
language plpgsql;

select * from getUserData() as fruit;

完整错误栈

DatabaseError [SequelizeDatabaseError]: tuple concurrently updated
        at Query.formatError (src\node_modules\sequelize\lib\dialects\postgres\query.js:366:16)
        at src\node_modules\sequelize\lib\dialects\postgres\query.js:72:18  
        at tryCatcher (src\node_modules\bluebird\js\release\util.js:16:23)  
        at Promise._settlePromiseFromHandler (src\node_modules\bluebird\js\release\promise.js:547:31)
        at Promise._settlePromise (src\node_modules\bluebird\js\release\promise.js:604:18)
        at Promise._settlePromise0 (src\node_modules\bluebird\js\release\promise.js:649:10)
        at Promise._settlePromises (src\node_modules\bluebird\js\release\promise.js:725:18)
        at _drainQueueStep (src\node_modules\bluebird\js\release\async.js:93:12)
        at _drainQueue (src\node_modules\bluebird\js\release\async.js:86:9) 
        at Async._drainQueues (src\node_modules\bluebird\js\release\async.js:102:5)
        at Async.drainQueues (src\node_modules\bluebird\js\release\async.js:15:14)
        at process.processImmediate (node:internal/timers:483:21) {
      parent: error: tuple concurrently updated
          at Parser.parseErrorMessage (src\node_modules\pg-protocol\dist\parser.js:287:98)
          at Parser.handlePacket (src\node_modules\pg-protocol\dist\parser.js:126:29)
          at Parser.parse (src\node_modules\pg-protocol\dist\parser.js:39:38)
          at Socket.<anonymous> (src\node_modules\pg-protocol\dist\index.js:11:42)
          at Socket.emit (node:events:520:28)
          at addChunk (node:internal/streams/readable:559:12)
          at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
          at Readable.push (node:internal/streams/readable:390:5)
          at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {
        length: 90,
        severity: 'ERROR',
        code: 'XX000',
        detail: undefined,
        hint: undefined,
        position: undefined,
        internalPosition: undefined,
        internalQuery: undefined,
        where: undefined,
        schema: undefined,
        table: undefined,
        column: undefined,
        dataType: undefined,
        constraint: undefined,
        file: 'heapam.c',
        line: '4260',
        routine: 'simple_heap_update',
        sql: 'create or replace function getUserData() returns json as \n' +
          '        $body$\n' +
          '          declare \n' +
          '            belongs_to_default_group json;\n' +
          '            has_permission_in_group json;\n' +
          '          begin\n' +
          '            if (\n' +
          '              select case when exists(\n' +
          '                SELECT u.email , utg.id, g.group_name, g.id \n' +
          '                  FROM users as u\n' +
          '                  inner join public.users_to_groups as utg on utg.user_id = u.id\n' +
          '                  inner join "groups" as g on g.id = utg.group_id \n' +
          '                  where u.email=\'email@email.com\' \n' +
          '                  and g.group_name =\'necessary group\'\n' +
          '                  and g.status_id = 1 for share\n' +
          '                ) then cast(1 as integer) else cast(0 as integer) end\n' +
          '            ) = 1 then\n' +
          '                    select \n' +
          '                      jsonb_build_object(\n' +
          '                        \'user_id\', u.id\n' +
          '                      )\n' +
          '                      from users as u \n' +
          '                      inner join users_to_groups utg on utg.user_id=u.id \n' +
          '                      inner join "groups" g on g.id = utg.group_id\n' +
          '                      inner join users_to_permissions utp on utp.user_group_id = utg.id \n' +
          '                      inner join permissions p on p.id =utp.permission_id \n' +
          '                      where u.email= \'email@email.com\' \n' +
          '                      and g.group_name =\'another Group\' \n' +
          '                      and p."name" = \'some permission\'\n' +
          '                      and g.status_id = 1\n' +
          '                      into has_permission_in_group for share;\n' +
          '                    return to_json(has_permission_in_group);\n' +
          '                else\n' +
          '                    belongs_to_default_group = jsonb_build_object(\'belongs_to_default_group\',false);\n' +
          '                    return to_json(belongs_to_default_group);\n' +
          '            end if;\n' +
          '            commit;\n' +
          '        end; \n' +
          '        $body$ \n' +
          '        language plpgsql;\n' +
          '        \n' +
          '        select * from getUserData() as fruit;',
        parameters: undefined
      },
      original: error: tuple concurrently updated
          at Parser.parseErrorMessage (src\node_modules\pg-protocol\dist\parser.js:287:98)
          at Parser.handlePacket (src\node_modules\pg-protocol\dist\parser.js:126:29)
          at Parser.parse (src\node_modules\pg-protocol\dist\parser.js:39:38)
          at Socket.<anonymous> (src\node_modules\pg-protocol\dist\index.js:11:42)
          at Socket.emit (node:events:520:28)
          at addChunk (node:internal/streams/readable:559:12)
          at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
          at Readable.push (node:internal/streams/readable:390:5)
          at TCP.onStreamRead (node:internal/stream_base_commons:191:23) {
        length: 90,
        severity: 'ERROR',
        code: 'XX000',
        detail: undefined,
        hint: undefined,
        position: undefined,
        internalPosition: undefined,
        internalQuery: undefined,
        where: undefined,
        schema: undefined,
        table: undefined,
        column: undefined,
        dataType: undefined,
        constraint: undefined,
        file: 'heapam.c',
        line: '4260',
        routine: 'simple_heap_update',
        sql: 'create or replace function getUserData() returns json as \n' +
          '        $body$\n' +
          '          declare \n' +
          '            belongs_to_default_group json;\n' +
          '            has_permission_in_group json;\n' +
          '          begin\n' +
          '            if (\n' +
          '              select case when exists(\n' +
          '                SELECT u.email , utg.id, g.group_name, g.id \n' +
          '                  FROM users as u\n' +
          '                  inner join public.users_to_groups as utg on utg.user_id = u.id\n' +
          '                  inner join "groups" as g on g.id = utg.group_id \n' +
          '                  where u.email=\'email@email.com\' \n' +
          '                  and g.group_name =\'necessary group\'\n' +
          '                  and g.status_id = 1 for share\n' +
          '                ) then cast(1 as integer) else cast(0 as integer) end\n' +
          '            ) = 1 then\n' +
          '                    select \n' +
          '                      jsonb_build_object(\n' +
          '                        \'user_id\', u.id\n' +
          '                      )\n' +
          '                      from users as u \n' +
          '                      inner join users_to_groups utg on utg.user_id=u.id \n' +
          '                      inner join "groups" g on g.id = utg.group_id\n' +
          '                      inner join users_to_permissions utp on utp.user_group_id = utg.id \n' +
          '                      inner join permissions p on p.id =utp.permission_id \n' +
          '                      where u.email= \'email@email.com\' \n' +
          '                      and g.group_name =\'another Group\' \n' +
          '                      and p."name" = \'some permission\'\n' +
          '                      and g.status_id = 1\n' +
          '                      into has_permission_in_group for share;\n' +
          '                    return to_json(has_permission_in_group);\n' +
          '                else\n' +
          '                    belongs_to_default_group = jsonb_build_object(\'belongs_to_default_group\',false);\n' +
          '                    return to_json(belongs_to_default_group);\n' +
          '            end if;\n' +
          '            commit;\n' +
          '        end; \n' +
          '        $body$ \n' +
          '        language plpgsql;\n' +
          '        \n' +
          '        select * from getUserData() as fruit;',
        parameters: undefined
      },
      sql: 'create or replace function getUserData() returns json as \n' +
        '        $body$\n' +
        '          declare \n' +
        '            belongs_to_default_group json;\n' +
        '            has_permission_in_group json;\n' +
        '          begin\n' +
        '            if (\n' +
        '              select case when exists(\n' +
        '                SELECT u.email , utg.id, g.group_name, g.id \n' +
        '                  FROM users as u\n' +
        '                  inner join public.users_to_groups as utg on utg.user_id = u.id\n' +
        '                  inner join "groups" as g on g.id = utg.group_id \n' +
        '                  where u.email=\'email@email.com\' \n' +
        '                  and g.group_name =\'necessary group\'\n' +
        '                  and g.status_id = 1 for share\n' +
        '                ) then cast(1 as integer) else cast(0 as integer) end\n' +
        '            ) = 1 then\n' +
        '                    select \n' +
        '                      jsonb_build_object(\n' +
        '                        \'user_id\', u.id\n' +
        '                      )\n' +
        '                      from users as u \n' +
        '                      inner join users_to_groups utg on utg.user_id=u.id \n' +
        '                      inner join "groups" g on g.id = utg.group_id\n' +
        '                      inner join users_to_permissions utp on utp.user_group_id = utg.id \n' +
        '                      inner join permissions p on p.id =utp.permission_id \n' +
        '                      where u.email= \'email@email.com\' \n' +
        '                      and g.group_name =\'another Group\' \n' +
        '                      and p."name" = \'some permission\'\n' +
        '                      and g.status_id = 1\n' +
        '                      into has_permission_in_group for share;\n' +
        '                    return to_json(has_permission_in_group);\n' +
        '                else\n' +
        '                    belongs_to_default_group = jsonb_build_object(\'belongs_to_default_group\',false);\n' +
        '                    return to_json(belongs_to_default_group);\n' +
        '            end if;\n' +
        '            commit;\n' +
        '        end; \n' +
        '        $body$ \n' +
        '        language plpgsql;\n' +
        '        \n' +
        '        select * from getUserData() as fruit;',
      parameters: undefined
    }

Node.js调用代码

const requestHandler = async(ctx) =>{
   const result = await sequelize.query('plpgsql function as described above');
   if(result)  {ctx.body=result; ctx.status = 200}
   else {ctx.status=409}
}

问题根源与修复方案

1. 核心错误原因

从错误栈的SQL内容可以看出，你的Node.js代码在并发执行函数定义语句create or replace function，而非仅调用函数。函数作为数据库对象，修改操作属于写操作，并发执行时必然触发元组更新冲突，这才是报错的根本原因。此外，函数中的commit语句完全无效：函数是纯查询逻辑，无DML操作，且return语句会终止函数执行，commit永远不会被执行。

2. 具体修复步骤

修正Node.js调用逻辑：先在数据库中执行一次函数定义，之后每次请求仅调用函数：

const requestHandler = async(ctx) =>{
   const result = await sequelize.query('select * from getUserData() as fruit');
   if(result)  {ctx.body=result; ctx.status = 200}
   else {ctx.status=409}
}

移除函数内无效代码：删掉函数末尾的commit;语句。
优化函数查询逻辑：将两次查询合并为一次，减少IO开销与锁竞争：

create or replace function getUserData() returns json as 
$body$
begin
    return coalesce(
        (select 
            jsonb_build_object('user_id', u.id)
            from users as u 
            inner join users_to_groups utg on utg.user_id=u.id 
            inner join "groups" g on g.id = utg.group_id
            inner join users_to_permissions utp on utp.user_group_id = utg.id 
            inner join permissions p on p.id =utp.permission_id 
            where u.email= 'email@email.com' 
            and g.group_name = 'another Group' 
            and p."name" = 'some permission'
            and g.status_id = 1
            and exists(
                SELECT 1
                FROM users_to_groups utg2
                inner join "groups" g2 on g2.id = utg2.group_id 
                where utg2.user_id = u.id
                and g2.group_name ='necessary group

PostgreSQL 14.5 PL/pgSQL函数并发执行时出现tuple concurrently updated错误

PL/pgSQL函数代码 #

完整错误栈 #

Node.js调用代码 #