MYSQL METADATA LOCK (MDL LOCK) MDL lock problem analysis

MYSQL METADATA LOCK (MDL LOCK) MDL lock problem analysis

1. Introduction

MDL lock in MYSQL has always been a headache. When we talk about locks, we usually prefer gap lock, next key lock, row lock, etc. at the lower level of INNODB, because they are easy to understand and observe. However, we know very little about MDL LOCK, because it is really hard to observe. We can only see it by checking show processlist when there is a problem.

The so-called Waiting for table metadata lock and other states are simple. In fact, MDL LOCK is a very complex subsystem in the upper layer of MYSQL and has its own deadlock detection mechanism.

(Undirected graph?) And what people usually say is whether the table is locked is actually referring to this, which shows its criticality and seriousness. The author also learned some (the tip of the iceberg) according to his own needs, and he does not have the ability to read all the code, but the author adds a TICKET printing function to print out the MDL LOCK locking process of the statement for easy study and research. Let's start with some basics and then tell you what has been modified. Finally, each MDL TYPE is tested and analyzed. If you are not interested in the basic concepts and adding printing functions, you can directly refer to the fifth part of the lock test, but if you don't understand the basics, it may seem a bit difficult.

I just encountered a deadlock in MDL LOCK recently. I will give a case in the next article. Here I will only look at the theory.

----At the MYSQL SERVER level, in fact, MDL LOCK is acquired as early as in the open_table function. It can be said that it is the earliest LOCK structure acquired

----Earliest acquisition stage: THD::enter_stage: 'Opening tables'

Call stack frame

#0 open_table_get_mdl_lock (thd=0x7fffd0000df0, ot_ctx=0x7fffec06fb00, 
  table_list=0x7fffd00067d8, flags=0, mdl_ticket=0x7fffec06f950)
  at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:2789
#1 0x0000000001516e17 in open_table (thd=0x7fffd0000df0, 
  table_list=0x7fffd00067d8, ot_ctx=0x7fffec06fb00)
  at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3237

----Deadlock detection error code:

{ "ER_LOCK_DEADLOCK", 1213, "Deadlock found when trying to get lock; try restarting transaction" },
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The deadlock error of MDL LOCK is exactly the same as that of INNODB deadlock, except that SHOW ENGINE INNODB does not have deadlock information.

----Involved code: mdl.h mdl.cc

2. Basic important data structures (classes) and concepts

1. MDL TYPE

MDL_INTENTION_EXCLUSIVE(IX)

MDL_SHARED(S)

MDL_SHARED_HIGH_PRIO(SH)

MDL_SHARED_READ(SR)

MDL_SHARED_WRITE(SW)

MDL_SHARED_WRITE_LOW_PRIO(SWL)

MDL_SHARED_UPGRADABLE (SU)

MDL_SHARED_READ_ONLY (SRO)

MDL_SHARED_NO_WRITE(SNW)

MDL_SHARED_NO_READ_WRITE(SNRW)

MDL_EXCLUSIVE(X)

Each TYPE will be tested in detail later, and the explanation in the source code will be given at the end.

2. MDL NAMESPACE

In MDL, MDL_KEY is represented by NAMESPACE+DB+OBJECT_NAME. The so-called namespace is not important.

Below are the categories of NAMESPACE

- GLOBAL is used for the global read lock.

- TABLESPACE is for tablespaces.

- SCHEMA is for schemas (aka databases).

- TABLE is for tables and views.

- FUNCTION is for stored functions.

- PROCEDURE is for stored procedures.

- TRIGGER is for triggers.

- EVENT is for event scheduler events.

- COMMIT is for enabling the global read lock to block commits.

- USER_LEVEL_LOCK is for user-level locks.

- LOCKING_SERVICE is for the name plugin RW-lock service

3. Implement classification

scope lock: generally corresponds to the global MDL LOCK, such as flush table with read lock is namespace space: GLOBAL type: S

Object lock: As its name suggests, object-level MDL LOCK, such as TABLE

Here are the comments in the source code:

 /**
  Helper struct which defines how different types of locks are handled
  for a specific MDL_lock. In practice we use only two strategies: "scoped"
  lock strategy for locks in GLOBAL, COMMIT, TABLESPACE and SCHEMA namespaces
  and "object" lock strategy for all other namespaces.
 */

4. MDL Compatibility Matrix

scope lock:
     | Type of active |
 Request | scoped lock |
  type | IS(*) IX SX |
 ---------+------------------+
 IS | + + + + |
 IX | + + - - |
 S | + - + - |
 X | + - - - |

