MySQL metadata lock源码分析

By | 2014 年 4 月 9 日

花了些时间理了下MySQL Meatadata Lock(元数据锁,有人将其翻译为数据字典锁)的实现,文章内容比较偏源码层面,在如何使用方面有些欠缺,需要对数据库系统原理或者MySQL实现有一定基础才不会觉得枯燥,如果连S/X/IS/IX及他们之间的关系都不清楚,建议先学习这部分知识!

源码版本:
Oracle MySQL community server 5.6.16

关键数据结构:

MDL_map
管理系统中所有的mdl(MDL_lock),为了减小锁竞争,被划分为mdl_locks_hash_partitions(默认为8)部分,该类全局只有一个对象:mdl_locks

MDL_map_partition
MDL_map中的一个partition,使用hash存储部分mdl(MDL_lock)

MDL_context
管理一个session中的所有mdl(MDL_ticket),包括不同duration类型(MDL_STATEMENT/MDL_TRANSACTION/MDL_EXPLICIT)的已获得的mdl(MDL_ticket)链表和当前等待的mdl(MDL_wait_for_subgraph),是session加/解锁请求的入口,主要接口函数:acquire_lock/release_lock

MDL_key
mdl的key部分,代表一个对象或者一个范围,包含mdl_namespace,db_name,object_name

enum enum_mdl_namespace { GLOBAL=0,
                          SCHEMA,
                          TABLE,
                          FUNCTION,
                          PROCEDURE,
                          TRIGGER,
                          EVENT,
                          COMMIT,
                          /* This should be the last ! */
                          NAMESPACE_END };

对于GLOBAL/COMMIT两种mdl,db_name和object_name为空

MDL_request
一个mdl请求,包括mdl_type,mdl_duration,key(MDL_key类型),以及mdl请求成功后的MDL_ticket

mdl_type:

enum enum_mdl_type {
  /*
    An intention exclusive metadata lock. Used only for scoped locks.
    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,
 
  /*
    A shared metadata lock.
    To be used in cases when we are interested in object metadata only
    and there is no intention to 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. SNRW (We can't get by 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 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,
 
  /*
    A high priority shared metadata lock.
    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 an INFORMATION_SCHEMA table.
    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. 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,
 
  /*
    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,
 
  /*
    A shared metadata lock for cases when there is an intention to modify
    (and not just read) data in the table.
    A connection holding SW lock can read table metadata and modify or read
    table data (after acquiring appropriate table and row-level locks).
    To be used for tables to be modified by INSERT, UPDATE, DELETE
    statements, but not LOCK TABLE ... WRITE or DDL). Also taken by
    SELECT ... FOR UPDATE. */
  MDL_SHARED_WRITE,
 
  /*
    An upgradable shared metadata lock for cases when there is an intention
    to modify (and not just read) data in the table.
    Can be upgraded to MDL_SHARED_NO_WRITE and MDL_EXCLUSIVE.
    A connection holding SU lock can read table metadata and modify or read
    table data (after acquiring appropriate table and row-level locks).
    To be used for the first phase of ALTER TABLE. */
  MDL_SHARED_UPGRADABLE,
 
  /*
    An upgradable shared metadata lock which blocks all attempts to update
    table data, allowing reads.
    A connection holding this kind of lock can read table metadata and read
    table data.
    Can be upgraded to X metadata lock.
    Note, that since this type of lock is not compatible with SNRW or SW
    lock types, acquiring appropriate engine-level locks for reading
    (TL_READ* for MyISAM, shared row locks in InnoDB) should be
    contention-free.
    To be used for the first phase of ALTER TABLE, when copying data between
    tables, to allow concurrent SELECTs from the table, but not UPDATEs. */
  MDL_SHARED_NO_WRITE,
 
  /*
    An upgradable shared metadata lock which allows other connections
    to access table metadata, but not data.
    It blocks all attempts to read or update table data, while allowing
    INFORMATION_SCHEMA and SHOW queries.
    A connection holding this kind of lock can read table metadata modify and
    read table data.
    Can be upgraded to X metadata lock.
    To be used for LOCK TABLES WRITE statement.
    Not compatible with any other lock type except S and SH. */
  MDL_SHARED_NO_READ_WRITE,
 
