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