---
title: "Current read와 locking read: SELECT ... FOR UPDATE/SHARE의 동작"
description: "InnoDB locking read가 최신 커밋 버전을 읽고 record·gap lock을 획득하는 원리와 FOR UPDATE, FOR SHARE의 운영 기준을 설명한다."
tags: [ MySQL, InnoDB, 트랜잭션, 락, 운영 ]
image: "mysql-report-bg.png"
published: "2026-08-01"
updated: "2026-08-01"
author: "MySQL 기술 노트"
source_url: ""
---

InnoDB의 일반 `SELECT`는 대개 MVCC snapshot에서 잠금 없이 일관된 과거 버전을 읽는다. 그러나 재고 차감, 작업 선점, 계좌 상태 변경처럼 **읽은 값을 근거로 곧바로 쓰기 결정을 내려야 하는 업무**에서는 과거 버전만 읽어서는 부족하다. 조회 직후 다른 트랜잭션이 같은 행을 바꾸면 애플리케이션의 판단 전제가 사라지기 때문이다. 이때 사용하는 도구가 `SELECT ... FOR UPDATE`와 `SELECT ... FOR SHARE`다.

두 문장은 단순히 일반 `SELECT`에 잠금만 덧붙인 구문이 아니다. InnoDB가 최신 커밋 버전을 기준으로 조건을 다시 판정하는 **current read**이며, 검색에 사용한 인덱스 레코드와 경우에 따라 레코드 사이의 gap까지 잠근다. 따라서 snapshot read와 가시성, 동시성, deadlock 위험, 인덱스 설계의 의미가 모두 달라진다. 이 글은 MySQL 8.0 이상을 기준으로 current read의 실행 경로, `FOR UPDATE`와 `FOR SHARE`의 차이, `NOWAIT`·`SKIP LOCKED`, 격리 수준과 인덱스에 따른 잠금 범위, 운영 진단 절차를 설명한다.

## 1. Snapshot read와 current read의 경계

일반 `SELECT`와 locking read의 가장 중요한 차이는 “잠금을 잡는가”보다 **어느 행 버전을 업무 판단의 기준으로 삼는가**에 있다.

| 읽기 방식 | 대표 문장 | 가시성 기준 | 대표 잠금 | 주 용도 |
|---|---|---|---|---|
| Snapshot read | 일반 `SELECT` | Read View에 보이는 버전 | 보통 사용자 레코드 잠금 없음 | 조회, 보고서, 화면 표시 |
| Current locking read | `SELECT ... FOR SHARE` | 실행 시점의 최신 커밋 버전과 자기 변경 | shared 계열 record/gap lock | 존재·상태를 보호하며 후속 읽기 또는 제한된 쓰기 |
| Current locking read | `SELECT ... FOR UPDATE` | 실행 시점의 최신 커밋 버전과 자기 변경 | exclusive 계열 record/gap lock | 읽은 행을 같은 트랜잭션에서 변경 |
| Current write | `UPDATE`, `DELETE` | 최신 버전에서 조건 재평가 | exclusive 계열 record/gap lock | 원자적 데이터 변경 |

`REPEATABLE READ` 트랜잭션에서 이미 일반 `SELECT`로 `balance = 100`을 본 뒤 다른 세션이 `130`으로 변경하고 커밋했다고 가정하자. 같은 트랜잭션의 다음 일반 `SELECT`는 기존 Read View를 사용해 계속 `100`을 볼 수 있다. 반면 `SELECT ... FOR UPDATE`는 current read이므로 `130`을 읽고 그 최신 레코드를 잠근다.

여기서 주의할 점은 locking read가 기존 Read View를 새 시점으로 이동시키는 것이 아니라는 사실이다. `FOR UPDATE`로 `130`을 본 뒤 다시 일반 `SELECT`를 실행하면 자기 트랜잭션이 값을 변경하지 않은 한 기존 snapshot의 `100`이 다시 보일 수 있다. 하나의 트랜잭션 안에서 서로 다른 가시성 규칙이 공존하는 것이다.

```mermaid
flowchart TD
    Q[SELECT 실행] --> L{locking clause가 있는가}
    L -->|없음| S[Read View로 snapshot read]
    S --> V[보이지 않는 최신 버전은<br/>undo chain으로 과거 버전 재구성]
    L -->|FOR SHARE| C[최신 커밋 버전으로 조건 판정]
    L -->|FOR UPDATE| C
    C --> I[검색 인덱스 레코드 접근]
    I --> R{검색 형태와 격리 수준}
    R -->|unique exact match| REC[대체로 record lock]
    R -->|range 또는 non-unique| NEXT[next-key 또는 gap 포함 가능]
    REC --> HOLD[COMMIT 또는 ROLLBACK까지 유지]
    NEXT --> HOLD
```

Current read도 다른 트랜잭션의 **미커밋 값**을 dirty read하는 것은 아니다. 대상 최신 버전이 다른 트랜잭션에 의해 변경 중이면 호환되지 않는 잠금이 풀릴 때까지 기다린 뒤, 커밋 또는 rollback 결과를 반영해 조건을 다시 판정한다. “current”는 미커밋 데이터까지 무시하고 읽는다는 뜻이 아니라, 사용할 수 있는 최신 커밋 상태에서 잠금과 변경의 직렬화 지점을 만든다는 뜻이다.

## 2. Locking read의 내부 실행 경로

`SELECT ... FOR UPDATE/SHARE`의 동작은 다음 순서로 이해할 수 있다.