  /*
    An exclusive metadata lock.
    A connection holding this lock can modify both table's metadata and data.
    No other type of metadata lock can be granted while this lock is held.
    To be used for CREATE/DROP/RENAME TABLE statements and for execution of
    certain phases of other DDL statements. */
  MDL_EXCLUSIVE,
 
  /* This should be the last !!! */
  MDL_TYPE_END};

不同mdl_type之间兼容矩阵将在后面MDL_scoped_lock/MDL_object_lock中交代

mdl_duration:

enum enum_mdl_duration {
  /**
    Locks with statement duration are automatically released at the end
    of statement or transaction. */
  MDL_STATEMENT= 0,
 
  /**
    Locks with transaction duration are automatically released at the end
    of transaction. */
  MDL_TRANSACTION,
 
  /**
    Locks with explicit duration survive the end of statement and transaction.
    They have to be released explicitly by calling MDL_context::release_lock(). */
  MDL_EXPLICIT,
 
  /* This should be the last ! */
  MDL_DURATION_END };

MDL_ticket
单个granted/waiting mdl,最小加锁单位,保存了mdl_type及其从属的MDL_context及MDL_lock,通过其从属的MDL_lock,可以获得对应的mdl_namespace,MDL_ticket继承MDL_wait_for_subgraph类,在死锁检测时被用到

MDL_lock
系统中一个MDL_key上的所有mdl加锁信息,主要包含对应的key及两个list: granted/waiting list(MDL_ticket)

根据mdl_namespace不同,MDL_lock有两个派生类:
GLOBAL/SCHEMA/COMMIT: MDL_scoped_lock
TABLE/FUNCTION/PROCEDUER/TRIGGER/EVENT: MDL_object_lock

MDL_scoped_lock
范围锁,mdl_namespace为GLOBAL/SCHEMA/COMMIT
request与granted兼容矩阵:

             | Type of active   |
     Request |   scoped lock    |
      type   | IS     IX   S  X |
    ---------+------------------+
    IS       |  +      +   +  + |
    IX       |  +      +   -  - |
    S        |  +      -   +  - |
    X        |  +      -   -  - |

request与waiting兼容矩阵:

             |    Pending      |
     Request |  scoped lock    |
      type   | IS     IX  S  X |
    ---------+-----------------+
    IS       |  +      +  +  + |
    IX       |  +      +  -  - |
    S        |  +      +  +  - |
    X        |  +      +  +  + |

MDL_object_lock
对象锁,mdl_namespace为TABLE/FUNCTION/PROCEDUER/TRIGGER/EVENT
request与granted兼容矩阵:(0为不可能情况)

     Request  |  Granted requests for lock       |
      type    | S  SH  SR  SW  SU  SNW  SNRW  X  |
    ----------+----------------------------------+
    S         | +   +   +   +   +   +    +    -  |
    SH        | +   +   +   +   +   +    +    -  |
    SR        | +   +   +   +   +   +    -    -  |
    SW        | +   +   +   +   +   -    -    -  |
    SU        | +   +   +   +   -   -    -    -  |
    SNW       | +   +   +   -   -   -    -    -  |
    SNRW      | +   +   -   -   -   -    -    -  |
    X         | -   -   -   -   -   -    -    -  |
    SU -> X   | -   -   -   -   0   0    0    0  |
    SNW -> X  | -   -   -   0   0   0    0    0  |
    SNRW -> X | -   -   0   0   0   0    0    0  |

request与waiting兼容矩阵:

     Request  |  Pending requests for lock      |
      type    | S  SH  SR  SW  SU  SNW  SNRW  X |
    ----------+---------------------------------+
    S         | +   +   +   +   +   +     +   - |
    SH        | +   +   +   +   +   +     +   + |
    SR        | +   +   +   +   +   +     -   - |
    SW        | +   +   +   +   +   -     -   - |
    SU        | +   +   +   +   +   +     +   - |
    SNW       | +   +   +   +   +   +     +   - |
    SNRW      | +   +   +   +   +   +     +   - |
    X         | +   +   +   +   +   +     +   + |
    SU -> X   | +   +   +   +   +   +     +   + |
    SNW -> X  | +   +   +   +   +   +     +   + |
    SNRW -> X | +   +   +   +   +   +     +   + |

