시스템 운영 중 특정 시간대마다 서비스 응답이 느려지거나 로그인이 실패하는 현상이 발생한다면, 데이터베이스 내의 트랜잭션 락(Lock) 경합을 의심해봐야 합니다. 특히 주기적으로 발생하는 문제는 자동화된 배치 작업이나 스케줄러에 의한 대량의 데이터 조작이 원인일 가능성이 높습니다. MySQL에서 발생한 락 대기 현상을 추적하고 분석하는 과정을 정리합니다.

1. 락 상태 확인을 위한 기본 쿼리

현재 데이터베이스에서 어떤 트랜잭션이 충돌하고 있는지 확인하기 위해 다음과 같은 시스템 뷰를 조회할 수 있습니다. (MySQL 5.7 이전 버전과 8.0 이상 버전은 조회 테이블이 다를 수 있으나, 여기서는 INFORMATION_SCHEMA를 기준으로 설명합니다.)

-- 진행 중인 전체 트랜잭션 상태 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 현재 잠금이 걸린 상태 확인 (InnoDB Lock)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 잠금으로 인해 대기 중인 트랜잭션 간의 관계 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 실시간으로 실행 중인 프로세스 목록 확인
SHOW FULL PROCESSLIST;

2. 트랜잭션 충돌 분석 사례

실제 조회 결과에서 특정 레코드에 대해 서로 다른 트랜잭션이 충돌하는 상황을 가정해 보겠습니다. INNODB_LOCKS 테이블에서 다음과 같은 데이터를 확인했다고 가정합니다.

위 데이터에서 트랜잭션 45982369는 S-Lock(공유 잠금)을 보유하고 있으며, 트랜잭션 45982383은 X-Lock(배타적 잠금)을 획득하려고 대기 중입니다. S-Lock이 걸린 상태에서 X-Lock을 얻으려 하면 충돌이 발생하여 대기 상태에 빠지게 됩니다.

3. 충돌을 유발하는 SQL 식별

잠금을 유발한 원인 쿼리를 분석하면 대개 다음과 같은 구조적 문제가 발견됩니다.

S-Lock(공유 잠금) 발생 쿼리 예시

대량의 데이터를 조인하여 다른 테이블에 삽입하는 배치 쿼리는 참조하는 테이블에 공유 잠금을 걸 수 있습니다.

INSERT INTO audit_log.user_sync_history (
    user_idx,
    dept_id,
    dept_name,
    sync_ts
)
SELECT 
    u.id,
    d.dept_id,
    d.dept_name,
    NOW()
FROM 
    core_db.user_master u
INNER JOIN 
    core_db.dept_info d ON u.dept_id = d.dept_id
WHERE 
    u.status = 'ACTIVE'
ON DUPLICATE KEY UPDATE 
    sync_ts = VALUES(sync_ts);

X-Lock(배타적 잠금) 대기 쿼리 예시

동일한 사용자의 정보를 수정하려는 일반적인 비즈니스 로직 쿼리가 위 배치 작업 때문에 차단됩니다.

UPDATE core_db.user_master 
SET last_login_at = '2023-10-27 10:18:00' 
WHERE login_id = 'user_01';

이 경우 배치 작업이 끝날 때까지 일반 사용자의 로그인 정보 업데이트가 중단되어 시스템 전체의 로그인 장애로 이어질 수 있습니다.

4. 응급 조치: 블로킹 프로세스 강제 종료

운영 환경에서 서비스 불능 상태를 즉시 해소해야 할 경우, 락을 점유하고 있는 세션을 찾아 강제로 종료해야 합니다. Bash 스크립트를 사용하여 Locked 상태인 프로세스를 추출하고 종료하는 방법입니다.

#!/bin/bash

# 락이 걸린 프로세스 아이디 추출 및 kill 명령어 생성
mysql -u [USER] -p[PASSWORD] -e "SHOW PROCESSLIST" | grep -i "Locked" | awk '{print "KILL " $1 ";"}' > kill_list.sql

# 생성된 스크립트가 비어있지 않은지 확인 후 실행
if [ -s kill_list.sql ]; then
    echo "잠긴 세션을 종료합니다..."
    mysql -u [USER] -p[PASSWORD] < kill_list.sql
else
    echo "종료할 락 세션이 없습니다."
fi

MySQL 쉘 내에서 직접 실행할 때는 INNODB_TRX 테이블의 trx_mysql_thread_id를 확인하여 KILL [THREAD_ID]; 명령을 수행합니다.

5. 개선 방향

근본적인 해결을 위해서는 다음과 같은 최적화가 필요합니다.

• 트랜잭션 범위 최소화: 대량 데이터 처리 시 한 번에 처리하지 않고 LIMIT 등을 이용해 배치 크기를 나누어 수행합니다.
• 인덱스 최적화: UPDATE나 DELETE 시 인덱스가 적절히 설정되지 않으면 레코드 락이 아닌 테이블 풀 스캔(Gap Lock 포함)이 발생할 확률이 높습니다.
• 격리 수준 검토: 서비스 특성에 따라 READ COMMITTED 격리 수준을 사용하여 락 경합을 줄이는 방안을 검토합니다.