격리 수준과 잠금

트랜잭션 두 개가 모두 원자적이어도 같은 행을 동시에 읽고 바꾸면 예상하지 못한 순서가 생깁니다.

격리 수준은 다른 트랜잭션의 변경을 언제 보게 할지 정하고, 잠금 읽기는 앞으로 변경할 행을 현재 버전 기준으로 잠급니다.

MySQL InnoDB의 기본 격리 수준인 REPEATABLE READ에서 일반 SELECT는 일관된 스냅샷을 읽습니다.

반면 SELECT ... FOR UPDATE는 최신 커밋 버전을 읽고 배타 잠금을 잡습니다.

두 읽기를 섞어 잔액 판단을 하면 서로 다른 시점을 볼 수 있으므로 목적을 구분합니다.

독립 fixture인 member_point_accounts에서 회원 1의 게시판 포인트를 두 DB 세션이 동시에 차감하려 합니다.

세션 A가 행 잠금을 가진 동안 세션 B는 NOWAIT로 오류를 즉시 반환하게 하여 대기 대신 재시도 정책을 적용합니다.

이 문서의 잠금 예제는 게시글 등록 트랜잭션과 분리된 작은 포인트 계정으로 행 경쟁만 관찰합니다.

먼저 아무 연결에서나 fixture를 한 번 만들고 모든 경쟁 예제를 100·20에서 시작합니다.

DROP TABLE IF EXISTS member_point_accounts;
CREATE TABLE member_point_accounts (
  member_id BIGINT UNSIGNED NOT NULL,
  balance INT UNSIGNED NOT NULL,
  PRIMARY KEY (member_id),
  CONSTRAINT fk_point_account_member
    FOREIGN KEY (member_id) REFERENCES members (id)
) ENGINE = InnoDB;

INSERT INTO member_point_accounts (member_id, balance)
VALUES (1, 100), (2, 20);

그다음 터미널 A와 B를 열고, 아래 문장을 각 터미널에서 한 번씩 실행합니다.

SET SESSION은 서버 전체가 아니라 실행한 연결 하나에만 적용되므로 한 터미널에서 대신 실행할 수 없습니다.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

표준 격리 수준

격리 수준은 동시 실행을 완전히 없애는 설정이 아니라 어떤 읽기 이상을 허용할지 정하는 단계입니다.

먼저 세 용어를 구분합니다.

• 더티 리드(Dirty Read)는 다른 트랜잭션이 아직 커밋하지 않은 값을 읽는 현상입니다. 상대가 롤백하면 읽었던 값은 확정된 적이 없는 값이 됩니다.
• 반복 불가능 읽기(Non-repeatable Read)는 같은 행을 두 번 읽는 사이 다른 트랜잭션이 커밋해 두 결과가 달라지는 현상입니다.
• 팬텀 리드(Phantom Read)는 같은 범위 조건을 다시 조회했을 때 새로 삽입되거나 삭제된 행 때문에 결과 집합의 행 수가 달라지는 현상입니다.

SQL 표준의 큰 틀은 다음과 같습니다.

발생 가능은 반드시 매번 일어난다는 뜻이 아니라 해당 격리 수준이 그 현상을 금지하지 않는다는 뜻입니다.

MySQL InnoDB의 REPEATABLE READ는 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어) 스냅샷과 다음-키 잠금을 사용해 표준 표보다 강한 동작을 보이는 경우가 많습니다.

MVCC는 읽기가 특정 시점의 행 버전을 보게 해 읽기와 쓰기의 불필요한 충돌을 줄이는 방식입니다.

그러나 일반 스냅샷 SELECT와 SELECT ... FOR UPDATE의 읽기 시점은 다르고, 조건식·인덱스·잠금 종류에 따라 동작도 달라집니다.

표를 외운 뒤 곧바로 “MySQL에서는 팬텀이 절대 없다”고 단정하지 말고 실제 구문과 실행 계획을 함께 확인합니다.

현재 연결의 격리 수준은 다음처럼 조회하고 바꿀 수 있습니다.

SET SESSION은 다음 트랜잭션부터 현재 연결에 적용하므로 연결 풀에 반환하기 전에 원래 설정으로 복구해야 합니다.

SELECT @@transaction_isolation AS current_isolation;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT @@transaction_isolation AS isolation_in_transaction;
COMMIT;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

두 터미널에서 더티 리드를 작게 재현하면 각 수준의 차이가 분명해집니다.

