1205 오류 뒤 COMMIT하면 무엇이 남을까? innodb_rollback_on_timeout OFF·ON 재현

개발 환경에서 받은 락 문의, 원인은 COMMIT 없이 멈춘 세션이었습니다
개발자에게서 특정 테이블에 락이 걸린 것 같으니 확인해 달라는 요청을 받은 적이 있습니다. 개발 환경이었고, 가장 먼저 전체 세션 목록과 상태를 조회했습니다. 목록에는 개발자 계정으로 접속한 세션 하나가 COMMIT 없이 대기 중인 상태로 남아 있었습니다. 이 세션을 담당자에게 알리고 KILL하겠다고 전한 뒤 정리했고, 락 문제는 그것으로 풀렸습니다.
그때 남아 있던 세션은 사람이 열어 두고 잊은 트랜잭션이었습니다. 같은 모습이 애플리케이션에서도 생길 수 있는지 궁금했습니다. 특히 1205 Lock wait timeout exceeded를 받은 트랜잭션이 그 자리에서 끝나는지, 그 앞에서 실행한 문장은 어떻게 되는지가 핵심입니다. 이 두 가지를 MySQL 8.4.11에서 직접 재현해 확인했습니다.
지갑 이체 한 건을 세 개의 연결로 재현했습니다
테스트는 Windows에 설치한 MySQL Community Server 8.4.11과 Workbench로 진행했습니다. 시작할 때의 설정은 다음과 같았습니다.
- 격리 수준
REPEATABLE-READ, autocommit 켜짐 innodb_lock_wait_timeout50초(기본값)innodb_rollback_on_timeout꺼짐(기본값)

Workbench에서 같은 연결을 세 번 열고, CONNECTION_ID()가 서로 다른지 먼저 확인했습니다. 세 연결의 역할은 이렇습니다.
- A: 입금 대상 행을 먼저 잡아 두는 블로커
- B: 이체 트랜잭션을 실행하는 세션
- M: 다른 두 세션의 상태를 조회하는 모니터
잔액 테이블은 1,000건으로 만들었습니다. 락 동작은 건수와 상관이 없고, 이 정도 크기면 data_locks에서 잠긴 행을 하나씩 셀 수 있기 때문입니다. 잔액은 wallet_id로 정해지는 고정 분포라서 다시 만들어도 같은 값이 나옵니다. 시작 시점의 전체 합계는 104,500,000입니다.
CREATE DATABASE lab_1205;
USE lab_1205;
CREATE TABLE wallet_balance (
wallet_id INT NOT NULL,
owner_code VARCHAR(20) NOT NULL,
balance BIGINT NOT NULL,
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (wallet_id)
) ENGINE=InnoDB;
CREATE TABLE wallet_ledger (
ledger_id BIGINT NOT NULL AUTO_INCREMENT,
from_wallet INT NOT NULL,
to_wallet INT NOT NULL,
amount BIGINT NOT NULL,
memo VARCHAR(50) NOT NULL,
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (ledger_id),
KEY ix_ledger_from (from_wallet)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 2000;
INSERT INTO wallet_balance (wallet_id, owner_code, balance)
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)
SELECT n, CONCAT('W', LPAD(n, 5, '0')), 100000 + (n MOD 10) * 1000 FROM seq;
B 세션에서만 SET SESSION innodb_lock_wait_timeout = 5로 대기 시간을 5초로 줄였습니다. 기본값 50초를 매번 기다리지 않기 위해서입니다.
1205를 받은 B 세션은 Sleep인데, 트랜잭션은 RUNNING이었습니다
시나리오는 101번 지갑에서 205번 지갑으로 5,000을 보내는 이체입니다. A가 205번 행을 먼저 잡아 둔 상태에서, B가 출금, 원장 기록, 입금을 차례로 실행합니다.
-- Session A
START TRANSACTION;
SELECT wallet_id, balance FROM wallet_balance WHERE wallet_id = 205 FOR UPDATE;
-- Session B
SET SESSION innodb_lock_wait_timeout = 5;
START TRANSACTION;
UPDATE wallet_balance SET balance = balance - 5000 WHERE wallet_id = 101;
INSERT INTO wallet_ledger (from_wallet, to_wallet, amount, memo)
VALUES (101, 205, 5000, 'R1 transfer');
UPDATE wallet_balance SET balance = balance + 5000 WHERE wallet_id = 205;
마지막 입금 UPDATE는 205번 행을 기다리다가 1205 오류로 실패했습니다.

