Locking Read와 SELECT FOR UPDATE

Overview

Locking Read는 조회 결과를 곧 변경할 때 다른 Transaction과의 경쟁을 통제하기 위한 읽기다. SELECT ... FOR UPDATE는 조회한 Record에 배타적인 변경 의도를 선언하고, FOR SHARE는 다른 Transaction의 변경을 막으면서 공유 읽기를 허용한다.

핵심 용어

용어	보호 대상
Record Lock	실제 Index Record
Gap Lock	Index Record 사이의 삽입 공간
Next-key Lock	Record와 앞 Gap의 결합
Locking Read	현재 Version을 읽고 Lock을 획득하는 조회

왜 필요한가

잔액을 읽고 계산한 뒤 Update하는 Read–Modify–Write 흐름에서 두 요청이 같은 초기값을 읽으면 한 변경이 사라지는 Lost Update가 생길 수 있다. Locking Read는 먼저 획득한 Transaction이 Commit/Rollback할 때까지 경쟁 Transaction을 기다리게 해 순서를 만든다.

핵심 원리

Lock은 SQL 문자열이 아니라 실행 계획이 접근한 Index Record와 Range를 기준으로 잡힌다. 조건에 적절한 Index가 없으면 예상보다 넓은 범위를 Scan하고 Lock할 수 있다. 그래서 Lock 문제와 Index 설계를 따로 볼 수 없다.

Record Lock: 실제 Index Record를 잠근다.
Gap Lock: Index Record 사이의 빈 Range에 새 값이 들어오는 것을 막는다.
Next-key Lock: Record Lock과 그 앞 Gap Lock의 결합이다.

내부 동작

sequenceDiagram; participant A as Transaction A; participant DB as InnoDB; participant B as Transaction B; A->>DB: SELECT ... FOR UPDATE; DB-->>A: 현재 Row + X Lock; B->>DB: 같은 Row UPDATE; DB-->>B: Lock Wait; A->>DB: UPDATE 후 COMMIT; DB-->>B: Lock 획득 후 계속

InnoDB REPEATABLE READ의 Range Locking Read는 Next-key Lock으로 검색 범위에 새 Row가 삽입되는 것을 막을 수 있다. READ COMMITTED에서는 Gap Lock 사용 범위가 더 제한적이다. 정확한 Lock은 Index, 조건, 존재하는 Row, Isolation Level에 따라 달라지므로 performance_schema와 SHOW ENGINE INNODB STATUS 같은 증거로 확인한다.

Example

START TRANSACTION;
 
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
 
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
 
COMMIT;

단순 차감이라면 읽고 계산하는 대신 조건부 Atomic Update가 더 짧고 안전할 수 있다.

UPDATE accounts
SET balance = balance - 100
WHERE id = 1
  AND balance >= 100;

영향 Row 수가 1인지 확인하면 잔액 부족과 경쟁을 별도 읽기 없이 처리할 수 있다.

실무에서 발생하는 문제

Transaction 밖이나 Autocommit 문맥에서 Lock의 생명주기를 오해한다.
외부 API를 호출하는 동안 Lock을 유지한다.
Index가 없는 조건으로 넓은 Row와 Gap을 잠근다.
여러 업무가 서로 다른 순서로 Row를 잠가 Deadlock을 만든다.
Queue처럼 처리하려고 FOR UPDATE를 썼지만 대기 정책과 재시도를 정하지 않는다.

Trade-off

비관적 Lock은 충돌이 잦고 반드시 한 번만 처리해야 하는 짧은 작업에 직관적이다. 충돌이 드물고 대기를 피하고 싶다면 Version Column 기반 낙관적 Lock이 유리할 수 있다. Atomic Update와 Unique Constraint는 더 단순한 불변식에 우선 검토한다.

흔한 오해

FOR UPDATE가 Application 전체의 동시 요청을 막는 것이 아니라 접근한 DB Lock 범위만 막는다.
조회 조건에 맞는 Row가 없다고 Lock이 항상 없는 것은 아니다. Range와 Gap이 잠길 수 있다.
Lock을 쓰면 Deadlock이 없어지는 것이 아니라 오히려 Lock 순서가 중요해진다.
synchronized는 Process 내부만 보호하므로 여러 Instance의 DB 경쟁을 대신하지 못한다.

Production Considerations

Lock 획득 순서를 통일하고 Transaction을 짧게 유지한다. Lock Wait Timeout과 Deadlock Error는 정상적인 동시성 실패로 취급해 제한된 Retry를 설계한다. Slow Query만 보지 말고 Lock Wait 시간, 대기 Transaction, 실행 계획, 영향 Row 수를 함께 수집한다.

다른 사람에게 설명한다면

30초: “FOR UPDATE는 조회한 값을 곧 바꿀 때 경쟁 Transaction을 기다리게 합니다. InnoDB는 실행 계획이 접근한 Index와 범위에 Record·Gap·Next-key Lock을 잡을 수 있어 Isolation Level과 함께 봐야 합니다.”

2분: Snapshot Read와 Locking Read, 존재하지 않는 Row의 Gap Lock, Atomic Update 대안과 Deadlock Retry를 차례로 설명한다.

Interview Questions

Consistent Read와 Locking Read의 차이는 무엇인가?
Record, Gap, Next-key Lock은 각각 무엇을 보호하는가?
Index가 Lock 범위에 영향을 주는 이유는?
SELECT FOR UPDATE 대신 Atomic Update가 나은 경우는?
비관적 Lock과 낙관적 Lock을 어떤 충돌 패턴에서 선택하는가?

Isolation Level과 MVCC, Deadlock, Index와 실행 계획을 함께 확인한다.