MySQL锁机制 你所不了解的一些事儿_MySQL

1.MySQL中并发和隔离控制机制

Meta-data元数据锁：在table cache缓存里实现的，为DDL（Data Definition Language）提供隔离操作。一种特别的meta-data元数据类型，叫Name Lock。（SQL层）

表级table-level数据锁（SQL层）

存储引擎特有机制 -- row locks行锁，page locks页锁，table locks表级，versioning版本（在引擎中实现）

全局读锁 -- FLUSH TABLES WITH READ LOCK（SQL层）

2.在语句执行中表的生命周期

DML（Data Manipulation Language）例子：

计算语句使用到的所有表

在每个表：打开open表 -- 从table cache缓存里得到TABLE对象，并在此表加上meta-data元数据锁

等待全局读锁后改变数据

在每个表：锁lock表 -- 在表加上table-level数据锁

执行语句：调用：handler::write_row()/read_rnd()/read_index()，等；隐式地调用引擎级engine-level锁机制

在每个表：释放表的数据锁

在每个表：释放表的DDL锁并把表放回table cache缓存里

DDL语句也是一样，没有典型的执行计划。

 3.获取meta-data元数据锁

meta-data元数据锁的实现作为TABLE对象的一个属性，TABLE对象代表了table cache缓存。

meta-data元数据锁为如下任何一种：shared共享锁 -- 隐式地加锁，只通过标记TABLE对象“被使用”；semi-exclusive半独享锁，也叫Name Lock，RENAME操作会在源表和目标加上此锁；exclusive独享，也叫exclusive name lock，CREATE TABLE ... SELECT操作会在目标表上加上此锁，如果没有的话。

 4.表高速缓存（table cache）

是一个HASH变量，叫open_cache

TABLE对象是HASH元素

以HASH的操作被LOCK_open mutex互斥量保护

内部结构（The table cache: internal structure）

在缓存里，每个物理表可能被多个TABLE实例表示

相同表的所有TABLE实例，通过相连的列（a linked list）连接着

每个TABLE实例有一个table cache缓存版本的复制 -- TABLE实例保存的版本不会和当前table cache缓存版本一致，而是保存旧的和从缓存删除的

被某些语句使用的TABLE实例被会标记为对其它的语句来说是无效的 -- 这就是meta-data元数据锁的本质

在缓存中的TABLE实例通常地有一个有效的句柄实例连接着它

内部运算（The table cache: operations）

主要的代码在：sql/sql_base.cc，sql/lock.cc，sql/table.h，sql/sql_table.cc

主要的方法：open_table()，close_thread_tables()，close_cached_table()，lock_table_names()

事实上，一个概念/对象组合不仅用于缓存或锁定：LOCK_open mutex互斥量也用到其它的操作，如：使磁盘上和处理中的表创建的原子性

典型的操作，来自隔离等级Pov的重要（注：isolation PoV没研究出是什么意思）：语句查询时，打开和关闭表 -- shared共享锁；强制和等待直到表的所有实例被关闭 --  exclusive独享（但不完全）；Name Lock -- 特殊地情况，当手上没有TABLE实例，只能使用一个特殊的占位符（甚至表可能不存在）。

锁多表（The table cache: locking multiple tables）

使用一种尝试和回退（try and back-off）的技术来避免死锁（乐观锁）

为了DDL操作的一套诀窍，如使锁升级或者防止DDL失效

LOCK_open问题

Lock_open互斥量：

保护table cache缓存内的结构

分组存储引擎内的表和对象的.frm文件的创建，也为RENAME操作提供原子性操作

在每个语句访问表时会使用它两次：在open_tables()和close_thread_tables()

在使用DDL操作时，磁盘读写和甚至同步（sync）都会使用它

5.ALTER TABLE例子

ALTER TABLE执行的简化计划：

以TL_WRITE_ALLOW_READ的打开和加锁表

创建一个以临时名字的被ALTER的复制表

强制并等待直到表的所有实例都关闭（锁升级）

交换新和旧的版本

删除旧的版本

这是一个常规的情况，还有一些被优化的情况。

ALTER TABLE执行的调试：

