InnoDB 트랜잭션 락 실습 & 아키텍처 보고서 1 / 11

0. 실습 데이터 모델링 구조와 락(Lock)의 본질

마스터(acct) - 상세(acct_txn) 구조와 InnoDB 엔진의 실제 락 규칙

📊 실습 테이블 및 컬럼 구조

1. 계좌 마스터 (acct): 계좌의 최종 상태 관리

  • acct_no (PK): 계좌번호 ('10288000000001')
  • balance_amt: 최종 잔액 (초기값 1,000,000원)
  • CHECK (balance_amt >= 0): 음수 잔액 차단 제약

2. 거래 원장 (acct_txn): 개별 거래 이력

  • txn_id (PK · 대리키), txn_unique_no (UNIQUE · 거래고유번호)
  • txn_amt (거래금액)
  • acct_no (FK): acct(acct_no)를 참조하는 외래키
🔒 S락(공유락)과 X락(배타락)의 실체

InnoDB는 행 단위로 두 종류의 락을 겁니다.

  • S락 (Shared · 공유락): "참조 중이니 지우거나 수정하지 마라."
    👉 여러 트랜잭션이 한 행에 동시에 획득 가능.
  • X락 (Exclusive · 배타락): "내가 값을 바꿀 테니 아무도 손대지 마라."
    👉 오직 한 세션만 독점 가능.
  • ★ FK의 숨은 S락 동작: 자식(acct_txn)에 INSERT할 때, 부모(acct) 계좌가 사라지지 않도록 엔진이 부모 행에 자동으로 S락을 걺!
  • 호환 규칙: S와 S는 함께 가질 수 있고, S와 X · X와 X는 함께 가질 수 없음. 받지 못한 쪽은 대기.

1-1. [입금 ↔ 입금] 데드락(Deadlock) 발생 이론

FK 검증 시 암묵적으로 부여되는 S락과 잔액 UPDATE X락의 교차 대기

1. 업무 요구사항
동시 입금(각 1만 원) 누적

동일 계좌(100만 원)에 세션 A와 B가 동시에 1만 원씩 입금할 때, 두 거래 모두 승인되어 최종 잔액 1,020,000원이 되어야 함.

2. 코드 및 데드락 메커니즘 
INSERT INTO acct_txn ... VALUES (...); -- ① 원장 기록
UPDATE acct 
   SET balance_amt = balance_amt + 10000; -- ② 잔액 갱신
데드락 완성 루프:
• A, B 둘 다 ① 실행: 부모 계좌 행에 S락 각각 획득
• A가 ② 실행: X락 요구 ➔ B의 S락 때문에 WAIT
• B가 ② 실행: X락 요구 ➔ A의 S락 때문에 WAIT
➔ 서로 상대 S락이 풀리기만을 무한히 기다리는 순환 대기 형성!
3. 결과 및 문제점
  • ERROR 1213 (Deadlock found): 엔진이 순환 대기를 감지해 한쪽 트랜잭션을 통째로 롤백. 실습에서는 B가 취소됨 (어느 쪽일지는 엔진이 선택).
  • 재시도 필요: A의 1만 원만 반영(1,010,000원). B는 오류를 받고 롤백되어 입금을 다시 요청해야 함. 돈이 사라진 것은 아님.

1-1-T. 시나리오 1 터미널 시뮬레이션 [01_deadlock.sql]

A와 B 터미널의 교차 실행 순서와 OBS 관측 뷰(v_locks, v_waits) 모니터링

🖥️ OBS terminal
─□✕
[STEP 0] 초기화
OBS> CALL lock_lab.reset();
-- 잔액: 1,000,000원

[STEP 2 & 4] 락 상태 관측
OBS> SELECT who, tbl, kind, raw_mode, status FROM lock_lab.v_locks;
| who | tbl | kind | raw_mode | status |
| A | acct | S (read) | S,REC_NOT_GAP | GRANTED |
| B | acct | S (read) | S,REC_NOT_GAP | GRANTED |
➔ A, B 둘 다 계좌 행에 S락(공유)을 쥐고 있음!

