未加星标

MySQL--MDL,元数据锁的粒度

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

/**

Type of metadata lock request.

@sa Comments for MDL_object_lock:: can_grant_lock () and MDL_scoped_lock:: can_grant_lock () for details.

*/

enum enum_mdl_type { // 元数据锁的粒度定义,但属于 mysql Server 层的元数据锁。如下的注释写得很好,仔细阅读理解为好

/* An intention exclusive metadata lock. Used only for scoped locks . // scoped locks, 范围类型的锁,参见“ 9 相容性”小节

Owner of this type of lock can acquire upgradable exclusive locks on individual objects.// 可升级的排它锁,从意向升级到非意向的排它

Compatible with other IX locks, but is incompatible with scoped S and X locks. */

MDL_INTENTION_EXCLUSIVE = 0, // 意向排它锁,缩写为 IX 。与其他的 IX 是兼容的,但是与范围类型的 S 和 X 锁不兼容

// 此锁用于 GLOBAL/COMMIT/TABLESPACE/SCHEMA/EVENT/PROCEDURE/TABLE 这些元数据对象上

/* A shared metadata lock. // 共享锁,元数据共享锁,即表级的共享锁

To be used in cases when we are interested in object metadata only and there is no intention to // 读操作“元数据”获取 S 锁

access object data (e.g. for stored routines or during preparing prepared statements). 例如存 储过程等

We also mis-use this type of lock for open HANDLERs, since lock acquired by this statement has to be compatible with

lock acquired by LOCK TABLES ... WRITE statement, i.e. // “ HANDLER tbl_name OPEN/READ... ”操作不能获得 S 锁

SNRW (We can't get by acquiring S lock at HANDLER ... OPEN time and upgrading it to SR lock for HANDLER ... READ

as it doesn't solve problem with need to abort DML statements which wait on table level lock while having

open HANDLER in the same connection).

To avoid deadlock which may occur when SNRW lock is being upgraded to X lock for table // 升级到 X 锁避免死锁

on which there is an active S lock which is owned by thread which waits in its turn for table-level lock owned by thread

performing upgrade we have to use thr_abort_locks_for_thread () facility in such situation.

This problem does not arise for locks on stored routines as we don't use SNRW locks for them.

It also does not arise when S locks are used during PREPARE calls as table-level locks are not acquired in this case. */

MDL_SHARED , // 共享锁,缩写为 S 。需要对元数据进行读操作

/* A high priority shared metadata lock. // 高优先级的共享锁,高优先级的元数据共享锁,即高优先级的 S 锁

Used for cases when there is no intention to access object data (i.e. data in the table). // 用于不需要意向锁的情况下

" High priority " means that, unlike other shared locks, it is granted ignoring pending requests for exclusive locks.

Intended for use in cases when we only need to access metadata and not data, e.g. when filling antable.

// 如上是在说, 高优先级是在在表级操作但不会影响数据(如修改列的数据类型则会影响数据,修改表名则不会影响数据)

Since SH lock is compatible with SNRW lock, the connection that holds SH lock lock should not try to

acquire any kind of table-level or row-level lock, as this can lead to a deadlock. // 持有 SH 锁则不应该在请求表级或行级锁

Moreover, after acquiring SH lock, the connection should not wait for any other resource,

as it might cause starvation for X locks and a potential deadlock during upgrade of SNW or SNRW to X lock

(e.g. if the upgrading connection holds the resource that is being waited for). */

MDL_SHARED_HIGH_PRIO , // 高优先级的共享锁,缩写为 SH 。用于操作 INFORMATION_SCHEMA 中的表,如执行 SHOW CREATE TABLE t1 或 DESC t1 等

/* A shared metadata lock for cases when there is an intention to read data from table. // 存在读数据意向的锁

A connection holding this kind of lock can read table metadata and read table data // 先在元数据上加此锁

(after acquiring appropriate table and row-level locks). // 之后才会获取表锁和行级锁

This means that one can only acquire TL_READ, TL_READ_NO_INSERT, and similar table-level locks on table

if one holds SR MDL lock on it.

To be used for tables in SELECTs, subqueries, and LOCK TABLE ... READ statements. */

MDL_SHARED_READ , 意向读锁 ,缩写为 SR 。也许会读表的元数据(表级元数据锁)和表的数据(行级锁)

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQL数据TIDUSQL ServerHIGEPA
分页:12
转载请注明
本文标题:MySQL--MDL,元数据锁的粒度
本站链接:http://www.codesec.net/view/533599.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(22)