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

数据库 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执行的调试:

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

 6.RENAME TABLE例子

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

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

删除name-lock锁

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

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

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

【编辑推荐】

  1. MySQL数据库锁机制的原理浅析
  2. MySQL锁定机制的原理
  3. 使用调度和锁定进行MySQL查询优化
  4. MySQL数据库表里如何进行锁定?
  5. MySQL表级锁,行级锁,页级锁各显神通
责任编辑:彭凡 来源: 博客园
相关推荐

2019-11-21 15:08:13

DevOps云计算管理

2017-04-11 09:29:45

WOT

2013-11-11 10:07:43

静态路由配置

2018-07-16 09:00:32

LinuxBash数组

2017-03-13 17:25:00

移动支付技术支撑易宝

2012-03-13 09:32:15

C#协变

2011-03-29 15:44:41

对日软件外包

2021-07-12 07:01:39

AST前端abstract sy

2010-08-19 10:12:34

路由器标准

2019-04-03 09:10:35

Rediskey-value数据库

2015-06-05 09:52:41

公有云风险成本

2014-07-29 16:21:57

Git

2017-12-26 11:37:32

云原生CNCF容器

2021-01-14 08:31:54

Web开发应用程序

2012-02-21 09:20:50

Hadoop大数据

2020-12-10 08:13:15

ARM架构 嵌入式

2021-03-09 10:05:06

5G运营商技术

2014-05-06 10:31:21

KillallLinux命令行

2019-05-14 14:51:40

Java语法糖用法

2023-02-16 07:38:38

非机械键电路薄膜,
点赞
收藏

51CTO技术栈公众号