1. Optimizer가 조건에 맞는 access path를 선택한다.
2. InnoDB가 해당 인덱스를 따라 후보 레코드를 찾는다.
3. 최신 레코드가 다른 트랜잭션에 의해 변경 중이면 필요한 잠금을 기다린다.
4. 기다림이 끝나면 최신 커밋 상태에서 `WHERE` 조건을 다시 평가할 수 있다.
5. 일치하는 레코드와 검색 범위에 필요한 lock을 획득한다.
6. 문장 결과를 반환하지만 잠금은 보통 트랜잭션 종료까지 유지한다.

따라서 locking read의 잠금 범위는 결과 행 수만으로 정해지지 않는다. **어떤 인덱스로 어떤 범위를 탐색했는가**가 중요하다. 적절한 인덱스가 없어서 full scan을 수행하면 많은 인덱스 레코드를 방문하고 넓은 범위를 잠가 동시성을 크게 낮출 수 있다. 결과가 한 행이어도 비효율적인 검색 경로가 광범위한 잠금을 만들 수 있다.

### 2.1 `FOR SHARE`

`FOR SHARE`는 읽은 레코드에 shared 성격의 잠금을 건다. 다른 트랜잭션이 호환되는 shared 잠금을 획득하는 것은 가능하지만, 해당 레코드를 변경·삭제하거나 exclusive 잠금을 얻으려는 작업은 기다릴 수 있다. 부모 행의 존재를 확인한 뒤 트랜잭션 동안 삭제되지 않게 보호하는 상황처럼, “현재 상태를 읽되 내가 직접 그 행을 변경할 필요는 없는” 경우에 적합하다.

그러나 `FOR SHARE`가 모든 후속 업무를 보호하는 만능 예약 표시는 아니다. 두 트랜잭션이 같은 행을 `FOR SHARE`로 읽고 각각 나중에 exclusive lock으로 승격하려 하면 lock conversion 교착이 발생할 수 있다. 최종적으로 그 행을 변경할 계획이라면 처음부터 `FOR UPDATE`로 잠금 의도를 분명히 하는 편이 일반적으로 안전하다.

### 2.2 `FOR UPDATE`

`FOR UPDATE`는 검색한 레코드에 DML과 유사한 exclusive 성격의 잠금을 획득한다. 다른 트랜잭션의 `UPDATE`, `DELETE`, 같은 행에 대한 `FOR UPDATE`, 충돌하는 `FOR SHARE`를 직렬화한다. 읽은 값으로 애플리케이션에서 계산한 뒤 같은 트랜잭션에서 갱신해야 할 때 사용한다.

잠금이 정합성을 제공하려면 다음 조건이 함께 충족되어야 한다.

- 읽기와 쓰기가 **같은 DB 트랜잭션** 안에 있어야 한다.
- 잠근 조건과 실제 변경 조건 사이에 업무 의미가 일치해야 한다.
- 여러 행을 잠글 때 모든 코드 경로가 가능한 한 같은 순서를 사용해야 한다.
- 외부 API 호출이나 사용자 입력을 기다리며 잠금을 오래 보유하지 않아야 한다.
- 오류, timeout, connection 반환 경로에서 `ROLLBACK`이 보장되어야 한다.

Autocommit 상태에서 문장 하나만 실행하면 문장이 끝나는 즉시 트랜잭션도 종료되므로, 다음 애플리케이션 문장까지 보호하는 잠금으로 사용할 수 없다. Locking read의 실질적인 목적은 명시적 `START TRANSACTION`과 `COMMIT` 또는 `ROLLBACK` 경계 안에서 달성한다.

## 3. 실행 재현: 같은 트랜잭션에서 과거 값과 최신 값 보기

다음 예제는 Event Scheduler를 별도 서버 세션으로 사용해 외부 커밋을 만든다. 검증 전용 MySQL 인스턴스를 전제로 하며, 운영 서버에서 예제 목적으로 `event_scheduler`를 켜지 않는다.

세션 A는 `REPEATABLE READ` consistent snapshot에서 잔액 `100`을 읽는다. 예약된 이벤트가 이를 `130`으로 변경하고 커밋한 뒤에도 일반 `SELECT`는 `100`을 반환한다. 같은 세션의 `FOR UPDATE`는 최신 값 `130`을 읽고 primary key 레코드에 exclusive lock을 획득한다. 그 직후 일반 `SELECT`는 다시 기존 snapshot의 `100`을 반환한다.

```sql
DROP EVENT IF EXISTS current_read_writer;
DROP TABLE IF EXISTS current_read_account;
CREATE TABLE current_read_account (
    account_id BIGINT PRIMARY KEY,
    balance INT NOT NULL,
    account_state VARCHAR(20) NOT NULL
) ENGINE = InnoDB;
INSERT INTO current_read_account
VALUES (1, 100, 'active'), (2, 200, 'active');
SET GLOBAL event_scheduler = ON;
CREATE EVENT current_read_writer
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 SECOND
    ON COMPLETION NOT PRESERVE
    DO UPDATE current_read_account
       SET balance = 130
     WHERE account_id = 1;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT balance AS snapshot_before
FROM current_read_account
WHERE account_id = 1;
DO SLEEP(4);
SELECT balance AS snapshot_after_commit
FROM current_read_account
WHERE account_id = 1;
SELECT balance AS current_for_update
FROM current_read_account
WHERE account_id = 1
FOR UPDATE;
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'current_read_account'
  AND THREAD_ID = (
      SELECT THREAD_ID
      FROM performance_schema.threads
      WHERE PROCESSLIST_ID = CONNECTION_ID()
  )
ORDER BY LOCK_TYPE, INDEX_NAME, LOCK_DATA;
SELECT balance AS snapshot_after_locking_read
FROM current_read_account
WHERE account_id = 1;
COMMIT;
SELECT balance AS value_after_commit
FROM current_read_account
WHERE account_id = 1;
```

