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执行的调试：

T@8: | query: alter table t1 add column k int  
T@8: | >mysql_parse  
T@8: | | >mysql_execute_command  
T@8: | | | >mysql_alter_table  
T@8: | | | | >open_ltable  
T@8: | | | | | >open_table  
T@8: | | | | | <open_table 
T@8: | | | | | >mysql_lock_tables  
T@8: | | | | | | >get_lock_data  
T@8: | | | | | | | >ha_innobase::store_lock  
T@8: | | | | | | | <ha_innobase::store_lock 
T@8: | | | | | | <get_lock_data 
T@8: | | | | | | >lock_external  
T@8: | | | | | | | >ha_innobase::external_lock  
T@8: | | | | | | | | enter: lock_type: 1  
T@8: | | | | | | | | >trans_register_ha  
T@8: | | | | | | | | | enter: stmt  
T@8: | | | | | | | | <trans_register_ha 
T@8: | | | | | | | <ha_innobase::external_lock 
T@8: | | | | | | <lock_external 
T@8: | | | | | | >thr_multi_lock  
T@8: | | | | | | | >thr_lock  
T@8: | | | | | | | <thr_lock 
T@8: | | | | | | <thr_multi_lock 
T@8: | | | | | <mysql_lock_tables 
T@8: | | | | <open_ltable 
T@8: | | | | >mysql_create_table  
T@8: | | | | <mysql_create_table 
T@8: | | | | >open_temporary_table  
T@8: | | | | | >openfrm  
T@8: | | | | | | >handler::ha_open  
T@8: | | | | | | | enter: name: ./test/#sql-3081_1 db_type: 12 db_stat: 7 mode: 2 lock_test: 2  
T@8: | | | | | | | >ha_innobase::open  
T@8: | | | | | | | <ha_innobase::open 
T@8: | | | | | | <handler::ha_open 
T@8: | | | | | <openfrm 
T@8: | | | | <open_temporary_table 
T@8: | | | | >copy_data_between_tables  
T@8: | | | | <copy_data_between_tables 
T@8: | | | | >closefrm  
T@8: | | | | <closefrm 
T@8: | | | | >close_cached_table  
T@8: | | | | | enter: table: t1  
T@8: | | | | | >wait_while_table_is_used  
T@8: | | | | | | >get_lock_data  
T@8: | | | | | | <get_lock_data 
T@8: | | | | | | >thr_abort_locks  
T@8: | | | | | | <thr_abort_locks 
T@8: | | | | | | >remove_table_from_cache  
T@8: | | | | | | | enter: Table: 'test.t1' flags: 2  
T@8: | | | | | | <remove_table_from_cache 
T@8: | | | | | <wait_while_table_is_used 
T@8: | | | | | >mysql_unlock_tables  
T@8: | | | | | | >thr_multi_unlock  
T@8: | | | | | | | lock: data: 0x8b7f9b0 count: 1  
T@8: | | | | | | | >thr_unlock  
T@8: | | | | | | | <thr_unlock 
T@8: | | | | | | <thr_multi_unlock 
T@8: | | | | | | >unlock_external  
T@8: | | | | | | | >ha_innobase::external_lock  
T@8: | | | | | | | <ha_innobase::external_lock 
T@8: | | | | | | <unlock_external 
T@8: | | | | | <mysql_unlock_tables 
T@8: | | | | | >unlink_open_table  
T@8: | | | | | | >hash_delete  
T@8: | | | | | | | >free_cache_entry  
T@8: | | | | | | | | >closefrm  
T@8: | | | | | | | | | >ha_innobase::close  
T@8: | | | | | | | | | <ha_innobase::close 
T@8: | | | | | | | | <closefrm 
T@8: | | | | | | | <free_cache_entry 
T@8: | | | | | | <hash_delete 
T@8: | | | | | <unlink_open_table 
T@8: | | | | <close_cached_table 
T@8: | | | | >mysql_rename_table  
T@8: | | | | | >ha_innobase::rename_table  
T@8: | | | | | <ha_innobase::rename_table 
T@8: | | | | <mysql_rename_table 
T@8: | | | | >mysql_rename_table  
T@8: | | | | | >ha_innobase::rename_table  
T@8: | | | | | <ha_innobase::rename_table 
T@8: | | | | <mysql_rename_table 
T@8: | | | | >my_delete  
T@8: | | | | | my: name ./test/#sql2-3081-1.frm MyFlags 0  
T@8: | | | | <my_delete 
T@8: | | | | >ha_delete_table  
T@8: | | | | | >ha_innobase::delete_table  
T@8: | | | | | <ha_innobase::delete_table 
T@8: | | | | <ha_delete_table 
T@8: | | | | >ha_commit_trans 
T@8: | | | | <ha_commit_trans 
T@8: | | | | >ha_commit_trans 
T@8: | | | | <ha_commit_trans 
T@8: | | | <mysql_alter_table 
T@8: | | <mysql_execute_command 
T@8: | <mysql_parse 
T@8: <dispatch_command 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.