object lock:
    Request | Granted requests for lock |
    type | S SH SR SW SWLP SU SRO SNW SNRW X |
   ----------+---------------------------------------------+
   S | + + + + + + + + + - |
   SH | + + + + + + + + + - |
   SR | + + + + + + + + - - |
   SW | + + + + + + - - - - |
   SWLP | + + + + + + - - - - |
   SU | + + + + + - + - - - |
   SRO | + + + - - + + + - - |
   SNW | + + + - - - + - - - |
   SNRW | + + - - - - - - - - |
   X | - - - - - - - - - - - |

5. MDL duration and when does MDL last

This does not need too much explanation, just look at the source code comments

MDL_STATEMENT: Locks with statement duration are automatically released at the end
       of statement or transaction.
MDL_TRANSACTION: Locks with transaction duration are automatically released at the end
        of transaction
MDL_EXPLICIT: Locks with explicit duration survive the end of statement and transaction.
       They have to be released explicitly by calling MDL_context::release_lock().

6. MDL LOCK FAST PATH (unobtrusive) OR SLOW PATH (obtrusive)

The purpose of using two different methods is to optimize the implementation of MDL lock. The following are the comments in the source code.

A) "unobtrusive" lock types
      1) Each type from this set should be compatible with all other
        types from the set (including itself).
      2) These types should be common for DML operations
     Our goal is to optimize acquisition and release of locks of this
     type by avoiding complex checks and manipulations on m_waiting/
     m_granted bitmaps/lists. We replace them with a check of and
     increment/decrement of integer counters.
     We call the latter type of acquisition/release "fast path".
     Use of "fast path" reduces the size of critical section associated
     with MDL_lock::m_rwlock lock in the common case and thus increases
     scalability.
     The amount by which acquisition/release of specific type
     "unobtrusive" lock increases/decreases packed counter in
     MDL_lock::m_fast_path_state is returned by this function.
B) "obtrusive" lock types
      1) Granted or pending lock of those types is incompatible with
        some other types of locks or with itself.
      2) Not common for DML operations
     These locks have to be always acquired involving manipulations on
     m_waiting/m_granted bitmaps/lists, ie we have to use "slow path"
     for them. Moreover in the presence of active/pending locks from
     "obtrusive" set we have to acquire using "slow path" even locks of
     "unobtrusive" type.

7. MDL_request class

That is, the MDL LOCK requirement needs to be obtained after statement parsing, and then the MDL LOCK application is performed in the MDL subsystem through this class object, which probably includes the following properties

/** Type of metadata lock. */
 enum enum_mdl_type type; //Required type/** Duration for requested lock. */
 enum enum_mdl_duration duration; //Duration time/**
  Pointers for participating in the list of lock requests for this context.
 */
 MDL_request *next_in_list; //Doubly linked list implementation MDL_request **prev_in_list;
 /**
  Pointer to the lock ticket object for this lock request.
  Valid only if this lock request is satisfied.
 */
 MDL_ticket *ticket; //Note that if the application is successful (no waiting), it will point to an actual TICKET, otherwise it will be NULL
 /** A lock is requested based on a fully qualified name and type. */
 MDL_key key; //Note that this is a MDL_KEY type, the main thing is the NAMESPACE+DB+OBJECT_NAME mentioned above

The MDL_key class is the actual NAMESPACE+DB+OBJECT_NAME, which is put into a char array. It will appear in MDL_LOCK and MDL_REQUEST.

private:

uint16 m_length;

uint16 m_db_name_length;

char m_ptr[MAX_MDLKEY_LENGTH]; //Put it here

8. MDL_ticket

Just like a ticket, if you acquire MDL LOCK, you must return a ticket to MDL_request. If you wait, you will not source MDL_context::acquire_lock

It can be observed. Of course, this is also the category I mainly observe.

/**
  Pointers for participating in the list of lock requests for this context.
  Context private. As explained here is the formation of the linked list in the context, which is thread private*/
 MDL_ticket *next_in_context;
 MDL_ticket **prev_in_context;
 /**
  Pointers for participating in the list of satisfied/pending requests
  for the lock. Externally accessible. As explained here is the formation of the linked list in MDL_LOCK, which is global */
 MDL_ticket *next_in_lock;
 MDL_ticket **prev_in_lock;
/**
  Context of the owner of the metadata lock ticket. Externally accessible.
  Obviously, this points to the owner of the ticket, which is MDL_context, which is a thread attribute*/
 MDL_context *m_ctx; 
 /**
  Pointer to the lock object for this lock ticket. Externally accessible.
  Obviously this is a pointer to MDL_LOCK*/
 MDL_lock *m_lock;
 /**
  Indicates that ticket corresponds to lock acquired using "fast path"
  algorithm. Particularly this means that it was not included into
  MDL_lock::m_granted bitmap/list and instead is accounted for by
  MDL_lock::m_fast_path_locks_granted_counter
  This indicates whether it is FAST PATH. From the comments, the fast path method does not occupy the granted bitmap and linked list in MDL LOCK. Instead, a counter m_fast_path_locks_granted_counter is used.
  This way the cost will definitely be less*/
 bool m_is_fast_path;
 /**
  Indicates that ticket corresponds to lock request which required
  storage engine notification during its acquisition and requires
  storage engine notification after its release.
 */
 bool m_hton_notified;

