? ? ? ?最近了解mysql MDL的设计。发现mysql "set global read_only=on/off"操作也依赖metadata lock(5.6.16, 推测是从5.5引入metadata lock后就这样了，没有查看更早版本的代码确认)。下面是set global read_only=on/off的实现。
? ? ? 调用路径如下：?
#0 fix_read_only (self=0x134f5a0, thd=0x2470e0a0, type=OPT_GLOBAL)
at /home/mysql-5.6.16/sql/sys_vars.cc:2219
#1 0x000000000064ab91 in sys_var::update (this=0x134f5a0, thd=0x2470e0a0, var=0x24691df0)
at /home/mysql-5.6.16/sql/set_var.cc:194
#2 0x000000000064afad in set_var::update (this=0x24691df0, thd=0x2470e0a0)
at /home/mysql-5.6.16/sql/set_var.cc:670
#3 0x000000000064a6c9 in sql_set_variables (thd=0x2470e0a0, var_list=<value optimized out>)
at /home/mysql-5.6.16/sql/set_var.cc:573
#4 0x00000000006d779b in mysql_execute_command (thd=0x2470e0a0)
at /home/mysql-5.6.16/sql/sql_parse.cc:3704
#5 0x00000000006dcb9b in mysql_parse (thd=0x2470e0a0, rawbuf=0x24691c90 "set global read_only=on",
length=0, parser_state=<value optimized out>) at /home/mysql-5.6.16/sql/sql_parse.cc:6235
#6 0x00000000006de6b0 in dispatch_command (command=COM_QUERY, thd=0x2470e0a0,
packet=0x246c7f61 "set global read_only=on", packet_length=23)
at /home/mysql-5.6.16/sql/sql_parse.cc:1334
#7 0x00000000006df977 in do_command (thd=0x2470e0a0)
at /home/mysql-5.6.16/sql/sql_parse.cc:1036
#8 0x00000000006a1f95 in do_handle_one_connection (thd_arg=0x2470e0a0)
at /home/mysql-5.6.16/sql/sql_connect.cc:982
? ? ? ?主要的实现都在fix_read_only函数内。主要分为2步：第一步初始化GRL 锁需要的第一个metadata lock -- 一个GLOBAL级别的显式的MDL_SHARED类型的metadata lock，第一步完成之后GRL的状态是GRL_ACQUIRED，并且这一步之后其他连接是无法拿到写锁的（新进来的写请求会被拒绝）；第二步获取一个COMMIT级别的显式的MDL_SHARED类型的metadata lock，并将GRL的状态修改为GRL_ACQUIRED_AND_BLOCKS_COMMIT，这一步之后所有的事务都不再允许在提交了(所以执行set global read_only操作时如果有大事务正在执行，set global read_only操作是会被卡住的)。?
fix_read_only(),sys_vars.cc:2219
--my_bool new_read_only= read_only;
--read_only= opt_readonly;
...
--lock_global_read_lock(),lock.cc:966 (enum_grl_state m_state = Global_read_lock::GRL_ACQUIRED_AND_BLOCKS_COMMIT -> m_state = GRL_ACQUIRED)
--mdl_request.init(MDL_key::GLOBAL, "", "", MDL_SHARED, MDL_EXPLICIT);# 初始化一个namespace=GLOBAL的显式的MDL_SHARED的metadata lock, dbname 和 name为“”
--thd->mdl_context.acquire_lock(&mdl_request,thd->variables.lock_wait_timeout) # 获取刚刚初始化过的metadata lock
--make_global_read_lock_block_commit(),lock.cc:1041 (m_state = GRL_ACQUIRED -> m_state = GRL_ACQUIRED_AND_BLOCKS_COMMIT)
--mdl_request.init(MDL_key::COMMIT, "", "", MDL_SHARED, MDL_EXPLICIT);
--thd->mdl_context.acquire_lock(&mdl_request,thd->variables.lock_wait_timeout))
... --opt_readonly= new_read_only;
--read_only= opt_readonly;
? ? ? 细看这段逻辑，其实 set global read_only=on/off最主要的操作还是要把全局变量opt_readonly设置为on/off。之所以有上面这段fix_read_only的逻辑，我觉得是要考虑如何处理处于提交阶段的事务：第一步可以阻止新的写请求，第二步阻止事务提交。
? ? ? Global_read_lock也很容易理解：
Global_read_lock主要有下面几个函数：
lock_global_read_lock()
unlock_global_read_lock()
make_global_read_lock_block_commit()
Global_read_lock主要有下面几个变量：
/**
In order to acquire the global read lock, the connection must
acquire shared metadata lock in GLOBAL namespace, to prohibit
all DDL.
*/
MDL_ticket *m_mdl_global_shared_lock;
/**
Also in order to acquire the global read lock, the connection
must acquire a shared metadata lock in COMMIT namespace, to
prohibit commits.
*/
MDL_ticket *m_mdl_blocks_commits_lock;
enum_grl_state m_state;
?? ? ? 前面说了set global read_only=on/off最重要的还是设置全局变量opt_readonly。那么只读是如何生效的呢？mysql在执行写操作前会去判断read_only状态(变量opt_readponly)，如果read_only=on && 当前用户没有超级权限，就会报1290错误(ER_OPTION_PREVENTS_STATEMENT)--“ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement”
bool trans_begin(THD *thd, uint flags) //transaction.cc
{
...
if (opt_readonly && !user_is_super)
{
my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only");
DBUG_RETURN(true);
}
...
?
?PS:
set global read_only=on/off是DBA经常用的一个操作：进行主备切换的时候，一般都会先对主库进行只读操作，只读操作(on)，然后主备同步完成后，再把备库置为可读写(off)。这样可以避免切换的过程中双写引起脏数据。
?mysqld.cc中同时定义了2个变量：my_bool read_only= 0, opt_readonly= 0;?opt_readonly是当前系统的read_only状态,read_only是要把read_only设置成的值。
原文：http://guduwhuzhe.iteye.com/blog/2162529