[STEP 6] 대기 상태 관측
OBS> SELECT * FROM lock_lab.v_waits;
waiting: A (wants X) ➔ blocked_by: B (holds S)

[STEP 9] 최종 결과
OBS> SELECT * FROM lock_lab.v_acct;
BALANCE: 1,010,000 (A 입금만 반영, B 취소됨)
👤 A terminal
─□✕
[STEP 1] 트랜잭션 시작 및 원장 기록
A> BEGIN;
A> INSERT INTO acct_txn (…) VALUES ('DEMO-S1-A', '1028...', ..., 10000, ...);
-- ★ FK 검증으로 acct 행에 S락 자동 부여됨 (GRANTED)

[STEP 5] 잔액 변경 시도 ➔ 대기!
A> UPDATE acct SET balance_amt = balance_amt + 10000 WHERE ...;
... [커서 깜빡임: B의 S락 때문에 멈춤] ...
➔ [STEP 7에서 B 롤백 순간 풀림] Query OK, 1 row affected
A> COMMIT;
👤 B terminal
─□✕
[STEP 3] 원장 기록 (S락 추가 획득)
B> BEGIN;
B> INSERT INTO acct_txn (…) VALUES ('DEMO-S1-B', '1028...', ..., 10000, ...);
-- S락끼리는 공유 가능하므로 B도 성공 (계좌 행에 S락 2개)

[STEP 7] 잔액 변경 시도 ➔ 데드락 폭발!
B> UPDATE acct SET balance_amt = balance_amt + 10000 WHERE ...;
ERROR 1213 (40001): Deadlock found when trying to get lock;
-- 엔진에 의해 B 강제 롤백됨 (B의 S락 해제 ➔ A가 풀림)
B> ROLLBACK;

1-2. [입금 ↔ 입금] FOR UPDATE를 통한 데드락 해결 이론

시작부터 배타락(X)을 획득하여 S↔X 교차 대기 조건을 원천 차단

1. 업무 요구사항
무결성 보장 및 무오류 순차 처리

두 입금이 동시 요청되어도 데드락 에러 없이, 큐(Queue)처럼 차례대로 실행되어 두 건 모두 100% 누적 반영되어야 함.

2. FOR UPDATE 문법의 메커니즘 
BEGIN;
-- ★ 핵심: 조회하면서 바로 쓰기락(X) 획득
SELECT balance_amt INTO @bal 
  FROM acct WHERE acct_no = '...' 
   FOR UPDATE; 
INSERT INTO acct_txn ...;
UPDATE acct SET balance_amt = ...;
COMMIT;
해결 원리: A가 FOR UPDATE로 X락을 먼저 쥠. B는 S락조차 못 쥐고 입구에서 대기하므로 "둘 다 S락을 쥔 상태" 자체가 만들어지지 않음!
3. 처리 효과
  • 데드락 원천 제거: B가 아무런 락을 쥐지 않은 채(WAITING) A만 기다리는 1방향 대기.
  • A 종료 후 B가 풀려 최종 1,020,000원 정상 저장.

1-2-T. 시나리오 2 터미널 시뮬레이션 [02_deadlock_fixed.sql]

A의 선제적 X락 점유로 B가 시작 지점에서 대기하고, A 커밋 후 B가 정상 완료되는 흐름

🖥️ OBS terminal
─□✕
[STEP 0] 초기화
OBS> CALL lock_lab.reset();

[STEP 2 & 4] 락 상태 관측
OBS> SELECT who, tbl, kind, status FROM lock_lab.v_locks;
| who | tbl | kind | status |
| A | acct | X (write) | GRANTED | ➔ A만 독점
| B | acct | X (write) | WAITING | ➔ B는 쥔 것 없이 대기