9. MDL_lock

Each MDL_key corresponds to an MDL_lock, which contains the so-called GRANTED linked list and WAIT linked list. Considering its complexity, you can directly refer to the source code comments, which are also very detailed. Here are the several properties I described.

/** The key of the object (data) being protected. */

MDL_key key;

/** List of granted tickets for this lock. */

Ticket_list m_granted;

/** Tickets for contexts waiting to acquire a lock. */

Ticket_list m_waiting;

10. MDL_context

This is a so-called context structure for the entire MYSQL thread to interact with the MDL lock subsystem. It contains many methods and properties. The properties I am more concerned about are as follows:

/**
  If our request for a lock is scheduled, or aborted by the deadlock
  detector, the result is recorded in this class.
 */
 MDL_wait m_wait;
/**
  Lists of all MDL tickets acquired by this connection.
  This is a linked list array of different MDL lock durations. In fact, it is a linked list of MDL_STATEMENT, a linked list of MDL_TRANSACTION, and a linked list of MDL_EXPLICIT*/
Ticket_list m_tickets[MDL_DURATION_END];
//This is a parent class pointer pointing to a child class object, a typical example of virtual function overriding. In fact, it points to a thread/*
class THD :public MDL_context_owner,
      public Query_arena,
      public Open_tables_state
*/
MDL_context_owner *m_owner;

11. MDL_wait

This class mainly deals with the current ticket acquisition status

enum_wait_status m_wait_status;

Include

EMPTY initialization

GRANTED Get success

VICTIM Deadlock

TIMEOUT

KILLED KILLED

12. Waiting for the mark

PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
{
 {0, "Waiting for global read lock", 0},
 {0, "Waiting for tablespace metadata lock", 0},
 {0, "Waiting for schema metadata lock", 0},
 {0, "Waiting for table metadata lock", 0},
 {0, "Waiting for stored function metadata lock", 0},
 {0, "Waiting for stored procedure metadata lock", 0},
 {0, "Waiting for trigger metadata lock", 0},
 {0, "Waiting for event metadata lock", 0},
 {0, "Waiting for commit lock", 0},
 {0, "User lock", 0}, /* Be compatible with old status. */
 {0, "Waiting for locking service lock", 0},
 {0, "Waiting for backup lock", 0},
 {0, "Waiting for binlog lock", 0}
};

3. Add MDL LOCK printing function

The best way to study MDL LOCK is to get the MDL lock, upgrade, and downgrade process. Because the source code is too large, it is impossible to cover everything.

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

select * from performance_schema.metadata_locks

However, if you want to see which MDL LOCKs a statement has acquired, it still seems powerless. So I added a function prototype to mdl.cc as follows

/*p_ticket in parameter*/

int my_print_ticket(const MDL_ticket* p_ticket)

And in the mdl_ticket class, this function prototype is added as a friend function, otherwise the private members cannot be obtained, and the public method given is more complicated

friend int my_print_ticket(const MDL_ticket* p_ticket);

The following information of MDL LOCK is mainly obtained and printed in the mysql err log:

The thread id is obtained through p_ticket->m_ctx->get_thd();

mdl lock database name is obtained through p_ticket->m_lock->key.db_name()

mdl lock object name can be obtained through p_ticket->m_lock->key.name()

mdl lock namespace is obtained through p_ticket->m_lock->key.mdl_namespace()

mdl lock fast path is obtained through p_ticket->m_is_fast_path. If yes, it is output, otherwise it is not output.

mdl lock type is obtained through p_ticket->m_type

mdl lock duration is obtained through p_ticket->m_duration

The output information is as follows:

2017-08-03T07:34:21.720583Z 3 [Note] (>MDL PRINT) Thread id is 3:

2017-08-03T07:34:21.720601Z 3 [Note] (->MDL PRINT) DB_name is:test

2017-08-03T07:34:21.720619Z 3 [Note] (-->MDL PRINT) OBJ_name is:test

2017-08-03T07:34:21.720637Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE

2017-08-03T07:34:21.720655Z 3 [Note] (---->MDL PRINT) Fast path is: (Y)

2017-08-03T07:34:21.720673Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)

2017-08-03T07:34:21.720692Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION

In fact, the information is similar to that in metadata_locks. This is the Thread id here, which is the id from show processlist, but I can get the lock acquisition history information. I don't have LOCK_STATUS: GRANTED here, but I can judge it from the MDL_context::acquire_lock logic.

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: test
OBJECT_INSTANCE_BEGIN: 140734412907760
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6314
OWNER_THREAD_ID: 39
OWNER_EVENT_ID: 241

