我嘗試在執行 MySQL 的 Amazon Relational Database Service (Amazon RDS) 資料庫執行個體上執行查詢,但查詢遭到封鎖。當時沒有執行其他查詢。為什麼查詢遭到封鎖,我該如何解決此問題?
簡短描述
之所以會發生封鎖查詢是因為 InnoDB 中的交易正在等待另一個交易釋放鎖定。查詢也可能會因未認可的交易而遭到封鎖。這些交易可以顯示為空值。請依照下列步驟識別可能會封鎖查詢的查詢或工作階段。
解決方法
識別未認可的交易
1. 針對 INNODB_TRX 資料表執行此查詢,以檢視目前執行中的交易:
select * from information_schema.innodb_trx\G
2. 執行此查詢以查看哪些交易正在等待,以及哪些交易正在封鎖這些交易。
對於 MySQL 5.7 及更早版本:
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;
對於 MySQL 8.0:
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id;
**注意:**在其他交易提交或回復之前,封鎖的交易無法繼續進行。
識別封鎖交易時,如果發出查詢的工作階段已閒置,則會為封鎖查詢報告空值。在此情況下,請使用步驟 2 中的查詢來尋找 blocking_thread processlist ID。
3. 對於 MySQL 5.7 或更高版本,請執行此查詢,透過替代 blocking_thread processlist ID 來判斷封鎖交易的 THREAD_ID:
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;
4. 使用 THREAD_ID 來查詢效能結構描述 events_statements_current 資料表。這會決定執行緒執行的最後一個查詢。
**注意:**請確保用步驟 3 中傳回的值替換 THREAD_ID。
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;
5. 識別封鎖工作階段或執行緒 ID之後,請執行下列程序停止交易:
注意: 執行此程序之前,請先評估您是否需要該交易或該交易是否可安全停止。
CALL mysql.rds_kill(thread-ID);
注意: 停止或復原長時間執行的操作可能非常耗時,而且需要大量 I/O。
相關資訊
結束工作階段或查詢
MySQL 資料庫執行個體的選項
MySQL 網站上的 INFORMATION_SCHEMA_INNODB_TRX 資料表
識別 MySQL 網站上的封鎖交易
MySQL 資料庫執行個體的常見 DBA 任務