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

간략한 설명

차단된 쿼리는 InnoDB의 트랜잭션이 다른 트랜잭션이 잠금을 해제하기를 기다리고 있기 때문에 발생할 수 있습니다. 커밋되지 않은 트랜잭션 때문에 쿼리가 차단될 수도 있습니다. 이러한 거래는 NULL로 표시될 수 있습니다. 쿼리를 차단할 수 있는 쿼리 또는 세션을 식별하려면 다음 단계를 수행합니다.

해결 방법

커밋되지 않은 트랜잭션 식별

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;

참고: 차단된 트랜잭션은 다른 트랜잭션이 커밋되거나 롤백될 때까지 진행할 수 없습니다.

차단 트랜잭션을 식별할 때 쿼리를 시작한 세션이 유휴 상태가 되면 차단 쿼리에 대해 NULL 값이 보고됩니다. 이 경우 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 테이블을 쿼리합니다. 이 테이블은 스레드에 의해 실행된 마지막 쿼리를 확인합니다.

참고: THREAD_ID를 3단계에서 반환된 값으로 대체해야 합니다.

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

5.    차단 세션 또는 thread-ID를 식별한 후 다음 절차를 실행하여 트랜잭션을 중지합니다.

참고: 이 절차를 실행하기 전에 트랜잭션이 필요한지 또는 중지해도 안전한지 평가하세요.

CALL mysql.rds_kill(thread-ID);

참고: 장기 실행 작업을 중지하거나 롤백하는 것은 시간이 많이 걸리고 I/O 집약적일 수 있습니다.

관련 정보

세션 또는 쿼리 종료

MySQL DB 인스턴스 옵션

MySQL 웹사이트의 INFORMATION_SCHEMA_INNODB_TRX 테이블

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

MySQL DB 인스턴스에 대한 일반 DBA 태스크