격리 수준과 잠금

트랜잭션 두 개가 모두 원자적이어도, 같은 행을 동시에 읽고 바꾸면 예상하지 못한 순서가 생깁니다. 격리 수준(isolation level)은 다른 트랜잭션의 변경을 언제 보게 할지 정하고, 잠금 읽기(locking read)는 앞으로 바꿀 행을 지금의 버전으로 읽으며 잠급니다.

MySQL InnoDB의 기본 격리 수준인 REPEATABLE READ에서 일반 SELECT는 일관된 스냅샷을 읽습니다. 반면 SELECT ... FOR UPDATE는 지금 확정된 최신 값을 읽고 배타 잠금을 잡으며, 다른 트랜잭션이 확정하지 않은 변경이 있으면 기다릴 수 있고 자기 트랜잭션의 변경도 봅니다. 두 읽기를 섞어 잔액을 판단하면 서로 다른 시점을 보게 되므로, 목적을 나누어 씁니다.

두 연결로 실습을 준비한다

이 글은 게시글 등록과 떨어진 작은 포인트 계정 member_point_accounts로 행의 경쟁만 살펴봅니다. 회원 1의 게시판 포인트를 두 연결이 동시에 깎으려 하는 상황이며, 회원 1과 2가 members에 있어야 합니다. 경쟁을 시작하기 전에 한 연결에서만 테이블을 만들거나 잔액을 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);

그다음 터미널 두 개로 각각 MySQL에 접속해 세션 A와 세션 B로 쓰고, 아래 문장을 두 터미널에서 한 번씩 실행합니다. SET SESSION은 서버 전체가 아니라 실행한 연결 하나에만 적용되므로, 한 터미널에서 대신 실행할 수 없습니다. 이 글의 결과는 다른 연결이 끼어들지 않는다는 전제에서 문서의 규칙으로 정리한 것입니다.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

표준 SQL의 격리 수준

격리 수준은 동시 실행을 없애는 설정이 아니라, 어떤 읽기의 이상을 허용할지 정하는 단계입니다. 더티 리드(dirty read)는 다른 트랜잭션이 아직 커밋하지 않은 값을 읽는 현상으로, 상대가 롤백하면 읽은 값은 한 번도 확정된 적이 없는 값이 됩니다. 반복 불가능 읽기(non-repeatable read)는 같은 행을 두 번 읽는 사이에 다른 트랜잭션이 커밋해 두 결과가 달라지는 현상이고, 팬텀 리드(phantom read)는 같은 범위의 조건을 다시 조회했을 때 새로 들어오거나 지워진 행 때문에 결과의 행 수가 달라지는 현상입니다.

표의 "허용"은 매번 일어난다는 뜻이 아니라 그 격리 수준이 그 현상을 금지하지 않는다는 뜻입니다. SERIALIZABLE은 세 이상을 모두 막지만, 충돌하는 작업의 대기와 재시도라는 비용이 따릅니다.

InnoDB의 일반 읽기는 다중 버전 동시성 제어(Multi-Version Concurrency Control, MVCC)로 특정 시점의 행 버전을 보여 주어, 읽기와 쓰기의 불필요한 충돌을 줄입니다. READ COMMITTED에서는 문장마다 새 스냅샷을, REPEATABLE READ에서는 트랜잭션의 첫 일관 읽기가 정한 스냅샷을 다시 씁니다. 같은 트랜잭션에서 자기가 바꾼 것은 보이므로, 결과가 언제나 처음 모습과 같다는 뜻은 아닙니다.

InnoDB의 REPEATABLE READ는 스냅샷과, 인덱스 레코드 하나와 그 바로 앞의 빈 구간을 함께 잠그는 다음 키 잠금(next-key lock)을 써서 표준의 최소 보장보다 강하게 동작하는 경우가 많습니다. 그러나 일반 스냅샷 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;

current_isolation
REPEATABLE-READ

isolation_in_transaction
READ-COMMITTED

변수의 값은 이름의 공백 대신 하이픈을 써서 나타납니다.

더티 리드를 두 연결로 비교한다