➔ B가 락을 쥐지 않았으므로 데드락 가능성 제로!

[STEP 7] 최종 결과
OBS> SELECT * FROM lock_lab.v_acct;
| item | value | note |
| BALANCE | 1,020,000 | NULL |
| DEMO-S2-A | +10,000 | after 1,010,000 |
| DEMO-S2-B | +10,000 | after 1,020,000 |
✔ 두 건 모두 성공, 잔액 완벽 일치
👤 A terminal
─□✕
[STEP 1] X락 잡으며 조회
A> BEGIN;
A> SELECT balance_amt INTO @bal FROM acct ... FOR UPDATE;
-- A가 acct 행에 배타락(X) 선점 완료

[STEP 5] 원장 기록 ➔ 잔액 변경 ➔ COMMIT
A> INSERT INTO acct_txn (…) VALUES ('DEMO-S2-A', ...);
A> UPDATE acct SET balance_amt = balance_amt + 10000 WHERE ...;
A> COMMIT; ➔ A의 X락 해제 (B가 깨어남!)
👤 B terminal
─□✕
[STEP 3] B도 FOR UPDATE 시도 ➔ 대기
B> BEGIN;
B> SELECT balance_amt INTO @bal FROM acct ... FOR UPDATE;
... [커서 깜빡임: A의 X락 때문에 멈춤] ...
➔ [STEP 5에서 A 커밋 순간 풀림!] locked_balance: 1010000.00

[STEP 6] 최신 잔액 기반으로 정상 처리
B> INSERT INTO acct_txn (…) VALUES ('DEMO-S2-B', ...);
B> UPDATE acct SET balance_amt = balance_amt + 10000 WHERE ...;
B> COMMIT; -- 완벽한 순차 성공!

2-1. [출금 ↔ 출금] 갱신 분실(Lost Update)과 CHECK의 한계

락 없는 단순 조회 판정의 위험성과 CHECK 제약조건이 사고를 못 막는 이유

1. 업무 요구사항
초과 인출 차단 (한도 엄수)

잔액 100만 원 계좌에서 동시 70만 원 출금 시, 반드시 1명만 승인되고 나머지 1명은 잔액 부족으로 거절되어야 함.

2. 무잠금 출금의 오류 
-- [PART A: 앱 변수 덮어쓰기]
UPDATE acct SET balance_amt = @bal - 700000;
-- [PART B: DB 직접 차감]
UPDATE acct SET balance_amt = balance_amt - 700000;
  • PART A: 둘 다 100만을 보고 30만 원으로 덮어씀. 140만 원 인출됐으나 잔액은 30만 원 (70만 원 공중 분실). 30만 원은 양수라 CHECK 통과!
  • PART B: DB가 -40만을 감지해 CHECK 제약으로 막음.
3. CHECK 제약의 한계

"CHECK는 잔액은 지키지만, 잘못된 판단은 막지 못한다"

B는 100만 원을 보고 이미 승인 판단을 내린 뒤에 실패했습니다. 판단 직후 ATM이 현금을 내줬다면 돈은 나갔는데 전산 기록은 없는 사고가 됩니다. CHECK는 마지막 방어선이지 올바른 처리 방법이 아닙니다.

2-1-T. 시나리오 3 터미널 시뮬레이션 [03_lost_update.sql]

PART A: 에러 없이 70만 원이 증발하는 과정 vs PART B: 사후 CHECK 에러 폭탄

🖥️ OBS terminal
─□✕
[PART A 관측: 갱신 분실]
OBS> SELECT * FROM lock_lab.v_acct;
BALANCE: 300,000
★ 140만 원이 나갔는데 30만 원이 남음!
-- B가 A의 출금 사실을 모르고 30만을 덮어써서 70만 원 증발
-- CHECK(balance_amt >= 0)은 30만이 양수라 감지 못함!