4. Add a print function at the appropriate location for observation

Since we want to study the locking, upgrading and downgrading of MDL LOCK, we need to find their function entry, and then add the printing function at the appropriate position for observation. The printing position is marked below, and most of the source code is deleted. Please check the source code for reference.

1. Lock: MDL_context::acquire_lock

bool
MDL_context::acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout)
{
 if (mdl_request->ticket) //Get ticket successfully
 {
  /*
   We have managed to acquire lock without waiting.
   MDL_lock, MDL_context and MDL_request were updated
   Accordingly, so we can simply return success.
  */
  //REQUESET obtains TICKET successfully, print return FALSE here;
 }
 /*
  Our attempt to acquire lock without waiting has failed.
  As a result of this attempt we got MDL_ticket with m_lock
  member pointing to the corresponding MDL_lock object which
  has MDL_lock::m_rwlock write-locked.
 */
 //Unsuccessful acquisition, join the MDL_lock waiting queue lock= ticket->m_lock;
 lock->m_waiting.add_ticket(ticket);
 will_wait_for(ticket); //Deadlock detection/* There is a shared or exclusive lock on the object. */
 DEBUG_SYNC(get_thd(), "mdl_acquire_lock_wait");
 find_deadlock(); 
 //Print TICKET here and enter the waiting process if (lock->needs_notification(ticket) || lock->needs_connection_check())
 {
  }
 done_waiting_for(); //Wait for the deadlock detection waiting graph to be adjusted and remove the waiting edge (undirected graph)
 //Of course, here we succeeded after waiting and the status is GRANTED
 DBUG_ASSERT(wait_status == MDL_wait::GRANTED);
 m_tickets[mdl_request->duration].push_front(ticket);
 mdl_request->ticket = ticket;
 mysql_mdl_set_status(ticket->m_psi, MDL_ticket::GRANTED);
 //Here prints the TICKET obtained by waiting for REQUEST
 return FALSE;
}

2. Downgrade: void MDL_ticket::downgrade_lock(enum_mdl_type new_type)

void MDL_ticket::downgrade_lock(enum_mdl_type new_type)
{
 /* Only allow downgrade from EXCLUSIVE and SHARED_NO_WRITE. */
 DBUG_ASSERT(m_type == MDL_EXCLUSIVE ||
       m_type == MDL_SHARED_NO_WRITE);
//Print out the TICKET before downgrade here
 if (m_hton_notified)
 {
  mysql_mdl_set_status(m_psi, MDL_ticket::POST_RELEASE_NOTIFY);
  m_ctx->get_owner()->notify_hton_post_release_exclusive(&m_lock->key);
  m_hton_notified = false;
  mysql_mdl_set_status(m_psi, MDL_ticket::GRANTED);
 }
//The end of the function promises to issue the downgraded TICKET
}

3. Upgrade: MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,enum_mdl_type new_type, ulong lock_wait_timeout)

bool
MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,
                 enum_mdl_type new_type,
                 ulong lock_wait_timeout)
{
 MDL_REQUEST_INIT_BY_KEY(&mdl_new_lock_request,
             &mdl_ticket->m_lock->key, new_type,
             MDL_TRANSACTION); //Construct a request
 //The TICKET type printed here if (acquire_lock(&mdl_new_lock_request, lock_wait_timeout)) //Try to lock with the new LOCK_TYPE DBUG_RETURN(TRUE);
 is_new_ticket = ! has_lock(mdl_svp, mdl_new_lock_request.ticket);
 lock = mdl_ticket->m_lock;
 //The following is a series of maintenance on MDL_LOCK and the so-called merge operation/* Code below assumes that we were upgrading to "obtrusive" type of lock. */
 DBUG_ASSERT(lock->is_obtrusive_lock(new_type));
 /* Merge the acquired and the original lock. @todo: move to a method. */
 mysql_prlock_wrlock(&lock->m_rwlock);
 if (is_new_ticket)
 {
  m_tickets[MDL_TRANSACTION].remove(mdl_new_lock_request.ticket);
  MDL_ticket::destroy(mdl_new_lock_request.ticket);
 }
 //The upgraded TICKET type printed here DBUG_RETURN(FALSE);
}

Of course, I am only printing in these places now. If necessary, I can add functions to other places in the future.

5. Various MDL LOCK TYPE lock tests

1. MDL_INTENTION_EXCLUSIVE (IX)

This lock will appear in many operations. For example, it will be triggered when performing any DML/DDL operation. In fact, DML operations such as DELETE/UPDATE/INSERT/FOR UPDATE will add an IX lock on GLOBAL and then lock the object. DDL statements will at least add an IX lock on GLOBAL and an IX lock on the SCHEMA to which the object belongs. The lock on this object is the GLOABL IX MDL LOCK triggered by DELETE.