준비 DDL과 대기용 `DO SLEEP(4)` 출력은 줄이고, 가시성 전환과 실제 획득 잠금을 보여 주는 결과를 발췌했다.

실행 결과(MySQL 8.0.x):

```text
mysql> CREATE TABLE current_read_account (...);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO current_read_account
    -> VALUES (1, 100, 'active'), (2, 200, 'active');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance AS snapshot_before
    -> FROM current_read_account WHERE account_id = 1;
+-----------------+
| snapshot_before |
+-----------------+
|             100 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT balance AS snapshot_after_commit
    -> FROM current_read_account WHERE account_id = 1;
+-----------------------+
| snapshot_after_commit |
+-----------------------+
|                   100 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT balance AS current_for_update
    -> FROM current_read_account WHERE account_id = 1 FOR UPDATE;
+--------------------+
| current_for_update |
+--------------------+
|                130 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks ...;
+----------------------+------------+-----------+---------------+-------------+-----------+
| OBJECT_NAME          | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+----------------------+------------+-----------+---------------+-------------+-----------+
| current_read_account | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| current_read_account | NULL       | TABLE     | IX            | GRANTED     | NULL      |
+----------------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

mysql> SELECT balance AS snapshot_after_locking_read
    -> FROM current_read_account WHERE account_id = 1;
+-----------------------------+
| snapshot_after_locking_read |
+-----------------------------+
|                         100 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance AS value_after_commit
    -> FROM current_read_account WHERE account_id = 1;
+--------------------+
| value_after_commit |
+--------------------+
|                130 |
+--------------------+
1 row in set (0.00 sec)
```

핵심 관찰점은 다음과 같다.

- `snapshot_before = 100`: 첫 consistent read의 Read View가 `100`을 보게 한다.
- `snapshot_after_commit = 100`: 다른 세션이 `130`을 커밋해도 기존 snapshot은 유지된다.
- `current_for_update = 130`: locking read는 최신 커밋 레코드를 읽는다.
- `LOCK_MODE = X,REC_NOT_GAP`: primary key의 정확한 일치 검색이 해당 record를 exclusive로 잠갔음을 보여 준다.
- `snapshot_after_locking_read = 100`: locking read가 기존 Read View 자체를 갱신하지 않는다.
- `value_after_commit = 130`: 트랜잭션 종료 뒤 새 일반 조회는 최신 값을 본다.

`LOCK_MODE`의 세부 문자열과 `LOCK_DATA` 표시는 MySQL minor version과 검색 방식에 따라 달라질 수 있다. 핵심은 `TABLE`의 `IX` 의도 잠금과 `PRIMARY` 레코드의 granted exclusive 잠금을 함께 확인하는 것이다.

## 4. `FOR SHARE`가 실제 쓰기를 기다리게 하는 과정

이번에는 세션 A가 `FOR SHARE`로 행을 읽고, Event Scheduler 세션이 같은 행을 `UPDATE`하도록 한다. 세션 A가 잠금을 유지하는 동안 이벤트의 exclusive lock 요청은 대기한다. `performance_schema.data_lock_waits`에서 요청 lock과 차단 lock을 직접 연결한 뒤 세션 A가 커밋하면 이벤트가 진행된다.

```sql
DROP EVENT IF EXISTS share_lock_writer;
UPDATE current_read_account
SET account_state = 'ready'
WHERE account_id = 2;
CREATE EVENT share_lock_writer
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 SECOND
    ON COMPLETION PRESERVE
    DO UPDATE current_read_account
       SET account_state = 'event-updated'
     WHERE account_id = 2;

START TRANSACTION;
SELECT account_id, account_state
FROM current_read_account
WHERE account_id = 2
FOR SHARE;
DO SLEEP(4);
SELECT rl.OBJECT_NAME,
       rl.INDEX_NAME,
       rl.LOCK_MODE AS waiting_mode,
       rl.LOCK_STATUS AS waiting_status,
       bl.LOCK_MODE AS blocking_mode,
       bl.LOCK_STATUS AS blocking_status,
       rl.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks rl
  ON rl.ENGINE = w.ENGINE
 AND rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl
  ON bl.ENGINE = w.ENGINE
 AND bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE rl.OBJECT_SCHEMA = DATABASE()
  AND rl.OBJECT_NAME = 'current_read_account';
SELECT account_state AS state_while_share_lock
FROM current_read_account
WHERE account_id = 2;
COMMIT;
DO SLEEP(2);
SELECT account_state AS state_after_release
FROM current_read_account
WHERE account_id = 2;
DROP EVENT IF EXISTS share_lock_writer;
```

이 결과는 여러 문장 가운데 shared/exclusive lock의 충돌과 해제 전후 상태를 보여 주는 핵심 부분을 발췌한 것이다.

실행 결과(MySQL 8.0.x):

```text
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT account_id, account_state
    -> FROM current_read_account WHERE account_id = 2 FOR SHARE;
+------------+---------------+
| account_id | account_state |
+------------+---------------+
|          2 | ready         |
+------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT rl.OBJECT_NAME, rl.INDEX_NAME,
    ->        rl.LOCK_MODE AS waiting_mode,
    ->        rl.LOCK_STATUS AS waiting_status,
    ->        bl.LOCK_MODE AS blocking_mode,
    ->        bl.LOCK_STATUS AS blocking_status,
    ->        rl.LOCK_DATA
    -> FROM performance_schema.data_lock_waits w ...;
+----------------------+------------+---------------+----------------+---------------+-----------------+-----------+
| OBJECT_NAME          | INDEX_NAME | waiting_mode  | waiting_status | blocking_mode | blocking_status | LOCK_DATA |
+----------------------+------------+---------------+----------------+---------------+-----------------+-----------+
| current_read_account | PRIMARY    | X,REC_NOT_GAP | WAITING        | S,REC_NOT_GAP | GRANTED         | 2         |
+----------------------+------------+---------------+----------------+---------------+-----------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT account_state AS state_while_share_lock
    -> FROM current_read_account WHERE account_id = 2;
+------------------------+
| state_while_share_lock |
+------------------------+
| ready                  |
+------------------------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT account_state AS state_after_release
    -> FROM current_read_account WHERE account_id = 2;
+---------------------+
| state_after_release |
+---------------------+
| event-updated       |
+---------------------+
1 row in set (0.00 sec)
```