오류 직후 같은 B 세션에서 잔액을 조회했습니다.
- B에서 본 값: 101번
96000, 205번105000. 출금은 그대로 남았고 입금만 취소된 상태입니다. - 같은 시점에 M에서 본 101번:
101000. 출금이 아직 확정되지 않은 변경이라는 뜻입니다.
M에서 B의 트랜잭션 상태를 조회했습니다.
SELECT trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
trx_rows_modified, trx_rows_locked, trx_query
FROM information_schema.INNODB_TRX
WHERE trx_mysql_thread_id = @b_conn;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_THREAD_ID(@b_conn);

조회 결과를 정리하면 다음과 같습니다.
INNODB_TRX:trx_state는RUNNING,trx_rows_modified는 2,trx_query는 NULL이었습니다.processlist(같은 이체를 한 번 더 재현했을 때 확인):COMMAND는Sleep,TIME은 19,INFO는 NULL이었습니다.events_transactions_current:STATE는ACTIVE,AUTOCOMMIT은NO였습니다.
세션 목록만 보면 쉬고 있는 연결이지만, 실제로는 트랜잭션이 열린 상태입니다. 개발 환경에서 찾았던 COMMIT 없는 세션과 같은 모습입니다.
B가 쥐고 있는 락도 확인했습니다.
- 두 테이블의 IX 락
wallet_balancePRIMARY의 101번 레코드에 대한X,REC_NOT_GAP락(GRANTED)
기다리던 205번 락 요청은 목록에서 사라졌습니다. 원장에 INSERT한 행은 레코드 락으로 나타나지 않았습니다. 새로 넣은 행은 다른 세션이 접근하기 전까지 암묵적 락으로만 관리되어 목록에 보이지 않은 것으로 판단합니다. 다만 이 부분은 추정입니다. trx_rows_locked가 2인데 보이는 레코드 락이 1개인 것도 같은 이유로 보이지만, 측정으로 확인하지는 않았습니다.
남은 락이 다른 세션을 실제로 막는지는 M에서 NOWAIT으로 확인했습니다.
SELECT wallet_id FROM wallet_balance WHERE wallet_id = 101 FOR UPDATE NOWAIT;

오류를 무시하고 COMMIT하자 합계에서 5,000이 빠졌습니다
애플리케이션이 1205를 로그로만 남기고 COMMIT을 호출하는 상황을 재현했습니다. B에서 COMMIT, A에서 ROLLBACK을 실행한 뒤 M에서 결과를 조회했습니다.

| 항목 | 이체 전 | COMMIT 후 |
|---|---|---|
| 101번 잔액 | 101000 | 96000 |
| 205번 잔액 | 105000 | 105000 |
| 원장 | 0건 | 1건 (101 → 205, 5000) |
| 전체 잔액 합계 | 104,500,000 | 104,495,000 |
같은 이체를 두 번 재현했고, 두 번 모두 결과가 같았습니다. 원장과 합계 값은 두 번째 실행 기준입니다.
원장에는 205번이 5,000을 받았다고 적혀 있지만, 205번 잔액은 그대로입니다. 오류가 난 문장 하나만 빠진 채 나머지가 확정되면서 기록과 잔액이 서로 다른 말을 하게 됐습니다.
여러 행을 바꾸던 UPDATE는 되돌아갔지만, 락은 남았습니다
이번에는 한 문장이 여러 행을 바꾸다가 중간에 막히는 경우를 확인했습니다. A가 305번 행을 잡아 둔 상태에서, B가 300번부터 310번까지 범위 UPDATE를 실행했습니다.
-- Session A
START TRANSACTION;
SELECT wallet_id FROM wallet_balance WHERE wallet_id = 305 FOR UPDATE;
-- Session B
SET SESSION innodb_lock_wait_timeout = 5;
START TRANSACTION;
UPDATE wallet_balance SET balance = balance + 100 WHERE wallet_id BETWEEN 300 AND 310;
1205가 난 뒤 B에서 300~310번을 조회하니 모든 행이 초기값 그대로였습니다(300번 100000부터 304번 104000까지 포함). 305번 앞까지 바꿔 둔 행들도 문장이 실패하면서 함께 되돌아간 것입니다.