<ol class="dp-xml">
<li class="alt"><span><span>T@8: | query: alter table t1 add column k int  </span></span></li>
<li>
<span>T@8: | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_parse  </span>
</li>
<li class="alt">
<span>T@8: | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_execute_command  </span>
</li>
<li>
<span>T@8: | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_alter_table  </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>open_ltable  </span>
</li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>open_table  </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">open_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_lock_tables  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>get_lock_data  </span>
</li>
<li>
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::store_lock  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::store_lock</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">get_lock_data</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>lock_external  </span>
</li>
<li>
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::external_lock  </span>
</li>
<li class="alt"><span>T@8: | | | | | | | | enter: lock_type: 1  </span></li>
<li>
<span>T@8: | | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>trans_register_ha  </span>
</li>
<li class="alt"><span>T@8: | | | | | | | | | enter: stmt  </span></li>
<li>
<span>T@8: | | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">trans_register_ha</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::external_lock</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">lock_external</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>thr_multi_lock  </span>
</li>
<li>
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>thr_lock  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">thr_lock</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">thr_multi_lock</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_lock_tables</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">open_ltable</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_create_table  </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_create_table</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>open_temporary_table  </span>
</li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>openfrm  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>handler::ha_open  </span>
</li>
<li><span>T@8: | | | | | | | enter: name: ./test/#sql-3081_1 db_type: 12 db_stat: 7 mode: 2 lock_test: 2  </span></li>
<li class="alt">
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::open  </span>
</li>
<li>
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::open</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">handler::ha_open</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">openfrm</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">open_temporary_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>copy_data_between_tables  </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">copy_data_between_tables</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>closefrm  </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">closefrm</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>close_cached_table  </span>
</li>
<li class="alt"><span>T@8: | | | | | enter: table: t1  </span></li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>wait_while_table_is_used  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>get_lock_data  </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">get_lock_data</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>thr_abort_locks  </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">thr_abort_locks</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>remove_table_from_cache  </span>
</li>
<li><span>T@8: | | | | | | | enter: Table: 'test.t1' flags: 2  </span></li>
<li class="alt">
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">remove_table_from_cache</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">wait_while_table_is_used</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_unlock_tables  </span>
</li>
<li>
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>thr_multi_unlock  </span>
</li>
<li class="alt"><span>T@8: | | | | | | | lock: data: 0x8b7f9b0 count: 1  </span></li>
<li>
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>thr_unlock  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">thr_unlock</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">thr_multi_unlock</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>unlock_external  </span>
</li>
<li>
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::external_lock  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::external_lock</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">unlock_external</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_unlock_tables</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>unlink_open_table  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>hash_delete  </span>
</li>
<li>
<span>T@8: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>free_cache_entry  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>closefrm  </span>
</li>
<li>
<span>T@8: | | | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::close  </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::close</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">closefrm</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">free_cache_entry</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">hash_delete</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">unlink_open_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">close_cached_table</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_rename_table  </span>
</li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::rename_table  </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::rename_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_rename_table</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_rename_table  </span>
</li>
<li>
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::rename_table  </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::rename_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_rename_table</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>my_delete  </span>
</li>
<li><span>T@8: | | | | | my: name ./test/#sql2-3081-1.frm MyFlags 0  </span></li>
<li class="alt">
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">my_delete</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_delete_table  </span>
</li>
<li class="alt">
<span>T@8: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::delete_table  </span>
</li>
<li>
<span>T@8: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::delete_table</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_delete_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_commit_trans </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_commit_trans</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_commit_trans </span>
</li>
<li>
<span>T@8: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_commit_trans</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_alter_table</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_execute_command</span></span></font></strong><span> </span>
</li>
<li>
<span>T@8: | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_parse</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@8: </span><strong><font color="#006699"><span class="tag"><span class="tag-name">dispatch_command</span></span></font></strong><span> </span>
</li>
</ol>

 6.RENAME TABLE例子

得到源表和目的表的name-lock锁：在table cache缓存内插入特殊的TABLE实例的占位符并等待直到这些表的所有实例都关闭

重命名这些表的.frm文件和调用handler::rename_table()方法

删除name-lock锁

在整个解析过程中，都使用LOCK_open