이 결과에서 waiting lock은 event 세션의 `X,REC_NOT_GAP`, blocking lock은 세션 A의 `S,REC_NOT_GAP`로 관찰되는 것이 핵심이다. `state_while_share_lock`은 `ready`이고, 세션 A가 커밋해 shared lock을 해제한 뒤에는 이벤트가 완료되어 `state_after_release`가 `event-updated`가 된다.

이 예제는 잠금 대기가 “SELECT가 느려진 현상”이 아니라 **요청 lock과 이미 획득한 비호환 lock 사이의 관계**임을 보여 준다. 운영에서는 대기 문장만 종료하기 전에 blocker의 트랜잭션 시작 시각, 마지막 업무 문장, 수정 행 수, rollback 비용과 재시도 가능성을 함께 확인해야 한다.

## 5. Record lock, gap lock, next-key lock

Locking read가 무엇을 잠그는지 이해하려면 결과 행보다 인덱스 검색 구간을 봐야 한다.

### 5.1 Unique index의 정확한 일치 검색

Primary key 또는 모든 컬럼이 지정된 unique index로 단일 레코드를 정확히 찾으면 InnoDB는 대체로 해당 index record만 잠그고 앞쪽 gap은 잠그지 않는다. `LOCK_MODE`에 `REC_NOT_GAP`이 나타나는 전형적인 경우다.

```text
START TRANSACTION;
SELECT balance
FROM account
WHERE account_id = 42
FOR UPDATE;
-- 같은 트랜잭션에서 UPDATE 후 COMMIT 또는 ROLLBACK
```

위 코드는 일반 운영 테이블을 가정한 구조 예시다. 실제 잠금 범위는 테이블 정의, 선택된 index, isolation level을 확인해 판단한다.

### 5.2 Non-unique 조건과 범위 검색

Non-unique index 조건이나 `<`, `>`, `BETWEEN`, 일부 prefix 검색은 하나 이상의 인덱스 구간을 탐색한다. InnoDB의 `REPEATABLE READ`에서는 검색 중 만난 record와 앞쪽 gap을 결합한 next-key lock, 또는 필요한 gap lock을 사용해 다른 트랜잭션의 삽입으로 검색 범위가 바뀌는 것을 막을 수 있다.

다음 예제는 같은 테이블에서 primary key exact match와 non-unique range locking read의 잠금 모양을 비교한다. `data_locks`의 행 순서와 내부 record 표현은 실행마다 달라질 수 있으므로 `LOCK_MODE`, `INDEX_NAME`, `LOCK_DATA`의 방향을 해석한다.

```sql
DROP TABLE IF EXISTS locking_inventory;
CREATE TABLE locking_inventory (
    item_id INT PRIMARY KEY,
    category VARCHAR(20) NOT NULL,
    quantity INT NOT NULL,
    INDEX ix_category (category, item_id)
) ENGINE = InnoDB;
INSERT INTO locking_inventory VALUES
    (10, 'A', 5),
    (20, 'A', 8),
    (30, 'B', 13),
    (40, 'C', 21);

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT item_id, quantity
FROM locking_inventory
WHERE item_id = 10
FOR UPDATE;
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'locking_inventory'
  AND LOCK_TYPE = 'RECORD'
  AND THREAD_ID = (
      SELECT THREAD_ID
      FROM performance_schema.threads
      WHERE PROCESSLIST_ID = CONNECTION_ID()
  )
ORDER BY INDEX_NAME, LOCK_DATA;
ROLLBACK;

START TRANSACTION;
SELECT item_id, quantity
FROM locking_inventory
WHERE category = 'A'
ORDER BY item_id
FOR UPDATE;
SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = DATABASE()
  AND OBJECT_NAME = 'locking_inventory'
  AND LOCK_TYPE = 'RECORD'
  AND THREAD_ID = (
      SELECT THREAD_ID
      FROM performance_schema.threads
      WHERE PROCESSLIST_ID = CONNECTION_ID()
  )
ORDER BY INDEX_NAME, LOCK_DATA;
ROLLBACK;
DROP TABLE locking_inventory;
```

준비·정리 DDL 출력은 줄이고, exact match와 range search의 잠금 차이를 보여 주는 결과를 발췌했다.

실행 결과(MySQL 8.0.x):

```text
mysql> CREATE TABLE locking_inventory (... INDEX ix_category (category, item_id)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO locking_inventory VALUES
    -> (10, 'A', 5), (20, 'A', 8), (30, 'B', 13), (40, 'C', 21);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT item_id, quantity
    -> FROM locking_inventory WHERE item_id = 10 FOR UPDATE;
+---------+----------+
| item_id | quantity |
+---------+----------+
|      10 |        5 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks ...;
+------------+-----------+---------------+-------------+-----------+
| INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+------------+-----------+---------------+-------------+-----------+
| PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |
+------------+-----------+---------------+-------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT item_id, quantity
    -> FROM locking_inventory WHERE category = 'A'
    -> ORDER BY item_id FOR UPDATE;
+---------+----------+
| item_id | quantity |
+---------+----------+
|      10 |        5 |
|      20 |        8 |
+---------+----------+
2 rows in set (0.00 sec)

mysql> SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
    -> FROM performance_schema.data_locks ...;
+-------------+-----------+---------------+-------------+-----------+
| INDEX_NAME  | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-------------+-----------+---------------+-------------+-----------+
| ix_category | RECORD    | X             | GRANTED     | 'A', 10   |
| ix_category | RECORD    | X             | GRANTED     | 'A', 20   |
| ix_category | RECORD    | X,GAP         | GRANTED     | 'B', 30   |
| PRIMARY     | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |
| PRIMARY     | RECORD    | X,REC_NOT_GAP | GRANTED     | 20        |
+-------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)
```

