Bug #57612 SET GLOBAL READ_ONLY=1 cannot progress when a table is locked with LOCK TABLES
Submitted: 20 Oct 2010 22:58 Modified: 6 Mar 2012 1:27
Reporter: Sasha Pachev Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1.49, 5.5.7-bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R3 (Medium) / E4 (High)

[20 Oct 2010 22:58] Sasha Pachev
Read-Locking a table in another thread followed by a long-running select on an InnoDB table will cause set global read_only=1 to hang waiting for COND_refresh.

Without a select, and using a MyISAM table I got random luck in reproducing the results sometimes getting an error:

1192: Can't execute the given command because you have active locked tables or an active transaction

The wait happens here (sql_base.cc/close_cached_tables()):

988             if (table->needs_reopen_or_name_lock() && (table->db_stat ||
989                 (table->open_placeholder && wait_for_placeholders)))
990             {
991               found=1;
992               DBUG_PRINT("signal", ("Waiting for COND_refresh"));
993               pthread_cond_wait(&COND_refresh,&LOCK_open);
994               break;
995             }

with this backtrace:

#0  0x000000319da0a899 in pthread_cond_wait@@GLIBC_2.3.2 ()
   from /lib64/libpthread.so.0
#1  0x00000000005de324 in close_cached_tables (thd=0xdb3cb00, 
    tables=<value optimized out>, have_lock=false, wait_for_refresh=true, 
    wait_for_placeholders=true) at sql_base.cc:993
#2  0x00000000005a4ce0 in sys_var_opt_readonly::update (this=0xc3bcc0, 
    thd=0xdb3cb00, var=0xdb477b8) at set_var.cc:4618
#3  0x00000000005a416f in set_var::update (this=0xdb477b8, thd=0xdb3cb00)
    at set_var.cc:3810
#4  0x00000000005b027d in sql_set_variables (thd=0xdb3cb00, 
    var_list=<value optimized out>) at set_var.cc:3675
#5  0x000000000059ed45 in mysql_execute_command (thd=0xdb3cb00)
    at sql_parse.cc:3592
#6  0x00000000005a1bc9 in mysql_parse (thd=0xdb3cb00, 
    inBuf=0xdb47680 "set global read_only=1", length=22, 
    found_semicolon=0x4aa4de38) at sql_parse.cc:6148
#7  0x00000000005a25f9 in dispatch_command (command=COM_QUERY, thd=0xdb3cb00, 
    packet=<value optimized out>, packet_length=<value optimized out>)
    at sql_parse.cc:1270
#8  0x00000000005a3290 in do_command (thd=0xdb3cb00) at sql_parse.cc:895
#9  0x0000000000595117 in handle_one_connection (arg=0xdb3cb00)
    at sql_connect.cc:1738
#10 0x000000319da06367 in start_thread () from /lib64/libpthread.so.0

How to repeat:
--source include/have_innodb.inc

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
use test;
create table t1(n int) engine=innodb;
insert into t1 values(10000);
lock tables t1 read;
let $id=`select connection_id()`
send select sleep(n) from t1;
connection  con2;
use test;
set global read_only=1;
set global read_only=0;
eval kill $id;
drop table t1;

Suggested fix:
Have not tried to fix it yet. A workaround is to not run SET GLOBAL READ_ONLY=1 and anything that could lock the tables (e.g. mysqldump). This issue is relevant for MMM role switching. MMM executes SET GLOBAL READ_ONLY=1 when switch roles, and luck would have it that a mysqldump job was active at that time, thus I discovered this bug. Fortunately it was in a testing environment.
[21 Oct 2010 4:02] Valeriy Kravchuk
Verified also with current mysql-.5.5 from bzr on Mac OS X.
[26 Oct 2010 19:59] Konstantin Osipov
Set global read_only involves FLUSH TABLES WITH READ LOCK. 
FLUSH TABLES can not complete until there is an open table. 
Rewriting SET GLOBAL READ ONLY to not involve FLUSH TABLES is R3/E4, and can be done only on 5.5+ codebase.
[6 Mar 2012 1:27] Paul Dubois
Noted in 5.6.5 changelog.

If tables were locked by LOCK TABLES ... READ in another session, SET
GLOBAL read_only = 1 failed to complete.
[3 May 2012 8:15] Dmitry Lenev
Bug #65157 "set read_only=1|0 kills performance" has been marked as duplicate of this one.