MYSQL CONTENTION
SAFE OPERATING BOUNDARY
KILL QUERY·CONNECTION은 실행 전 상태로 되돌릴 수 없습니다. 애플리케이션 connection 재수립과 안전한 transaction 재시도, InnoDB rollback 진행을 확인합니다. index·SQL·설정 변경은 별도 migration의 down 절차와 실행 계획·성능 기준으로 복원하고, 데이터 변경 transaction은 백업과 업무 보정 절차를 사용합니다.
BEFORE YOU START
CHECK THE BRANCH
data_lock_waits의 요청·차단 transaction을 확인하고 열린 시간과 소유 애플리케이션을 찾습니다. blocker PID만 보고 즉시 KILL하면 미완료 transaction rollback과 서비스 오류가 커질 수 있습니다.
InnoDB는 보통 victim transaction 하나를 자동 rollback합니다. 애플리케이션의 제한된 재시도와 동일한 테이블·행 접근 순서, 짧은 transaction, 적절한 index가 핵심입니다.
performance_schema digest의 count, total·average latency, rows examined를 보고 후보를 좁힙니다. 단일 순간의 SHOW PROCESSLIST나 전체 일반 로그 활성화만으로 판단하지 않습니다.
테이블 변경은 열린 transaction과 충돌할 수 있습니다. DDL 재실행 전에 performance_schema.metadata_locks와 배포 이력을 확인하고 점검 시간·온라인 변경 전략을 별도로 세웁니다.
FOLLOW THE FLOW
mysql --login-path=<OPS_LOGIN_PATH>는 mysql_config_editor로 사전 구성한 최소 조회 계정을 뜻합니다. 비밀번호를 -pPASSWORD 인자로 넣지 않습니다.
mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT VERSION(), @@read_only, @@transaction_isolation;"mysql --login-path=<OPS_LOGIN_PATH> -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Slow_queries');"mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_DB,PROCESSLIST_COMMAND,PROCESSLIST_TIME,PROCESSLIST_STATE FROM performance_schema.threads WHERE TYPE='FOREGROUND' ORDER BY PROCESSLIST_TIME DESC LIMIT 30;"mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT COUNT(*) AS lock_waits FROM performance_schema.data_lock_waits;"lock_waits가 있으면 잠금 분기, 없지만 Threads_running·Slow_queries가 높으면 slow query/CPU·I/O 분기입니다. read_only 서버인지도 변경 전에 확인합니다.
장애 시작 시각과 애플리케이션 배포·배치 시각을 맞춰 보고 상세 대기 관계로 이동합니다.
transaction ID와 thread ID, 시작 시각을 확인하되 원문 SQL·바인드 값은 기본 출력하지 않습니다.
mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT REQUESTING_ENGINE_TRANSACTION_ID,BLOCKING_ENGINE_TRANSACTION_ID,REQUESTING_ENGINE_LOCK_ID,BLOCKING_ENGINE_LOCK_ID FROM performance_schema.data_lock_waits;"mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT trx_id,trx_state,trx_started,trx_wait_started,trx_mysql_thread_id,trx_rows_locked,trx_rows_modified FROM information_schema.innodb_trx ORDER BY trx_started;"mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE LOCK_STATUS='PENDING';"오래 열린 transaction이 많은 행을 수정하며 다른 transaction을 막는지, DDL metadata lock인지 구분합니다. blocker가 백업·마이그레이션·정산 작업일 수도 있습니다.
thread owner와 업무 영향, 자동 rollback 비용을 애플리케이션·DB 담당자와 확인합니다.
SHOW ENGINE INNODB STATUS에는 SQL·테이블·호스트 정보가 포함될 수 있으므로 제한된 화면에서 확인하고 공유 전 마스킹합니다.
mysql --login-path=<OPS_LOGIN_PATH> -e "SHOW ENGINE INNODB STATUS\G"mysql --login-path=<OPS_LOGIN_PATH> -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_deadlocks','Innodb_row_lock_current_waits','Innodb_row_lock_time','Innodb_row_lock_waits');"mysql --login-path=<OPS_LOGIN_PATH> -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_deadlock_detect','innodb_lock_wait_timeout','transaction_isolation');"LATEST DETECTED DEADLOCK의 두 transaction이 테이블·행을 반대 순서로 잠그는지 봅니다. 한 번의 deadlock은 정상적인 동시성 결과일 수 있지만 반복되면 코드·index·재시도 설계를 바꿔야 합니다.
deadlock detection·timeout 값을 즉흥적으로 바꾸지 말고 transaction 경계와 접근 순서를 우선 수정안으로 잡습니다.
리터럴이 제거된 performance_schema digest로 누적 부하를 비교하고 후보 SELECT의 실행 계획을 읽습니다.
mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_s,ROUND(AVG_TIMER_WAIT/1000000000,3) AS avg_ms,SUM_ROWS_EXAMINED,SUM_ROWS_SENT FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT IS NOT NULL ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;"mysql --login-path=<OPS_LOGIN_PATH> -e "SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','slow_query_log_file','long_query_time','min_examined_row_limit','log_output');"mysql --login-path=<OPS_LOGIN_PATH> -e "EXPLAIN FORMAT=TREE <REVIEWED_SELECT_WITHOUT_LITERALS>;"COUNT_STAR·total_s가 큰 빈번한 SQL과 avg_ms·rows examined가 큰 개별 SQL을 구분합니다. index가 있어도 낮은 선택도·잘못된 join 순서·lock 대기 때문에 느릴 수 있습니다.
index·쿼리 변경은 staging 계획과 배포·rollback을 준비하고, 긴 blocker 종료가 긴급히 필요한지는 별도 승인으로 판단합니다.
이 단계는 실행 중 요청이나 connection을 중단할 수 있습니다. thread owner, transaction 규모, 자동 재시도, 복제·업무 영향을 확인하고 아래 분기 중 승인된 하나만 실행합니다.
mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_DB,PROCESSLIST_COMMAND,PROCESSLIST_TIME,PROCESSLIST_STATE FROM performance_schema.threads WHERE PROCESSLIST_ID=<APPROVED_THREAD_ID>;"mysql --login-path=<DBA_LOGIN_PATH> -e "KILL QUERY <APPROVED_THREAD_ID>;"mysql --login-path=<DBA_LOGIN_PATH> -e "KILL CONNECTION <APPROVED_THREAD_ID>;"mysql --login-path=<OPS_LOGIN_PATH> -e "SELECT trx_id,trx_state,trx_started,trx_rows_locked,trx_rows_modified FROM information_schema.innodb_trx WHERE trx_mysql_thread_id=<APPROVED_THREAD_ID>; SELECT COUNT(*) AS lock_waits FROM performance_schema.data_lock_waits; SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_running','Innodb_deadlocks','Innodb_row_lock_current_waits','Slow_queries');"lock wait와 Threads_running이 줄고 애플리케이션 오류율·복제 지연이 회복돼야 합니다. KILL은 근본 해결이 아니며 rollback이 끝날 때까지 부하가 지속될 수 있습니다.
세션 종료는 되돌릴 수 없으므로 애플리케이션이 connection을 재수립하고 transaction을 안전하게 재시도하는지 확인합니다. 재발 방지는 짧은 transaction, 동일 잠금 순서, 적절한 index, 제한된 deadlock retry, digest·lock wait·slow query 알림을 코드와 운영 기준에 반영합니다.
PRIMARY REFERENCES
배포판과 버전에 따라 옵션·로그 위치가 다를 수 있습니다. 실행 전 서버의 --help와 로컬 매뉴얼을 함께 확인하세요.