첫 번째 조회는 `PRIMARY`의 `10`에 대한 `X,REC_NOT_GAP`가 중심이다. 두 번째 조회는 `ix_category`의 `'A'` 구간과 대응 primary records를 잠그며, 범위 끝을 보호하는 gap 성격의 lock이 추가될 수 있다. Secondary index record를 exclusive로 잠근 뒤 clustered record에도 잠금이 필요하므로 한 결과 행이 lock 한 건과 일대일로 대응한다고 생각하면 안 된다.

### 5.3 일치하는 행이 없어도 잠금이 생길 수 있다

`REPEATABLE READ`에서 범위 조건의 `FOR UPDATE`가 0행을 반환해도 “잠금이 없다”는 뜻이 아니다. 해당 조건에 새 행이 삽입되는 것을 막기 위해 검색한 gap을 잠글 수 있다. 이 특성은 phantom 방지에는 필요하지만, 존재하지 않는 key를 예약하는 패턴에서는 hot gap과 긴 대기를 만들 수 있다.

반대로 `READ COMMITTED`에서는 일반 검색과 scan에서 gap locking이 크게 줄어들고 nonmatching record lock도 더 일찍 해제될 수 있다. 다만 Foreign key 검사와 duplicate-key 검사처럼 정합성에 필요한 gap lock은 여전히 발생할 수 있다. 격리 수준을 낮추면 모든 gap lock이 사라진다고 단정해서는 안 된다.

## 6. `NOWAIT`와 `SKIP LOCKED`

기본 locking read는 비호환 잠금이 해제될 때까지 기다리며, `innodb_lock_wait_timeout`을 넘으면 오류가 발생한다. 일부 업무는 기다림보다 즉시 실패하거나 이미 잠긴 행을 건너뛰는 편이 낫다.

```text
-- 즉시 선점하지 못하면 오류로 반환
SELECT job_id, payload
FROM job_queue
WHERE status = 'ready'
ORDER BY job_id
LIMIT 1
FOR UPDATE NOWAIT;

-- 다른 worker가 잠근 행을 제외하고 다음 후보를 선택
SELECT job_id, payload
FROM job_queue
WHERE status = 'ready'
ORDER BY job_id
LIMIT 10
FOR UPDATE SKIP LOCKED;
```

두 문장은 실제 애플리케이션 테이블과 다중 worker를 전제로 하므로 단일 세션 검증 SQL이 아닌 구조 예시로 제시했다.

### 6.1 `NOWAIT`

`NOWAIT`는 대상 lock을 즉시 얻지 못하면 기다리지 않고 오류를 반환한다. 짧은 사용자 요청, leader 역할 획득, 충돌 시 상위 계층에서 명시적으로 재시도하는 업무에 적합하다. 애플리케이션은 이 오류를 일반 장애와 구분하고 jitter를 포함한 제한적 retry, 다른 업무 선택, 사용자에게 충돌 알림 가운데 하나로 처리해야 한다.

### 6.2 `SKIP LOCKED`

`SKIP LOCKED`는 다른 트랜잭션이 잠근 레코드를 결과에서 제외한다. 다중 worker가 queue의 서로 다른 작업을 병렬로 선점할 때 유용하다. 그러나 반환 결과는 잠긴 행이 빠진 **불완전한 관측**이므로 잔액 조회, 재고 총량, 권한 판정, 감사 보고서에 사용하면 안 된다.

Queue에 적용할 때도 다음 설계가 필요하다.

- 선점한 행을 같은 트랜잭션에서 `running` 상태로 바꾸거나 owner와 lease 만료 시각을 기록한다.
- worker crash 뒤 재처리할 lease recovery가 있어야 한다.
- 작업 자체는 중복 실행을 견디도록 idempotency key를 사용한다.
- 계속 잠기는 오래된 작업이 starvation되지 않는지 별도 지표로 감시한다.
- `ORDER BY`와 적절한 index로 선점 순서와 잠금 범위를 제한한다.

`NOWAIT`와 `SKIP LOCKED`는 lock contention을 없애는 기능이 아니라 **대기 정책을 애플리케이션에 노출하는 기능**이다. 충돌률이 높다면 트랜잭션 길이, hot key, worker 수, batch 크기와 index를 먼저 조정해야 한다.

## 7. Locking read가 하위 쿼리까지 자동 전파되지는 않는다

바깥 `SELECT`에 locking clause를 붙였다고 해서 별도 query block인 subquery의 테이블이 항상 같은 방식으로 잠기는 것은 아니다. 잠금이 필요한 query block에는 의도를 명시하고, 실제 실행 계획과 lock 관찰로 검증해야 한다.

```text
-- 바깥 테이블 parent_row의 후보만 잠근다고 이해해야 한다.
SELECT *
FROM parent_row
WHERE parent_id IN (
    SELECT parent_id
    FROM child_row
    WHERE child_state = 'ready'
)
FOR UPDATE;

-- child_row에도 locking read가 필요하다면 query block에 명시한다.
SELECT *
FROM parent_row
WHERE parent_id IN (
    SELECT parent_id
    FROM child_row
    WHERE child_state = 'ready'
    FOR SHARE
)
FOR UPDATE;
```

