다른 활성 세션이 없는데 Amazon RDS for MySQL DB 인스턴스에 대한 쿼리가 차단된 이유는 무엇입니까?

해결 방법

차단된 쿼리는 InnoDB의 트랜잭션이 다른 트랜잭션이 잠금 해제하기를 기다리고 있기 때문에 발생합니다. 커밋되지 않은 트랜잭션은 쿼리를 차단할 수도 있습니다. 이러한 트랜잭션은 NULL로 표시됩니다.

INNODB_TRX 테이블을 사용하여 커밋되지 않은 트랜잭션 식별

쿼리를 차단하는 쿼리 또는 세션을 식별하려면 다음 단계를 완료하십시오.

1. 현재 실행 중인 트랜잭션을 보려면 INNODB_TRX 테이블에 대해 다음 쿼리를 실행합니다.

   select * from information_schema.innodb_trx\G

2. 대기 중인 트랜잭션과 이를 차단하고 있는 트랜잭션을 보려면 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를 찾으십시오.

3. MySQL 5.7 이상의 경우 다음 쿼리를 실행하여 대체 차단 THREAD_ID로 차단 트랜잭션의 PROCESSLIST_ID를 확인합니다.

   SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

4. events_statements_current 테이블에서 THREAD_ID를 사용해 다음 쿼리를 실행하여 스레드가 마지막으로 실행한 쿼리를 확인합니다.

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

   참고: THREAD_ID를 3단계에서 받은 값으로 바꾸십시오.

5. 다음 명령을 실행하여 트랜잭션을 중지합니다.

   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를 16진수로 변환하고 트랜잭션을 롤백합니다.

"ERROR 1397 (XAE04): XAER_NOTA: Unknown 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 문을 참조하십시오.

관련 정보

RDS for MySQL에 대한 세션 또는 쿼리 종료

MySQL DB 인스턴스에 대한 옵션

MySQL 웹사이트의 INFORMATION_SCHEMA_INNODB_TRX 테이블

MySQL 웹사이트의 차단 트랜잭션 식별

MySQL DB 인스턴스의 일반적인 DBA 작업