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

2 分的閱讀內容

當我在我的 Amazon Relational Database Service (Amazon RDS) 上針對 MySQL 資料庫執行個體執行查詢時，查詢遭到封鎖。當時沒有正在執行的其他查詢。我想對此問題進行疑難排解。

解決方法

之所以會發生封鎖查詢，是因為 InnoDB 中的交易正在等待另一個交易釋放鎖定。未提交的交易也可能會封鎖查詢。這些交易會顯示為 NULL。

使用 INNODB_TRX 資料表來識別未提交的交易

若要識別封鎖查詢的查詢或工作階段，請完成下列步驟：

若要檢視目前執行中的交易，請針對 INNODB_TRX 資料表執行下列查詢：

select * from information_schema.innodb_trx\G

若要檢視正在等待的交易以及封鎖該交易的交易，請根據您的 MySQL 版本執行下列其中一個查詢。
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;

**注意：**只有在提交或回復其他交易後，封鎖的交易才能繼續進行。當發出查詢的工作階段閒置時，會報告 NULL 值來表示封鎖查詢。在這種情況下，請使用先前的查詢來尋找 blocking_thread PROCESSLIST_ID。

對於 MySQL 5.7 或更高版本，請執行下列查詢，透過替代封鎖的 THREAD_ID 來判斷封鎖交易的 PROCESSLIST_ID：

SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

使用 THREAD_ID 對 events_statements_current 資料表執行下列查詢，以確定執行緒執行的最後一個查詢：

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 1;

**注意：**以您在步驟 3 中收到的值取代 THREAD_ID。

執行下列命令以停止交易：
```
CALL mysql.rds_kill(PROCESSLIST_ID);
```
**注意：**停止或回復長期執行的交易十分耗時，而且需要處理大量 I/O。

識別 XA 封鎖交易的情形

如果 blocking_thread 為 0，並且 CALL mysql.rds_kill(PROCESSLIST_ID); 無效，則 XA 交易可能會造成封鎖。

若要識別造成封鎖的 XA 交易，請執行下列命令：

XA RECOVER;

先前的指令會傳回處於「已準備」狀態之 XA 交易的輸出。若要停止封鎖，請使用資料欄來回復或提交交易：

XA RECOVER;

+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
|   1      |           10 |            0 | RePostTest         |
+----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

XA COMMIT 'RePostTest';
XA ROLLBACK 'RePostTest';

如果您在執行提交或回復時收到下列錯誤，請將 XID 轉換為十六進位並回復交易：

「錯誤 1397 (XAE04)： XAER_NOTA：未知的 XID」

若要恢復交易，請執行下列命令以擷取 gtrid_length 和 bqual_length，進而擷取資料欄位的必要部分：

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------------------------+
| formatID | gtrid_length | bqual_length | data                                   |
+----------+--------------+--------------+----------------------------------------+
|   1      |           10 |            0 | 0x5265506F737454657374                 |
+----------+--------------+--------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> XA ROLLBACK X'5265506F737454657374';
Query OK, 0 rows affected (0.01 sec)

如需如何轉換 XID 值的詳細資訊，請參閱 MySQL 網站上的 XA 交易 SQL 陳述式。