Optimizer rewrite와 materialization 여부에 따라 access path는 달라질 수 있다. 복잡한 subquery에 잠금을 이용해 업무 정합성을 만들려 하기보다, 잠글 key를 명확히 정하고 짧은 순서로 접근하는 설계가 검증하기 쉽다.

## 8. 애플리케이션 설계 패턴

### 8.1 읽고 계산한 뒤 갱신

재고를 읽고 애플리케이션에서 새 수량을 계산해야 한다면 다음 논리 경계가 필요하다.

```text
START TRANSACTION;
SELECT quantity
FROM inventory
WHERE item_id = ?
FOR UPDATE;

-- quantity가 충분한지 판단하고 같은 트랜잭션에서 변경
UPDATE inventory
SET quantity = ?
WHERE item_id = ?;
COMMIT;
```

하지만 계산을 SQL 조건으로 표현할 수 있다면 locking read와 round trip을 줄인 원자적 DML이 더 단순할 수 있다.

```text
UPDATE inventory
SET quantity = quantity - ?
WHERE item_id = ?
  AND quantity >= ?;
-- affected rows가 1이면 성공, 0이면 재고 부족 또는 대상 없음
```

두 번째 패턴도 DML 내부에서는 current read와 lock을 사용하지만, 애플리케이션이 읽기와 쓰기 사이에 오래 머무는 구간을 없앤다.

### 8.2 여러 행 또는 여러 테이블 잠금

계좌 A와 B 사이의 이체처럼 여러 행을 잠글 때는 모든 코드 경로가 같은 정렬 기준으로 접근한다. 한 경로가 A→B, 다른 경로가 B→A 순서로 잠그면 deadlock cycle이 만들어지기 쉽다.

- account ID 오름차순처럼 전역 순서를 정한다.
- 필요한 행만 index exact match로 잠근다.
- 잠금 획득 뒤 외부 시스템을 호출하지 않는다.
- deadlock은 완전히 제거할 수 없으므로 전체 트랜잭션 retry를 idempotent하게 구현한다.
- 부분 문장만 재실행하지 말고 rollback된 업무 단위를 처음부터 재검증한다.

### 8.3 낙관적 잠금과 선택 기준

충돌이 드문 긴 편집 흐름에서는 DB 트랜잭션을 계속 열어 두는 대신 version column을 사용할 수 있다.

```text
UPDATE document
SET body = ?, version_no = version_no + 1
WHERE document_id = ?
  AND version_no = ?;
```

Affected rows가 0이면 다른 트랜잭션이 먼저 바꿨음을 의미한다. `FOR UPDATE`는 짧은 DB 트랜잭션 안에서 강한 직렬화가 필요할 때, optimistic locking은 사용자 입력이나 외부 작업 때문에 DB 잠금을 오래 유지할 수 없을 때 유리하다.

## 9. 운영 진단: 기다리는 문장과 막는 트랜잭션 연결

MySQL 8.0 이상에서는 제거된 `information_schema.INNODB_LOCKS`나 `INNODB_LOCK_WAITS` 대신 `performance_schema.data_locks`와 `data_lock_waits`를 사용한다. 다음 쿼리는 대기·차단 트랜잭션, thread, 현재 SQL, 잠금 객체를 연결한다. 대기가 없는 정상 검증 인스턴스에서는 `Empty set`이 반환된다.

```sql
SELECT r.trx_id AS waiting_trx_id,
       rt.PROCESSLIST_ID AS waiting_thread,
       bs.SQL_TEXT AS waiting_query,
       b.trx_id AS blocking_trx_id,
       bt.PROCESSLIST_ID AS blocking_thread,
       es.SQL_TEXT AS blocking_query,
       rl.OBJECT_SCHEMA,
       rl.OBJECT_NAME,
       rl.INDEX_NAME,
       rl.LOCK_MODE AS waiting_lock_mode,
       bl.LOCK_MODE AS blocking_lock_mode,
       rl.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r
  ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b
  ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN performance_schema.data_locks rl
  ON rl.ENGINE = w.ENGINE
 AND rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl
  ON bl.ENGINE = w.ENGINE
 AND bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads rt
  ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads bt
  ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current bs
  ON bs.THREAD_ID = w.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.events_statements_current es
  ON es.THREAD_ID = w.BLOCKING_THREAD_ID
ORDER BY r.trx_started;
DROP TABLE current_read_account;
```

실행 결과(MySQL 8.0.x):

```text
mysql> SELECT r.trx_id AS waiting_trx_id,
    ->        rt.PROCESSLIST_ID AS waiting_thread,
    ->        bs.SQL_TEXT AS waiting_query,
    ->        b.trx_id AS blocking_trx_id,
    ->        bt.PROCESSLIST_ID AS blocking_thread,
    ->        es.SQL_TEXT AS blocking_query,
    ->        rl.OBJECT_SCHEMA,
    ->        rl.OBJECT_NAME,
    ->        rl.INDEX_NAME,
    ->        rl.LOCK_MODE AS waiting_lock_mode,
    ->        bl.LOCK_MODE AS blocking_lock_mode,
    ->        rl.LOCK_DATA
    -> FROM performance_schema.data_lock_waits w
    -> JOIN information_schema.INNODB_TRX r
    ->   ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
    -> JOIN information_schema.INNODB_TRX b
    ->   ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
    -> JOIN performance_schema.data_locks rl
    ->   ON rl.ENGINE = w.ENGINE
    ->  AND rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
    -> JOIN performance_schema.data_locks bl
    ->   ON bl.ENGINE = w.ENGINE
    ->  AND bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
    -> LEFT JOIN performance_schema.threads rt
    ->   ON rt.THREAD_ID = w.REQUESTING_THREAD_ID
    -> LEFT JOIN performance_schema.threads bt
    ->   ON bt.THREAD_ID = w.BLOCKING_THREAD_ID
    -> LEFT JOIN performance_schema.events_statements_current bs
    ->   ON bs.THREAD_ID = w.REQUESTING_THREAD_ID
    -> LEFT JOIN performance_schema.events_statements_current es
    ->   ON es.THREAD_ID = w.BLOCKING_THREAD_ID
    -> ORDER BY r.trx_started;

Empty set (0.00 sec)

mysql> DROP TABLE current_read_account;

Query OK, 0 rows affected (0.00 sec)
```

