Shulou(Shulou.com)06/01 Report--

This article is based on the source code version 5.7.14

The level is limited, please forgive me for any mistake.

The author has added the version of the MDL acquisition process and release process to github as follows:

Https://github.com/gaopengcarl/percona-server-locks-detail-5.7.22

For reference

The author has a deep understanding of the column of master-slave principle.

Https://www.jianshu.com/nb/43148932

I. Overview of MDL Lock

MDL locks in MySQL have always been a headache, and we talk about blocking row lock (gap lock/next key lock/key lock), which is generally more inclined to the InnoDB layer, because it is easy to understand and observe. Less consideration is given to MDL Lock, because it is really difficult to observe. Only when there is a problem looking at show processlist, you can see a simple so-called 'Waiting for table metadata lock'' state. In fact, MDL Lock is a very complex subsystem in the upper layer of MySQL and has its own deadlock detection mechanism.

Generally speaking, it is said that whether a large part of the locking table is related to MDL Lock, which shows its criticality and seriousness, the author also learned some according to his own needs, and did not have the ability to read all the code, but the author added a TICKET print function to make the MDL Lock locking process of the statement printed out to facilitate learning. The following starts with some basic concepts and then tells you how the author does the printing function. Finally, the possible statements of each kind of MDL TYPE are tested and analyzed. If you are not interested in basic concepts and adding print functions, you can directly refer to the fifth part of the sentence plus MDL Lock testing and analysis. I hope these tests can help you diagnose the problem.

It just so happens that the author recently encountered a deadlock in MDL Lock will give a case in the next article, this article only looks at the theory.

In the hierarchy: the MySQL layer, MDL LOCK actually started getting it as early as in the open_table function. Earliest acquisition phase: 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 0x0000000001516e17inopen_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 detected 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 can find that MDL Lock's deadlock error is exactly the same as Innodb deadlock, except that 'show engine innodb status' does not have deadlock information. 2. Important data structures and concepts 1. MDL Lock types

The main types of our research are as follows:

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)

The fifth part will test and explain each type in detail.

2 、 MDL Lock namespace

In MDL, MDL_KEY is represented as namespace+DB+OBJECT_NAME, and the so-called namespace is also more important. here is the classification 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

In this article, we mainly describe GLOBAL/SCHEMA/TABLE namespace, but for COMMIT namespace, it will be used when submitting. If you encounter a wait, the status is' Waiting for commit lock',: FTWRL blocking COMMIT. Please refer to section 15 of my "in-depth understanding of MySQL Master-Slave principle". Other namespace will not describe it.

3. MDL Lock implementation classification scope lock: it generally corresponds to the global MDL Lock. For example, flush table with read lock will get the MDL Lock of namespace space:GLOBAL type:S and namespace space:COMMIT type:S. It contains GLOBAL, COMMIT, TABLESPACE and SCHEMAobject lock: object-level MDL Lock, as its name suggests, such as TABLE-level MDL Lock, which is the core of this article. It contains other namespace.

Here are the source code comments:

/ * 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 Lock compatibility matrix

Here the compatibility matrix is the focus of learning lock jams, many types are much more than Innodb row lock types, do not have to remember, just need to encounter to know.

5. MDL Lock duration (MDL Lock duration)

For this enum_mdl_duration corresponding to the source code, we usually need to pay attention to whether the MDL Lock is released after the transaction commits or after the statement ends, which is actually this, which is important for the scope of MDL lock blocking. I copy the interpretation of the source code directly.

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, FAST PATH (unobtrusive) and SLOW PATH (obtrusive) of MDL Lock

The purpose of using two different methods is to optimize the implementation of MDL Lock. The following is the comments on the source code, which can be properly understood:

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 type is incompatible withsome 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, I.E. 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 structure partial attributes

That is, you need to obtain the MDL Lock requirements after parsing the statement, and then apply for MDL Lock in the MDL subsystem through this class object, which probably contains the following attributes:

