MySQL 교착 상태(Deadlock)란?
교착 상태(deadlock)는 두 개 이상의 트랜잭션이 서로가 보유한 잠금(lock)을 기다리면서 어느 쪽도 진행하지 못하고 멈춰 있는 상황을 의미합니다. MySQL의 InnoDB 스토리지 엔진은 이러한 교착 상태를 자동으로 감지하여 한쪽 트랜잭션을 강제로 롤백시키지만, 문제의 근본 원인을 분석하고 애플리케이션 로직을 개선하려면 관련 로그를 반드시 확인해야 합니다.
1단계: innodb_print_all_deadlocks 활성화
먼저 innodb_print_all_deadlocks 시스템 변수를 활성화해야 합니다. 이 옵션을 켜면 InnoDB에서 발생하는 모든 교착 상태 정보가 MySQL 오류 로그(error log)에 기록됩니다. 다음 구문을 실행하세요.
SET GLOBAL innodb_print_all_deadlocks = 1;이 설정은 동적으로 변경할 수 있으며, 서버를 재시작한 후에도 유지하려면 MySQL 설정 파일(my.cnf 또는 my.ini)에 아래와 같이 추가하는 것이 좋습니다.
[mysqld]
innodb_print_all_deadlocks = 12단계: SHOW ENGINE INNODB STATUS 실행
변수를 활성화한 후, 아래 명령어를 실행하면 InnoDB 엔진의 전체 상태 정보를 확인할 수 있습니다.
SHOW ENGINE INNODB STATUS;실제 실행 예시는 다음과 같습니다.
mysql> SET GLOBAL innodb_print_all_deadlocks = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS;이 명령을 실행하면 다음과 같은 InnoDB 모니터 출력 결과를 얻을 수 있습니다.
+--------+------+------------------------------------------------+
| Type | Name | Status |
+--------+------+------------------------------------------------+
| InnoDB | |
=====================================
2019-05-03 16:44:53 0xf9c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 38 srv_active, 0 srv_shutdown, 5120 srv_idle
------------
TRANSACTIONS
------------
Trx id counter 136292
Purge done for trx's n:o < 136292 undo n:o < 0 state: running but idle
History list length 27
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283092444694336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
1947 OS file reads, 971 OS file writes, 397 OS fsyncs
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 549453824
Buffer pool size 32768
Free buffers 30848
Database pages 1894
Number of rows inserted 80, updated 355, deleted 12, read 83378
----------------------------
END OF INNODB MONITOR OUTPUT
============================
+--------+------+------------------------------------------------+
1 row in set (0.00 sec)출력 결과 해석
위 출력에는 배경 스레드(BACKGROUND THREAD), 세마포어(SEMAPHORES), 트랜잭션(TRANSACTIONS), 파일 입출력(FILE I/O), 버퍼 풀 및 메모리(BUFFER POOL AND MEMORY), 행 연산(ROW OPERATIONS) 등 InnoDB 내부 상태에 대한 상세 정보가 포함되어 있습니다. 특히 최근에 감지된 교착 상태가 존재한다면 LATEST DETECTED DEADLOCK 섹션에서 관련 트랜잭션과 잠금 정보를 확인할 수 있습니다.
주의 사항
SHOW ENGINE INNODB STATUS는 가장 최근에 감지된 교착 상태 하나만 표시합니다.innodb_print_all_deadlocks = 1로 설정하면 발생한 모든 교착 상태가 오류 로그 파일에 기록되므로, 지속적인 모니터링과 이력 추적에는 오류 로그 확인이 더욱 유용합니다.- 해당 명령은 서버의 민감한 내부 정보를 노출하므로, 운영 환경에서는 충분한 권한을 가진 계정으로만 실행해야 합니다.