2017-08-03T18:22:38.092100Z 3 [Note] Test2:open_tables_for_query()
2017-08-03T18:22:38.092205Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:22:38.092242Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:22:38.092276Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T18:22:38.092310Z 3 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T18:22:38.092344Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T18:22:38.092380Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT 
2017-08-03T18:22:38.092551Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY

The following is the GLOABLIX MDL LOCK and SCHEMA-level MDL LOCK triggered by the ALETER statement

2017-08-03T18:46:05.894871Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:46:05.894915Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:46:05.894948Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T18:46:05.894980Z 3 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T18:46:05.895012Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T18:46:05.895044Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT 
2017-08-03T18:46:05.895076Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T18:46:05.895116Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:46:05.895147Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:46:05.895206Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T18:46:05.895243Z 3 [Note] (-->MDL PRINT) OBJ_name is: 
2017-08-03T18:46:05.895276Z 3 [Note] (--->MDL PRINT) Namespace is:SCHEMA 
2017-08-03T18:46:05.895325Z 3 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T18:46:05.895357Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T18:46:05.895390Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T18:46:05.895421Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY

So this MDL LOCK is everywhere, and the only question is whether it is compatible. If it is not compatible, it will be blocked. SCOPED's IX type is generally compatible unless it encounters
S Type

2. MDL_SHARED(S)

This lock is generally used in flush tables with read lock

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
2017-08-03T18:19:11.603911Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:19:11.603947Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:19:11.603971Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T18:19:11.603994Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2017-08-03T18:19:11.604045Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_EXPLICIT 
2017-08-03T18:19:11.604073Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T18:19:11.604133Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:19:11.604156Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:19:11.604194Z 3 [Note] (--->MDL PRINT) Namespace is:COMMIT 
2017-08-03T18:19:11.604217Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2017-08-03T18:19:11.604240Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_EXPLICIT 
2017-08-03T18:19:11.604310Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY

We notice that their namspace is GLOBAL and COMMIT is obviously SCOPED LOCK, and their TYPE is S, so it is obvious that according to the compatibility principle
SCOPED's MDL IX is incompatible with MDL S. Flush tables with read lock; will block all DELTE/UPDATE/INSERT/FOR UPDATE operations.
etc. DML and DDL operations (because these operations require a GLOBAL MDL IX lock)

3. MDL_SHARED_HIGH_PRIO (SH)

This lock is basically used frequently by everyone, but you don't feel it. For example, we usually use the desc operation

compatibility:

    Request | Granted requests for lock |
    type | S SH SR SW SWLP SU SRO SNW SNRW X |
   ----------+---------------------------------------------+
   SH | + + + + + + + + + - |
mysql> desc test.testsort10;

2017-08-03T19:06:05.843277Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:06:05.843324Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:06:05.843359Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:06:05.843392Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:06:05.843425Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:06:05.843456Z 4 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T19:06:05.843506Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO(SH) 
2017-08-03T19:06:05.843538Z 4 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:06:05.843570Z 4 [Note] (------->MDL PRINT) Mdl status is:EMPTY

This type has a higher priority, but is incompatible with X. It is also easy to understand that, for example, the desc operation cannot be performed during the rename phase.

4. MDL_SHARED_READ(SR)

This lock is generally used for compatibility in non-current read selects:

    Request | Granted requests for lock |
    type | S SH SR SW SWLP SU SRO SNW SNRW X |
   ----------+---------------------------------------------+
   SR | + + + + + + + + - - |
mysql> select * from test.testsort10 limit 1;

2017-08-03T19:13:52.338764Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:13:52.338813Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:13:52.338847Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:13:52.338883Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:13:52.338917Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:13:52.338950Z 4 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T19:13:52.339025Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ(SR) 
2017-08-03T19:13:52.339062Z 4 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:13:52.339097Z 4 [Note] (------->MDL PRINT) Mdl status is:EMPTY

Here we still have to mention that we occasionally encounter situations where select is blocked (for example, an object MDL X lock is required at a certain stage of DDL). We have to complain
MYSQL actually blocks select. In fact, this is the incompatibility problem between object mdl lock X and SR (refer to the previous compatibility matrix).

5. MDL_SHARED_WRITE (SW)

This lock is generally used to lock the table (currently read) for DELETE/UPDATE/INSERT/FOR UPDATE operations, excluding DDL operations. However, please note that DML operations actually have a GLOBAL IX lock. As mentioned earlier, this lock is only on the object.

compatibility:

    Request | Granted requests for lock |
    type | S SH SR SW SWLP SU SRO SNW SNRW X |
   ----------+---------------------------------------------+
   SW | + + + + + + - - - - |
mysql> select * from test.testsort10 limit 1 for update;

