원문 캡처 · hakibenita.com
원문 캡처 · hakibenita.com

두 테이블에 외래 키가 설정돼 있고 해당 열이 NOT NULL이며 제약 조건도 유효하게 적용 중인데, 두 테이블을 조인한 쿼리가 결과를 하나도 반환하지 않을 수 있을까? PostgreSQL에서 조인과 행 잠금을 함께 사용하면 특정 상황에서 이런 일이 생길 수 있다. 핵심 조건과 재현 과정, 예방 방법을 살펴본다.

문제 재현

자동차 소유권을 관리한다고 가정하자. owner 테이블에는 소유자를, car 테이블에는 자동차와 소유자 ID를 저장한다.

db=# CREATE TABLE owner (
 id int PRIMARY KEY,
 name text NOT NULL
);
CREATE TABLE

db=# CREATE TABLE car (
 id int PRIMARY KEY,
 owner_id int NOT NULL,
 CONSTRAINT car_owner_id_fk FOREIGN KEY (owner_id) REFERENCES owner(id)
);
CREATE TABLE

소유자 세 명과 자동차 한 대를 추가한다.

db=# INSERT INTO owner (id, name) VALUES
 (1, 'haki'),
 (2, 'jerry'),
 (3, 'george')
RETURNING *;
 id │ name
────┼───────
 1 │ haki
 2 │ jerry
 3 | george
(3 rows)

INSERT 0 2

db=# INSERT INTO car (id, owner_id) VALUES(1, 1) RETURNING *;
 id │ owner_id
────┼──────────
 1 │ 1
(1 row)

INSERT 0 1

자동차 ID가 1인 차는 현재 haki(ID 1)의 소유다. 소유권을 바꾸기 전에 현재 소유자의 미납 벌금이나 법원 명령을 확인하고, 현재 소유자와 새 소유자가 같은 사람인지도 검사한다고 하자. 먼저 자동차와 현재 소유자를 조인해 읽으면서 자동차 행에 잠금을 건다.

db=# BEGIN;
BEGIN

db=*# SELECT *
FROM car JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1
FOR NO KEY UPDATE OF car;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 1
id │ 1
name │ haki

이 잠금은 다른 세션이 동시에 같은 자동차의 소유권을 바꾸지 못하게 한다. 필요한 검사를 마쳤다면 owner_id를 2로 바꾸고 트랜잭션을 커밋한다.

db=*# UPDATE car SET owner_id = 2 WHERE id = 1 RETURNING *;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2

db=*# COMMIT;
COMMIT

동시 실행에서 발생하는 현상

같은 절차를 두 세션이 동시에 수행하면 결과가 달라진다. 첫 번째 세션은 자동차를 읽고 잠근 뒤 소유자를 3으로 바꾸고 커밋한다.

-- First session
db=# BEGIN;
BEGIN

db=*# SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2
id │ 2
name │ jerry

db=*# UPDATE car SET owner_id = 3
WHERE id = 1 RETURNING *;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3

db=*# COMMIT;
COMMIT

그사이 두 번째 세션은 같은 쿼리를 실행하다가 첫 번째 세션의 잠금이 풀릴 때까지 기다린다.

-- Second session
db=# BEGIN;
BEGIN;

db=*# SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car;
-- Session is blocked
--
--
--
--
--
--
--
--
--
--
--
--
-- Lock is released by first session
(0 rows)

잠금이 풀린 뒤에도 두 번째 쿼리는 결과를 반환하지 않는다. 자동차와 이전·새 소유자가 모두 존재하고 외래 키도 유효한데 내부 조인 결과가 사라진 것이다.

내부 조인을 왼쪽 외부 조인으로 바꿔도 문제가 드러난다. 두 번째 세션은 갱신된 자동차 행을 얻지만 owner 쪽 열은 비어 있다.

db=# BEGIN;
BEGIN

db=*# SELECT * FROM car
LEFT JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2
id │ 2
name │ jerry

db=*# UPDATE car SET owner_id = 3
WHERE id = 1 RETURNING *;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3

db=*# COMMIT;
COMMIT

