MYSQL METADATA LOCK (MDL LOCK) theory and lock type test
MYSQL METADATA LOCK (MDL LOCK) learning theory and lock type testPreface: Source code version: 5.7.14 1. Preliminary understanding MDL locks in MYSQL have always been a headache. When we talk about locks, we usually tend to talk about gap locks, next key locks, row locks, etc. at the lower level of INNODB. ----At the level: 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 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:
2. Basic important data structures (classes) and concepts 1. MDL TYPEMDL_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. The following 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 service3. Implement classification scope lock: generally corresponds to the global
/**
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 Matrixscope 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 lastSee 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 classThat 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 above7. MDL_key class It is the actual 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
/**
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_lockEach 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_contextThis 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 contains the current ticket acquisition status enum_wait_status m_wait_status; Include:
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 process of MDL locking, upgrading, and downgrading. Because the source code is too large, it is impossible to cover everything. Although 5.7 adds the following method to view
However, it is still difficult to check which MDL LOCKs a statement has obtained. So I added a function prototype to mdl.cc as follows :
And in the The following information of MDL LOCK is mainly obtained and printed in the mysql err log: The output information is as follows:
In fact, the information is similar to that in
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 location for observation. The printing location is marked below, and most of the source code is deleted. Please refer to it yourself. View source code: 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. Downgradevoid 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
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 tests1. MDL_INTENTION_EXCLUSIVE (IX) This lock will appear in many operations, such as any DML/DDL operation will trigger it. Below is the GLOABLIX MDL LOCK triggered by DELETE
The following is the GLOABLIX MDL LOCK and SCHEMA-level MDL LOCK triggered by the ALETER statement
2. MDL_SHARED(S)
mysql> flush tables with read lock; Query OK, 0 rows affected (0.01 sec)
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 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;
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)
compatibility:
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SR | + + + + + + + + - - |
mysql> select * from test.testsort10 limit 1;
5. MDL_SHARED_WRITE (SW)
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;
6. MDL_SHARED_WRITE_LOW_PRIO (SWL) This lock is rarely used. Source code comments only 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;
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. 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, so this SU lock does not block DML/SELECT read and write operations.
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 A simple analysis:
First, get the testsort12 table
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 8. MDL_SHARED_NO_WRITE (SNW)
compatibility:
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.
9. MDL_SHARED_READ_ONLY (SRO)
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.
10. MDL_SHARED_NO_READ_WRITE (SNRW)
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.
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. compatibility:
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
X | - - - - - - - - - - - |
There is no accidental blocking of everything above, but it is also blocked by everything, such as the add column operation just now.
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 };
The above is the details of MYSQL METADATA LOCK (MDL LOCK) theory and lock type test. For more information about MYSQL METADATA LOCK (MDL LOCK), please pay attention to other related articles on 123WORDPRESS.COM! You may also be interested in:
|
<<: Use label tag to select the radio button by clicking the text
>>: Correct way to write empty links to prevent page jumps after clicking a href # problem
Recommend
MySQL fuzzy query statement collection
SQL fuzzy query statement The general fuzzy state...
TypeScript enumeration basics and examples
Table of contents Preface What are enums in TypeS...
mysql zip file installation tutorial
This article shares the specific method of instal...
Explanation of the configuration and use of MySQL storage engine InnoDB
MyISAM and InnoDB are the most common storage eng...
js converts a multidimensional array into a one-dimensional array and then reorders it
Table of contents Let's talk about flattening...
Is the tag li a block-level element?
Why can it set the height, but unlike elements lik...
Vue implementation counter case
This article example shares the specific code of ...
How to run multiple MySQL instances in Windows
Preface In Windows, you can start multiple MySQL ...
In-depth understanding of Mysql logical architecture
MySQL is now the database used by most companies ...
Detailed explanation of MySQL database isolation level and MVCC
Table of contents 1. Isolation Level READ UNCOMMI...
Solve the problem that element edit form el-radio cannot be selected after echoing
Table of contents Preface question Online solutio...
MySQL replication table details and example code
MySQL replication table detailed explanation If w...
HTML dynamically loads css styles and js scripts example
1. Dynamically loading scripts As the demand for ...
Introduction to Computed Properties in Vue
Table of contents 1. What is a calculated propert...
Introduction to Linux compression and decompression commands
Table of contents Common compression formats: gz ...