trx_rows_modified는 0, trx_rows_locked는 6, trx_lock_structs는 3이었습니다. 남은 락은 다음과 같았습니다.
- 300번:
X,REC_NOT_GAP. 범위 시작점이라 레코드만 잠겼습니다. - 301~304번:
X. 앞쪽 간격까지 함께 잠그는 다음 키 락입니다.
즉, 데이터 변경은 문장 단위로 취소되지만 그 문장이 잡은 락은 트랜잭션이 끝날 때까지 남습니다. 락 목록대로라면 B가 이 상태로 Sleep에 머무는 동안 300~304번 행을 수정하려는 다른 트랜잭션은 계속 막히게 됩니다.
트랜잭션을 점검할 때 trx_rows_modified만 보고 아무것도 바꾸지 않았으니 괜찮다고 판단하면 이런 트랜잭션을 놓칩니다. trx_rows_locked도 함께 봐야 합니다.
설정을 켰다고 생각한 첫 시도는 OFF와 결과가 같았습니다
innodb_rollback_on_timeout은 실행 중에 SET으로 바꿀 수 없는 정적 변수입니다. 켜려면 my.ini를 수정하고 서버를 재시작해야 합니다.
첫 시도에서는 my.ini를 고치지 않았고 서비스도 재시작하지 않은 채로 ON 확인 절차를 진행했습니다. 결과는 OFF와 똑같았습니다. 1205가 난 뒤에도 INNODB_TRX에 RUNNING, trx_rows_modified 2가 그대로 나왔습니다.
원인은 설정값의 출처를 조회하자 바로 드러났습니다. VARIABLE_SOURCE가 COMPILED이고 VARIABLE_PATH는 비어 있었습니다. 설정 파일이 아니라 컴파일 기본값이 그대로 쓰이고 있다는 뜻입니다.
그래서 C:\ProgramData\MySQL\MySQL Server 8.4\my.ini의 [mysqld] 섹션에 한 줄을 추가하고 재시작했습니다. 다시 조회하자 값은 1, 출처는 EXPLICIT로 바뀌었고 경로에는 이 파일이 표시됐습니다.
# my.ini [mysqld] 섹션
innodb_rollback_on_timeout=ON
-- 재시작 후 확인
SELECT @@GLOBAL.innodb_rollback_on_timeout;
SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = 'innodb_rollback_on_timeout';
정적 변수는 값만 보고는 재시작 전후를 구분하기 어렵습니다. 그래서 이후에는 값과 함께 출처, 파일 경로까지 확인한 다음 진행했습니다.
ON에서는 전부 되돌아갔고, 바로 다음 문장은 즉시 확정됐습니다
잔액을 초기 상태로 돌린 뒤 같은 이체를 다시 실행했습니다. 원장은 비우지 않아 OFF에서 확정된 행이 남아 있는 상태였습니다. 이번에도 1205가 났고, 직후 B에서 조회한 결과는 OFF 때와 달랐습니다.
- 101번
101000, 205번105000. 출금 UPDATE까지 취소됐습니다. - 원장에는 앞서 OFF에서 확정된
R1 transfer한 건만 남았습니다. 이번 이체의 원장 행은 함께 사라졌습니다. - M에서 조회한
INNODB_TRX에 B의 행이 없었습니다. - 101번에 대한 NOWAIT 요청은 성공했습니다. 락도 풀렸다는 뜻입니다.