db=# BEGIN;
BEGIN

db=*# SELECT * FROM car
LEFT JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car;
-- Session is blocked
--
--
--
--
--
--
--
--
--
--
--
-- Lock is released by first session
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3
id │ ¤
name │ ¤

car.owner_id에는 NOT NULL 제약 조건과 외래 키 제약 조건이 모두 있고, 데이터베이스가 두 조건을 유효하게 적용한다. 그런데도 조인 결과가 없거나 조인한 테이블의 열이 비어 있을 수 있다.

Read Committed에서 생기는 이유

이 사례는 PostgreSQL의 기본 격리 수준인 Read Committed에서 발생한다. PostgreSQL 문서는 이 격리 수준에서 UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE가 대상 행을 찾는 방식은 SELECT와 같으며, 명령이 시작된 시점에 커밋된 행을 찾는다고 설명한다.

다만 잠그려는 행을 다른 트랜잭션이 먼저 갱신·삭제하거나 잠근 상태라면, 현재 명령은 그 트랜잭션이 커밋 또는 롤백할 때까지 기다린다. 첫 번째 트랜잭션이 커밋하면 대기 중인 명령은 갱신된 행에 작업을 다시 적용하려고 한다. 이때 WHERE 조건을 새 행에 대해 다시 평가한다.

이 쿼리에서는 조인 결과의 일부가 잠금 전에 이미 만들어져 있다. 실행 흐름은 다음과 같다.

  • 첫 번째 세션이 자동차 1의 행을 잠그고 소유자를 ID 1에서 ID 3으로 바꾼다.
  • 두 번째 세션은 쿼리를 시작해 자동차 1과 당시 소유자 ID 1을 찾지만, 자동차 행의 잠금 때문에 대기한다.
  • 첫 번째 세션이 커밋해 잠금을 해제한다.
  • 두 번째 세션은 새 자동차 행을 대상으로 조건을 다시 평가한다. 자동차의 소유자는 이제 ID 3이지만 조인 결과에 있던 소유자 ID는 1이다. 내부 조인은 행을 버리고, 외부 조인은 소유자 열을 NULL로 반환한다.

따라서 이 쿼리는 잠금이 풀린 뒤 처음부터 다시 실행되는 것이 아니다. 잠금 전후에 쿼리의 서로 다른 부분이 실행되는 셈이다. PostgreSQL 내부에서 잠금 해제 후 조건을 다시 평가하는 과정은 EvalPlanQual에서 처리된다.

PostgreSQL 문서는 Read Committed에서 복잡한 검색 조건을 포함한 명령이 일관되지 않은 스냅샷을 볼 수 있다고 경고한다. 같은 행에 대한 동시 갱신의 효과는 보면서도 데이터베이스의 다른 행에 대한 그 명령의 효과는 보지 못할 수 있어, 복잡한 검색 조건을 사용하는 명령에는 이 격리 수준이 적합하지 않을 수 있다는 설명이다.

따라서 조인이 포함된 쿼리에서 한 행을 잠그고 같은 트랜잭션에서 조인 대상과 연결된 외래 키를 갱신한다면, 동시 실행 중인 다른 트랜잭션이 일부 결과만 받거나 결과를 전혀 받지 못할 수 있다.

검토한 해결 방법

조인에 포함된 소유자 행까지 함께 잠그는 방법은 효과가 없다. 두 번째 세션이 잠금을 얻을 때는 자동차의 소유자가 이미 바뀌었으므로, 이전 소유자 행을 잠가도 새 조인 결과를 얻지 못한다.

db=# BEGIN;
BEGIN;

db=*# SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car, owner;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2
id │ 2
name │ jerry

db=*# UPDATE car SET owner_id = 3
WHERE id = 1 RETURNING *;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3

db=*# COMMIT;
COMMIT

db=# BEGIN;

db=*# SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car, owner;
-- Session is locked
--
--
--
--
--
--
--
--
--
--
--
-- Lock is released by first session
(0 rows)