세션 A가 값을 바꾸되 커밋하지 않은 상태에서, 세션 B가 READ UNCOMMITTED와 READ COMMITTED로 한 번씩 읽습니다. A가 미커밋이라는 조건을 같게 두어야 두 격리 수준의 차이를 볼 수 있습니다.

-- session A: 확정된 balance=100에서 시작
START TRANSACTION;
UPDATE member_point_accounts
SET balance = 777
WHERE member_id = 1;
-- 아직 COMMIT하지 않는다.

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

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM member_point_accounts WHERE member_id = 1;
COMMIT;

-- session A
ROLLBACK;

-- session B: 뒤의 실습이 기본값에서 시작하게 되돌린다.
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

balance
777

balance
100

READ UNCOMMITTED의 B는 A가 확정하지 않은 777을 읽었고, READ COMMITTED의 B는 마지막으로 확정된 100을 읽었습니다. 일반 SELECT는 A가 잡은 행 잠금을 기다리지 않고 확정된 버전을 읽으므로 두 번째 조회도 바로 끝납니다. A가 롤백한 뒤의 확정 값은 다시 100이며, 777은 한 번도 확정된 적이 없는 값이 되었습니다. MySQL 8.4의 격리 수준 설명은 두 수준의 이 차이를 구별합니다.

반복 불가능 읽기는 세션 A가 같은 행을 앞뒤로 두 번 읽는 사이에 세션 B가 그 행을 바꾸어 커밋하게 해서 봅니다. READ COMMITTED에서는 두 번째 값이 달라질 수 있고, REPEATABLE READ의 일반 SELECT는 같은 스냅샷의 값을 유지합니다. 팬텀 리드는 한 행 대신 WHERE balance >= 100 같은 범위를 두 번 세고, 그사이 조건을 만족하는 행을 INSERT하는 방식으로 같은 비교를 합니다. SERIALIZABLE은 이상을 줄이는 대신 대기와 교착 상태의 가능성을 높이므로, 정확성이 필요하다는 이유만으로 모든 요청에 일괄 적용하지 않습니다.

스냅샷으로 계산한 값을 쓰면 갱신을 잃는다

A와 B가 모두 잔액 100을 읽고, 각자 30과 50을 뺀 결과인 70과 50을 고정된 값으로 UPDATE한다고 해 봅시다. 아래는 두 연결에서 실행할 문장을 시간 순서대로 한곳에 적은 것으로, -- session A와 -- session B 주석이 어느 연결에서 실행할지를 나타냅니다. 두 세션 모두 SELECT로 읽은 값을 애플리케이션에서 계산한 뒤, 그 결과를 SET balance = 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;

SELECT balance FROM member_point_accounts WHERE member_id = 1;

balance
70

이 순서대로 모두 성공하면 최종 잔액은 70입니다. 두 차감을 차례로 반영했다면 100에서 50과 30을 뺀 20이어야 하지만, B가 쓴 50을 A가 낡은 계산값 70으로 덮어 B의 차감 50이 사라졌습니다. 이것을 손실 갱신(lost update)이라 하며, MySQL은 이 순서에서 오류를 내지 않습니다.

일반 SELECT와 나중의 UPDATE 사이에 다른 트랜잭션이 끼어들 수 있으며, REPEATABLE READ의 스냅샷이 같은 값을 오래 보여 준다고 그 값으로 계산한 결과가 보호되지는 않습니다. 가능하면 SET balance = balance - ? WHERE balance >= ?처럼 지금의 값에서 상대적으로 바꾸는 조건부 UPDATE 하나로 원자화합니다. 여러 행이나 추가의 판단이 필요하다면 FOR UPDATE로 최신의 행을 잠그고, 언제나 같은 순서로 접근합니다.

경쟁을 분석할 때는 두 세션의 문장을 시간의 축에 놓고, 각 문장이 시작하고 기다리고 커밋한 순서를 적습니다. 읽기가 일반 스냅샷인지 잠금을 잡는 지금의 읽기인지, 쓰기가 고정값의 덮어쓰기인지 상대적인 UPDATE인지 표시하고, 잠금 대기 시간 초과와 교착 상태 같은 특정한 오류만 제한적으로 재시도합니다.

