I have a mariaDB event that runs every hour and copies data from multiple tables to others. Since I’m using INSERT INTO SELECT, and one of the tables is quite big, I end up having deadlocks, because one “insert into select” in one of the tables is taking too long, and the table is currently used in a production environment.

If I change TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED in the beginning of the event, I no longer have deadlocks, and the possible data inconsistencies are no problem to me, but I’m not sure if I’m changing the TRANSACTION ISOLATION LEVEL just for this event session (is there such a thing?), or if I’m changing the TRANSACTION ISOLATION LEVEL on a higher level (and possibly changing it for all the sessions?).

DELIMITER $$
CREATE EVENT event_name
ON SCHEDULE EVERY 1 HOUR
STARTS '2024-05-23 21:00:00'
DO
BEGIN
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    /*<sql code>*/
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; /* resetting to the previous value*/
END$$

I found no information regarding changing the transaction isolation level on mariadb/mysql events. Does anybody have any information regarding this?

I know I have dozens of other options for this data copy job, but I currently have a time/resource constraint, and I can’t handle it in another way.

Thank you all