當沒有其他作用中的工作階段時，為什麼對 Amazon RDS MySQL 資料庫執行個體的查詢會遭到封鎖？

簡短描述

之所以會發生封鎖查詢是因為 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 任務