<ol class="dp-xml">
<li class="alt"><span><span>T@10: | query: rename table t1 to t2  </span></span></li>
<li>
<span>T@10: | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_parse  </span>
</li>
<li class="alt">
<span>T@10: | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_execute_command  </span>
</li>
<li>
<span>T@10: | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_rename_tables  </span>
</li>
<li class="alt">
<span>T@10: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>lock_table_names  </span>
</li>
<li>
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>lock_table_name  </span>
</li>
<li class="alt"><span>T@10: | | | | | | enter: db: test name: t1  </span></li>
<li>
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">lock_table_name</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>remove_table_from_cache  </span>
</li>
<li><span>T@10: | | | | | | enter: Table: 'test.t1' flags: 0  </span></li>
<li class="alt">
<span>T@10: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>hash_delete  </span>
</li>
<li>
<span>T@10: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>free_cache_entry  </span>
</li>
<li class="alt">
<span>T@10: | | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>closefrm  </span>
</li>
<li>
<span>T@10: | | | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::close  </span>
</li>
<li class="alt">
<span>T@10: | | | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::close</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">closefrm</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">free_cache_entry</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">hash_delete</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">remove_table_from_cache</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>lock_table_name  </span>
</li>
<li class="alt"><span>T@10: | | | | | | enter: db: test name: t2  </span></li>
<li>
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">lock_table_name</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>remove_table_from_cache  </span>
</li>
<li><span>T@10: | | | | | | enter: Table: 'test.t2' flags: 0  </span></li>
<li class="alt">
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">remove_table_from_cache</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">lock_table_names</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>rename_tables  </span>
</li>
<li>
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>do_rename  </span>
</li>
<li class="alt">
<span>T@10: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>mysql_rename_table  </span>
</li>
<li>
<span>T@10: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>ha_innobase::rename_table  </span>
</li>
<li class="alt">
<span>T@10: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">ha_innobase::rename_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>my_rename  </span>
</li>
<li class="alt"><span>T@10: | | | | | | | | my: from ./test/t1.frm to ./test/t2.frm MyFlags 16  </span></li>
<li>
<span>T@10: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">my_rename</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_rename_table</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">do_rename</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">rename_tables</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>unlock_table_names  </span>
</li>
<li class="alt">
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>unlock_table_name  </span>
</li>
<li>
<span>T@10: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>hash_delete  </span>
</li>
<li class="alt">
<span>T@10: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>free_cache_entry  </span>
</li>
<li>
<span>T@10: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">free_cache_entry</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">hash_delete</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">unlock_table_name</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>unlock_table_name  </span>
</li>
<li>
<span>T@10: | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>hash_delete  </span>
</li>
<li class="alt">
<span>T@10: | | | | | | | </span><span class="tag"><strong><font color="#006699">></font></strong></span><span>free_cache_entry  </span>
</li>
<li>
<span>T@10: | | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">free_cache_entry</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">hash_delete</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">unlock_table_name</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">unlock_table_names</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_rename_tables</span></span></font></strong><span> </span>
</li>
<li class="alt">
<span>T@10: | | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_execute_command</span></span></font></strong><span> </span>
</li>
<li>
<span>T@10: | </span><strong><font color="#006699"><span class="tag"><span class="tag-name">mysql_parse</span></span></font></strong><span> </span>
</li>
</ol>

7.表级table-level锁

主要源代码见：sql/lock.cc，mysys/thr_lock.cc。mysql_lock/unlock_tables()（SQL层操作）和thr_multi_lock()/thr_lock()（锁兼容逻辑lock-compatibility logic）

表是以打开着被加锁的。被加锁的对象被句柄关联着；存储引擎会调整锁的类型。如innodb/bdb，事实上大量的对象被加锁的，如merge/partition，见handler::store_lock()方法。

使用锁等级避免死锁。所有表一次性加锁；如果存储引擎调整锁造成死锁，由存储引擎负责

在一些情况下，表会更早地被解锁

8 .预加锁（pre-locking）

历史上避免死锁方案用于表级table-level数据锁，是要求一次性加锁一个语句内的所有表

因此，对语句使用的函数/触发，我们不得不打开所有直接地或间接地用到的表，且对它们加锁。为这个，我们建立一个被使用表的可传送闭包

为了有效实现，我们混合层次和访问（layers and access）成某些解析/语句上下文（parser/statement context），这些上下文来自主要处理表的模板

9.全局读锁（global read lock）

实现为FLUSH TABLES WITH READ LOCK，用来备份

从执行上防止DDL和DML

劝告：每个DDL/DML语句检查是否有一个正挂着的全局读锁和停止是否有任何一个。通过直接调用wait_if_global_read_lock()（在这个情况我们会设置来自全局读锁的保护，且只有调用start_waiting_global_read_lock()来消除这个保护，通常在这情况下没有打开的表）；或者通过mysql_lock_tables()（在后一种情况下，我们还重新打开表）

线程操作FLUSH TABLES WITH READ LOCK来设置一个全局读锁的标识，初始一个FLUSH TABLES语句。然后等待直到所有的表都清空（flush）

原文标题：MySQL 锁机制概述

链接：http://www.cnblogs.com/popgo/archive/2010/07/26/1778803.html