2017-08-03T19:25:41.218428Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:25:41.218461Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:25:41.218493Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:25:41.218525Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:25:41.218557Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:25:41.218588Z 4 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T19:25:41.218620Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW) 
2017-08-03T19:25:41.218677Z 4 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:25:41.218874Z 4 [Note] (------->MDL PRINT) Mdl status is:EMPTY

6. MDL_SHARED_WRITE_LOW_PRIO (SWL)

This lock is rarely used. Source code comments only

Used by DML statements modifying

tables and using the LOW_PRIORITY clause

Will use

compatibility:

    Request | Granted requests for lock |
    type | S SH SR SW SWLP SU SRO SNW SNRW X |
   ----------+---------------------------------------------+
   SWLP | + + + + + + - - - - |
mysql> update LOW_PRIORITY test.testsort10 set id1=1000 where id1= 96282;
2017-08-03T19:32:47.433507Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:32:47.433521Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:32:47.433533Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:32:47.433547Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:32:47.433560Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:32:47.433572Z 4 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T19:32:47.433594Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE_LOW_PRIO(SWL) 
2017-08-03T19:32:47.433607Z 4 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:32:47.433620Z 4 [Note] (------->MDL PRINT) Mdl status is:EMPTY

7. MDL_SHARED_UPGRADABLE (SU)

This lock is usually used in the ALTER TABLE statement. It can be upgraded to SNW, SNRW, X, and at least the X lock can be downgraded to SU.

In fact, INNODB ONLINE DDL is very dependent on it, DML (SW) and SELECT (SR) will not be blocked

compatibility:

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

We need to study its compatibility. We can see that (SELECT)SR (DML)SW are all allowed in OBJECT LOCK. In SCOPED LOCK, although DML DDL will be locked in GLOBAL, their types are all IX. Therefore, this SU lock does not block DML/SELECT read and write operations from entering the INNODB engine layer. It is the foundation of ONLINE DDL. If it is not compatible, you cannot enter the INNODB engine layer, let alone ONLINE DDL. Note that I said ALGORITHM=INPLACE and do not set LOCK.

(For DDL operations with LOCK=DEFAULT, or with the LOCK clause omitted, MySQL uses the lowest level
of locking that is available for that kind of operation, allowing concurrent queries, DML, or both wherever
possible. This is the setting to use when making pre-planned, pre-tested changes that you know will not
cause any availability problems based on the workload for that table
When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it
is more efficient than using ALGORITHM=COPY because:
? No undo logging or associated redo logging is required for ALGORITHM=INPLACE. These operations add
overhead to DDL statements that use ALGORITHM=COPY.
? The secondary index entries are pre-sorted, and so can be loaded in order.
? The change buffer is not used, because there are no random-access inserts into the secondary indexes.
)

Such as the following statement

mysql> alter table testsort12 add column it int not null;
Query OK, 0 rows affected (6.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let me analyze it briefly:

2017-08-03T19:46:54.781453Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:46:54.781487Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.781948Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.781990Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.782026Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.782060Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_UPGRADABLE(SU) 
2017-08-03T19:46:54.782096Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.782175Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T19:46:54.803898Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK will upgrade
2017-08-03T19:46:54.804201Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK upgrade TO
2017-08-03T19:46:54.804240Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.804254Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.804267Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.804280Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.804293Z 3 [Note] (----->MDL PRINT) Mdl type :MDL_EXCLUSIVE(X) 
2017-08-03T19:46:54.804306Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.804319Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T19:46:54.855563Z 3 [Note] (downgrade_lock)THIS MDL LOCK will downgrade
2017-08-03T19:46:54.855693Z 3 [Note] (downgrade_lock) to this MDL lock
2017-08-03T19:46:54.855706Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.855717Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.856053Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.856069Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.856082Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_UPGRADABLE(SU) 
2017-08-03T19:46:54.856094Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.856214Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T19:47:00.260166Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK will upgrade
2017-08-03T19:47:00.304057Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK upgrade TO
2017-08-03T19:47:00.304090Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:47:00.304105Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:47:00.304119Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:47:00.304132Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:47:00.304181Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X) 
2017-08-03T19:47:00.304196Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:47:00.304211Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T19:47:01.032329Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!

First, get the testsort12 table

2017-08-03T19:46:54.781487 Got MDL_SHARED_UPGRADABLE (SU)
2017-08-03T19:46:54.804293 Upgrade MDL_EXCLUSIVE(X) 
2017-08-03T19:46:54.855563 Downgrade MDL_SHARED_UPGRADABLE (SU)
2017-08-03T19:47:00.304057 Upgrade MDL_EXCLUSIVE(X)