먼저 세션 A가 값을 바꾸되 커밋하지 않습니다.

-- session A
START TRANSACTION;
UPDATE member_point_accounts
SET balance = 777
WHERE member_id = 1;
-- 아직 COMMIT하지 않는다.

-- session B: READ UNCOMMITTED에서 실행
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM member_point_accounts WHERE member_id = 1;
COMMIT;

-- session A
ROLLBACK;

-- session B: READ COMMITTED에서 다시 실행
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM member_point_accounts WHERE member_id = 1;
COMMIT;

-- session B cleanup: 뒤 실습이 기본값에서 시작하게 복구한다.
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

READ UNCOMMITTED: 777을 볼 수 있음
session A ROLLBACK 뒤 확정 값: 원래 balance
READ COMMITTED: 미확정 777을 보지 않음

반복 불가능 읽기는 세션 B가 중간에 같은 행을 커밋하도록 하고 세션 A가 앞뒤로 같은 행을 읽어 비교합니다.

READ COMMITTED에서는 두 번째 값이 달라질 수 있고, REPEATABLE READ의 일반 SELECT는 같은 스냅샷 값을 유지합니다.

팬텀 리드는 단일 행 대신 WHERE balance >= 100 같은 범위를 두 번 세고 사이에 조건을 만족하는 행을 INSERT해 같은 방식으로 관찰합니다.

SERIALIZABLE은 이상 현상을 줄이는 대신 읽기·쓰기 대기와 교착 상태 가능성을 높이므로 정확성이 필요하다는 이유만으로 모든 요청에 일괄 적용하지 않습니다.

스냅샷 기반 갱신의 위험

A와 B가 모두 잔액 100을 읽고 각각 30, 50을 뺀 절대값 70과 50을 UPDATE합니다.

마지막 UPDATE가 앞선 결과를 덮으면 실제 80점이 아닌 50점만 차감된 것처럼 남는 손실 갱신이 됩니다.

-- session A
START TRANSACTION;
SELECT balance FROM member_point_accounts WHERE member_id = 1; -- 100

-- session B
START TRANSACTION;
SELECT balance FROM member_point_accounts WHERE member_id = 1; -- 100
UPDATE member_point_accounts SET balance = 50 WHERE member_id = 1;
COMMIT;

-- session A
UPDATE member_point_accounts SET balance = 70 WHERE member_id = 1;
COMMIT;

두 차감 의도는 80점이지만 마지막 절대값 쓰기가 다른 변경을 덮어 최종 잔액이 70이 될 수 있습니다.

initial balance: 100
session A intends: -30
session B intends: -50
expected serial result: 20
final absolute-write result: 70
lost change: session B's -50

일반 SELECT와 나중의 UPDATE 사이에 다른 트랜잭션이 끼어들 수 있습니다.

REPEATABLE READ 스냅샷이 오래 같은 값을 보여 주는 것은 계산 결과를 자동 보호한다는 뜻이 아닙니다.

가능하면 SET balance = balance - ? WHERE balance >= ? 같은 상대 조건부 UPDATE 하나로 원자화합니다.

여러 행·추가 판단이 필요하면 FOR UPDATE로 최신 행을 잠그고 일관된 순서로 접근합니다.

두 세션의 읽기와 쓰기를 시간축에 놓기

1. 두 세션 표 — 각 구문의 시작·대기·커밋 순서를 타임라인으로 적습니다.
2. 읽기 종류 — 일반 스냅샷인지 잠금 현재 읽기인지 표시합니다.
3. 쓰기 모양 — 절대값 덮어쓰기인지 상대 UPDATE인지 구분합니다.
4. 재시도 조건 — 잠금 시간 초과·교착 상태 SQLState만 제한적으로 재시도합니다.

스냅샷 읽기와 잠금 읽기

일반 SELECT는 일관된 보고서·조회에 적합합니다.

FOR UPDATE와 FOR SHARE는 최신 커밋 버전을 기준으로 잠금을 얻어 후속 변경의 전제 조건을 보호합니다.

REPEATABLE READ는 같은 스냅샷에서 반복 불가능 읽기를 막지만 범위 잠금과 누락 구간 잠금 비용도 고려해야 합니다.

READ COMMITTED는 구문마다 새 스냅샷을 보므로 관찰 방식이 달라지지만 손실 갱신을 자동으로 모두 해결하지 않습니다.

