Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use MDL dictionary locks in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

What is MDL?

MDL,Meta Data lock, metadata lock, is commonly referred to as dictionary lock. Dictionary locks correspond to data locks. The dictionary lock is to protect the data object from being changed. Generally, some DDL will change the dictionary object. For example, if two TX,TX1 queries the table first, and then the TX2 tries to DROP, the dictionary lock will lock the TX2 until the TX1 ends (commit or rollback). A data lock protects the data in a table. For example, when two TX updates a row at the same time, the TX that first gets the row lock will be executed first, and the latter can only wait.

Design goals of MDL

Dictionary locks are designed for metadata of database objects. Achieve the following three goals.

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

two。 Ensure concurrency of DML. For example, if TX1 queries table T1, TX2 also inserts table T1.

3. Ensure that some operations are mutually exclusive, such as DML and most DDL (except ALTER TABLE). For example, TX1 inserts table T1 and TX2 performs DROP TABLE, these two operations do not allow concurrency, so table objects need to be protected to ensure the correctness of binlog logic. (it seems that in previous versions, dictionary locks were statement-level, resulting in binlog illogical bug. )

Supported lock types

The basic lock types in database theory are S and X, and the intention locks IS and IX are introduced for hierarchical locking. For example, to modify the data in a table, you may first put a table-level IX lock on the table, and then put a row-level X lock on the modified data, which ensures that other things that try to modify the table definition wait because they cannot get the table-level X lock.

In MySQL, the type of dictionary lock is further subdivided according to the function of different sentences, which is based on the operation of dictionary and data. The advantage of subdivision is that it can improve the concurrency efficiency to some extent, because if only X and S locks are defined, it will inevitably lead to the limitation of the compatibility matrix. MySQL spares no effort to define the following lock types.

You can see that MySQL allows other transactions to read tables when it is in ALTER TABLE. It is important to note that the read operation cannot be concurrency unless the ALTER TABLE acquires the MDL_SHARED_NO_WRITE lock. This application scenario should be that when ALTER a larger table, other things can still be read, and concurrency is improved.

Lock compatibility

The compatibility of locks is the compatibility matrix that we often see, X and S must be mutually exclusive, S and S are compatible. MySQL according to the type of lock, we can also know that its compatibility matrix is as follows:

1 for compatibility, 0 for incompatibility. You may find that X and IX are compatible, yes, in fact, the IX here is no longer the traditional sense of IX, this IX is used on the scope lock, so the X lock is not mutually exclusive.

Data structure

The lock-related data structures involved are mainly as follows:

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

MDL_request: dictionary lock request. Contains a request for some lock on an object.

MDL_ticket: dictionary lock queue. MDL_request is to get a ticket.

MDL_lock: lock resources. An object is globally unique. Multiple things that can be concurrently can be obtained at the same time.

The files involved are mainly sql/mdl.cc

Lock resource

Lock resources are shared in the system, that is, global, stored in the hash linked list of static MDL_map mdl_locks;, for an object in the hashkey must be unique, corresponding to a lock resource. When multiple transactions operate on a table at the same time, the requested lock is also the same memory object. To get the lock in mdl_locks, you need to protect _ mutex_lock (& m_mutex) with global mutexes; m_mutex is a member of MDL_map.

Locking process

A session connection corresponds to a THD entity in the implementation, and a THD corresponds to a MDL_CONTEXT, which represents the required mdl lock resources. A MDL_CONTEXT contains multiple MDL_REQUEST, and a MDL_REQUEST is a certain type of lock request to an object. There is a ticket object on each mdl_request, and the ticket contains lock.

The one that is locked is locked according to 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 if

If (lock can be granted to mdl_request)

Set lock to ticket

Set ticket to mdl_request

Else

Wait for lock

End if

To explain a little bit, first of all, check the mdl_request itself to see if there is an equivalent or stronger ticket, and if so, use it directly. Otherwise, create a ticket and find the lock corresponding to the locked object. If not, create it. Check whether lock can be assigned to this transaction, and if it can be returned directly, otherwise wait for the lock

Lock waiting and awakening

The lock wait of a dictionary object is caused by a lock on which two things are incompatible with the same object. Of course, because of the uniqueness of lock, first-come-first-served people can only wait.

How to determine whether a lock can be grant to a TX? This needs to be viewed in conjunction with the lock structure. There are two members on the lock, grant and wait,grant represent what locks are on the things allowed by this lock, and wait indicates which locks are required for the waiting transaction. The logic for judging whether a thing can be grant is as follows:

If (compatible (lock.grant, tx.locktype))

If (compatible (lock.wait, tx.locktype))

Return can_grant

End if

End if

That is to say, we first judge whether the lock type in grant is compatible with the current transaction, and then judge whether the lock type in wait is compatible with the current transaction. If you are careful, you will think that the lock types in wait do not need to be compared with the current transaction, does that mean that this comparison is superfluous? In fact, it is not, because the compatibility matrix of wait is different from the matrix above, and the compatibility matrix of wait feels like it is waiting for DDL to prevent DML from coming in (the wait matrix is not written out, you can take a look in the code).

For example:

TX1 TX2 TX3

SELECT T1

DROP T1

SELECT T1

At this time, TX2 will block, TX3 will also block, and will be blocked by TX2, that is to say, it will be blocked by the event of wait. This may be to ensure that DML is prohibited while DDL is waiting, because DML is really not so important in front of DDL.

How do you wake up a waiting transaction? For example, wake up TX2. When the TX1 ends, release_all_locks_for_name is called to wake up the locked transaction. The specific operation is encapsulated in the reschedule_waiters function, and the waiting time flag bit is reset 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)

}

So much for sharing about how to use MDL dictionary locks in MySQL. I hope the above content can be helpful to you and you can learn more. If you think the article is good, you can share it for more people to see.

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.