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

This article mainly introduces "MySQL DDL Lock Table situation Analysis". In daily operation, I believe many people have doubts about MySQL DDL Lock Table situation Analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL DDL Lock Table situation Analysis". Next, please follow the editor to study!

Version 5.7.22, isolation level RR

When the DDL table is stored in a slow query, DDL the table at this time. Because the metadata lock cannot be obtained, it will wait for the lock, causing the table to be locked, and all subsequent DML operations will enter the waiting state.

Session1:

Session2:

Session3:

Session4:

Tips:

Select sleep (N) from t; indicates that the time of query t is the number of rows in t * N, as follows:

Before an online DDL operation can finish, it must wait for transactions that hold metadata locks on the table to commit or roll back. An online DDL operation may briefly require an exclusive metadata lock on the table during its execution phase, and always requires one in the final phase of the operation when updating the table definition. Consequently, transactions holding metadata locks on the table can cause an online DDL operation to block. The transactions that hold metadata locks on the table may have been started before or during the online DDL operation. A long running or inactive transaction that holds a metadata lock on the table can cause an online DDL operation to timeout.

Before the online DDL operation completes, you must wait for the transaction holding the metadata lock on the table to be committed or rolled back. An online DDL operation may briefly require an exclusive metadata lock on a table during the execution phase, and a lock is always required at the last stage of the operation when updating the table definition. Therefore, transactions that hold metadata locks on the table may cause online DDL operations to block. A transaction that holds a metadata lock on a table may be started before or during an DDL online operation. Long-running or inactive transactions that hold metadata locks on the table may cause online DDL operations to time out.

Https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

Online DDL and Metadata Locks

Online DDL operations can be viewed as having three phases: the online operation of DDL can be divided into three stages:

Phase 1: Initialization initialization

In the initialization phase, the server determines how much concurrency is permitted during the operation, taking into account storage engine capabilities, operations specified in the statement, and user-specified ALGORITHM and LOCK options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition.

During the initialization phase, the server determines how much concurrency is allowed during the operation, taking into account the functionality of the storage engine, the actions specified in the statement, and the algorithm and locking options specified by the user. At this stage, the current table definition is protected with shared scalable metadata locks.

Phase 2: Execution

In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation.

At this stage, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors evaluated during the initialization phase. If an exclusive metadata lock is required, it is locked only briefly during statement preparation.

Phase 3: Commit Table Definition submission table definition

In the commit table definition phase, the metadata lock is upgraded to exclusive to evict the old table definition and commit the new one. Once granted, the duration of the exclusive metadata lock is brief.

During the commit table definition phase, upgrade the metadata lock to exclusive to delete the old table definition and submit the new table definition. Once authorized, the duration of the exclusive metadata lock is short.

Due to the exclusive metadata lock requirements outlined above, an online DDL operation may have to wait for concurrent transactions that hold metadata locks on the table to commit or rollback. Transactions started before or during the DDL operation can hold metadata locks on the table being altered. In the case of a long running or inactive transaction, an online DDL operation can time out waiting for an exclusive metadata lock. Additionally, a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table.

Due to the exclusive metadata lock requirements listed above, online DDL operations may have to wait for the concurrent transaction of the metadata lock on the holding table to be committed or rolled back. Transactions started before or during the DDL operation can hold metadata locks on the modified table. In the case of long-running or inactive transactions, online DDL operations may time out and wait for an exclusive metadata lock. In addition, pending exclusive metadata locks requested by online DDL operations block subsequent transactions on the table.

At this point, the study of "MySQL DDL lock table analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.