car.owner_id가 NULL을 허용하는 설계라면 외부 조인을 사용해야 한다. 하지만 SELECT FOR UPDATE는 외부 조인의 NULL 가능 쪽에 적용할 수 없다.

db=# BEGIN;
BEGIN

db=*# SELECT * FROM car
LEFT JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car, owner;
ERROR: FOR NO KEY UPDATE cannot be applied to the nullable side of an outer join

더 엄격한 격리 수준을 사용할 수도 있다. Repeatable Read는 트랜잭션 중 다른 트랜잭션이 커밋한 변경을 보지 않는다. 같은 동시 실행을 이 격리 수준에서 수행하면 두 번째 세션은 잠금이 풀린 뒤 동시 갱신 오류를 받는다.

db=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

db=*# SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2
id │ 2
name │ jerry

db=*# UPDATE car SET owner_id = 3
WHERE id = 1 RETURNING *;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3

db=*# COMMIT;
COMMIT

db=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

db=*# SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car;
-- Session is blocked
--
--
--
--
--
--
--
--
--
--
--
-- Lock is released by first session
ERROR: could not serialize access due to concurrent update

오류는 부정확한 데이터로 작업하는 것보다 낫지만, 애플리케이션에서 이 오류를 별도로 처리해야 한다. 가장 강한 격리 수준인 Serializable에서도 결과는 Repeatable Read와 같다. 잠재적인 동시성 문제가 감지돼 오류가 발생한다. 원문 사례에서는 이런 오류 처리와 더 강한 격리 수준이 다른 곳에 미칠 수 있는 예상 밖 영향을 우려해 격리 수준을 바꾸지 않기로 했다.

더 직접적인 방법은 조인을 두 쿼리로 나누는 것이다. 먼저 자동차 행을 잠가 최신 owner_id를 얻은 다음, 그 ID로 소유자를 조회한다.

db=# BEGIN;
BEGIN

db=*# SELECT * FROM car WHERE car.id = 1
FOR NO KEY UPDATE;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2

db=*# SELECT * FROM owner WHERE id = 2;
─[ RECORD 1 ]─┬────────
id │ 2
name │ jerry

db=*# UPDATE car SET owner_id = 3 WHERE id = 1
RETURNING *;
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3

db=*# COMMIT;
COMMIT

db=# BEGIN;
BEGIN

db=*# SELECT * FROM car WHERE car.id = 1
FOR NO KEY UPDATE;
-- Session is blocked
--
--
--
--
--
--
--
--
--
--
--
--
--
--
--
-- Lock is released by first session
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 3

db=*# SELECT * FROM owner WHERE id = 3;
─[ RECORD 1 ]─┬────────
id │ 3
name │ george

이 방식은 데이터베이스를 두 번 왕복한다. 왕복을 줄이려면 잠금을 조인보다 먼저 얻도록 쿼리를 구성할 수 있다. 기존 쿼리의 실행 계획에는 조인 뒤에 LockRows 노드가 나타난다.

db=# BEGIN;
BEGIN

db=*# EXPLAIN SELECT * FROM car
JOIN owner ON car.owner_id = owner.id
WHERE car.id = 1 FOR NO KEY UPDATE OF car

 QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────
LockRows (cost=0.31..16.37 rows=1 width=56)
 -> Nested Loop (cost=0.31..16.36 rows=1 width=56)
 -> Index Scan using car_pkey on car (cost=0.15..8.17 rows=1 width=14)
 Index Cond: (id = 1)
 -> Index Scan using owner_pkey on owner (cost=0.15..8.17 rows=1 width=42)
 Index Cond: (id = car.owner_id)

LockRows는 행을 잠그고 다시 평가할 대상으로 표시하는 노드다(PostgreSQL 소스). 이 계획에서는 조인을 수행한 뒤 잠금을 얻는다.

자동차만 가져오는 CTE(공통 테이블 표현식) 안에서 먼저 잠금을 획득하고, 그 결과를 바깥 쿼리에서 소유자 테이블과 조인할 수 있다.

db=# BEGIN;
BEGIN;

