MySQL Lock series 3 MDL Lock

In order to protect the data dictionary metadata, MySQL uses the metadata lock, the MDL Lock, to ensure the consistency of the structural changes in the case of concurrency.

The addition of the MDL Lock mode and the source of the organization and the previous blog in the MySQL table lock implementation of the same way, have adopted the "mutex+condition+queue" to achieve concurrency, blocking, wake-up control.

Here's a look at the MDL Lock:

1. Important Data Structures:

1. Mdl_map

Mdl_map uses the hash table, saves all MySQL Mdl_lock, shares globally, uses Mdl_key as key to table, key= "Db_name+table_name" uniquely locates a table.

2. Mdl_context

Mdl_context when MySQL creates the THD for each connection, it initializes an MDL context that holds the MDL information for the current session request.

3. Mdl_lock

Mdl_lock represents a MDL lock on the system, and all MDL requests request the corresponding Mdl_lock, which holds two queue, and one is the request queue that the Grant_queue represents to get lock.

One is that wait_queue represents the blocking queue that requests this mdl_lock.

4. mdl_wait

Mdl_wait packaged a mutex and a condition, providing all the lock-up, wait,notify operation.

5. Mdl_request

At open table, a request is init that contains the requested Enum_mdl_type,enum_mdl_duration,mdl_ticket,mdl_key.

Here's a look at three important enumeration types:

Enum Enum_mdl_namespace {GLOBAL=0,                            SCHEMA,                            TABLE,                            FUNCTION,                            PROCEDURE,                            TRIGGER, EVENT,COMMIT,                            /*This should is the last !*/Namespace_end};

enumenum_mdl_duration {/** Locks With statement duration is automatically released at the end of statement or transaction. */mdl_statement=0,  /** Locks with transaction duration is automatically released at the end of transaction. */Mdl_transaction,/** Locks with explicit duration survive the end of statement and transaction.  They is released explicitly by calling Mdl_context::release_lock (). */Mdl_explicit,/*This should is the last !*/Mdl_duration_end};

enum Enum_mdl_type {  mdl_intention_exclusive0,  mdl_shared,  Mdl_shared_high_prio,  Mdl_shared_read,  Mdl_shared_write,  mdl_shared_no_write,  mdl_shared_no_read_write,  mdl_exclusive,  mdl_type_ END};

First: Enum_mdl_namespace represents the scope of the mdl_request, such as the ALTER TABLE operation, which needs to get the table scope.

Then: Enum_mdl_duration represents the persistent type of mdl_request, such as the ALTER TABLE operation, where the type is mdl_statement, which is the end of the statement, which releases the MDL lock. Another example is the autocommit=0;select operation, the type is mdl_transaction, must be displayed in the commit to release the MDL Lock.

Finally: Enum_mdl_type represents the lock type of the Mdl_request, based on this enumeration type, to determine compatibility and mutual exclusion.

2. Testing

Following a test, look at the lock, release, blocking process, already the main function call stack:

Session1:session2:

Set autocommit=0; ALTER TABLE PP add name varchar (100):

select * from PP;

2.1 During the creation of the connection process, initialize the Mdl_context.

Function call:

Handle_connections_sockets

Mdl_context::init: Each of the connection corresponds to a mdl_context

2.2 Initializing Mdl_request

Function call:

Parse_sql

St_select_lex::add_table_to_list
Mdl_request::init

Description: In the process of Session1, create the Mdl_request:

Mdl_namespace=mdl_key::table,

Db_arg=0x8c7047c8 "Xpchild",

Name_arg=0x8c7047d0 "PP",
Mdl_type_arg=mdl_shared_read,

Mdl_duration_arg=mdl_transaction

2.3 Plus Lock

Acquire_lock:

if (lockthis)) {    lock-m_granted.add_ticket (ticket);    Mysql_prlock_unlock (&lock-m_rwlock);    M_tickets[mdl_request, Duration].push_front (ticket);    Mdl_request->ticket= ticket;  }

Description: First to make compatibility judgment, if compatible, then add ticket to the queue, lock successfully.

function Call stack

Open_and_lock_tables

Open_table

1. Exclusive lock use
Lock_table_names
Mdl_context::acquire_locks
2. Shared Lock usage
Open_table_get_mdl_lock
Mdl_context::try_acquire_lock

2.4 Blocking

Enter Session2 below. Because Session1 got the share read lock of the PP table, the mdl_request type of the Session2 alter operation is: Mdl_intention_exclusive, the compatibility judgment is mutually exclusive, so the DDL is blocked.

 while (!m_wait_status &&!thd_killed (THD) &&         ! = etimedout && Wait_result! = etime) c4/>{    Wait_result= mysql_cond_timedwait (&m_cond_wait_status, &M_lock_wait_status,abs_ timeout);  }

Note: The above code, session2 into the blocking state, waiting for timeout or mdl_wait in the condition variable.

2.5 Wake-up

Session1 commit action, commit.  Then Session1 release Mdl_lock, finally wake up Session2. Session 2 completes the Alte operation.

    Mdl_context::release_lock ();         lock-Remove_ticket ();                Reschedule_waiters ();              while　　　　　　((ticket= it++))  {    　　　　　　if (Can_grant_lock (Ticket->get_type (), ticket->Get_ctx ()))    {      　　　　　　　　 C15>if (! ticket->get_ctx (),m_wait.set_status (mdl_wait::granted))    mdl_wait::set_status ();        Mysql_cond_signal (&m_cond_wait_status);

Description: Commit operation, release the MDL transaction lock held by session 1, then traverse the wait queue, judge the compatibility test, and finally wakeup Session2.

Summary: According to the above tests, we see that the mechanism of MDL and the mechanism of table locking basic consistency, but from the above test and source design, also see the MySQL table lock, MDL Lock where the egg hurts.

3. An egg-sore lock

Below is a brief introduction to the two places where the MySQL lock is an egg sore:

1. Start of the transaction BEGIN TRANSACTION Location

• 　　MySQL Design: When setting the autocommit=0;read_commited, regardless of whether the first statement in the session is select or DML, start a transaction, and then until commit, The MDL Lock held has been maintained until the end of the commit.
• Oracle Design: transaction is created when the first UPDATE statement of the session is initiated, reducing the likelihood of blocking occurring on a system that reads more than one. In particular, when a developer initiates a SELECT statement, it is considered not to be updated and commits no more. But on MySQL, it is very dangerous to launch a SELECT statement and forget commit.

2. DDL Statement Blocking

• 　　MySQL design:When DDL statements are initiated, if an exclusive lock cannot be acquired, then the DDL will go into a blocking state, but because it is a queue design, it blocks all subsequent DML and SELEC operations, which can cause avalanches on high-concurrency systems.
• Oracle Design: before Oracle 11g, DDL statements are fast fail and do not enter a blocking state, so busy tables are frequently encountered with DDL operations when they are ora-00054:resource busy. However, after 11g, although it can be blocked, and provide ddl_time_out parameters such as control, but on the high concurrency of the system, operations are still not used, but fast fail.

Something:

This can be improved by referring to Oracle's design, which is relatively simple to block relative to the source of the DDL statement. The position at which the transaction begins involves the MVCC and transaction isolation levels, and the changes are larger.

Next blog introduces the InnoDB of the lock.