스냅샷 읽기와 잠금 읽기

일반 SELECT는 일관된 보고서와 조회에 알맞습니다. FOR UPDATE와 FOR SHARE는 지금의 값을 읽으며 잠금을 얻어, 뒤따를 변경의 전제를 보호합니다. 자기 트랜잭션의 변경을 포함하고, 다른 트랜잭션이 확정하지 않은 변경이 있으면 잠금의 규칙에 따라 기다립니다.

REPEATABLE READ는 같은 스냅샷에서 반복 불가능 읽기를 막지만, 범위 잠금과 빈 구간을 잠그는 갭 잠금(gap lock)의 비용도 함께 고려해야 합니다. READ COMMITTED는 문장마다 새 스냅샷을 보므로 보이는 모습이 달라질 뿐, 손실 갱신을 모두 자동으로 해결하지는 않습니다. 격리 수준을 높이는 대신 쿼리를 원자화하거나 필요한 행만 명시적으로 잠그는 설계가 더 분명할 수 있습니다.

선택의 순서는 이렇습니다. 한 행이라면 조건부 상대 UPDATE 하나로 해결할 수 있는지 먼저 보고, 여러 행이라면 언제나 작은 member_id부터 같은 순서로 잠급니다. 짧게 기다릴지 NOWAIT로 곧바로 실패할지는 API의 SLA에 맞추고, 재시도는 새 스냅샷에서 트랜잭션 전체를 다시 실행하되 대기 간격과 횟수에 제한을 둡니다.

더 깊이: InnoDB가 실제로 잠그는 범위

잠금 읽기와 UPDATE, DELETE는 문장을 처리하면서 훑은 인덱스 레코드마다 잠금을 겁니다. InnoDB는 WHERE 조건 자체를 기억하지 않고 어느 인덱스 범위를 읽었는지만 알기 때문에, 훑은 레코드라면 WHERE가 나중에 걸러 낸 행이라도 잠깁니다. 이 잠금은 보통 레코드와 그 바로 앞의 빈 구간까지 막는 다음 키 잠금입니다. 그래서 알맞은 인덱스가 없어 테이블 전체를 훑는 UPDATE는 테이블의 모든 행을 잠그고, 다른 사용자의 INSERT까지 모두 막습니다.

예외는 유일한 인덱스를 유일한 조건으로 찾는 경우입니다. 이때 InnoDB는 찾은 레코드만 잠그고 그 앞의 빈 구간은 잠그지 않으므로, 이 글의 이동 예제처럼 회원의 기본 키로 행을 잠그면 잠금의 범위가 행 하나로 줄어듭니다. 보조 인덱스로 찾아 배타 잠금을 걸 때는, 그 항목이 가리키는 클러스터형 인덱스의 레코드도 함께 잠급니다.

READ COMMITTED에서는 범위가 달라집니다. MySQL 8.4 매뉴얼에 따르면 이 수준의 빈 구간 잠금은 외래 키 검사와 중복 키 검사에만 쓰이므로, 다른 세션이 빈 구간에 행을 넣어 팬텀이 생길 수 있습니다. UPDATE와 DELETE가 훑은 행 가운데 WHERE에 맞지 않는 행의 레코드 잠금은 WHERE를 평가한 뒤 풀립니다. 또 UPDATE가 이미 잠긴 행을 만나면, 마지막으로 커밋된 버전을 읽어 WHERE에 맞는지부터 판단하는 반일관 읽기(semi-consistent read)를 합니다.

INSERT의 잠금도 따로 알아 둘 만합니다. INSERT는 행을 넣기 전에 삽입 의도 잠금(insert intention lock)이라는 빈 구간 잠금을 걸어, 같은 구간이라도 서로 다른 위치에 넣는 트랜잭션끼리는 기다리지 않게 합니다. 반면 중복 키 오류가 나면 겹친 레코드에 공유 잠금을 걸기 때문에, 같은 키를 여러 세션이 동시에 넣다가 먼저 넣은 세션이 롤백하면 남은 세션끼리 교착 상태에 빠질 수 있습니다. 같은 요청 키의 재시도가 동시에 몰리면 1062뿐 아니라 1213도 받을 수 있다는 뜻입니다.

