0

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

  • You cannot have optimistic concurrency on the inner SELECT because its result-set is being used by the UPDATE so those ids need to be locked. – Randolph West Jan 2 '19 at 22:02
  • Use a JOIN instead of IN ( SELECT ... ). How many rows do you typically change? – Rick James Jan 3 '19 at 4:24
  • @RandolphWest I disagree that I can not or I should not. I agree that the resultset is non-determenistic. – Roman Jan 3 '19 at 16:14
0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.