/ * Type of metadata lock. * / enum enum_mdl_type type; / / Type of requirement / * * Duration for requested lock. * / enum enum_mdl_duration duration; / / duration / * * Pointers for participating in the list of lock requests for this context. * / MDL_request * next_in_list; / / two-way linked list to implement 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: if the application is successful (without 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. * / 8. Some attributes of MDL_key structure

This is the actual namespace+DB+OBJECT_NAME, which is put into a char array, and it will appear in MDL_LOCK and MDL_REQUEST.

Private:uint16m_length;uint16m_db_name_length;charm_ PTR [Max _ MDLKEY_LENGTH]; / / put here 9. MDL_ticket structure partial attributes

Like tickets, if you get the MDL Lock, you must return a ticket to MDL_request, and if you wait, it will not be allocated. The source code MDL_context::acquire_lock can observe. Some attributes are as follows:

/ * Pointers for participating in the list of lock requests for this context. Context private. As explained here is the formation of linked lists in context, which is private to the thread * / 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 linked lists in MDL_LOCK, which is the global * / MDL_ticket * next_in_lock; MDL_ticket * * prev_in_lock;/** Context of the owner of the metadata lock ticket. Externally accessible. This obviously points to the owner of this ticket, MDL_context, which is the thread attribute * / MDL_context * mroomctx; / * * Pointer to the lock object for this lock ticket. Externally accessible. Obviously here is a pointer to MDL_LOCK * / MDL_lock * mlockout; / * 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 here represents whether FAST PATH from an annotated point of view, fast path will not take up granted bitmaps and linked lists in MDL LOCK and will be replaced by a statistician, m_fast_path_locks_granted_counter, which will definitely cost 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. * / 10. Some attributes of MDL_lock structure

Each MDL_key corresponds to a MDL_lock, which contains the so-called GRANTED linked list and WAIT linked list. Considering its complexity, you can refer directly to the source code comments and are also very detailed. The attributes I describe here are as follows:

/ * The key of the object (data) being protected. * / MDL_key key;/** List of granted tickets for this lock. * / Ticket_list massively grantedandracking * Tickets for contexts waiting to acquire a lock. * / Ticket_list masking waitingwitting 11, MDL_context structure partial attributes

This is a so-called context structure in which the entire MySQL thread interacts with the MDL Lock subsystem, which contains a lot of methods and properties. The attributes I am more concerned with 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 massively waitworthy * Lists of all MDL tickets acquired by this connection. This is a linked list array of different MDL lock durations. It is actually MDL_STATEMENT a linked list MDL_TRANSACTION, a linked list MDL_EXPLICIT, a linked list * / Ticket_list m _ tickets [MDL _ DURATION_END]; / / this is a parent pointer pointing to a subclass object, which is typical of virtual function rewriting, which actually points to a thread / * class THD: public MDL_context_owner, public Query_arena, public Open_tables_state*/MDL_context_owner * m_owner. 12. All wait statu

The source code gives all the waiting marks as follows:

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}}

What we often see is:

"Waiting for table metadata lock": usually namespace TABLE-level MDL Lock, refer to section 5 according to compatibility matrix. "Waiting for global read lock": usually namespace GLOBAL-level MDL Lock, usually related to flush table with read lock, refer to section 5. "Waiting for commit lock": usually namespace COMMIT-level MDL Lock, usually related to flush table with read lock, refer to section 5. Third, add print function my_print_ticket

The best way to learn MDL Lock is, of course, to get all the MDL Lock locked by a single statement, including the process of locking, upgrading, demoting, and releasing. Although 5.7adds the diagnostic MDL Lock method:

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

But the flow of all the MDL Lock fetched by each statement is still not easy to observe, so I added the print function:

/ * p_ticket in parameter*/int my_print_ticket (const MDL_ticket* p_ticket)

And add this function prototype to the mdl_ticket class as the friend function:

Friend int my_print_ticket (const MDL_ticket* p_ticket)

The lock information of MDL Lock is mainly captured and printed to the err log, and the information is as follows:

Thread id: through pairticket-> masked ctx-> get_thd () Get mdl lock database name: get mdl lock object name through pairticket-> mlockout-> key.db_name (): get mdl lock namespace through pairticket-> mlockout-> key.name (): get mdl lock fast path through pairticket-> mlocklock-> key.mdl_namespace (): get the output by pairticket-> m_is_fast_path, otherwise do not output mdl lock type: get mdl lock through pairticket-> m_type Duration: obtained through pairticket-> m_duration

All of the above information has been described earlier. The specific 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

This is actually similar to the information in metadata_locks, as follows:

MySQL > select * from performance_schema.metadata_locks\ gateway * 1. Row * * OBJECT_TYPE: TABLE OBJECT_SCHEMA: testOBJECT_ NAME: testOBJECT_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

Once we have this function, we just need to add it appropriately where it is locked, upgraded, degraded, and released.

Fourth, add the my_print_ticket print function in the appropriate location

Since we are going to study the locking of MDL Lock? Upgrade? Downgrade, then we need to find their function entry, and then in the appropriate location to add the print function my_print_ticket to observe, the print location is marked below.