[PART B 관측: DB에서 계산한 경우]
OBS> CALL lock_lab.reset();
-- (A · B 가 PART B 를 진행한 뒤)
OBS> SELECT * FROM lock_lab.v_acct;
BALANCE: 300,000
➔ 잔액은 지켜졌지만, B는 승인 판단을 내린 뒤 실패함
👤 A terminal (출금 70만)
─□✕
[STEP A1] 락 없이 잔액 조회
A> SELECT balance_amt INTO @bal ...; -- 1,000,000원 확인
-- A 판단: "100만 원 있으니 70만 원 출금 승인"

[STEP A3 & A6] 잔액 덮어쓰기 & 커밋
A> UPDATE acct SET balance_amt = @bal - 700000; -- 30만 저장
A> COMMIT; ➔ B의 대기가 풀림

[PART B · STEP B1 & B3] DB에서 차감 & 커밋
A> UPDATE acct SET balance_amt = balance_amt - 700000;
A> COMMIT;
👤 B terminal (출금 70만)
─□✕
[STEP A2] B도 락 없이 잔액 조회
B> SELECT balance_amt INTO @bal ...; -- 똑같이 1,000,000원 확인
-- B 판단: "100만 원 있으니 나도 70만 원 출금 승인!"

[STEP A4 & A6] 덮어쓰기 대기 ➔ 풀린 후 실행
B> UPDATE acct SET balance_amt = @bal - 700000;
... [A의 X락 때문에 잠시 멈춤] ...
➔ [A 커밋 후] B도 옛날 기준의 "300,000원"을 그대로 덮어씀!
B> COMMIT; -- 에러 없이 금융사고 발생

[PART B · STEP B2] 조회 후 DB에서 차감 시도
B> SELECT balance_amt INTO @bal ...; -- 1,000,000 (A 커밋 전이라 안 보임)
B> UPDATE acct SET balance_amt = balance_amt - 700000;
... [A의 X락 때문에 대기] ...
➔ [A 커밋 후] ERROR 3819: Check constraint 'ck_acct_balance' is violated.
-- 30만 - 70만 = -40만 → CHECK 가 거부
B> ROLLBACK;

2-2. [조회 ↔ 출금] 단순 조회와 판단용 조회는 다르다

화면 조회는 잠그지 않는 것이 맞고, 출금·이체 판단에 쓰는 조회만 잠근다

1. 업무 요구사항
두 가지를 동시에 만족

① 고객의 잔액 조회 화면은 다른 채널에서 출금이 진행 중이어도 막히지 않아야 함.

② 출금·이체 판단은 다른 거래가 끼어들지 못하는 최신 확정 잔액으로 해야 함.

2. 충돌 흐름 메커니즘 
-- [세션 1] 이체 판단용 조회 (잠그지 않음)
SELECT balance_amt INTO @bal FROM acct ...; -- 100만
-- [세션 2] ATM 전액 출금 후 COMMIT
UPDATE acct SET balance_amt = balance_amt - 1000000;
COMMIT;
-- [세션 1] @bal(100만)을 믿고 이체 진행
문제는 조회 결과가 아니라 "틈"입니다. 읽은 순간의 100만은 맞는 값이었습니다. 하지만 그 값으로 이체하기까지의 사이에 다른 거래가 끼어들었습니다. 판단용 조회를 잠그지 않은 것이 원인입니다.
3. 해결 원리
  • 화면 조회 → 일반 SELECT: 락을 요청하지 않으므로 출금 중에도 기다리지 않고 마지막 확정 잔액을 보여줌 (MVCC).
  • 판단용 조회 → FOR UPDATE: 읽는 순간 잠가서, 판단부터 처리까지 아무도 끼어들지 못하게 함.
  • 어떤 조회인지는 DB가 알아내는 것이 아니라 개발자가 문장으로 정함.

2-2-T. 시나리오 5 터미널 시뮬레이션 [05_read_while_locked.sql]

