REPEATABLE READ下读操作引发INSERT锁超时问题及隔离级别疑问

问题背景 #

有一个每小时运行的存储过程，用于生成交易汇总数据：从transaction_log表读取数据，向transaction_log_summary表插入或更新记录（两表无外键约束）。每日首次运行该存储过程时，若同时有新的INSERT语句写入transaction_log表，INSERT会触发Lock wait timeout exceeded; try restarting transaction错误。默认使用InnoDB的REPEATABLE READ隔离级别，改为READ COMMITTED后问题消失，疑问点：

1. 读操作为何会阻止表中插入新行？
2. READ COMMITTED隔离级别是否能彻底解决问题？

相关代码 #

更新汇总表的存储过程

DELIMITER //
CREATE PROCEDURE func_update_summary(IN date_param DATE, IN last_summary_index BIGINT)
BEGIN
    -- Update the summary for the input date with logs having newer ids 
        UPDATE transaction_log_summary tls
        JOIN (
            SELECT 
                DATE(created_at) AS date,
                SUM(CASE WHEN channel = 'NATS' THEN 1 ELSE 0 END) AS channel_NATS,
                SUM(CASE WHEN channel = 'SQS' THEN 1 ELSE 0 END) AS channel_SQS,
                SUM(CASE WHEN channel = 'API' THEN 1 ELSE 0 END) AS channel_API,
                SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS status_SUCCESS,
                SUM(CASE WHEN status = 'FAILURE' THEN 1 ELSE 0 END) AS status_FAILURE,
                SUM(CASE WHEN status = 'SKIPPED' THEN 1 ELSE 0 END) AS status_SKIPPED,
                MAX(id) AS last_transaction_log_index
            FROM 
                transaction_log
            WHERE
                DATE(created_at) = date_param
                AND id > last_summary_index
            GROUP BY 
                DATE(created_at)
        ) AS temp ON tls.date = temp.date
        SET 
            tls.channel_NATS = tls.channel_NATS + temp.channel_NATS,
            tls.channel_SQS = tls.channel_SQS + temp.channel_SQS,
            tls.channel_API = tls.channel_API + temp.channel_API,
            tls.status_SUCCESS = tls.status_SUCCESS + temp.status_SUCCESS,
            tls.status_FAILURE = tls.status_FAILURE + temp.status_FAILURE,
            tls.status_SKIPPED = tls.status_SKIPPED + temp.status_SKIPPED,
            tls.last_transaction_log_index = temp.last_transaction_log_index
        WHERE
            tls.date = date_param;
END//

DELIMITER ;

主调度存储过程

DELIMITER //

CREATE PROCEDURE proc_update_transaction_log_summary()
BEGIN
    DECLARE present_date DATE;
    DECLARE last_summary_date DATE;
    DECLARE last_summary_index BIGINT;
    DECLARE last_log_index BIGINT;
    DECLARE has_records INT;

    DECLARE error_code INT;
    DECLARE error_msg VARCHAR(255);

    DECLARE lock_acquired INT DEFAULT 0;

    -- Try to acquire the named lock immediately
    SET lock_acquired = GET_LOCK('update_summary_lock', 0); -- 0 means try to acquire immediately
    
    IF lock_acquired = 1 THEN
        BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                GET DIAGNOSTICS CONDITION 1 error_code = MYSQL_ERRNO, error_msg = MESSAGE_TEXT;
                ROLLBACK;
                SELECT CONCAT('Error ', error_code, ': ', error_msg);
                DO RELEASE_LOCK('update_summary_lock');
            END;

            START TRANSACTION;
            
            SET present_date = CURDATE();
            
            -- Get the last index and date from transaction_log_summary table
            SELECT last_transaction_log_index, date INTO last_summary_index, last_summary_date
            FROM transaction_log_summary
            ORDER BY date DESC
            LIMIT 1;

            -- If last summary date is less than current date, update the summary
            IF last_summary_date < present_date THEN
                BEGIN
                    -- Get the last index from the transaction log for the last_summary_date
                    SELECT MAX(id) INTO last_log_index FROM transaction_log 
                    WHERE DATE(created_at) = last_summary_date;

                    IF last_log_index > last_summary_index THEN
                        CALL func_update_summary(last_summary_date, last_summary_index);
                    END IF;
                    
                    -- Check if there are records for the present date
                    SELECT EXISTS ( SELECT 1 FROM transaction_log WHERE DATE(created_at) =
                    CURRENT_DATE LIMIT 1) INTO has_records;

                    IF has_records THEN
                        
                        -- Insert new record in the summary table for the current date
                        INSERT INTO transaction_log_summary (date, channel_NATS, channel_SQS, channel_API, status_SUCCESS, status_FAILURE, status_SKIPPED, last_transaction_log_index)
                        SELECT present_date,
                            SUM(CASE WHEN channel = 'NATS' THEN 1 ELSE 0 END) AS channel_NATS,
                            SUM(CASE WHEN channel = 'SQS' THEN 1 ELSE 0 END) AS channel_SQS,
                            SUM(CASE WHEN channel = 'API' THEN 1 ELSE 0 END) AS channel_API,
                            SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS status_SUCCESS,
                            SUM(CASE WHEN status = 'FAILURE' THEN 1 ELSE 0 END) AS status_FAILURE,
                            SUM(CASE WHEN status = 'SKIPPED' THEN 1 ELSE 0 END) AS status_SKIPPED,
                            MAX(id) AS last_transaction_log_index
                        FROM transaction_log
                        WHERE DATE(created_at) = present_date;

                    ELSE
                        -- Insert new record in the summary table for the present date with last index from previous day
                        INSERT INTO transaction_log_summary (date,  last_transaction_log_index)
                        SELECT present_date, MAX(id) AS last_transaction_log_index
                        FROM transaction_log;

                    END IF;
                END;
            ELSE
                BEGIN
                    -- Get the last index from the transaction log for the last_summary_date
                    SELECT MAX(id) INTO last_log_index FROM transaction_log 
                    WHERE DATE(created_at) = present_date;
                    
                    -- Check if there are new entries in the log table
                    IF last_log_index > last_summary_index THEN
                        CALL func_update_summary(present_date, last_summary_index);
                    END IF;
                END;
            END IF;
            
            COMMIT;
            SELECT ''; -- empty msg utilised by app layer to identify successful end of execution
            DO RELEASE_LOCK('update_summary_lock');
        END;
    END IF;