동시 갱신을 직렬화한다

잔액의 차감 하나는 원자적인 UPDATE로 처리하고, 두 계정 사이의 이동은 두 행을 같은 순서로 잠근 뒤 처리합니다. 아래 SQL은 앞의 손실 갱신이 바꾼 값을 먼저 되돌린 뒤 회원 1에서 2로 60점을 옮기는 성공 경로입니다. 잠금 SELECT로 두 행과 잔액을 확인하고, 각 UPDATE가 바꾼 행이 1인지 확인한 경우에만 다음 단계와 COMMIT으로 넘어갑니다.

-- 앞의 손실 갱신 예제가 바꾼 값을 다시 고정한다.
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;

member_id | balance
1         | 100
2         | 20

잠금 SELECT는 두 행을 보여 주고, 두 UPDATE가 각각 한 행을 바꾼 뒤 커밋하면 잔액은 40과 80이 되어 합계 120이 그대로입니다. 같은 계정의 잠금이 충돌하면 뒤의 트랜잭션은 기다리며, 잠금을 얻은 뒤에는 그때 읽은 잔액으로 조건을 다시 판단해야 합니다. 잠금 자체가 잔액이 부족한 이동을 승인해 주지는 않습니다.

이어서 같은 방향으로 60점을 더 옮기려 하면, 잔액 40은 balance >= 60을 만족하지 못해 차감이 0행입니다. 이때 두 번째 UPDATE를 계속 실행하면 40과 140이 되어 합계가 180이 되고, 없던 60점을 만든 셈이 됩니다. 조건부 차감의 0행을 성공으로 읽지 말고 전체를 ROLLBACK해야 하며, UPDATE마다 영향받은 행의 수를 검사하는 것은 단순한 기록이 아니라 다음 SQL을 실행할지 정하는 분기입니다.

모든 경로가 기본 키의 같은 순서로 잠금을 얻도록 설계합니다. 다만 ORDER BY는 결과의 순서일 뿐 실제로 잠금을 얻는 순서까지 보장하지는 않으므로, 실행 계획을 확인하거나 순서를 확실히 정해야 한다면 기본 키로 한 행씩 같은 순서로 잠급니다. FOR UPDATE의 범위를 인덱스가 좁히지 못하면 예상보다 많은 행과 빈 구간을 잠글 수 있으므로, 기본 키나 알맞은 UNIQUE 키로 정확한 행을 찾고 트랜잭션 안에서 사용자의 입력이나 바깥 호출을 기다리지 않습니다.

NOWAIT로 잠금의 충돌을 확인한다

NOWAIT를 붙이면 잠금을 기다리지 않고 곧바로 오류를 돌려줍니다. 두 터미널에서 문장의 순서를 고정해 실행합니다.

-- 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;
ROLLBACK;

-- session A
COMMIT;

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

A가 회원 1의 잠금을 쥐고 있는 동안 B의 NOWAIT는 오류 3572를 냅니다. B는 열린 트랜잭션을 ROLLBACK으로 끝내고, A의 값은 바뀌지 않습니다. 이것은 데이터의 오류가 아니라 동시성의 충돌이므로, 짧게 무작위로 기다린 뒤 B의 트랜잭션 전체를 처음부터 다시 실행할 수 있습니다. 오류 문자열을 복사해 두기보다, 세션과 순서, SQLSTATE와 재시도의 결과를 함께 기록합니다.

게시글의 버전으로 늦은 편집을 막는다

게시글의 수정에는 긴 잠금 대신 version을 비교하는 낙관적 동시성 제어(optimistic concurrency control)가 알맞을 수 있습니다. 두 편집자가 같은 버전을 읽었더라도 먼저 성공한 UPDATE만 버전을 올리고, 늦게 온 요청은 0행으로 드러납니다. 다음 예는 앞 글에서 넣은 post-20260714-001의 글을 요청 키로 찾으며, 그 글의 버전은 0입니다. 누적 데이터를 바꾸지 않도록 트랜잭션 안에서 실행하고 되돌립니다.