진단할 때는 다음 순서를 사용한다.

1. `OBJECT_SCHEMA`, `OBJECT_NAME`, `INDEX_NAME`, `LOCK_DATA`로 충돌 지점을 찾는다.
2. waiting SQL뿐 아니라 blocking transaction의 `trx_started`, 현재·최근 SQL과 애플리케이션 요청을 연결한다.
3. `LOCK_MODE`에서 record, gap, next-key 성격을 확인하고 실행 계획의 선택 index와 대조한다.
4. 결과 행 수보다 실제 scan 범위가 넓지 않았는지 `EXPLAIN`과 rows examined를 확인한다.
5. blocker가 idle이면 connection pool이 commit하지 않은 채 연결을 반환했는지 조사한다.
6. 강제 종료 전 `trx_rows_modified`, rollback 예상 시간, 업무 중요도와 재시도 가능성을 평가한다.
7. 해소 뒤 lock wait 시간, transaction duration, deadlock 발생률과 hot key 분포를 추적한다.

`events_statements_current.SQL_TEXT`는 thread가 현재 실행 중인 문장이 없으면 `NULL`일 수 있다. 차단 세션이 idle로 보여도 이전 DML이나 locking read 뒤 트랜잭션을 종료하지 않았을 수 있으므로 SQL text 하나만으로 blocker를 배제하지 않는다.

## 10. Deadlock과 timeout을 다르게 다룬다

Lock wait timeout은 한 트랜잭션이 지정 시간 동안 필요한 lock을 얻지 못한 상태다. Deadlock은 트랜잭션 사이의 대기 그래프가 cycle을 만든 상태이며, InnoDB는 보통 rollback 비용이 작다고 판단한 victim 하나를 선택해 즉시 중단한다.

- `Lock wait timeout exceeded; try restarting transaction`: 대기 시간이 한도를 넘었다.
- `Deadlock found when trying to get lock; try restarting transaction`: cycle 탐지로 victim이 됐다.

두 오류 모두 retry할 수 있지만 무제한 즉시 retry는 contention을 악화시킨다. 전체 업무 트랜잭션을 rollback한 뒤, idempotency를 보장하고, 제한 횟수와 exponential backoff/jitter를 적용한다. `innodb_lock_wait_timeout`을 크게 늘리는 것은 blocker를 해결하지 않고 대기 queue와 connection 점유만 늘릴 수 있다.

Deadlock은 오류 처리만으로 끝내지 않는다. `SHOW ENGINE INNODB STATUS`의 latest detected deadlock, error log의 deadlock 기록 설정, 애플리케이션 trace를 이용해 다음을 찾는다.

- 잠금 순서가 서로 반대인 코드 경로
- index 부재로 지나치게 넓어진 scan과 lock 범위
- 한 트랜잭션에 과도하게 묶인 batch
- shared lock 뒤 exclusive lock으로 승격하는 패턴
- Foreign key 검사, unique check처럼 직접 SQL만 봐서는 놓치기 쉬운 잠금

## 11. Aurora MySQL에서의 운영 해석

Aurora MySQL도 MySQL 호환 InnoDB 트랜잭션 계층에서 `FOR UPDATE`, `FOR SHARE`, record/gap lock과 deadlock의 핵심 의미를 유지한다. 분산 스토리지가 row lock contention을 자동으로 제거하지 않는다.

다만 운영에서는 endpoint와 failover를 추가로 고려한다.

- Locking read와 후속 쓰기는 writer에서 수행해야 한다. Reader endpoint의 read-only 연결은 쓰기 트랜잭션 선점 용도로 사용할 수 없다.
- Writer에서 잠근 뒤 reader endpoint에서 후속 조회하는 것은 같은 트랜잭션이나 lock 보호 범위가 아니다.
- Failover나 connection reset이 발생하면 기존 session과 transaction, row lock은 유지된다고 가정할 수 없다. 애플리케이션은 전체 업무를 재시도하고 선점 상태를 DB의 durable column으로 확인해야 한다.
- Database Insights 또는 Performance Insights의 DB load와 wait를 `INNODB_TRX`, Performance Schema lock 관계, 애플리케이션 trace와 함께 해석한다.
- 긴 blocking transaction이 reader lag, failover 영향, connection 폭증과 동시에 나타났는지 시간축을 맞춘다.
- 파라미터 그룹에서 isolation level이나 timeout을 바꾸기 전에 cluster/instance 범위와 기존 connection의 session 값을 확인한다.

Aurora의 빠른 crash recovery나 스토리지 복제 구조는 애플리케이션의 transaction retry와 idempotency를 대신하지 않는다. 특히 queue worker가 `SKIP LOCKED`로 작업을 가져갔다면 row lock 자체가 작업 완료 기록이 아니므로 owner, state, lease와 결과의 중복 방지 키를 영속적으로 관리해야 한다.

## 12. 흔한 오해와 실패 패턴

### 12.1 “`FOR UPDATE`는 일반 SELECT 결과에 잠금만 추가한다”

아니다. Locking read는 current read이므로 기존 snapshot보다 최신 커밋 버전을 볼 수 있다. 같은 트랜잭션에서 일반 `SELECT`와 다른 값이 반환될 수 있다.