END//

DELIMITER ;

调度事件

DELIMITER //
CREATE EVENT job_update_transaction_log_summary
ON SCHEDULE
    EVERY 1 HOUR
DO
BEGIN
    CALL proc_update_transaction_log_summary();
END//
DELIMITER ;

读操作阻塞INSERT的核心原因 #

问题出在InnoDB的REPEATABLE READ（RR）隔离级别下的**间隙锁（Gap Lock）**机制：

1. RR级别为了防止幻读，InnoDB会对查询涉及的索引范围加间隙锁，不仅锁定已存在的记录，还会锁定记录之间的间隙，阻止新数据插入到这个范围内。
2. 存储过程中的多个查询会触发间隙锁：
   • SELECT MAX(id) FROM transaction_log WHERE DATE(created_at) = xxx;：该查询需要扫描created_at匹配的所有行，若created_at没有合适的索引，会进行全表扫描，InnoDB会对整个表的主键索引范围加间隙锁；即使有索引，也会对DATE(created_at)匹配的索引区间加间隙锁。
   • SELECT EXISTS(...) WHERE DATE(created_at) = CURRENT_DATE;和插入汇总时的聚合查询，同样会对匹配的索引范围加间隙锁。
3. 当应用向transaction_log插入新行时，需要获取插入意向锁，但插入意向锁与间隙锁互斥，导致INSERT操作被阻塞，最终触发锁等待超时。

READ COMMITTED隔离级别的解决效果 #

将隔离级别改为READ COMMITTED（RC）后，问题消失的原因：

1. RC级别下，InnoDB关闭了间隙锁（除了外键约束和唯一索引的间隙锁场景），仅使用记录锁，不会锁定索引间隙。
2. RC级别采用快照读时，每次读取都是当前事务的最新快照，不需要通过间隙锁来防止幻读，因此插入新行时不会被间隙锁阻塞。

是否能彻底解决问题？ #

在当前业务场景下，READ COMMITTED可以彻底解决锁等待问题：

• 业务是周期性（每小时）汇总数据，即使RC级别下出现不可重复读（同一事务内多次读同一数据得到不同结果），后续的调度任务会再次汇总遗漏的数据，不会影响最终的汇总准确性。
• RC级别消除了间隙锁导致的INSERT阻塞，完全适配当前“写入交易日志+周期性汇总”的业务流程。

唯一需要注意的点：如果后续业务中出现针对transaction_log的范围UPDATE/DELETE操作，RC级别下的锁行为与RR不同，但当前业务核心是INSERT，因此不会引发新的问题。

内容的提问来源于stack exchange，提问作者midhun d kumar