events_transactions_current에서는 B가 STATE COMMITTED, AUTOCOMMIT YES로 나왔습니다. 롤백된 이체가 COMMITTED로 보인 이유는 이렇게 해석합니다. 이 값은 롤백된 이체가 아니라, B가 그 뒤 autocommit으로 실행한 조회의 기록입니다. 즉, B 세션은 이미 START TRANSACTION 범위를 벗어나 있었습니다.
이 해석이 맞는지 B에서 문장 하나를 더 실행해 확인했습니다.
-- 1205 직후 Session B
UPDATE wallet_balance SET balance = balance - 1 WHERE wallet_id = 102;
ROLLBACK;
SELECT wallet_id, balance FROM wallet_balance WHERE wallet_id = 102;
102번은 101999였습니다. ROLLBACK이 되돌릴 트랜잭션이 없었고, UPDATE는 실행되는 순간 바로 확정된 것입니다.
| 1205 이후 | OFF (기본값) | ON |
|---|---|---|
| B 트랜잭션 | RUNNING, 변경 2건 유지 | INNODB_TRX에서 사라짐 |
| 앞서 실행한 출금 | 남아 있음 (B에서 96000) | 취소됨 (101000) |
| 101번 행 락 | 유지, NOWAIT 3572 실패 | 해제, NOWAIT 성공 |
| 애플리케이션이 계속 진행하면 | COMMIT 시 출금만 확정, 합계 5,000 감소 | 다음 문장이 autocommit으로 즉시 확정, ROLLBACK 효과 없음 |
설정 하나로 해결되지 않는 이유와 먼저 확인할 것
두 설정 모두 1205를 받은 뒤 애플리케이션이 무엇을 하느냐에 따라 결과가 갈렸습니다.
- OFF: 오류 뒤 COMMIT하면 일부만 확정됩니다.
- ON: 오류 뒤 같은 연결에서 나머지 문장을 이어 실행하면, 그 문장들이 하나씩 따로 확정됩니다.
ON이 오히려 맞지 않는 경우도 있습니다. 1205를 받은 문장만 다시 실행하는 재시도 로직이 그렇습니다. OFF에서는 앞 문장이 살아 있어서 이 방식이 겉으로는 맞아떨어집니다. 그런데 ON으로 바꾸면 앞 문장은 사라진 상태에서 마지막 문장만 autocommit으로 확정됩니다. 이번 결과대로라면 출금 없이 입금만 남는, 반대 방향의 불일치가 생길 수 있습니다.
또한 이 설정은 서버 전역에 적용되고, 바꾸려면 재시작이 필요합니다. 운영에서 바꾸기 전에 애플리케이션의 재시도 코드가 어느 쪽 동작을 가정하고 있는지부터 확인해야 합니다.
1205가 의심되는 트랜잭션을 INNODB_TRX에서 볼 때는 이번 결과에서 나온 세 가지를 더 확인합니다.
trx_query가 NULL인 채로 트랜잭션 나이만 늘어나는 Sleep 세션은 오류를 받고 정리되지 않은 트랜잭션일 수 있습니다.trx_rows_modified가 0이어도trx_rows_locked가 남아 있으면 다른 세션을 막는 트랜잭션입니다.innodb_rollback_on_timeout은 값만 보지 말고performance_schema.variables_info의 출처까지 함께 확인합니다.
세션 조회부터 블로커 확인, 담당자 확인 후 KILL까지 운영에서 잠금 대기를 처리하는 절차는 data_locks로 잠금 대기를 추적한 글에 따로 정리했습니다.
데드락(1213)은 1205와 달리 트랜잭션 전체가 롤백되는 오류입니다. 그 경우의 재시도 흐름은 Next-Key 락 데드락 글에서 다뤘습니다.
1205를 받은 코드가 다음에 해야 할 일
1205를 받은 트랜잭션은 설정값과 상관없이 그 자리에서 버리는 것을 기준으로 둡니다. 오류 처리 코드는 다음 순서를 따르는지 확인합니다.
- 1205를 받으면 같은 연결에서 바로
ROLLBACK을 실행합니다. OFF에서는 이 한 줄이 없으면 출금 변경과 101번 행 락이 계속 남습니다. - 재시도할 때는 실패한 문장 하나만 다시 보내지 말고,
START TRANSACTION부터 트랜잭션 전체를 다시 실행합니다. 실패한 문장만 다시 보내면 ON에서는 입금만 확정될 수 있습니다. - 오류를 로그로만 남기고
COMMIT으로 넘어가는 경로가 없는지 봅니다. 이번 재현에서 합계 5,000이 빠진 것이 이 경로였습니다.
커넥션 풀이 1205를 받은 연결을 어떤 상태로 반납하는지는 이번에 확인하지 않았습니다. ROLLBACK 없이 반납된다면, 이번 B 세션처럼 Sleep이면서 트랜잭션이 열려 있는 연결이 풀에 남게 됩니다.




댓글 0
첫 댓글을 남겨보세요.