Because no matter what, this alter operation is still time-consuming. From the time we can see that the time from 2017-08-03T19:46:54 when the downgrade is completed to 2017-08-03T19:47:00 is actually the most time-consuming. In fact, this is the actual COPY operation, but this process is actually in MDL SU mode, so it will not block DML/SELECT operations.
Here I would like to remind you that the so-called ONLINE DDL only does not block DML/SELECT operations during the COPY phase. It is best to run it when the database pressure is light.
For example, if a DML is not submitted or a SELECT is not completed, SW SR will block X, and X can block everything and has a high priority. The phenomenon caused by this is that the DDL operation is blocked because the DML is not submitted, and the DDL operation blocks all operations, basically blocking all operations for this TABLE. For ALGORITHM=COPY, the rest of the code is similar, but the SNW lock is used in the COPY phase. Next, I will first look at the SNW lock.

8. MDL_SHARED_NO_WRITE (SNW)

SU can be upgraded to SNW and SNW can be upgraded to X, as mentioned earlier for use in ALGORITHM=COPY to protect data consistency.
Let’s check its compatibility first

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

You can see that SR works but SW doesn't. Of course, DML (SW) is blocked while SELECT (SR) is not blocked. Below I just give the key parts.

mysql> alter table testsort12 add column ik int not null, ALGORITHM=COPY;
2017-08-03T20:07:58.413215Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK upgrade TO
2017-08-03T20:07:58.413241Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T20:07:58.413257Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T20:07:58.413273Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T20:07:58.413292Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T20:07:58.413308Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_NO_WRITE(SNW) 
2017-08-03T20:07:58.413325Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T20:07:58.413341Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T20:08:25.392006Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK upgrade TO
2017-08-03T20:08:25.392024Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T20:08:25.392086Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T20:08:25.392159Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T20:08:25.392199Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T20:08:25.392214Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X) 
2017-08-03T20:08:25.392228Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T20:08:25.392242Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T20:07:58.413308 Got MDL_SHARED_NO_WRITE (SNW) 
2017-08-03T20:08:25.392006 Upgraded to MDL_EXCLUSIVE(X)

The time from 2017-08-03T20:07:58.413308 to 2017-08-03T20:08:25.392006 is the actual COPY time. It can be seen that only DML can be performed during the entire COPY period.
The inability to SELECT is also a key difference between ALGORITHM=COPY and ALGORITHM=INPLACE.

9. MDL_SHARED_READ_ONLY (SRO)

For LOCK TABLES READ statement

Compatibility is as follows

    Request | Granted requests for lock |
    type | S SH SR SW SWLP SU SRO SNW SNRW X |
   ----------+---------------------------------------------+
   SRO | + + + - - + + + - - |
Blocks DML (SW) but SELECT (SR) is still possible.
mysql> lock table testsort12 read;
Query OK, 0 rows affected (0.01 sec)
2017-08-03T21:08:27.267947Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:08:27.267979Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:08:27.268009Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T21:08:27.268040Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T21:08:27.268070Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T21:08:27.268113Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO) 
2017-08-03T21:08:27.268145Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T21:08:27.268175Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY

10. MDL_SHARED_NO_READ_WRITE (SNRW)

For LOCK TABLES WRITE statement

compatibility:

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

It can be seen that DML (SW) and SELECT (SR) are blocked, only SH is still available, and DESC (SH) is also available.

mysql> lock table testsort12 write;
Query OK, 0 rows affected (0.00 sec)
2017-08-03T21:13:07.113347Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:13:07.113407Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:13:07.113435Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T21:13:07.113458Z 3 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T21:13:07.113482Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T21:13:07.113505Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT 
2017-08-03T21:13:07.113604Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T21:13:07.113637Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:13:07.113660Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:13:07.113681Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T21:13:07.113703Z 3 [Note] (-->MDL PRINT) OBJ_name is: 
2017-08-03T21:13:07.113725Z 3 [Note] (--->MDL PRINT) Namespace is:SCHEMA 
2017-08-03T21:13:07.113746Z 3 [Note] (---->MDL PRINT) Fast path is: (Y)
2017-08-03T21:13:07.113768Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T21:13:07.113791Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T21:13:07.113813Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY 
2017-08-03T21:13:07.113842Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:13:07.113865Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:13:07.113887Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T21:13:07.113922Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T21:13:07.113945Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T21:13:07.113975Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_NO_READ_WRITE(SNRW) 
2017-08-03T21:13:07.113998Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T21:13:07.114021Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY

In addition, lock table also requires IX locks on GLOBAL and SCHEMA. In other words, flush tables with read lock; will block
lock table testsort12 write; however, lock table testsort12 read will not be blocked.

11. MDL_EXCLUSIVE(X)

Used for various DDL operations, annotated as CREATE/DROP/RENAME TABLE operations. In fact, almost all DDL operations involve this lock, as analyzed above.
add column operation, but the duration is generally short.

compatibility:

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

There is no accident blocking everything, but it is also blocked by everything.

For example, the add column operation just now

2017-08-03T19:46:54.804240Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.804254Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.804267Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.804280Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.804293Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_EXCLUSIVE(X) 
2017-08-03T19:46:54.804306Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.804319Z 3 [Note] (------->MDL PRINT) Mdl status is:EMPTY

