Sergei, Here is the new patch: http://lists.askmonty.org/pipermail/commits/2012-May/003324.html I tested things in various ways, and it seems working decent.
1st client: LOCK TABLE t1 WRITE; done
2nd client: SET READ_ONLY=1; if the t1 is MyISAM, it hangs until t1 unlocked if the t1 is InnoDB, it completes immediately.
1st client: INSERT t1 VALUES (1);
if the t1 is MyISAM, the INSERT succeedes at once while the SET READ_ONLY is hanging in the 2nd client. if the t1 is InnoDB, the INSERT fails ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
That's right, it' won't work with the mixed types of tables. But as the customer didn't ask for that, maybe the simplest solution will do here. I'm not sure about it. It's basically a gotcha. "SET READ_ONLY will not flush transactional tables, but only if the statement uses no non-transactional tables. Otherwise it'll flush all tables, transactional or not". One cannot always know if non-transactional tables are involved. Think of log tables, tables used in triggers and stored routines.
Temporary tables shouldn't be a problem here i think. Otherwise, I wasn't able to come out with something better. Regards HF