共計 2963 個字符，預(yù)計需要花費 8 分鐘才能閱讀完成。
這篇文章主要介紹“MySQL DDL 鎖表情況分析”，在日常操作中，相信很多人在 MySQL DDL 鎖表情況分析問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL DDL 鎖表情況分析”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學(xué)習(xí)吧！
版本 5.7.22，隔離級別 RR
當(dāng) DDL 的表存在慢查詢時，此時對該表做 DDL，由于無法獲得 metadata 鎖，所以會等待該鎖，造成鎖表，后續(xù) DML 操作全部進(jìn)入等待狀態(tài)。
session1：
session2：
session3：
session4：
tips:
select sleep(N) from t; 表示查詢 t 的時間為 t 中的行數(shù) *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 操作完成之前，必須等待持有表上的元數(shù)據(jù)鎖的事務(wù)提交或回滾。在線 DDL 操作在執(zhí)行階段可能會短暫地需要表上的獨占元數(shù)據(jù)鎖，并且在更新表定義時總是在操作的最后階段需要一個鎖。因此，持有表上的元數(shù)據(jù)鎖的事務(wù)可能導(dǎo)致在線 DDL 操作阻塞。表上持有元數(shù)據(jù)鎖的事務(wù)可能在 DDL 在線操作之前或期間啟動。在表上持有元數(shù)據(jù)鎖的長時間運行或不活動的事務(wù)可能導(dǎo)致在線 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.
在初始化階段，服務(wù)器考慮存儲引擎功能、語句中指定的操作以及用戶指定的算法和鎖選項，確定操作期間允許多少并發(fā)性。在此階段，使用共享的可升級元數(shù)據(jù)鎖來保護(hù)當(dāng)前表定義。
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.
在這個階段，語句被準(zhǔn)備好并執(zhí)行。元數(shù)據(jù)鎖是否升級為 exclusive 取決于初始化階段評估的因素。如果需要獨占元數(shù)據(jù)鎖，則只在語句準(zhǔn)備期間進(jìn)行短暫的鎖定。
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.
在提交表定義階段，將元數(shù)據(jù)鎖升級為 exclusive，以刪除舊表定義并提交新表定義。一旦獲得授權(quán)，獨占元數(shù)據(jù)鎖的持續(xù)時間很短。
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.
由于上面列出的獨占元數(shù)據(jù)鎖需求，在線 DDL 操作可能必須等待持有表上的元數(shù)據(jù)鎖的并發(fā)事務(wù)提交或回滾。DDL 操作之前或期間啟動的事務(wù)可以在被修改的表上持有元數(shù)據(jù)鎖。在長時間運行或不活動的事務(wù)的情況下，在線 DDL 操作可能會超時，等待獨占元數(shù)據(jù)鎖。此外，在線 DDL 操作請求的掛起的獨占元數(shù)據(jù)鎖會阻塞表上的后續(xù)事務(wù)。
到此，關(guān)于“MySQL DDL 鎖表情況分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