1. Lock: MDL_context::acquire_lockboolMDL_context::acquire_lock (MDL_request * mdl_request, ulong lock_wait_timeout) {if (mdl_request- > ticket) / / obtain ticket {/ * We have managed to acquire lock without waiting successfully. MDL_lock, MDL_context and MDL_request were updated accordingly, so we can simply return success. * / REQUESET obtained TICKET successfully printing return FALSE;} / * Our attempt to acquire lock without waiting has failed here. 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. * / / get not successfully joined the MDL_lock waiting queue lock= ticket- > mlockout; 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 to remove the waiting edge edge (undirected graph) / / of course, it is also through waiting here that 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) / / print here to get TICKET return FALSE;} 2 by waiting for REQUEST, 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 the pre-degraded TICKET if (m_hton_notified) {MySQL_mdl_set_status (m_psi, MDL_ticket::POST_RELEASE_NOTIFY); mroomctx-> get_owner ()-> notify_hton_post_release_exclusive (& mlocklock-- > key); mSecrethtonnotified= false MySQL_mdl_set_status (m_psi, MDL_ticket::GRANTED) } / / the function ends with TICKET} 3. Upgrade: MDL_context::upgrade_shared_lock (MDL_ticket * mdl_ticket,enum_mdl_type new_type, ulong lock_wait_timeout) boolMDL_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- > mlockout-> key, new_type, MDL_TRANSACTION) / / construct a request / / the TICKET type if (acquire_lock (& mdl_new_lock_request, lock_wait_timeout)) printed here / / try to lock DBUG_RETURN (TRUE) with the new LOCK_TYPE; is_new_ticket=! Has_lock (mdl_svp, mdl_new_lock_request.ticket); lock= mdl_ticket- > mlockout; / / A series of maintenance of 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 DBUG_RETURN (FALSE) printed here;} 4, release: general MDL Lock type lock test 1, MDL_INTENTION_EXCLUSIVE (IX)

This lock will appear in many operations, for example, any DML/DDL operation will trigger. In fact, DML operations such as DELTE/UPDATE/INSERT/FOR UPDATE will add an IX lock on the GLOBAL, and then add a lock on the object. The DDL statement will at least add an IX lock on the GLOBAL, an IX lock on the SCHEMA to which the object belongs, and a lock on the object.

Here is the GLOABL IX MDL LOCK triggered by DELETE:

2017-08-03T18:22:38.092205Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire okying 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

Let's note that its duration is at the statement level.

Here is the GLOABL IX MDL Lock triggered by the ALETER statement:

2017-08-03T18:46:05.894871Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire okying 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

So this MDL Lock is everywhere, and there is only the question of compatibility, and if it is not compatible, it will be blocked. The IX type of scope lock is generally compatible except when it comes to the S type, which is discussed below.

2. MDL_SHARED (S)

This lock is commonly used in flush tables with read lock, as follows:

MySQL > flush tables with read lock Query OK 0 rows affected (0.01sec) 2017-08-03T18:19:11.603911Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire oklines 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 oktimes 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, obviously they are scope lock, and their TYPE is S, so it is obvious that according to the compatibility principle, scope lock's MDL IX is not compatible with MDL S, and flush tables with read lock will block all DML and DDL operations such as DELTE/UPDATE/INSERT/FOR UPDATE, as well as commit operations.

3. MDL_SHARED_HIGH_PRIO (SH)

Basically, we often use this lock but do not feel it. For example, for our general desc operation, the compatibility matrix is as follows:

The operation record is as follows:

MySQL > desc test.testsort10 2017-08-03T19:06:05.843277Z 4 [Note] (acquire_lock) THIS MDL LOCK acquire oklines 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 it is not compatible with X. Note the duration is MDL_TRANSACTION.

4. MDL_SHARED_READ (SR)

This lock is generally used in select that is not currently read, and the compatibility is as follows:

The operation record is as follows:

MySQL > select * from test.testsort10 limit 1 2017-08-03T19:13:52.338764Z 4 [Note] (acquire_lock) THIS MDL LOCK acquire oklines 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 Mdl status is:EMPTY 52.339097Z 4 [Note] (- > MDL PRINT)

I would like to mention here that we occasionally get blocked by select (for example, an object MDL X lock is required at some stage of DDL). We have to complain that MySQL actually blocks select. In fact, this is the problem of incompatibility between object mdl lock X and SR (see the compatibility matrix above). Note the duration is MDL_TRANSACTION.

5. MDL_SHARED_WRITE (SW)

This lock is generally used for locking table by DELTE/UPDATE/INSERT/FOR UPDATE and other operations (currently read), and does not include DDL operation. However, note that DML operation actually has a GLOBAL IX lock. As mentioned earlier, this lock is only on the object, and the compatibility is as follows:

The operation record is as follows:

MySQL > select * from test.testsort10 limit 1 for update 2017-08-03T19:25:41.218428Z 4 [Note] (acquire_lock) THIS MDL LOCK acquire oklines 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 25Mdl status is:EMPTY 41.218874Z 4 [Note] (- > MDL PRINT)

Note the duration is MDL_TRANSACTION.

6. MDL_SHARED_WRITE_LOW_PRIO (SWL)

Source code comments are rarely used in this lock, only as follows:

Used by DML statements modifying tables and using the LOW_PRIORITY clause

No more explanations.

7. MDL_SHARED_UPGRADABLE (SU)

