SQL触发器更新引发的递归锁如何破？检查数据库递归设置的正确方法

触发器是数据库中非常实用的对象，它能在INSERT、UPDATE、DELETE操作发生时自动执行一段逻辑。但如果触发器内部的语句又作用于同一张表，就会出现触发器触发自己的情况，形成递归调用。递归一旦失控，轻则报错“超出了存储过程、函数、触发器的最大嵌套层数”，重则引发长时间的锁等待，让整张表甚至整个库都卡住。这篇文章就来详细分析递归锁产生的原因，并讲解如何检查和调整数据库的递归相关设置。

一、触发器递归是怎么形成的

先看一段典型的错误代码。假设有一张订单表，业务要求每次修改订单金额后自动同步更新表的最后修改时间，于是有人写了一个UPDATE触发器，却在触发器内部直接UPDATE了原表：

CREATE TRIGGER trg_order_update
ON orders
FOR UPDATE
AS
BEGIN
    -- 触发器内部又更新了orders表本身
    UPDATE orders
    SET last_modified = GETDATE()
    WHERE order_id IN (SELECT order_id FROM inserted)
END

这段代码的问题在于：触发器内部的UPDATE语句作用于orders表，这条UPDATE又会再次激活trg_order_update，形成直接递归。如果数据库允许递归，触发器会一层层嵌套下去，每一层都持有相应的锁资源。递归层数不断加深，锁的持有时间越来越长，其他会话想要访问这张表就会被阻塞，表现出来就是所谓的“递归锁”现象。

递归分为两种类型：一种是直接递归，即触发器触发自身，再次执行同样的操作；另一种是间接递归，比如表A的触发器更新表B，表B的触发器又更新表A，形成一个环。间接递归更隐蔽，排查时需要梳理多张表之间的触发器依赖关系，有时环路上甚至涉及三四张表。

二、检查各数据库的递归设置

SQL Server中的recursive triggers选项

SQL Server默认是禁止直接递归的，但间接递归由服务器级选项nested triggers控制。检查方法很简单：

-- 查看当前数据库的直接递归设置，返回1表示允许，0表示禁止
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsRecursiveTriggers') AS IsRecursive;

-- 查看服务器级别的嵌套触发器设置
EXEC sp_configure 'nested triggers';

-- 禁止当前数据库的触发器直接递归（推荐做法）
ALTER DATABASE 当前数据库名
SET RECURSIVE_TRIGGERS OFF;

需要注意的是，RECURSIVE_TRIGGERS只管直接递归。即使把它设为OFF，如果嵌套触发器选项是开启的，间接递归依然可能发生。触发器最大嵌套层数在SQL Server中固定为32层，超过就会报错并回滚整个事务，这也是很多人看到“maximum nesting level of 32 exceeded”错误的根源。

MySQL的递归限制

MySQL的触发器本身不会对同一事件递归激活，也就是说一个触发器修改自己的触发表时，不会再次触发同一个触发器。但触发器可以调用存储过程，存储过程再操作表触发其他触发器，深层调用会受到max_sp_recursion_depth参数限制。检查方式如下：

-- 查看存储过程递归深度限制，默认为0表示不允许递归
SHOW VARIABLES LIKE 'max_sp_recursion_depth';

-- 临时调大递归深度（重启后失效）
SET GLOBAL max_sp_recursion_depth = 10;

-- 查看某个表上定义了哪些触发器
SHOW TRIGGERS WHERE `Table` = 'orders';

如果发现这个参数被人为调得很大，就要警惕触发器和存储过程之间可能存在循环调用。定位这类问题时，可以开启general log观察语句的执行序列，看看是否出现反复执行同一条语句的现象。

PostgreSQL的session_replication_role与触发器检查

PostgreSQL中可以通过系统视图检查触发器定义，排查是否存在自我更新：

-- 查看表上的所有触发器及其定义
SELECT tgname, pg_get_triggerdef(oid) AS trigger_def
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal;

-- 检查函数体内是否包含对原表的UPDATE语句
SELECT proname, prosrc
FROM pg_proc
WHERE oid IN (
    SELECT tgfoid FROM pg_trigger
    WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal
);

PostgreSQL允许触发器递归，靠开发者自己控制退出条件，因此检查触发器函数体的逻辑格外重要。

三、从代码层面根治递归调用

调整数据库设置只是防守手段，真正稳妥的做法是让触发器逻辑本身不会无限自我触发。最常用的方案是利用update函数或条件判断，只在关心的列发生变化时才执行逻辑。回到开头的例子，如果last_modified列的更新不再触发进一步操作，递归链条就断了：

CREATE TRIGGER trg_order_update
ON orders
FOR UPDATE
AS
BEGIN
    -- 只有当业务列真正变化时才更新修改时间
    IF UPDATE(order_amount)
    BEGIN
        UPDATE o
        SET o.last_modified = GETDATE()
        FROM orders o
        INNER JOIN inserted i ON o.order_id = i.order_id
        -- 关键：排除仅修改last_modified引起的触发
        WHERE EXISTS (
            SELECT 1 FROM deleted d
            WHERE d.order_id = i.order_id
              AND ISNULL(d.order_amount, -1) <> ISNULL(i.order_amount, -1)
        )
    END

另一种思路是使用会话级标记。在触发器开头检查某个上下文标记，如果标记表明当前正处于触发器逻辑中，就直接返回。SQL Server中可以用CONTEXT_INFO实现：

CREATE TRIGGER trg_safe_update
ON orders
FOR UPDATE
AS
BEGIN
    DECLARE @ctx VARBINARY(8) = CONTEXT_INFO();
    -- 标记存在说明是触发器内部的更新，直接退出，阻断递归
    IF @ctx = 0x01 RETURN;

    SET CONTEXT_INFO 0x01;
    BEGIN
        UPDATE orders SET last_modified = GETDATE()
        WHERE order_id IN (SELECT order_id FROM inserted);
    END
    SET CONTEXT_INFO 0x00;
END

除了改代码，还可以借助锁等待视图实时诊断递归锁。SQL Server中查询sys.dm_os_waiting_stats或sys.dm_exec_requests，重点看blocking_session_id是否等于自身会话ID；PostgreSQL中查询pg_locks配合pg_stat_activity，观察同一个会话是否在等待自己持有的锁。如果发现会话被自己阻塞，基本可以断定是触发器递归引发的锁等待。

总结一下排查顺序：先确认数据库的递归相关设置，再用系统视图列出所有触发器定义，画出表之间的触发关系图找环，最后通过update函数、条件判断或会话标记改造触发器逻辑。禁止递归加上条件守护双管齐下，触发器递归锁问题就能彻底解决。

SQL触发器递归锁recursive_triggers修改时间：2026-09-13 13:58:33