A가 X락을 쥐고 출금 중이어도 B의 일반 조회는 기다리지 않고, 락을 요청하는 조회만 기다리는 흐름

🖥️ OBS terminal
─□✕
[STEP A0] 초기화
OBS> CALL lock_lab.reset();

[STEP A2] A 의 락 확인
OBS> SELECT who, kind, status FROM lock_lab.v_locks;
| who | kind | status |
| A | X (write) | GRANTED |
➔ A 가 계좌 행을 X락으로 쥐고 있음

[STEP A5] B 가 FOR SHARE 로 조회한 뒤
OBS> SELECT who, kind, status FROM lock_lab.v_locks;
| who | kind | status |
| A | X (write) | GRANTED |
| B | S (read) | WAITING |
➔ STEP A3 의 일반 조회 때는 B 가 이 목록에 없었음
-- 일반 SELECT 는 락을 요청하지 않기 때문

[정리]
SELECT 락 없음 → 바로 나옴
SELECT … FOR SHARE S 요청 → 기다림
SELECT … FOR UPDATE X 요청 → 기다림
👤 A terminal (출금 처리 중인 창구)
─□✕
[STEP A1] 잠그고 잔액 변경 (커밋 안 함)
A> BEGIN;
A> SELECT balance_amt INTO @bal FROM acct ... FOR UPDATE;
A> UPDATE acct SET balance_amt = balance_amt - 700000;
-- 잔액 30만으로 변경, 아직 확정 전 (X락 보유)

[STEP A6] 출금 확정
A> COMMIT; ➔ B 의 FOR SHARE 대기 해제
👤 B terminal (모바일 앱 잔액 조회)
─□✕
[STEP A3] 일반 조회
B> SELECT balance_amt AS plain_select FROM acct ...;
➔ 즉시 1000000.00 -- 기다리지 않음 · 확정된 값

[STEP A4] 락을 요청하는 조회 ➔ 대기
B> BEGIN;
B> SELECT balance_amt AS for_share FROM acct ... FOR SHARE;
... [A 의 X락 때문에 멈춤] ...
➔ [A 커밋 후 출력됨] 300000.00

[STEP A7] 정리 후 다시 일반 조회
B> ROLLBACK;
B> SELECT balance_amt AS plain_select FROM acct ...;
➔ 300000.00 -- 커밋됐으니 새 값

2-3. [출금 완결] 판단 시점부터 배타락으로 완벽 격리

시나리오 4: SELECT FOR UPDATE를 통한 "조건 검증-잔액 차감-원장 기입" 동기화

1. 업무 요구사항
완벽한 1명 승인 / 1명 거절

동시 70만 원 출금 시, B가 100만 원이라는 낡은 잔액을 보고 헛된 승인 판단을 내리지 않도록 판정 단계부터 대기시켜야 함.

2. 처리 순서 
BEGIN;
-- ① 보는 순간 X락으로 계좌 잠금
SELECT balance_amt INTO @bal FROM acct ... FOR UPDATE;

-- ② 판단
SELECT IF(@bal >= 700000, '승인', '거절 - 잔액 부족');

-- ③-a 승인이면
UPDATE acct SET balance_amt = balance_amt - 700000 ...;
INSERT INTO acct_txn (…) VALUES (…);
COMMIT;

-- ③-b 거절이면
ROLLBACK;
터미널에서는 ②의 결과를 보고 사람이 ③-a / ③-b를 고릅니다. Java 단계에서는 이 분기를 if 문으로 구현합니다.
3. 처리 효과
  • B는 A가 출금하고 커밋할 때까지 SELECT ... FOR UPDATE에서 대기.
  • A 커밋 후, B는 차감 완료된 실제 잔액(30만 원)을 안전하게 조회.
  • B는 시스템 에러 없이 비즈니스 로직에 의해 "잔액 부족 거절" 판정 후 롤백.

2-3-T. 시나리오 4 터미널 시뮬레이션 [04_withdraw_fixed.sql]

