PRO DBDATA PRO DBDATA

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

읽는 시간 약 19분
MySQL 1205 오류 이후 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_timeout 50초(기본값)
  • innodb_rollback_on_timeout 꺼짐(기본값)
Workbench 환경 조회 결과에 MySQL 8.4.11과 rollback_on_timeout false가 표시된 화면
기본 설정에서는 락 대기 시간이 50초이고, 타임아웃 시 트랜잭션 전체를 롤백하는 옵션은 꺼져 있었습니다.

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 오류로 실패했습니다.

205번 입금 UPDATE 실행 후 Output 창에 MySQL Error 1205 메시지가 나온 화면
A가 잡고 있던 205번 행을 기다리던 입금 UPDATE가 대기 시간을 넘겨 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, age_sec 136, trx_rows_modified 2가 보이는 화면
트랜잭션이 시작되고 136초가 지난 시점에도 B의 트랜잭션은 변경 2건을 그대로 들고 있었습니다.

조회 결과를 정리하면 다음과 같습니다.

  • 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_balance PRIMARY의 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;
101번 행 FOR UPDATE NOWAIT 실행 결과로 MySQL Error 3572가 나온 화면
오류가 난 트랜잭션이 101번 행 락을 계속 쥐고 있어서, 다른 세션의 NOWAIT 요청이 즉시 실패했습니다.

오류를 무시하고 COMMIT하자 합계에서 5,000이 빠졌습니다

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

COMMIT 후 잔액 조회 결과로 101번 96000, 205번 105000이 보이는 화면
COMMIT 한 번으로 출금만 확정되고 입금은 반영되지 않았습니다.
항목이체 전COMMIT 후
101번 잔액10100096000
205번 잔액105000105000
원장0건1건 (101 → 205, 5000)
전체 잔액 합계104,500,000104,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번 앞까지 바꿔 둔 행들도 문장이 실패하면서 함께 되돌아간 것입니다.

범위 UPDATE 실패 후 INNODB_TRX 결과에 trx_rows_modified 0, trx_rows_locked 6이 보이는 화면
변경된 행은 0건인데 잠긴 행은 6건으로 남아 있었습니다.
data_locks 결과에 300번 X,REC_NOT_GAP과 301~304번 X 락이 GRANTED 상태로 보이는 화면
실패한 범위 UPDATE가 잡았던 300~304번 레코드 락이 트랜잭션에 그대로 남아 있었습니다.

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 요청은 성공했습니다. 락도 풀렸다는 뜻입니다.
innodb_rollback_on_timeout ON 상태에서 1205 후 INNODB_TRX 조회 결과가 비어 있는 화면
설정을 켠 뒤에는 1205 오류와 함께 B의 트랜잭션이 목록에서 사라졌습니다.

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이면서 트랜잭션이 열려 있는 연결이 풀에 남게 됩니다.

PRODB
16년 차 현직 DBA. AWS RDS for MySQL과 Aurora MySQL을 운영하며 겪은 문제를 MySQL 8.4에서 직접 재현해 기록합니다.

함께 보면 좋은 글

댓글 0

첫 댓글을 남겨보세요.

광고 차단 알림

광고 클릭 제한을 초과하여 광고가 차단되었습니다.

단시간에 반복적인 광고 클릭은 시스템에 의해 감지되며, IP가 수집되어 사이트 관리자가 확인 가능합니다.