START TRANSACTION;

UPDATE posts
SET title = '첫 번째 편집',
    updated_at = CURRENT_TIMESTAMP(6),
    version = version + 1
WHERE author_id = 1
  AND request_key = 'post-20260714-001'
  AND version = 0;
SELECT ROW_COUNT() AS first_editor;

UPDATE posts
SET title = '두 번째 편집',
    updated_at = CURRENT_TIMESTAMP(6),
    version = version + 1
WHERE author_id = 1
  AND request_key = 'post-20260714-001'
  AND version = 0;
SELECT ROW_COUNT() AS second_editor;

ROLLBACK;

first_editor
1

second_editor
0

첫 편집은 버전 0을 기대했고 실제로 0이었으므로 한 행을 바꾸어 버전을 1로 올렸습니다. 두 번째 편집도 버전 0을 기대했지만 이미 1이 되었으므로 0행을 바꿉니다. 0행을 성공으로 숨기지 말고, 최신의 글과 버전을 다시 읽어 충돌을 응답하거나 사용자가 병합하게 합니다.

교착 상태와 재시도

두 트랜잭션이 서로가 쥔 잠금을 기다리면 교착 상태(deadlock)가 되고, InnoDB는 그중 하나를 골라 오류 1213으로 희생시킵니다. 교착 상태가 생기는 네 가지 조건은 운영체제의 교착 상태의 개념에서 자세히 다룹니다. 교착 상태를 모두 없애기보다, 잠금의 순서와 범위를 줄이고 멱등한 트랜잭션 전체를 다시 실행할 준비를 합니다.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

1213은 트랜잭션 전체를 롤백합니다. 반면 잠금을 기다리다 시간을 넘긴 1205는 기본 설정에서 시간을 넘긴 문장만 롤백하며, 범위는 innodb_rollback_on_timeout의 설정에 따라 달라집니다. 그래서 이 실습의 재시도는 언제나 남은 트랜잭션을 명시적으로 ROLLBACK한 뒤, 업무 전체를 새 트랜잭션으로 시작합니다. 문장 일부만 되풀이하면 스냅샷과 이미 차지한 행이 어긋납니다.

범위 쿼리의 다음 키 잠금은 팬텀을 막는 대신 그 범위에 대한 동시 INSERT를 막을 수 있습니다. 그래서 보고서에는 스냅샷 SELECT를, 예약이나 할당에는 좁은 잠금 읽기를 쓰는 식으로 목적에 맞게 나눕니다.

작업 큐에 쓰는 SKIP LOCKED는 잠긴 행을 조용히 건너뛰므로, 일반 목록의 정답을 구하는 데 쓰지 않습니다. FOR UPDATE를 명시적인 트랜잭션 없이 자동 커밋으로 실행하면 잠금이 문장과 함께 바로 풀려 기대와 다르게 동작합니다. 인덱스가 없는 조건은 많은 행을 잠가 처리량을 크게 떨어뜨리고, 오래 열린 스냅샷은 지워야 할 언두 로그의 이력을 붙잡아 키우므로 보고용 트랜잭션도 수명을 제한합니다.

운영에서는 교착 상태의 로그와 performance_schema의 data_locks, data_lock_waits를 모으되, 개인정보가 든 SQL 파라미터가 노출되지 않게 관리합니다. 재시도는 트랜잭션 전체를 대상으로 하고, 최대 횟수와 지수적으로 늘어나는 대기 간격, 무작위 지연을 둡니다.

두 터미널로 직접 이상을 만들어 보면 이해가 깊어집니다. 고정값으로 덮어쓰는 UPDATE와 상대 UPDATE를 비교하고, READ COMMITTED와 REPEATABLE READ에서 일반 SELECT의 두 번째 값을 기록합니다. 두 계정을 서로 반대 순서로 잠가 1213을 만든 뒤 정렬된 순서로 고쳐 보고, 인덱스가 있는 범위와 없는 범위에서 동시 INSERT의 대기를 비교합니다. 동시성의 규칙이 분명해도 트리거가 숨은 UPDATE를 실행하면 잠금의 순서와 오류의 위치를 예측하기 어려운데, 이 문제는 다음 글에서 다룹니다.