6.RENAME TABLE例子

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

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

删除name-lock锁

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

T@10: | query: rename table t1 to t2  
T@10: | >mysql_parse  
T@10: | | >mysql_execute_command  
T@10: | | | >mysql_rename_tables  
T@10: | | | | >lock_table_names  
T@10: | | | | | >lock_table_name  
T@10: | | | | | | enter: db: test name: t1  
T@10: | | | | | <lock_table_name 
T@10: | | | | | >remove_table_from_cache  
T@10: | | | | | | enter: Table: 'test.t1' flags: 0  
T@10: | | | | | | >hash_delete  
T@10: | | | | | | | >free_cache_entry  
T@10: | | | | | | | | >closefrm  
T@10: | | | | | | | | | >ha_innobase::close  
T@10: | | | | | | | | | <ha_innobase::close 
T@10: | | | | | | | | <closefrm 
T@10: | | | | | | | <free_cache_entry 
T@10: | | | | | | <hash_delete 
T@10: | | | | | <remove_table_from_cache 
T@10: | | | | | >lock_table_name  
T@10: | | | | | | enter: db: test name: t2  
T@10: | | | | | <lock_table_name 
T@10: | | | | | >remove_table_from_cache  
T@10: | | | | | | enter: Table: 'test.t2' flags: 0  
T@10: | | | | | <remove_table_from_cache 
T@10: | | | | <lock_table_names 
T@10: | | | | >rename_tables  
T@10: | | | | | >do_rename  
T@10: | | | | | | >mysql_rename_table  
T@10: | | | | | | | >ha_innobase::rename_table  
T@10: | | | | | | | <ha_innobase::rename_table 
T@10: | | | | | | | >my_rename  
T@10: | | | | | | | | my: from ./test/t1.frm to ./test/t2.frm MyFlags 16  
T@10: | | | | | | | <my_rename 
T@10: | | | | | | <mysql_rename_table 
T@10: | | | | | <do_rename 
T@10: | | | | <rename_tables 
T@10: | | | | >unlock_table_names  
T@10: | | | | | >unlock_table_name  
T@10: | | | | | | >hash_delete  
T@10: | | | | | | | >free_cache_entry  
T@10: | | | | | | | <free_cache_entry 
T@10: | | | | | | <hash_delete 
T@10: | | | | | <unlock_table_name 
T@10: | | | | | >unlock_table_name  
T@10: | | | | | | >hash_delete  
T@10: | | | | | | | >free_cache_entry  
T@10: | | | | | | | <free_cache_entry 
T@10: | | | | | | <hash_delete 
T@10: | | | | | <unlock_table_name 
T@10: | | | | <unlock_table_names 
T@10: | | | <mysql_rename_tables 
T@10: | | <mysql_execute_command 
T@10: | <mysql_parse 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.

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

【编辑推荐】

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

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