db=*# EXPLAIN
WITH c AS (
 SELECT * FROM car WHERE id = 1
 FOR NO KEY UPDATE
)
SELECT * FROM c
JOIN owner ON c.owner_id = owner.id;
 QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────
 Nested Loop (cost=8.33..16.39 rows=1 width=44)
 CTE c
 -> LockRows (cost=0.15..8.18 rows=1 width=14)
 -> Index Scan using car_pkey on car (cost=0.15..8.17 rows=1 width=14)
 Index Cond: (id = 1)
 -> CTE Scan on c (cost=0.00..0.02 rows=1 width=8)
 -> Index Scan using owner_pkey on owner (cost=0.15..8.17 rows=1 width=36)
 Index Cond: (id = c.owner_id)

이 실행 계획에서는 CTE가 자동차 행에 잠금을 얻은 뒤 바깥 쿼리가 소유자 테이블과 조인한다. 두 세션으로 실행하면 대기 후 새 소유자 정보를 얻는다.

db=# BEGIN;
BEGIN

db=*# WITH c AS (
 SELECT * FROM car WHERE id = 1
 FOR NO KEY UPDATE
)
SELECT * FROM c
JOIN owner ON c.owner_id = owner.id;

─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 1
id │ 1
name │ haki

db=*# UPDATE car SET owner_id = 2 WHERE id = 1;
UPDATE 1

db=*# COMMIT;
COMMIT

db=# BEGIN;
BEGIN

db=*# WITH c AS (
 SELECT * FROM car WHERE id = 1
 FOR NO KEY UPDATE
)
SELECT * FROM c
JOIN owner ON c.owner_id = owner.id;
-- Session is blocked
--
--
--
--
--
--
--
--
--
--
--
-- Lock is released by first session
─[ RECORD 1 ]─┬────────
id │ 1
owner_id │ 2
id │ 2
name │ jerry

WITH를 사용할 수 없다면 같은 방식으로 하위 쿼리에서 잠금을 먼저 걸 수 있다.

db=# BEGIN;
BEGIN;

db=*# EXPLAIN
SELECT * FROM (
 SELECT * FROM car
 WHERE id = 1 FOR NO KEY UPDATE
) c
JOIN owner ON c.owner_id = owner.id;

 QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────
 Nested Loop (cost=0.31..16.38 rows=1 width=44)
 -> Subquery Scan on c (cost=0.15..8.19 rows=1 width=8)
 -> LockRows (cost=0.15..8.18 rows=1 width=14)
 -> Index Scan using car_pkey on car (cost=0.15..8.17 rows=1 width=14)
 Index Cond: (id = 1)
 -> Index Scan using owner_pkey on owner (cost=0.15..8.17 rows=1 width=36)
 Index Cond: (id = c.owner_id)

예방 사례

이 문제는 실제 운영 환경에서 발생했다. 높은 동시 부하가 예상되는 처리 과정이 아니라, 사용자가 화면의 버튼을 빠르게 여러 번 눌러 비슷한 요청이 서버로 중복 전송되면서 나타났다. 코드에 여러 검증 절차가 있어 시스템과 사용자가 실제로 영향을 받지는 않았다.

문제가 되는 패턴을 파악한 뒤 영향 가능성이 있는 곳을 살폈다. 과거에는 Django 검사를 이용해 특정 패턴을 찾아 보고했지만, 이번 패턴은 각 구문이 실행되는 맥락까지 이해해야 하므로 자동으로 찾기 어려웠다. LLM과 여러 차례 논의한 뒤 영향을 받을 수 있는 위치를 찾아 수정했다. 수정은 모두 조인 대신 별도의 쿼리를 실행하는 방식이었다.

SELECT ... FOR UPDATE와 NO KEY 옵션에 관한 설명은 How to Get Foreign Keys Horribly Wrong, 데이터베이스에서 잠금 없이 동시성을 다루는 방법은 Handling Concurrency Without Locks에서 확인할 수 있다. Django 검사 프레임워크에 관한 글은 Automating the Boring Stuff in Django Using the Check Framework다.

PostgreSQL 격리 수준 문서: Read Committed, Repeatable Read, Serializable.