如何让SQL Server表的Insert触发器同一时间仅允许单用户执行？

首先，直接让触发器同一时间仅允许单个用户执行（比如用表级锁）并不是最优解——这会严重拖垮多用户场景下的插入性能，所有插入请求都得排队等待，完全没必要。我们可以用原子性的计数器更新来实现并发安全的TransactionID生成，既保证唯一性，又能保留较好的并发性能。

核心思路 #

创建一个专门的年份计数器表，用来记录每一年已生成的TransactionID数量。在触发器中通过原子更新操作获取下一个计数——这种操作是数据库层面保证线程安全的，同一时间只有一个会话能成功更新计数器，其他会话会等待直到锁释放，从根源上避免重复ID。

具体实现 #

1. 先创建年份计数器表 #

这个表用来存储每年的TransactionID累计数：

CREATE TABLE YearlyTransactionCounter (
    Year INT PRIMARY KEY, -- 存储年份，比如2024
    CurrentCount INT NOT NULL DEFAULT 0 -- 当年已生成的TransactionID数量
);

2. 编写并发安全的Insert触发器 #

下面分两种常见数据库给出示例，你可以根据自己的生产环境调整：

针对SQL Server的触发器

CREATE TRIGGER trg_InsertCustomer
ON YourCustomerTable -- 替换成你的表名
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CurrentYear INT = YEAR(GETDATE());
    DECLARE @NextCount INT;

    -- 原子更新计数器并获取下一个序号：这一步是并发安全的，同一时间只有一个会话能执行成功
    UPDATE YearlyTransactionCounter
    SET CurrentCount = CurrentCount + 1
    OUTPUT inserted.CurrentCount
    INTO @NextCount
    WHERE Year = @CurrentYear;

    -- 如果是当年第一条记录，初始化计数器
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO YearlyTransactionCounter (Year, CurrentCount)
        VALUES (@CurrentYear, 1);
        SET @NextCount = 1;
    END

    -- 插入新记录，同时计算total_salary和生成TransactionID
    INSERT INTO YourCustomerTable (customerid, name, total_salary, basicpay, cca, tax, TransactionID)
    SELECT 
        customerid,
        name,
        basicpay + cca - tax, -- 这里是total_salary的计算逻辑，根据你的业务调整
        basicpay,
        cca,
        tax,
        -- 生成格式：年份+补零的序号（比如2024001、2024123），保证ID格式统一
        CAST(@CurrentYear AS VARCHAR(4)) + RIGHT('00000' + CAST(@NextCount AS VARCHAR(5)), 5)
    FROM inserted;
END

针对MySQL的触发器

DELIMITER //
CREATE TRIGGER trg_InsertCustomer
BEFORE INSERT ON YourCustomerTable -- 替换成你的表名
FOR EACH ROW
BEGIN
    DECLARE current_year INT;
    DECLARE next_count INT;

    SET current_year = YEAR(NOW());

    -- 锁定当年的计数器行，避免并发修改（行级锁，只锁当前年份的行，性能影响小）
    SELECT CurrentCount INTO next_count
    FROM YearlyTransactionCounter
    WHERE Year = current_year
    FOR UPDATE;

    -- 处理当年第一条记录的情况
    IF next_count IS NULL THEN
        SET next_count = 1;
        INSERT INTO YearlyTransactionCounter (Year, CurrentCount) VALUES (current_year, next_count);
    ELSE
        SET next_count = next_count + 1;
        UPDATE YearlyTransactionCounter SET CurrentCount = next_count WHERE Year = current_year;
    END IF;

    -- 计算total_salary
    SET NEW.total_salary = NEW.basicpay + NEW.cca - NEW.tax;
    -- 生成TransactionID，这里用5位补零保证格式统一
    SET NEW.TransactionID = CONCAT(current_year, LPAD(next_count, 5, '0'));
END //
DELIMITER ;

关键注意事项 #

• 计数器初始化：如果你的表已经有历史数据，需要把对应年份的累计数提前插入到YearlyTransactionCounter表中，避免新生成的ID和历史ID重复。
• 序号位数：根据你每年的最大记录数调整补零的位数（比如如果每年最多10万条，就用5位），防止序号溢出导致ID格式混乱。
• 性能优势：这种方案用的是行级锁，只会锁定当前年份的计数器行，其他年份的插入操作不受影响，相比表级锁，并发性能提升非常明显。

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