MySQL을 실행 중인 Amazon Relational Database Service(RDS) 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 태스크