MDL_wait
申请mdl(MDL_request–>MDL_ticket)由于冲突不能满足时,session进入等待状态,在其他session释放相应的mdl时被唤醒,MDL_wait只是封装了用于cond-wait/signal的lock和condition变量及一个status

死锁检测

Deadlock_detection_visitor继承自MDL_wait_for_graph_visitor,被用于wait-for-graph死锁检测,当从MDL_context中申请mdl失败时,MDL_context会等待一个MDL_ticket,通过MDL_ticket可以找到其对应MDL_lock的granted/waiting list,list中保存MDL_ticket对象,可以找到其从属的MDL_context,因此可以找到一个wait-for-graph的子图,沿着起始的MDL_context根据加锁冲突依赖进行深度搜索,如果找到了一条环路或者搜索深度达到MAX_SEARCH_DEPTH(32),就认为发生了死锁,MySQL MDL死锁检测做了一个优化,在进入到wait-for-graph中的一个节点做深度搜索之前,先做一次广度搜索

加锁函数调用:(select语句)

mysql_execute_command --> execute_sqlcom_select --> open_normal_and_derived_tables
--> open_tables --> open_and_process_table --> open_table--> MDL_context::acquire_lock

MDL_context::acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout)
1. 检查MDL_context之前是否已经获得过满足要求的mdl(存在已granted锁且类型强于申请锁类型)
2. 如果没有找到或者类型不满足,根据hash算法在对应MDL_map_partition中找到MDL_lock(不存在则插入,MDL_map::find_or_insert)
3. 根据mdl_request生成ticket(MDL_ticket),通过兼容矩阵计算加锁能否满足,如果满足,将ticket其加入到MDL_lock中的granted list中,函数成功返回
3. 加锁请求无法立即满足,将ticket加入到MDL_lock中的waiting list,并设置MDL_context当前等待mdl(MDL_wait_for_subgraph)为ticket
4. 进行死锁检测,如果发现死锁并且为victim,加锁失败,函数返回
5. 等待其它session调用MDL_context::release_lock唤醒
6. 锁等待被唤醒/超时后,检查状态,如果不为granted则报错,否则,函数加锁成功返回

解锁函数调用:(select语句)

mysql_execute_command --> MDL_context::release_transactional_locks 
--> MDL_context::release_locks_stored_before --> MDL_context::release_lock

MDL_context::release_lock(enum_mdl_duration duration, MDL_ticket *ticket)
1. 通过ticket找到对应的MDL_lock,将ticket从MDL_lock中的granted list中移除
2. 判断移除ticket后,如果MDL_lock中granted/waiting list同时为空,将MDL_lock从全局mdl_lock中移除,函数返回
3. 如果非空,遍历MDL_lock中waiting list,通过兼容矩阵计算加锁请求是否可以grant,如果可以,将其从waiting list中移除,同时加入到granted list,并唤醒对应的session

关于MDL_key::GLOBAL和MDL_key::COMMIT

这两种namespace的mdl为scope lock,其中MDL_key::GLOBAL类型mdl在dml/ddl(mdl_type: MDL_INTENTION_EXCLUSIVE)/set read_only/flush tables with read lock(mdl_type: MDL_SHARED)时会申请,MDL_key::COMMIT类型mdl在事务提交(mdl_type: MDL_INTENTION_EXCLUSIVE)以及set read_only/flush tables with read lock(mdl_type: MDL_SHARED)时申请,本质上set read_only/flush tables with read lock做的事情差不多,由于这两种mdl的对应的MDL_key是常量,因此在实现时是独立出来的(没有保存在MDL_map_partition中):

class MDL_map
{
public:
  void init();
  void destroy();
  MDL_lock *find_or_insert(const MDL_key *key);
  void remove(MDL_lock *lock);
private:
  /** Array of partitions where the locks are actually stored. */
  Dynamic_array<MDL_map_partition *> m_partitions;
  /** Pre-allocated MDL_lock object for GLOBAL namespace. */
  MDL_lock *m_global_lock;
  /** Pre-allocated MDL_lock object for COMMIT namespace. */
  MDL_lock *m_commit_lock;
};

发表评论

电子邮件地址不会被公开。 必填项已用*标注