perform non locking select when updating another table

Question

We need to perform the following (periodic) query in mariadb:

update A set status='INACTIVE' where status='ACTIVE' and a.b_id in
(select id from B where ... LOCK IN SHARE MODE);

Unfortunately it locks table B for updates and causes deadlock errors for other transactions on B:

MySQL error code: 1213, SQLState: 40001, Msg: Deadlock found when trying to get lock;
try restarting transaction query: "UPDATE B set ... where id = ..."

We would like to have a lockless, non consistent isolation in the inner select. Is it possible? We perform the query periodically using EVENTs. Can we change the isolation level inside an event?

Our binlog format is binlog_format = mixed

Answer 1

I think I solved it by adding SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. Each event invocation opens a fresh session so changing the session inside an event handler seems to be safe. Here is full code snippet:

CREATE EVENT IF NOT EXISTS MY_EVENT ON SCHEDULE EVERY 1 DAY STARTS '2019-01-02 09:00:00'
    DO BEGIN
      /*  READ UNCOMMITTED should disable read locking during subquery selects */
      SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
      update A set status='INACTIVE' where status='ACTIVE' and b_id in
        (select id from B where ...);
    END;