.NET Core+PostgreSQL存储过程串行执行实现方案咨询

方案一：.NET应用层使用异步锁（单实例API场景） #

由于你的方法是异步类型，不能用常规的lock关键字，需要借助SemaphoreSlim实现异步互斥。要确保SemaphoreSlim是全局唯一的单例实例（比如在服务类中声明为静态成员，或通过依赖注入注册为单例），这样所有请求都会排队等待执行。

修改你的ExecuteAsync方法如下：

// 在服务类内部声明静态信号量，保证全局唯一
private static readonly SemaphoreSlim _executionSemaphore = new SemaphoreSlim(1, 1);

public async Task<bool> ExecuteAsync(bool isChecked)
{
    await _executionSemaphore.WaitAsync().ConfigureAwait(false);
    try
    {
        var connection = await _pSqlHelper.GetConnectionAsync(_configuration.GetConnectionString("DbConnection")!.ToString()).ConfigureAwait(false);

        NpgsqlParameter[] parameters =
                 {
                     new NpgsqlParameter("p_checked", DbType.Boolean)
                       {
                            Value = isChecked,
                            Direction=ParameterDirection.Input
                       }
                   };

        string commandText = @"call dbo.mysp(:p_checked)";
        var command = await _pSqlHelper.ExecuteNonQueryAsync(connection, commandText, parameters);
        await command.DisposeAsync();
        await connection.CloseAsync();

        return true;
    }
    finally
    {
        // 无论执行成功还是异常，都必须释放信号量
        _executionSemaphore.Release();
    }
}

注意：该方案仅适用于API单实例部署的场景，如果API是多实例集群部署，应用层锁无法跨实例生效，此时需要采用数据库端方案。

方案二：数据库端使用排他顾问锁（多实例API场景） #

利用PostgreSQL的顾问锁（Advisory Lock） 在存储过程内部实现串行执行，无论多少个API实例调用，都会在数据库层面排队等待锁释放。

修改你的存储过程dbo.mysp，添加锁的获取与释放逻辑：

CREATE OR REPLACE PROCEDURE dbo.mysp(p_checked boolean)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 获取排他顾问锁，使用一个唯一的锁ID（比如自定义12345，确保不与其他锁冲突）
    PERFORM pg_advisory_lock(12345);

    -- 此处编写原存储过程的业务逻辑
    -- ...

    -- 执行完成后释放锁
    PERFORM pg_advisory_unlock(12345);
EXCEPTION
    -- 捕获异常时必须释放锁，避免锁长期持有导致死锁
    WHEN OTHERS THEN
        PERFORM pg_advisory_unlock(12345);
        RAISE; -- 重新抛出异常，不掩盖原有错误
END;
$$;

说明：顾问锁是数据库级别的，只要锁ID唯一，就能确保该存储过程的所有调用串行执行。如果需要针对特定参数做细粒度串行（比如某类ID对应的请求单独排队），可以将参数与固定值组合生成锁ID（例如p_checked::int + 10000），实现更精准的控制。

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