보고용 읽기는 스냅샷으로, 바꾸기 전의 판단은 좁은 잠금 읽기나 원자적인 UPDATE로 나눕니다. 격리 수준의 이름만 높이기보다, 실제의 이상과 잠금의 범위를 재현해 봅니다.

직렬 가능성과 2단계 잠금

직렬 스케줄(serial schedule)은 T1을 끝낸 뒤 T2를 실행하는 것처럼, 트랜잭션 전체를 차례로 실행하는 것입니다. 직렬 가능한 스케줄(serializable schedule)은 연산이 섞였어도, 정해 둔 뜻에서 어떤 직렬 순서와 같은 결과를 냅니다. 우연히 최종 값 하나가 같다는 것만으로 같다고 증명하지는 않습니다.

충돌 직렬 가능성(conflict serializability)은 서로 다른 트랜잭션이 같은 항목에 접근하고 적어도 하나가 쓸 때 순서의 간선을 그려 판단합니다. w1(X) → r2(X)는 T1에서 T2로, w2(Y) → r1(Y)는 T2에서 T1로 가는 간선이고, 두 간선이 함께 있으면 순환이 생겨 그 스케줄은 충돌 직렬 가능하지 않습니다. 읽기와 읽기만으로는 충돌의 간선을 만들지 않습니다.

2단계 잠금(two-phase locking, 2PL)은 잠금을 얻기만 하는 확장 단계와, 처음 잠금을 푼 뒤로는 새 잠금을 얻지 않는 축소 단계로 나뉩니다. 알맞은 잠금 모델에서 2PL은 충돌 직렬 가능성을 보장하지만, 교착 상태가 생기지 않는다는 뜻은 아닙니다. 엄격한 2단계 잠금(strict 2PL)은 쓰기의 배타 잠금을 트랜잭션이 끝날 때까지 유지해, 끝나지 않은 값을 다른 트랜잭션이 쓰다가 함께 철회되는 연쇄 철회(cascading abort)를 막습니다.

이것은 교과서의 추상적인 잠금 규칙입니다. InnoDB의 일반 MVCC SELECT까지 모두 같은 공유 잠금을 얻는 단순한 2PL의 구현이라고 설명하지는 않으며, 실제의 격리 동작은 앞에서 본 스냅샷과 잠금 읽기, 인덱스의 범위로 확인합니다.

정리

• 일반 SELECT의 스냅샷 읽기와 FOR UPDATE의 잠금 읽기는 목적이 다릅니다.
• 스냅샷에서 계산한 고정값을 UPDATE하면 손실 갱신이 생기며, 상대적인 조건부 UPDATE가 한 행의 경쟁을 원자화합니다.
• 조건부 UPDATE가 0행을 바꾸면 다음 문장으로 넘어가지 말고 롤백합니다.
• 게시글의 version 조건은 늦게 도착한 편집이 최신 내용을 덮어쓰지 못하게 합니다.
• 여러 행은 일관된 순서로 좁게 잠그고, NOWAIT와 교착 상태의 재시도는 트랜잭션 전체를 대상으로 합니다.

연습 문제

1. 버전 7을 읽은 두 편집자

두 편집자가 같은 게시글의 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;

2. 2단계 잠금과 교착 상태

T1이 X의 잠금을 얻은 뒤 T2가 Y의 잠금을 얻고, 이어서 T1은 Y를, T2는 X를 요청합니다. 두 트랜잭션이 모두 2단계 잠금을 지킨다면 어떤 일이 생길 수 있나요?

3. 같은 순서로 잠그면

본문의 두 계정 이동에서, 한 트랜잭션은 1에서 2로, 다른 트랜잭션은 2에서 1로 동시에 포인트를 옮깁니다. 각 트랜잭션이 보내는 쪽의 행을 먼저 잠그면 어떤 일이 생길 수 있고, 어떻게 고치나요?

다음 글 프로시저·함수·트리거에서는 저장 프로시저와 함수, 트리거가 알맞은 경우와, 데이터베이스 안에 숨은 로직이 만드는 함정을 다룹니다.