격리 수준을 높이는 대신 쿼리를 원자화하거나 필요한 행만 명시적으로 잠그는 설계가 더 분명할 수 있습니다.

스냅샷과 현재 읽기를 구분하기

1. 단일 행 — 조건부 상대 UPDATE 하나로 해결할 수 있는지 먼저 봅니다.
2. 복수 행 — 항상 작은 member_id부터 같은 순서로 잠금을 획득합니다.
3. 대기 정책 — 짧게 기다릴지 NOWAIT로 오류를 즉시 반환할지 API SLA에 맞춥니다.
4. 재시도 — 전체 트랜잭션을 새 스냅샷에서 다시 실행하고 대기 간격·횟수 제한을 둡니다.

동시 갱신 직렬화

잔액 차감 하나는 원자 UPDATE로 처리합니다.

두 계정 이동은 ID 오름차순으로 FOR UPDATE하여 서로 반대 방향 이동도 같은 잠금 순서를 갖게 합니다.

-- 앞의 손실 갱신 예제가 바꾼 값을 다시 고정한다.
UPDATE member_point_accounts
SET balance = CASE member_id
  WHEN 1 THEN 100
  WHEN 2 THEN 20
END
WHERE member_id IN (1, 2);

START TRANSACTION;

SELECT member_id, balance
FROM member_point_accounts
WHERE member_id IN (1, 2)
ORDER BY member_id
FOR UPDATE;

UPDATE member_point_accounts
SET balance = balance - 60
WHERE member_id = 1 AND balance >= 60;

UPDATE member_point_accounts
SET balance = balance + 60
WHERE member_id = 2;

COMMIT;

동시에 같은 계정을 바꾸는 트랜잭션은 같은 ID 순서의 잠금에서 대기하고 앞 트랜잭션의 커밋 뒤 최신 잔액으로 다시 판단합니다.

session A: locks member 1, 2 → updates → COMMIT
session B: waits at member 1 lock
session B after wake: reads latest committed balances
lost update: none
lock order: 1 then 2 in every transfer

NOWAIT를 붙이면 기다리지 않고 오류 3572를 즉시 반환합니다.

이는 데이터 오류가 아니라 동시성 충돌이므로 짧은 무작위 지연 대기 간격 후 전체 트랜잭션을 다시 시도할 수 있습니다.

FOR UPDATE 범위를 인덱스가 좁히지 못하면 예상보다 많은 기록·누락 구간을 잠글 수 있습니다.

PK나 적절한 유일 제약 키로 정확한 행을 찾고 트랜잭션 안에서 사용자 입력 대기나 외부 호출을 하지 않습니다.

NOWAIT부터 역방향 이동까지 재현하기

1. 잠금 보유 — 세션 A가 FOR UPDATE 뒤 커밋하지 않은 상태를 만듭니다.
2. NOWAIT 충돌 — 세션 B에서 FOR UPDATE NOWAIT를 실행해 3572를 확인합니다.
3. 커밋 후 재시도 — A를 커밋하고 B 전체 트랜잭션을 처음부터 다시 실행합니다.
4. 역방향 이동 — 2→1 이동도 1,2 순으로 잠가 교착 상태 빈도를 비교합니다.

NOWAIT와 잠금 확인

두 터미널을 열어 구문 순서를 고정합니다.

오류 문자열을 복사하기보다 세션·순서·SQLState·재시도 결과를 함께 기록합니다.

-- session A
START TRANSACTION;
SELECT member_id, balance
FROM member_point_accounts
WHERE member_id = 1
FOR UPDATE;

-- session B, A가 아직 열린 동안
START TRANSACTION;
SELECT member_id, balance
FROM member_point_accounts
WHERE member_id = 1
FOR UPDATE NOWAIT;
-- ERROR 3572, then ROLLBACK

-- session A
COMMIT;

세션 B는 ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.를 받고 트랜잭션을 롤백합니다.

A의 값은 바뀌지 않으며 B는 새 트랜잭션으로 재시도합니다.

경쟁 제어 방식 비교

게시글 버전으로 손실 갱신 막기

회원 게시판의 글 수정은 긴 잠금 대신 version을 비교하는 낙관적 제어가 적합할 수 있습니다.

두 편집자가 모두 버전 4를 읽었더라도 먼저 성공한 UPDATE만 버전을 5로 올립니다.

UPDATE posts
SET title = '첫 번째 편집',
    content = '버전 4를 기준으로 저장',
    updated_at = CURRENT_TIMESTAMP(6),
    version = version + 1