This lock is usually used in ALTER TABLE statements. It can be upgraded to SNW, SNRW, X, and at least X lock can be downgraded to SU. In fact, it is very dependent on it in Innodb ONLINE DDL. Because of its existence, DML (SW) and SELECT (SR) will not be blocked. Compatibility is as follows:

We need to study its compatibility. We can see that both (SELECT) SR and (DML) SW are allowed in OBJECT LOCK, while in SCOPED LOCK, although DML DDL is locked on GLOBAL, its type is IX. So this SU lock does not block DML/SELECT read and write operations into the Innodb engine layer, it is the foundation of ONLINE DDL. If it's not compatible, you can't even get into the Innodb engine layer, let alone ONLINE DDL. Pay attention to the ONLINE DDL of ALGORITHM=INPLACE I'm talking about here.

Operation logging:

MySQL > alter table testsort12 add column it int not null Query OK 0 rows affected (6.27 sec) Records: 0 Duplicates: 0 Warnings: 02017-08-03T19:46:54.781453Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire okwords 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 upgrade2017-08-03T19:46:54.804201Z 3 [Note] (upgrade_shared_lock) THIS MDL LOCK upgrade TO2017-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 downgrade2017-08-03T19:46:54.855693Z 3 [Note] (downgrade_lock) to this MDL lock2017-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 upgrade2017-08-03T19:47:00.304057Z 3 [Note] (upgrade_shared_lock) THIS MDL LOCK upgrade TO2017-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!

We need to do a brief analysis, and the process to get the MDL Lock on the testsort12 table is as follows:

2017-08-03T19:46:54.781487 obtains MDL_SHARED_UPGRADABLE (SU) 2017-08-03T19:46:54.804293 upgrade MDL_EXCLUSIVE (X) preparation phase 2017-08-03T19:46:54.855563 downgrade MDL_SHARED_UPGRADABLE (SU) execution phase 2017-08-03T19:47:00.304057 upgrade MDL_EXCLUSIVE (X) submission phase

In any case, this ALTER operation is time-consuming. From 2017-08-03T19:46:54 downgrade completion (SU) to 2017-08-03T19:47:00, it is actually the most time-consuming. Actually, this is the actual Inplace reconstruction, but this process is actually in MDL SU mode, so it will not block the DML/SELECT operation. Here, I would like to remind you that the so-called ONLINE DDL only does not block DML/SELECT operations during the Inplace reconstruction phase, or tries to operate when the database pressure is low. If DML is not submitted or SELECT is not finished, SW or SR must block X, and X is a high priority that can block all operations. The result is that DML uncommitted blocks DDL operations, while DDL operations block all operations, and basically all table operations for this TABLE are blocked (SW blocking XMagne X blocks all operations).

While ALGORITHM=COPY uses SNW locks in the COPY phase, I'll take a look at SNW locks first.

8. MDL_SHARED_NO_WRITE (SNW)

SU can be upgraded to SNW and SNW can be upgraded to X, which is used in ALGORITHM=COPY as mentioned earlier to protect data consistency. Let's take a look at its compatibility as follows:

As you can see from the compatibility matrix, this lock will not block SR, but blocking SW will certainly block DML (SW) while SELECT (SR) will not. Here are some operation logs:

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 TO2017-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 TO2017-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

We can find the following:

2017-08-03T20:07:58.413308 obtained MDL_SHARED_NO_WRITE (SNW) 2017-08-03T20:08:25.392006 upgrade to MDL_EXCLUSIVE (X)

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

9. MDL_SHARED_READ_ONLY (SRO)

For LOCK TABLES READ statements, compatibility is as follows:

According to compatibility, it can be found that blocking DML (SW) but SELECT (SR) is OK. The following is the operation log:

MySQL > lock table testsort12 read Query OK 0 rows affected (0.01sec) 2017-08-03T21:08:27.267947Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire okages 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:EMPTY10, MDL_SHARED_NO_READ_WRITE (SNRW)

For LOCK TABLES WRITE statements, compatibility is as follows:

You can see that both DML (SW) and SELECT (SR) are blocked by it, but you can also DESC (SH).

The operation log is as follows:

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 oklines 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 okstores 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 Fast path is 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 okages 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 Namespace is:TABLE 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, you can find that statements also need IX locks on GLOBAL and SCHEMA, in other words, flush tables with read lock; will block 'lock table testsort12 write', but' lock table testsort12 read' will not.

11. MDL_EXCLUSIVE (X)

For all kinds of DDL operations, virtually all DDL will involve this lock, and even ONLINE DDL will acquire this lock during the preparation and submission phase. Therefore, ONLINE DDL is not completely unclogged, but the blocking time is very short. Compatibility is as follows:

We have seen the operation record when validating the SU and SNW MDL Lock types, so there is nothing to add.

Author Wechat: gp_22389860

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.