MySQL DDL鎖表情況分析

這篇文章主要介紹“MySQL DDL鎖表情況分析”，在日常操作中，相信很多人在MySQL DDL鎖表情況分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL DDL鎖表情況分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

版本5.7.22，隔離級別RR

當DDL的表存在慢查詢時，此時對該表做DDL，由于無法獲得metadata鎖，所以會等待該鎖，造成鎖表，后續DML操作全部進入等待狀態。
session1：

session2：

session3：

session4：

tips:
select sleep(N) from t;表示查詢t的時間為t中的行數*N，如下：

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.
在線DDL操作完成之前，必須等待持有表上的元數據鎖的事務提交或回滾。在線DDL操作在執行階段可能會短暫地需要表上的獨占元數據鎖，并且在更新表定義時總是在操作的最后階段需要一個鎖。因此，持有表上的元數據鎖的事務可能導致在線DDL操作阻塞。表上持有元數據鎖的事務可能在DDL在線操作之前或期間啟動。在表上持有元數據鎖的長時間運行或不活動的事務可能導致在線DDL操作超時。
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:    DDL的在線操作可以分為三個階段:

• Phase 1: 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.
  在初始化階段，服務器考慮存儲引擎功能、語句中指定的操作以及用戶指定的算法和鎖選項，確定操作期間允許多少并發性。在此階段，使用共享的可升級元數據鎖來保護當前表定義。

• 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.
  在這個階段，語句被準備好并執行。元數據鎖是否升級為exclusive取決于初始化階段評估的因素。如果需要獨占元數據鎖，則只在語句準備期間進行短暫的鎖定。

• Phase 3: Commit 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.
  在提交表定義階段，將元數據鎖升級為exclusive，以刪除舊表定義并提交新表定義。一旦獲得授權，獨占元數據鎖的持續時間很短。
  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.
  由于上面列出的獨占元數據鎖需求，在線DDL操作可能必須等待持有表上的元數據鎖的并發事務提交或回滾。DDL操作之前或期間啟動的事務可以在被修改的表上持有元數據鎖。在長時間運行或不活動的事務的情況下，在線DDL操作可能會超時，等待獨占元數據鎖。此外，在線DDL操作請求的掛起的獨占元數據鎖會阻塞表上的后續事務。
  

到此，關于“MySQL DDL鎖表情況分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！