6. Source code comments

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 (eg 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, ie 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()
  facilities in such a 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 (ie
  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 (eg 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,
 /*
  A version of MDL_SHARED_WRITE lock which has lower priority than
  MDL_SHARED_READ_ONLY locks. Used by DML statements modifying
  tables and using the LOW_PRIORITY clause.
 */
 MDL_SHARED_WRITE_LOW_PRIO,
 /*
  An upgradable shared metadata lock which allows concurrent updates and
  reads of table data.
  A connection holding this kind of lock can read table metadata and read
  table data. It should not modify data as this lock is compatible with
  SRO locks.
  Can be upgraded to SNW, SNRW and X locks. Once SU lock is upgraded to X
  or SNRW lock data modification can happen freely.
  To be used for the first phase of ALTER TABLE.
 */
 MDL_SHARED_UPGRADABLE,
 /*
  A shared metadata lock for cases when we need to read data from table
  and block all concurrent modifications to it (for both data and metadata).
  Used by LOCK TABLES READ statement.
 */
 MDL_SHARED_READ_ONLY,
 /*
  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};
/** Duration of metadata lock. */
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 };
/**
  Object namespaces.
  Sic: when adding a new member to this enum make sure to
  update m_namespace_to_wait_state_name array in mdl.
  Different types of objects exist in different namespaces
   - GLOBAL is used for the global read lock.
   - TABLESPACE is for tablespaces.
   - SCHEMA is for schemas (aka databases).
   - TABLE is for tables and views.
   - FUNCTION is for stored functions.
   - PROCEDURE is for stored procedures.
   - TRIGGER is for triggers.
   - EVENT is for event scheduler events.
   - COMMIT is for enabling the global read lock to block commits.
   - USER_LEVEL_LOCK is for user-level locks.
   - LOCKING_SERVICE is for the name plugin RW-lock service
  Note that although there isn't metadata locking on triggers,
  it's necessary to have a separate namespace for them since
  MDL_key is also used outside of the MDL subsystem.
  Also note that requests waiting for user-level locks to get special
  Treatment-waiting is aborted if connection to client is lost.
 */
 enum enum_mdl_namespace { GLOBAL=0,
              TABLESPACE,
              SCHEMA,
              TABLE,
              FUNCTION,
              PROCEDURE,
              TRIGGER,
              EVENT,
              COMMIT,
              USER_LEVEL_LOCK,
              LOCKING_SERVICE,
              BACKUP,
              BINLOG,
              /* This should be the last ! */
              NAMESPACE_END };

You may also be interested in:
  • Reasons and methods for Waiting for table metadata lock in MySQL
  • Detailed explanation of Metadata Lock that you must know when changing the MySQL table structure
  • MySQL slave delays foreign key check and auto-increment lock for a column
  • A brief discussion on the lock range of MySQL next-key lock
  • MySQL lock control concurrency method
  • Solution to PHP+MySQL high-concurrency locked transaction processing problem
  • MYSQL METADATA LOCK (MDL LOCK) theory and lock type test

<<:  Javascript asynchronous programming: Do you really understand Promise?

>>:  How to configure jdk environment under Linux

Recommend

Detailed explanation of mktemp, a basic Linux command

mktemp Create temporary files or directories in a...

Enter two numbers in html to realize addition, subtraction, multiplication and division functions

1. parseFloat() function Make a simple calculator...

MySQL 5.6.36 Windows x64 version installation tutorial detailed

1. Target environment Windows 7 64-bit 2. Materia...

MySQL 8.0.18 Installation Configuration Optimization Tutorial

Mysql installation, configuration, and optimizati...

Detailed tutorial on compiling and installing MySQL 5.7.24 on CentOS7

Table of contents Install Dependencies Install bo...

Ubuntu 20.04 firewall settings simple tutorial (novice)

Preface In today's increasingly convenient In...

MySQL 5.7.15 installation and configuration method graphic tutorial (windows)

Because I need to install MySQL, I record the ins...

Detailed explanation of HTML onfocus gain focus and onblur lose focus events

HTML onfocus Event Attributes Definition and Usag...

Perfect solution to MySQL common insufficient memory startup failure

1. If MySQL is not started successfully, check th...

Example code for converting Mysql query result set into JSON data

Mysql converts query result set into JSON data Pr...

Docker installation and deployment example on Linux

After reading the following article, you can depl...

Detailed tutorial on installing different (two) versions of MySQL database on Windows

1. Cause: I need to import a sql file, but I can&...

Summary of various methods for JavaScript to determine whether it is an array

Table of contents Preface Array.isArray construct...

Implementation steps for docker deployment lnmp-wordpress

Table of contents 1. Experimental Environment 2. ...

How can MySQL effectively prevent database deletion and running away?

Table of contents Safe Mode Settings test 1. Upda...