Mysql – Read query locking the insertion of a new record on a table with error – Lock wait timeout exceeded; try restarting transaction

I have a stored procedure that runs every one hour to generate a summary of the transactions that happened in the service. The stored procedure reads the data from a table called transaction_log and populates(insert or updates rows) another table called transaction_log_summary. There is no connection between the tables on a constraint level, ie no foreign key references etc. But for some reason when the stored procedure runs for the first time in a day and if a new insert query is also executed on the transaction_log at the same time, the insert statement failes with Lock wait timeout exceeded; try restarting transaction error. The default isolation level used here is REPEATABLE READ (innoDB). How can a read operation lock the table from new rows being inserted?

Sharing the stored procedure and the related code below.

Function used to update the entries in the summary table

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 ;

Stored procedure that performs date checks and updates the values using the above function

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 ;

Event scheduled to execute the procedure every one hour

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

Also, observed that in one of the deployment environment where the stored procedures isolation level was updated as READ COMMITTED, we didnt face the issue.
ie within the stored procedure, before the transaction is started, we set the isolation level.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

But how come a read operation cause this issue? And did this setting of the distinct isolation level for stored procedure really solve the issue or will we encounter the issue in a different use case?