MySQL source code Learning & mdash; MDL dictionary lock _ MySQL

MySQL source code learning: amp; mdash; MDL dictionary lock bitsCN.com

• What is MDL?

MDL, Meta Data lock, metadata lock, usually calledDictionary lock. The dictionary lock corresponds to the data lock. The dictionary lock is used to protect data objects from changes. generally, some DDL statements change the dictionary objects. for example, if two TX and TX1 tables are queried, TX2 tries to DROP them, the dictionary lock will lock TX2 and know that TX1 is over (commit or rollback ). The data lock is used to protect the data in the table. for example, when two tx s update a row at the same time, the TX s that first get the row lock will be executed first, and the latter can only wait.

• MDL Design goals

The dictionary lock is designed for the metadata of database objects. To the following three goals.

1. provides protection for the dictionary object cache (table definatin cache, TDC) in concurrent access memory. This is an internal requirement of the system.

2. ensure the concurrency of DML. For example, TX1 queries table T1, and TX2 inserts table T1.

3. make sure that some operations are mutually exclusive, such as DML and most DDL statements (except alter table. For example, if TX1 inserts TABLE T1 and TX2 executes drop table, the two operations do not allow concurrency. Therefore, the TABLE objects must be protected to ensure the correctness of the binlog logic. (It seems that the dictionary locks in the previous versions are statement-level, leading to a bug in the logic of binlog .)

• Supported lock types

In database theory, the basic lock types are S and X. intention lock IS and IX are introduced to lock layers. For example, if you want to modify the data in a table, you may first apply a table-level IX lock to the table, and then apply a row-level X lock to the modified data, in this way, other things that attempt to modify the table definition will wait because they cannot obtain the X lock at the table level.

MySQL further segments the dictionary lock types based on the functions of different statements,Segmentation is based on dictionary operations and data operations.. The benefit of subdivision is that it can improve the concurrency efficiency to a certain extent, because if only the X and S locks are defined, it will inevitably lead to the limitations of the compatibility matrix. MySQL has made every effort to define the following lock types.

Name	Meaning
MDL_INTENTION_EXCLUSIVE	Intention exclusive lock, used only for range Lock
MDL_SHARED	A shared lock is used to access dictionary objects without accessing data.
MDL_SHARED_HIGH_PRIO	Only access dictionary objects (such as desc table)
MDL_SHARED_READ	Shared read lock, used to read data (such as select)
MDL_SHARED_WRITE	Shared write lock, used to modify data (such as update)
MDL_SHARED_NO_WRITE	Share non-write locks, allow reading data, and block other TX changes (such as alter table)
MDL_SHARED_NO_READ_WRITE	Used to access dictionaries and read and write data Other TX read/write data is not allowed
MDL_EXCLUSIVE	Exclusive lock, which can modify dictionary and data

We can see that MySQL allows other transactions to read tables during alter table. Note that read operations must be performed after alter table acquires the MDL_SHARED_NO_WRITE lock. otherwise, concurrent operations cannot be performed. In this application scenario, when you ALTER a large table, other things can still be read, and the concurrency is improved.

• Lock compatibility

The compatibility of the lock is the compatibility matrix we often see. X and S are mutually exclusive, and S are compatible. We can also know the compatibility matrix of MySQL based on the lock type as follows:

	IX	S	SH	SR	SW	SNW	SNRW	X
IX	1	1	1	1	1	1	1	1
S	1	1	1	1	1	1	1	0
SH	1	1	1	1	1	1	1	0
SR	1	1	1	1	1	1	0	0
SW	1	1	1	1	1	0	0	0
SNW	1	1	1	1	0	0	0	0
SNRW	1	1	1	0	0	0	0	0
X	1	0	0	0	0	0	0	0

1 indicates compatibility, and 0 indicates incompatibility. You may find that X and IX are actually compatible. that's right. In fact, IX is no longer an IX in the traditional sense. This IX is used for range lock, so it is not mutually exclusive with the X lock.

• Data structure

The lock-related data structures involved are as follows:

MDL_context: dictionary lock context. Contains all dictionary lock requests for a transaction.

MDL_request: dictionary lock request. A request that contains a lock on an object.

MDL_ticket: queuing of dictionary locks. MDL_request is used to obtain a ticket.

MDL_lock: lock resources. An object is globally unique. Multiple concurrent tasks can be obtained at the same time.

The source code files involved are mainly SQL/mdl. cc

• Lock resources

Lock resources in the system isSharedGlobally stored in the static MDL_map mdl_locks; hash linked list. the hashkey of an object in the database must be unique and corresponds to a lock resource. When multiple transactions operate on a table at the same time, the applied lock is also the same memory object. To obtain the lock in mdl_locks, you must use the global mutex to protect mysql_mutex_lock (& m_mutex); m_mutex is a member of MDL_map.

• Lock process

A session connection corresponds to a THD entity in the implementation, and a THD corresponds to a MDL_CONTEXT, which indicates the required mdl lock resources. a MDL_CONTEXT contains multiple mdl_requests, A MDL_REQUEST is a type of lock request to an object.. Each mdl_request has a ticket object, which contains lock.

The lock is based on MDL_REQUEST.

Acquire_lock:if (mdl_request contains the needed ticket )return ticket;End if;Create a ticket;If (!find lock in lock_sys)Create a lock;End ifIf (lock can be granted to mdl_request)Set lock to ticket;Set ticket to mdl_request;ElseWait for lockEnd if

To explain this, first check whether there are equal ticket or stronger ticket in mdl_request. If yes, use it directly. Otherwise, a ticket is created to find the lock corresponding to the lock object. If no lock object exists, a ticket is created. Check whether the lock can be assigned to this transaction. if it can be directly returned, otherwise wait for this lock;

• Lock wait and wake up

The Lock wait of a dictionary object is caused by two incompatible locks on the same object. Of course, because of the uniqueness of lock, first come, first served, and then can only wait.

How can I determine whether a lock can grant a TX? This requires the lock structure. There are two members on the lock: grant and wait. grant indicates which locks are allowed for the lock, wait indicates the locks required for the pending transactions. The logic for determining whether a thing can be granted is as follows:

If(compatible(lock.grant, tx.locktype))    If (compatible(lock.wait, tx.locktype))return can_grant;    End ifEnd if

That is, first determine whether the lock type in the grant is compatible with the current transaction, and then determine whether the lock type in the wait is compatible with the current transaction. If you are careful, you will think that the lock type in wait does not need to be compatible with the current transaction. isn't that redundant? Actually, it is not because the wait compatibility matrix is different from the above matrix. the wait compatibility matrix feels that the DDL waits, prevent DML from coming in. (the wait matrix will not be written. you can check it in the code ).

For example:

TX1 TX2 TX3

SELECT T1

DROP T1

SELECT T1

At this time, TX2 will be blocked, TX3 will be blocked, and TX2 will be blocked, that is, it will be blocked by wait events. This may be to ensure that DML is disabled when DDL waits, because in front of DDL, DML does not seem so important.

How can we wake up a waiting transaction? For example, to wake up TX2, when TX1 ends, it will call release_all_locks_for_name to wake up the locked transaction. the specific operations are encapsulated inReschedule_waitersIn the function, reset the wait time flag to wake up. the key code is as follows:

if (can_grant_lock(ticket->get_type(), ticket->get_ctx()))    {      if (! ticket->get_ctx()->m_wait.set_status(MDL_wait::GRANTED))      {        /*          Satisfy the found request by updating lock structures.          It is OK to do so even after waking up the waiter since any          session which tries to get any information about the state of          this lock has to acquire MDL_lock::m_rwlock first and thus,          when manages to do so, already sees an updated state of the          MDL_lock object.        */        m_waiting.remove_ticket(ticket);        m_granted.add_ticket(ticket);}

Today, we have taken a look at the mdl system and have a preliminary understanding of lock requests, waits, and wakeup. Concurrency issues are the most difficult to debug. if you want to do a lock experiment, you can useFreeze threadsTo ensure that the concurrency control is fully presented according to your design philosophy.

Walking down the fallen leaves and pursuing my dream. Reprinted please indicate the source of the Green Channel: Good article to follow my favorites this article contact me bitsCN.com