WHERE id = 9
  AND version = 4;

첫 요청의 ROW_COUNT()는 1이고 뒤 요청은 같은 조건으로 0을 반환합니다.

0행을 성공으로 숨기지 말고 최신 글과 버전을 다시 읽어 충돌 응답을 만들거나 사용자가 병합하도록 합니다.

교착 상태와 다음-키 잠금의 실제 비용

교착 상태는 오류 1213으로 한 트랜잭션이 희생됩니다.

모든 교착 상태를 제거하기보다 잠금 순서와 범위를 줄이고, 멱등한 전체 트랜잭션 재시도를 준비합니다.

잠금 대기 시간 초과 1205와 원인·재시도 정책도 구분합니다.

범위 쿼리의 다음-키 잠금은 팬텀을 막는 대신 동시 INSERT를 차단할 수 있습니다.

보고서에는 스냅샷 SELECT, 예약·할당에는 좁은 잠금 읽기처럼 목적별로 사용합니다.

SKIP LOCKED·장기 스냅샷의 경계

• SKIP LOCKED는 작업 큐에는 유용하지만 잠긴 업무 행을 조용히 누락하므로 일반 목록 정답에 사용하지 않습니다.
• FOR UPDATE를 자동 커밋 밖의 명시적 트랜잭션 없이 쓰면 잠금 수명이 기대와 달라질 수 있습니다.
• 인덱스 없는 조건식은 많은 행을 잠가 처리량을 급격히 낮출 수 있습니다.
• 장기 스냅샷은 영구 삭제를 지연시켜 언두 로그 이력을 키울 수 있으므로 보고 트랜잭션도 수명을 제한합니다.

잠금 대기를 운영 신호로 읽기

교착 상태 로그와 performance_schema의 data_locks·data_lock_waits를 수집하되 개인정보가 있는 SQL 매개변수 노출을 관리합니다.

재시도는 트랜잭션 전체를 대상으로 하고 최대 횟수·지수 대기 간격·무작위 지연을 둡니다.

부분 구문만 반복하면 스냅샷과 원장 선점이 어긋납니다.

두 터미널로 이상을 만들기

1. 손실 갱신 — 절대값 UPDATE와 상대 UPDATE를 두 세션에서 비교합니다.
2. 격리 변경 — READ COMMITTED와 REPEATABLE READ의 일반 SELECT 두 번째 값을 기록합니다.
3. 교착 상태 — 두 계정을 반대 순서로 잠가 1213을 만든 뒤 정렬 순서로 고칩니다.
4. 범위 잠금 — 인덱스 있는 범위와 없는 범위에서 동시 INSERT 대기를 비교합니다.

동시성 경계가 명확해도 트리거가 숨은 UPDATE를 수행하면 잠금 순서와 오류 위치를 예측하기 어렵습니다.

다음 문서에서 DB 로직의 가시성과 현대적 대안을 판단합니다.

격리 수준과 잠금 선택

보고 읽기는 스냅샷, 변경 전 판단은 좁은 잠금 읽기 또는 원자 UPDATE로 구분합니다.

격리 수준 이름만 높이는 대신 실제 이상과 잠금 범위를 재현합니다.

연습 문제

두 편집자가 같은 게시글의 version=7을 읽고 서로 다른 제목으로 저장합니다.

먼저 저장한 변경을 뒤 요청이 덮어쓰지 않도록 조건부 UPDATE를 작성하세요.

UPDATE posts
SET title = '편집자 A의 제목',
    updated_at = CURRENT_TIMESTAMP(6),
    version = version + 1
WHERE id = 9
  AND version = 7;

SELECT ROW_COUNT() AS changed_rows;

핵심 정리

• 일반 SELECT 스냅샷과 FOR UPDATE 현재 읽기는 목적이 다릅니다.
• 상대 조건부 UPDATE는 단일 행 경쟁을 간단히 원자화합니다.
• 게시글 version 조건은 늦게 도착한 편집이 최신 내용을 덮어쓰지 못하게 합니다.
• 여러 행은 일관된 순서로 좁게 잠가 교착 상태를 줄입니다.
• NOWAIT·교착 상태 재시도는 전체 트랜잭션과 멱등성을 전제로 합니다.

다음 문서에서는 저장 프로시저·함수·트리거가 적합한 경계와 숨은 로직의 함정을 살펴봅니다.