MySQL 잠금 대기 관계 읽는 법: data_locks와 data_lock_waits로 차단 세션 찾기
기본 키로 한 행만 수정하는 UPDATE가 끝나지 않으면 대부분 실행계획이나 인덱스부터 확인합니다. 그런데 WHERE id = 1처럼 기본 키 동등 조건으로 한 행을 고치는 문장은 실행계획 때문에 오래 걸릴 일이 거의 없습니다. 이런 경우에는 다른 세션이 같은 행의 잠금을 쥐고 있는지부터 확인하는 편이 빠릅니다.
까다로운 점은 잠금을 쥐고 있는 세션이 Sleep 상태로 보인다는 것입니다. 실행 중인 SQL이 없으니 목록에서 지나치기 쉽습니다. 하지만 SQL 실행이 끝난 것과 트랜잭션이 끝난 것은 다릅니다. COMMIT이나 ROLLBACK을 하지 않은 세션은 Sleep 상태로 있어도 잠금을 계속 보유합니다.
이 글에서는 MySQL 8.4.11에서 이 상황을 직접 재현했습니다. 46번 세션이 Sleep 상태인 40번 세션을 기다리는 상황을 만들고, performance_schema.data_locks로 잠금을, data_lock_waits로 대기 방향을 확인한 뒤 트랜잭션 종료 전후 결과를 비교했습니다. 캡처의 각 값을 어떻게 읽어야 하는지, 운영에서 어디까지 판단해도 되는지를 중심으로 정리합니다.
실습 환경과 테스트 데이터
실습은 별도 스키마에서 진행했습니다. 같은 서버에 독립된 연결 세 개를 열고 역할을 나눴습니다. 세션 A는 잠금을 쥐고, 세션 B는 같은 행을 수정하며 기다리고, 세션 C는 두 세션을 관찰합니다.
| 항목 | 실험 조건 |
|---|---|
| 서버 버전 | MySQL Community 8.4.11 |
| 환경 | Windows 로컬 설치 MySQL, MySQL Workbench |
| 스키마와 테이블 | lock_wait_lab_24.account_balance |
| 스토리지 엔진 | InnoDB |
| 데이터와 인덱스 | 3행, PRIMARY KEY(id) 하나 |
| 세션 A·B | REPEATABLE READ, 명시적 트랜잭션(START TRANSACTION) |
| 관찰 세션 C | autocommit이 켜진 별도 연결 |
| B의 잠금 대기 제한 | 캡처 시간을 확보하려고 세션 값만 600초로 변경 |
| 비교 기준 | 트랜잭션 종료 전후의 잠금 관계와 id=1의 최종 잔액 |
600초는 캡처 도중 B가 타임아웃으로 빠져나가지 않게 하려고 넣은 실습용 값입니다. 기본값은 50초이며, 잠금 문제를 풀기 위해 운영에서 이 값을 늘리라는 의미가 아닙니다.
테스트 데이터는 다음 SQL로 만들었습니다. 이미 테이블이 있다면 다시 실행하지 않습니다.
CREATE DATABASE lock_wait_lab_24;
USE lock_wait_lab_24;
CREATE TABLE account_balance (
id INT NOT NULL,
owner_name VARCHAR(30) NOT NULL,
balance INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO account_balance (id, owner_name, balance)
VALUES
(1, 'account_1', 1000),
(2, 'account_2', 2000),
(3, 'account_3', 3000);
COMMIT;실습을 시작하기 전에 관찰 세션 C에서 버전, Performance Schema 활성화 여부, 테스트 행 수를 한 번에 확인했습니다. data_locks와 data_lock_waits는 Performance Schema 테이블이므로 이 기능이 꺼져 있으면 이후 단계가 의미가 없습니다.
SELECT
VERSION() AS mysql_version,
@@GLOBAL.performance_schema AS performance_schema_on,
@@SESSION.autocommit AS observer_autocommit,
(
SELECT COUNT(*)
FROM lock_wait_lab_24.account_balance
) AS test_rows;
결과는 8.4.11, true, true, 3이었습니다. 여기서 autocommit 값은 조회를 실행한 세션 C의 값입니다. 세션 A와 B는 START TRANSACTION으로 명시적 트랜잭션을 열기 때문에, 이 값으로 다른 연결의 트랜잭션 상태를 판단하면 안 됩니다.
SELECT *
FROM lock_wait_lab_24.account_balance
ORDER BY id;
초기 잔액은 id 1, 2, 3이 각각 1000, 2000, 3000입니다. 이번 실습에서는 id=1 한 행에서만 경합을 만듭니다. 이 화면은 잠금을 만들기 전의 상태이며, 대기 도중의 잔액을 측정한 결과가 아닙니다.
재현: 잠금을 쥔 채 Sleep으로 남은 세션 만들기
세션 A와 B에서 각각 다음 설정을 적용합니다. CONNECTION_ID() 값이 서로 다른지 확인해 두면, 이후 Performance Schema 결과에서 어느 쪽이 A이고 어느 쪽이 B인지 구분할 수 있습니다. 이번 실습에서는 A가 40번, B가 46번이었습니다.
USE lock_wait_lab_24;
SET SESSION transaction_isolation = 'REPEATABLE-READ';
SELECT CONNECTION_ID();B에서는 원래 대기 제한 값을 저장해 둔 뒤 600초로 변경합니다. 실습이 끝나면 이 변수로 복구합니다.
SET @saved_lock_wait_timeout :=
@@SESSION.innodb_lock_wait_timeout;
SET SESSION innodb_lock_wait_timeout = 600;세션 A에서 id=1의 잔액을 100 차감하고 COMMIT하지 않습니다. UPDATE 문장 자체는 즉시 끝나고, A는 다음 명령을 기다리는 Sleep 상태가 됩니다.
-- 세션 A (40번)
START TRANSACTION;
UPDATE lock_wait_lab_24.account_balance
SET balance = balance - 100
WHERE id = 1;이어서 세션 B에서 같은 행에 50을 더합니다. 이 UPDATE는 결과를 돌려주지 않고 실행 중으로 남습니다.
-- 세션 B (46번)
START TRANSACTION;
UPDATE lock_wait_lab_24.account_balance
SET balance = balance + 50
WHERE id = 1;애플리케이션에서 이 상황은 보통 이렇게 나타납니다. 트랜잭션 안에서 UPDATE를 실행한 뒤 외부 API 응답을 기다리거나, 예외가 발생한 경로에서 COMMIT이나 ROLLBACK 없이 커넥션이 풀로 반환되는 경우입니다. 사용자가 보는 증상은 “단순한 UPDATE 하나가 안 끝난다”뿐입니다.
1단계 증상 확인: threads에서 보이는 것과 보이지 않는 것
A의 트랜잭션을 열어 둔 상태에서 관찰 세션 C로 실습 스키마에 붙은 세션을 조회했습니다. SHOW PROCESSLIST 대신 performance_schema.threads를 사용하면 조건과 컬럼을 원하는 대로 고를 수 있습니다.
SELECT
PROCESSLIST_ID AS conn_id,
PROCESSLIST_COMMAND AS command_name,
PROCESSLIST_TIME AS state_seconds,
PROCESSLIST_STATE AS state_name,
LEFT(PROCESSLIST_INFO, 140) AS current_sql
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
AND PROCESSLIST_DB = 'lock_wait_lab_24'
AND PROCESSLIST_ID <> CONNECTION_ID()
ORDER BY PROCESSLIST_ID;
| conn_id | command_name | state_seconds | state_name | current_sql |
|---|---|---|---|---|
| 40 | Sleep | 31 | NULL | NULL |
| 46 | Query | 21 | updating | UPDATE … SET balance = balance + 50 WHERE id = 1 |
이 화면만 보면 46번이 문제처럼 보입니다. 실행 중인 SQL이 있고 상태가 updating이기 때문입니다. 반대로 40번은 SQL이 NULL이라 아무 일도 하지 않는 세션처럼 보입니다. 실제 원인은 그 반대입니다.
이 화면에서 읽을 수 있는 것과 읽을 수 없는 것을 나누면 다음과 같습니다.
- 읽을 수 있는 것: 46번이 id=1을 수정하는 UPDATE를 실행 중이며, 40번은 현재 실행 중인 문장이 없습니다.
- 읽을 수 없는 것: 40번이 열린 트랜잭션을 가지고 있는지, 46번이 CPU 작업 중인지 잠금 대기 중인지입니다. updating이라는 상태 문자열에는 잠금 대기라는 표현이 들어 있지 않습니다.
state_seconds 값도 주의해서 읽어야 합니다. 40번의 31은 마지막 명령이 끝난 뒤 Sleep으로 머문 시간이고, 46번의 21은 현재 상태가 이어진 시간입니다. 46번이 정확히 21초 동안 행 잠금만 기다렸다는 측정값은 아닙니다. 다만 차단 의심 세션의 Sleep 시간이 대기 세션의 실행 시간보다 길다는 점은 “먼저 잠금을 잡고 손을 놓은 세션”이라는 가설과 맞아떨어지는 단서가 됩니다. 단서일 뿐 증거는 아니므로 다음 단계에서 잠금 정보로 확인합니다.
2단계 잠금 확인: data_locks 네 행을 해석하는 방법
이 시점의 가설은 이렇습니다. 40번의 UPDATE는 끝났지만 트랜잭션이 종료되지 않아 id=1의 잠금이 남아 있고, 46번은 같은 레코드의 배타적 잠금을 기다리고 있습니다. 이를 확인하려면 잠금의 소유자와 상태를 직접 봐야 합니다.
data_locks는 THREAD_ID만 가지고 있으므로, 접속 번호로 읽으려면 threads와 연결합니다.
SELECT
t.PROCESSLIST_ID AS conn_id,
l.OBJECT_NAME AS table_name,
l.INDEX_NAME AS index_name,
l.LOCK_TYPE AS lock_type,
l.LOCK_MODE AS lock_mode,
l.LOCK_STATUS AS lock_status,
l.LOCK_DATA AS lock_data
FROM performance_schema.data_locks AS l
LEFT JOIN performance_schema.threads AS t
ON t.THREAD_ID = l.THREAD_ID
WHERE l.ENGINE = 'INNODB'
AND l.OBJECT_SCHEMA = 'lock_wait_lab_24'
AND l.OBJECT_NAME = 'account_balance'
ORDER BY
t.PROCESSLIST_ID,
l.LOCK_TYPE,
l.LOCK_STATUS;
| 세션 | index_name | lock_type | lock_mode | lock_status | lock_data |
|---|---|---|---|---|---|
| 40 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| 40 | NULL | TABLE | IX | GRANTED | NULL |
| 46 | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| 46 | NULL | TABLE | IX | GRANTED | NULL |
네 행을 하나씩 읽으면 다음과 같습니다.
- X,REC_NOT_GAP: X는 배타적 잠금, REC_NOT_GAP은 갭을 포함하지 않고 레코드 자체만 잠근다는 뜻입니다. 존재하는 기본 키를 동등 조건으로 찾았기 때문에 앞뒤 갭까지 잠그지 않았습니다. 같은 UPDATE라도 범위 조건이나 인덱스가 없는 컬럼을 조건으로 쓰면 다른 형태의 잠금이 나타날 수 있습니다.
- TABLE의 IX: 하위 레코드에 배타적 잠금을 걸 예정이라는 의도 잠금입니다. IX끼리는 서로 호환되므로 두 세션 모두 GRANTED입니다. TABLE이라는 글자만 보고 테이블 전체가 잠겼다고 해석하면 안 됩니다.
- GRANTED와 WAITING: 쿼리나 트랜잭션의 상태가 아니라 개별 잠금 요청의 상태입니다. 46번은 테이블 IX는 이미 받았고, 레코드 잠금 단계에서 멈춰 있습니다.
40번은 Sleep 상태인데도 PRIMARY 키 1의 배타적 잠금을 GRANTED로 보유하고 있습니다. 1단계 화면에서 SQL이 NULL이었던 세션이 실제 잠금 소유자라는 사실이 여기서 드러납니다.
다만 이 결과만으로 “46번을 막는 것은 40번”이라고 확정하면 안 됩니다. 이번 실습에서는 세션이 둘뿐이라 분명해 보이지만, 운영 환경에서는 같은 키에 여러 세션이 GRANTED나 WAITING으로 걸려 있을 수 있습니다. lock_data가 같다는 이유로 차단 관계를 추정하지 않고, 실제 대기 관계를 연결해서 확인합니다.
3단계 관계 확정: data_lock_waits로 누가 누구를 막는지 연결하기
data_locks는 잠금 하나하나의 정보이고, data_lock_waits는 “어떤 요청이 어떤 잠금에 막혀 있는지”라는 관계만 담고 있습니다. 관계 테이블에는 잠금 모드나 키 값이 없기 때문에, 요청 측과 차단 측 정보를 함께 보려면 data_locks를 두 번 연결해야 합니다.
SELECT
wt.PROCESSLIST_ID AS waiting_conn,
bt.PROCESSLIST_ID AS blocking_conn,
bt.PROCESSLIST_COMMAND AS blocker_cmd,
r.OBJECT_NAME AS table_name,
r.INDEX_NAME AS index_name,
r.LOCK_DATA AS waiting_key,
b.LOCK_DATA AS blocking_key,
CONCAT(
r.LOCK_MODE, ' / ', r.LOCK_STATUS
) AS requested_lock,
CONCAT(
b.LOCK_MODE, ' / ', b.LOCK_STATUS
) AS blocking_lock
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
ON r.ENGINE = w.ENGINE
AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
ON b.ENGINE = w.ENGINE
AND b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS wt
ON wt.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads AS bt
ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
WHERE w.ENGINE = 'INNODB'
AND r.OBJECT_SCHEMA = 'lock_wait_lab_24'
AND r.OBJECT_NAME = 'account_balance'
ORDER BY waiting_conn, blocking_conn;
| waiting_conn | blocking_conn | blocker_cmd | index_name | key | requested_lock | blocking_lock |
|---|---|---|---|---|---|---|
| 46 | 40 | Sleep | PRIMARY | 1 / 1 | X,REC_NOT_GAP / WAITING | X,REC_NOT_GAP / GRANTED |
결과는 한 행입니다. 46번의 X,REC_NOT_GAP 요청이 WAITING이고, 이를 막는 것은 40번이 GRANTED로 보유한 같은 모드의 잠금입니다. blocker_cmd가 Sleep으로 함께 표시되므로 “실행 중인 SQL이 없는 세션이 잠금을 쥐고 있다”는 가설이 한 줄로 확정됩니다.
이 쿼리를 다른 환경에 가져다 쓸 때 헷갈리기 쉬운 식별자는 다음과 같습니다.
| 식별자 | 용도 |
|---|---|
| ENGINE + ENGINE_LOCK_ID | data_lock_waits와 data_locks를 연결하는 실제 키입니다. |
| REQUESTING_ENGINE_LOCK_ID | 기다리고 있는 잠금 요청을 가리킵니다. |
| BLOCKING_ENGINE_LOCK_ID | 그 요청을 막고 있는 잠금을 가리킵니다. |
| THREAD_ID | Performance Schema 내부 스레드 번호입니다. 접속 번호와 다릅니다. |
| PROCESSLIST_ID | CONNECTION_ID(), KILL 명령에 쓰는 접속 번호입니다. |
LOCK_DATA는 사람이 대상 키를 읽기 위한 값이지 조인 키가 아닙니다. ENGINE_LOCK_ID 문자열을 쪼개 의미를 추정하는 방식도 버전에 따라 형식이 달라질 수 있어 피합니다. 또한 한 요청이 여러 잠금에 막히거나 한 세션이 여러 요청을 막을 수 있으므로, 결과 행 수를 곧바로 대기 세션 수로 해석하지 않습니다.
4단계 해소 검증: 트랜잭션 종료 전후 비교
차단 원인을 확인했으므로 세션 A에서 트랜잭션을 종료합니다.
-- 세션 A (40번)
COMMIT;A의 잠금이 풀리자 대기 중이던 B의 UPDATE가 바로 완료되었습니다. 여기서 주의할 점은 B의 UPDATE가 끝났다고 B의 트랜잭션까지 끝난 것은 아니라는 점입니다. 이 시점의 B는 방금 전의 A와 같은 상태, 즉 id=1의 잠금을 쥔 채 Sleep으로 남은 세션입니다. 그래서 B도 COMMIT한 뒤에 최종 상태를 확인합니다.
-- 세션 B (46번), UPDATE 완료 확인 후
COMMIT;
SET SESSION innodb_lock_wait_timeout = @saved_lock_wait_timeout;관찰 세션 C에서 최종 잔액, 남은 잠금 수, 남은 대기 관계 수를 한 번에 조회했습니다.
SELECT
(
SELECT balance
FROM lock_wait_lab_24.account_balance
WHERE id = 1
) AS final_balance,
(
SELECT COUNT(*)
FROM performance_schema.data_locks
WHERE ENGINE = 'INNODB'
AND OBJECT_SCHEMA = 'lock_wait_lab_24'
AND OBJECT_NAME = 'account_balance'
) AS remaining_locks,
(
SELECT COUNT(*)
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
ON r.ENGINE = w.ENGINE
AND r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
WHERE w.ENGINE = 'INNODB'
AND r.OBJECT_SCHEMA = 'lock_wait_lab_24'
AND r.OBJECT_NAME = 'account_balance'
) AS remaining_wait_edges;
| 비교 항목 | 트랜잭션 종료 전 | 두 트랜잭션 종료 후 |
|---|---|---|
| 잠금 대기 관계 | 1개 (46번 → 40번) | 0개 |
| 대상 테이블의 data_locks 행 | 4개 | 0개 |
| B의 UPDATE | 레코드 잠금 대기 | 완료 후 COMMIT |
| id=1 잔액 | 초기값 1000 | 950 |
최종 잔액 950은 초기값 1000에서 A가 100을 빼고 B가 50을 더한 값입니다. 두 변경이 모두 사라지지 않고 순서대로 반영되었다는 뜻입니다. B는 잠금을 기다린 뒤 A가 확정한 900을 기준으로 50을 더했습니다.
종료 전의 잠금 4개는 데이터 4행이 잠겼다는 의미가 아닙니다. 두 세션의 테이블 의도 잠금 2개와 레코드 잠금 요청 2개(보유 1, 대기 1)를 합친 수입니다. 이번 비교는 트랜잭션 종료로 대기가 풀리는 과정을 검증한 것이며, 인덱스나 SQL 튜닝으로 실행 시간이 줄어든 결과가 아닙니다.
같은 결과를 잘못 읽기 쉬운 지점
이번 실습의 화면을 그대로 운영 장애에 대입할 때 자주 생기는 오해를 정리했습니다.
| 잘못된 해석 | 실제 의미 |
|---|---|
| Sleep 세션은 아무 일도 안 하니 무시해도 된다 | 열린 트랜잭션이 있으면 Sleep 상태로도 잠금을 보유합니다. 이번 차단 세션이 바로 Sleep이었습니다. |
| 상태가 updating이면 갱신 작업 중이다 | 상태 문자열만으로는 CPU 작업과 잠금 대기를 구분할 수 없습니다. data_locks의 WAITING으로 확인해야 합니다. |
| TABLE 잠금이 보이면 테이블 전체가 막혔다 | IX는 의도 잠금이며 서로 호환됩니다. 실제 경합은 RECORD 행에서 일어났습니다. |
| GRANTED면 그 세션은 정상 진행 중이다 | GRANTED는 개별 잠금 요청의 상태입니다. 46번은 IX는 GRANTED였지만 레코드 잠금에서 멈춰 있었습니다. |
| 같은 LOCK_DATA를 가진 세션끼리 막고 있다 | 차단 관계는 data_lock_waits로만 확정합니다. 같은 키에 여러 세션이 걸린 경우 추정이 틀릴 수 있습니다. |
| 지금 data_lock_waits가 비어 있으니 대기는 없었다 | 현재 시점의 관계만 보여 줍니다. COMMIT, ROLLBACK, 타임아웃 뒤에는 사라지므로 장애 시점에 시각과 함께 기록해야 합니다. |
마지막 항목은 장애 보고서를 쓸 때 특히 중요합니다. 조회한 시각, 대기·차단 접속 번호, 인덱스와 키, 요청·보유 잠금 모드를 함께 남겨 두지 않으면 나중에 원인을 증명할 근거가 없습니다. 여러 조회는 서로 다른 시점에 실행되므로 부하가 있는 서버에서는 쿼리 사이에 결과가 바뀔 수 있다는 점도 기록에 함께 적어 둡니다.
Sleep 차단 세션을 더 깊게 추적하는 보조 쿼리
차단 세션이 Sleep이면 “이 세션이 무엇을 하다가 잠금을 남겼는가”라는 질문이 남습니다. 현재 SQL이 NULL이라 threads만으로는 답할 수 없습니다. 다음 쿼리들은 이번 글의 캡처에는 포함하지 않았지만, 같은 실습 상태에서 이어서 확인하기 좋은 항목입니다.
먼저 트랜잭션이 언제 시작되었는지 확인합니다. Sleep 시간은 마지막 명령 이후의 시간일 뿐이고, 트랜잭션 자체는 그보다 훨씬 오래 열려 있을 수 있습니다.
SELECT
trx_mysql_thread_id AS conn_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_sec,
trx_rows_locked,
trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;이번 상황이라면 40번은 trx_state가 RUNNING이고 trx_query가 NULL, 46번은 LOCK WAIT로 나타나야 합니다. trx_age_sec가 큰데 trx_query가 NULL인 세션은 커밋을 잊은 트랜잭션의 전형적인 모습입니다.
다음으로 차단 세션이 마지막에 실행한 문장을 확인합니다. events_statements_history는 스레드별로 최근 문장을 보관하며, MySQL 8.x에서는 이 수집 항목이 기본으로 켜져 있습니다.
SELECT
t.PROCESSLIST_ID AS conn_id,
h.EVENT_ID,
LEFT(h.SQL_TEXT, 120) AS sql_text
FROM performance_schema.events_statements_history AS h
JOIN performance_schema.threads AS t
ON t.THREAD_ID = h.THREAD_ID
WHERE t.PROCESSLIST_ID = 40
ORDER BY h.EVENT_ID;여기에 START TRANSACTION과 UPDATE는 있는데 COMMIT이 없다면, 애플리케이션의 어느 코드 경로에서 트랜잭션이 끝나지 않았는지 찾는 출발점이 됩니다. 운영에서는 이 결과로 해당 요청의 로그와 커넥션 반환 처리를 함께 추적합니다.
빠르게 확인만 하고 싶다면 sys 스키마의 innodb_lock_waits 뷰도 쓸 수 있습니다. 내부적으로 이 글의 조인과 같은 정보를 정리해서 보여 줍니다.
SELECT
waiting_pid,
blocking_pid,
wait_age,
locked_index,
waiting_query,
blocking_query
FROM sys.innodb_lock_waits;이 뷰는 편리하지만 결과의 sql_kill_blocking_connection 컬럼이 KILL 문장을 바로 만들어 준다는 점 때문에 조치를 서두르게 만들기 쉽습니다. 이번 사례처럼 차단 세션이 Sleep이면 blocking_query도 NULL로 나오므로, 그 세션이 무엇을 바꿨는지는 위의 두 쿼리로 따로 확인해야 합니다. 원리를 이해하려면 data_locks와 data_lock_waits를 직접 조인해 보는 편이 낫고, 반복 점검에는 sys 뷰가 효율적입니다.
COMMIT, ROLLBACK, KILL 중 무엇을 고를지 판단하는 기준
실습에서는 의도적으로 만든 테스트 트랜잭션이라 A를 COMMIT했습니다. 운영에서는 차단 세션을 정리하는 방법에 따라 데이터 결과가 달라지므로 같은 방식을 그대로 적용하면 안 됩니다.
| 조치 | A의 변경(-100) | 이번 데이터 기준 B 커밋 후 잔액 | 고려할 점 |
|---|---|---|---|
| A에서 COMMIT | 확정 | 950 (실측) | 변경 내용을 확정해도 되는지 업무 담당자 확인이 필요합니다. |
| A에서 ROLLBACK | 취소 | 1050 (계산상 기대값) | 애플리케이션이 성공으로 처리한 요청이 취소될 수 있습니다. |
| KILL 40 | 롤백으로 취소 | 1050 (계산상 기대값) | 열린 트랜잭션은 롤백됩니다. 변경량이 크면 롤백에도 시간이 걸립니다. |
COMMIT 외의 두 행은 이번에 직접 측정한 값이 아니라 InnoDB의 롤백 동작을 기준으로 계산한 값입니다. 핵심은 KILL이 “대기를 푸는 버튼”이 아니라 “차단 세션의 변경을 되돌리는 조치”라는 점입니다. 차단 세션의 접속 번호만 보고 바로 KILL하기 전에, 위의 보조 쿼리로 그 트랜잭션이 무엇을 바꿨는지 먼저 확인해야 하는 이유가 여기에 있습니다.
대기 세션 쪽에서도 알아 둘 동작이 있습니다. B가 innodb_lock_wait_timeout을 넘기면 1205 오류를 받는데, 기본 설정(innodb_rollback_on_timeout=OFF)에서는 그 문장만 롤백되고 트랜잭션은 열린 채로 남습니다. 애플리케이션이 이 오류를 받은 뒤 ROLLBACK 없이 커넥션을 반환하면, 그 트랜잭션이 앞선 문장에서 잡은 잠금은 그대로 남습니다. 이번 실습의 B는 UPDATE 하나만 실행해 해당하지 않지만, 여러 문장을 실행하는 실제 트랜잭션에서는 이 세션이 새로운 Sleep 차단 세션이 될 수 있습니다. 타임아웃을 늘려 대기를 길게 만드는 것보다, 오류 처리 경로에서 트랜잭션 종료를 보장하는 것이 근본적인 대응입니다.
같은 문제가 반복된다면 트랜잭션 안에 외부 API 호출이나 사용자 응답 대기 같은 긴 작업이 들어 있지 않은지, 정상 경로와 예외 경로 모두에서 COMMIT 또는 ROLLBACK이 호출되는지 확인해야 합니다. 이것은 이번 실습에서 관찰한 원인이 아니라 운영에서 이어서 점검할 항목입니다. 결국 이 글의 진단 순서는 threads로 증상을 보고, data_locks로 잠금 소유자를 찾고, data_lock_waits로 방향을 확정한 뒤, 트랜잭션의 내용까지 확인하고 나서 조치를 고르는 흐름입니다. “UPDATE가 느리다”는 현상을 “Sleep 상태인 40번이 PRIMARY 키 1의 배타적 잠금을 쥐고 46번을 막고 있다”는 문장으로 바꿀 수 있어야 조치도 정확해집니다.
댓글 0
첫 댓글을 남겨보세요.