B가 잔액 조회 자체를 대기하고, A의 70만 원 출금 완료 후 30만 원을 보고 스스로 거절하는 흐름

🖥️ OBS terminal
─□✕
[STEP 0] 초기화
OBS> CALL lock_lab.reset();

[STEP 3] 대기 상태 확인
OBS> SELECT * FROM lock_lab.v_waits;
waiting: B ➔ blocked_by: A
➔ B는 낡은 잔액을 볼 기회조차 차단됨!

[STEP 6] 최종 결과 확인
OBS> SELECT * FROM lock_lab.v_acct;
| item | value | note |
| BALANCE | 300,000 | NULL |
| DEMO-S4-A | -700,000 | after 300,000 |

✔ A 1건 승인, B 1건 거절! 잔액과 원장 완벽 일치
👤 A terminal (승인 세션)
─□✕
[STEP 1] 잠그며 읽기
A> BEGIN;
A> SELECT balance_amt INTO @bal FROM acct ... FOR UPDATE;
-- locked_balance: 1,000,000원 확인 (X락 독점)

[STEP 4] 판단 ➔ 차감 ➔ 원장 기록 ➔ 커밋
A> SELECT IF(@bal >= 700000, '승인', '거절'); -- '승인'
A> UPDATE acct SET balance_amt = balance_amt - 700000;
A> INSERT INTO acct_txn (…) VALUES ('DEMO-S4-A', ..., 'WDR', 700000, ...);
A> COMMIT; ➔ A 종료, B 대기 해제!
👤 B terminal (거절 세션)
─□✕
[STEP 2] 잠그며 읽기 시도 ➔ 대기
B> BEGIN;
B> SELECT balance_amt INTO @bal FROM acct ... FOR UPDATE;
... [커서 깜빡임: A 완료 때까지 읽는 것 자체를 멈춤] ...
➔ [A 커밋 후 출력됨] locked_balance: 300,000.00

[STEP 5] 정확한 최신 잔액으로 판단 ➔ 거절
B> SELECT IF(@bal >= 700000, '승인', '거절 - 잔액 부족');
결과: '거절 - 잔액 부족'
B> ROLLBACK; -- 에러 없이 안전하게 거래 종료

3. 최종 결론: 락(Lock)에 대한 새로운 관점

같은 기능끼리의 경합과, 서로 다른 기능 사이의 간섭을 함께 다뤄야 한다

❌ 처음의 생각

"같은 기능이 동시에 실행될 때만 신경 쓰면 된다?"

  • 입금할 때 다른 입금이 오면 줄 세우기
  • 출금할 때 다른 출금이 오면 줄 세우기
  • 단순히 동일한 API가 중복 호출되는 상황만을 제어 대상으로 좁게 인식함.
⭕ 실습에서 확인한 것

"한 거래 안의 단계 순서, 그리고 판단과 실행 사이의 틈"

  • 같은 기능끼리도 단계 순서가 문제: 원장 INSERT가 FK 확인으로 잡는 S락과 잔액 UPDATE의 X락이 엇갈려 데드락 (시나리오 1 · 2)
  • 판단과 실행 사이의 틈: 잠그지 않고 읽은 잔액으로 판단하면 갱신 분실 (시나리오 3 · 4)
  • 다른 기능 사이: 화면 조회는 잠그지 않아도 되고, 판단용 조회만 잠그면 됨 (시나리오 5)
💡 정리: 락은 계좌 잔액이라는 하나의 자원을 두고 조회 · 판단 · 기록 · 차감이 서로의 결과를 깨뜨리지 않도록 순서를 정하는 장치입니다. 무엇을 잠글지(판단용 조회)와 무엇을 잠그지 않을지(화면 조회)를 구분하는 것까지가 설계입니다.
키보드 방향키 [← / →] 또는 하단 버튼을 클릭하여 슬라이드를 이동하세요.