### 12.2 “반환 행이 한 건이면 한 건만 잠긴다”

잠금 범위는 선택된 index와 scan 구간에 의해 결정된다. Non-unique range나 full scan은 결과보다 훨씬 넓은 record와 gap을 잠글 수 있다.

### 12.3 “0행이면 잠금도 없다”

`REPEATABLE READ`의 범위 locking read는 일치 행이 없어도 해당 gap에 새 행이 들어오는 것을 막을 수 있다.

### 12.4 “`FOR SHARE` 뒤 필요할 때 `FOR UPDATE`로 바꾸면 더 효율적이다”

동시에 여러 트랜잭션이 shared lock을 얻은 뒤 exclusive lock으로 승격하면 deadlock이 생길 수 있다. 변경 의도가 있으면 처음부터 일관된 `FOR UPDATE` 순서를 검토한다.

### 12.5 “`SKIP LOCKED`는 더 빠른 일반 조회다”

잠긴 행을 결과에서 제외하므로 정합한 전체 집합이 아니다. Queue 선점에는 유용하지만 재고·잔액·권한·감사 조회에는 부적합하다.

### 12.6 “Autocommit에서도 다음 문장까지 보호된다”

문장 종료와 함께 transaction이 끝나면 lock도 해제된다. 읽기와 후속 변경을 같은 명시적 transaction에 넣어야 한다.

### 12.7 “격리 수준을 READ COMMITTED로 바꾸면 잠금 문제가 끝난다”

일반 검색의 gap locking은 줄어들 수 있지만 record lock 충돌, Foreign key 검사, duplicate-key 검사, 긴 transaction과 hot row 문제는 남는다. 업무 정합성 변화도 함께 검증해야 한다.

### 12.8 “Timeout을 늘리면 장애가 해결된다”

대기 시간을 늘리면 요청이 성공할 기회는 늘 수 있지만 connection과 worker가 더 오래 묶인다. Blocker 제거와 transaction/index 설계가 우선이다.

## 13. 설계 및 운영 점검표

### 트랜잭션 설계

- [ ] 일반 snapshot read와 current locking read 가운데 업무에 필요한 가시성을 정의했는가?
- [ ] 읽기와 후속 쓰기가 같은 명시적 transaction에 포함되는가?
- [ ] 최종 변경 의도가 있는 행을 처음부터 `FOR UPDATE`로 잠그는가?
- [ ] 원자적 조건부 `UPDATE`로 locking read round trip을 줄일 수 있는가?
- [ ] 여러 행·테이블의 잠금 순서를 모든 코드 경로에서 통일했는가?
- [ ] 외부 API, 사용자 입력, 파일 I/O를 기다리는 동안 transaction을 열어 두지 않는가?
- [ ] 오류와 connection pool 반환 시 `ROLLBACK`이 보장되는가?

### 인덱스와 잠금 범위

- [ ] Locking read의 `WHERE` 조건에 적합한 선택도 높은 index가 있는가?
- [ ] 실제 `EXPLAIN`에서 의도한 index와 access type을 사용하는가?
- [ ] Unique exact match인지 non-unique range인지 구분했는가?
- [ ] 0행 결과에서도 gap lock이 생길 수 있음을 동시성 테스트로 확인했는가?
- [ ] Batch 크기와 한 transaction에서 잠그는 최대 행 수를 제한했는가?
- [ ] Hot key와 starvation을 metric으로 관찰하는가?

### 오류와 장애 대응

- [ ] Lock wait timeout과 deadlock을 별도 지표·오류 코드로 분류하는가?
- [ ] Retry는 전체 transaction 단위이며 idempotent한가?
- [ ] Retry 횟수, backoff와 jitter에 상한이 있는가?
- [ ] `data_lock_waits`, `data_locks`, `INNODB_TRX`, thread와 SQL을 연결할 수 있는가?
- [ ] Blocker 종료 전에 수정 행 수와 rollback 비용을 확인하는가?
- [ ] `NOWAIT` 실패와 `SKIP LOCKED` 누락을 업무 흐름이 명시적으로 처리하는가?
- [ ] Aurora에서는 writer/reader endpoint와 failover 이력을 함께 기록하는가?

## 14. 정리

`SELECT ... FOR UPDATE`와 `SELECT ... FOR SHARE`는 MVCC snapshot 위에 단순 잠금 표시를 더하는 구문이 아니다. 두 문장은 최신 커밋 상태에서 조건을 판정하는 current read이며, 선택된 index 검색 경로에 따라 record, gap, next-key lock을 획득한다. `FOR SHARE`는 읽은 상태를 shared lock으로 보호하고, `FOR UPDATE`는 후속 변경을 위한 exclusive 직렬화 지점을 만든다.

정확한 사용법은 구문보다 transaction 경계에 달려 있다. 읽기와 변경을 같은 짧은 transaction에 묶고, 적절한 index로 잠금 범위를 줄이며, 여러 객체는 일관된 순서로 잠가야 한다. `NOWAIT`와 `SKIP LOCKED`는 대기 정책을 바꾸지만 contention 자체를 없애지 않으며, 오류 처리·lease·idempotency 설계가 뒤따라야 한다.

운영 장애에서는 결과 행 수나 느린 SQL 한 줄만 보지 말고 `data_lock_waits`의 요청·차단 관계, 선택된 index, transaction 수명과 rollback 비용을 연결해야 한다. 다음 단계에서는 record lock, gap lock, next-key lock이 실제 B+Tree 검색 구간에서 어떻게 만들어지고 phantom 방지와 쓰기 동시성 사이의 균형을 형성하는지 더 세밀하게 다룰 수 있다.