데이터베이스 기초 (6) - Transactions, Isolation, and Locks

데이터베이스 기초 시리즈의 6편입니다. 전체 목차는 0편에 있습니다.

이번 편은 terminal 두 개에서 psql session을 동시에 열고, 두 transaction이 같은 row를 고칠 때 생기는 문제를 직접 재현합니다. 실습 환경은 PostgreSQL 편에서 만든 pg-tutorial container를 그대로 사용합니다.

ACID 성질

transaction이 보장하는 네 가지 성질을 머리글자로 묶어 ACID라고 부릅니다.

성질	보장하는 것
Atomicity	transaction 안의 작업은 전부 반영되거나 전부 취소됩니다. 절반만 반영된 상태는 남지 않습니다.
Consistency	transaction이 끝난 시점에는 PK, FK, CHECK 같은 제약이 지켜진 상태만 남습니다.
Isolation	동시에 실행되는 transaction들이 서로의 중간 상태를 보지 못합니다.
Durability	COMMIT된 변경은 server가 비정상 종료되어도 사라지지 않습니다.

Atomicity의 기본 동작은 1편의 BEGIN과 COMMIT, ROLLBACK에서 다뤘고, Durability를 구현하는 장치인 WAL은 8편에서 다룹니다. 이 편의 주제는 Isolation입니다. 동시에 실행되는 transaction이 서로에게 어디까지 보이는지, 그리고 격리가 느슨한 지점에서 어떤 데이터가 깨지는지 확인합니다.

lost update 재현

terminal 두 개에서 각각 docker exec -it pg-tutorial psql -U postgres로 접속하고, 두 terminal 모두 접속 직후 \c board를 실행해서 posts table이 있는 board database로 전환합니다. terminal A에서 posts table에 좋아요 수를 저장할 column을 추가합니다.

ALTER TABLE posts ADD COLUMN likes INTEGER NOT NULL DEFAULT 0;

좋아요 버튼을 구현하는 흔한 방식은 애플리케이션이 현재 값을 SELECT로 읽고, 1을 더한 결과를 UPDATE로 쓰는 것입니다. 사용자 두 명이 글 1번(첫 글입니다)의 버튼을 동시에 누른 상황을 재현합니다. 먼저 terminal A에서 transaction을 열고 현재 값을 읽습니다. 결과는 0입니다.

BEGIN;
SELECT likes FROM posts WHERE id = 1;

terminal B에서도 같은 문장을 실행합니다. 여기서도 0을 읽습니다.

BEGIN;
SELECT likes FROM posts WHERE id = 1;

terminal A에서, 애플리케이션이 계산했다고 가정한 값 0 + 1 = 1을 씁니다. COMMIT은 아직 하지 않습니다.

UPDATE posts SET likes = 1 WHERE id = 1;

terminal B에서 같은 UPDATE 문장을 실행하면 prompt가 돌아오지 않고 멈춥니다. UPDATE는 대상 row에 lock을 걸고 COMMIT이나 ROLLBACK까지 유지하는데, terminal A가 이미 이 row의 lock을 잡고 있기 때문입니다. terminal A에서 COMMIT을 실행하면 그 순간 terminal B의 UPDATE가 완료되고, terminal B에서도 COMMIT합니다.

이제 어느 쪽에서든 SELECT likes FROM posts WHERE id = 1;로 확인하면 값은 1입니다. 두 명이 눌렀는데 좋아요는 한 번만 반영되었습니다. terminal B가 A의 변경 전에 읽은 값 0을 기준으로 계산한 1을 그대로 덮어썼기 때문입니다. 이렇게 한쪽의 갱신이 사라지는 현상을 lost update라고 부릅니다. row lock은 B의 쓰기를 A의 COMMIT까지 지연시켰을 뿐, 낡은 값 기준의 덮어쓰기 자체는 막지 못했습니다.

해결 두 가지

첫 번째 해결책은 읽기와 계산을 SQL 한 문장으로 합치는 원자적 UPDATE입니다. terminal A에서 UPDATE posts SET likes = 0 WHERE id = 1;로 값을 0으로 되돌린 뒤, 두 terminal에서 아래 문장을 한 번씩 실행합니다. BEGIN 없이 실행하면 문장 하나가 곧 transaction 하나입니다.

UPDATE posts SET likes = likes + 1 WHERE id = 1;

likes + 1은 애플리케이션이 아니라 database가 계산합니다. 이 문장은 row lock을 잡은 뒤의 최신 값에 1을 더하므로, 두 실행이 겹쳐도 순서대로 처리되어 결과는 2가 됩니다. 좋아요 수, 조회수, 재고 수량 같은 대부분의 counter는 이 방식으로 충분합니다.

두 번째 해결책은 SELECT … FOR UPDATE입니다. 읽은 값으로 애플리케이션에서 복잡한 계산이나 조건 판단을 해야 해서 한 문장으로 합칠 수 없을 때는, 읽는 시점에 row를 잠급니다. 애플리케이션이 방금 읽은 값 2로 계산한 결과 3을 쓴다고 가정합니다.

BEGIN;
SELECT likes FROM posts WHERE id = 1 FOR UPDATE;
UPDATE posts SET likes = 3 WHERE id = 1;
COMMIT;

FOR UPDATE가 붙은 SELECT는 UPDATE와 같은 row lock을 겁니다. 다른 transaction이 같은 row를 FOR UPDATE로 읽으려 하면 이 transaction의 COMMIT까지 대기했다가 최신 값을 읽습니다. 읽기와 쓰기 사이에 다른 transaction이 끼어들 수 없으므로 lost update가 발생하지 않습니다.

MVCC와 isolation level

위 실습에서 terminal B의 UPDATE는 대기했지만, B가 UPDATE 없이 SELECT만 했다면 대기 없이 바로 결과를 받았을 것입니다. PostgreSQL은 MVCC(Multi-Version Concurrency Control) 방식으로 동작해서, UPDATE가 기존 row를 덮어쓰는 대신 새 버전을 만들고 각 transaction은 자기에게 보여야 할 버전을 읽습니다. 그래서 일반 SELECT는 쓰기를 막지 않고 쓰기도 일반 SELECT를 막지 않으며, row lock 대기는 같은 row를 잠그려는 쓰기(FOR UPDATE 같은 잠금 읽기 포함)끼리 만날 때 발생합니다.

transaction 사이의 격리 강도는 isolation level로 조절합니다. SQL 표준은 격리가 약할 때 생기는 세 가지 현상을 정의합니다. dirty read는 다른 transaction이 아직 COMMIT하지 않은 변경을 읽는 현상이고, non-repeatable read는 한 transaction 안에서 같은 row를 두 번 읽는 사이에 값이 바뀌는 현상이며, phantom read는 같은 조건으로 두 번 조회하는 사이에 row 개수가 달라지는 현상입니다. isolation level 4단계는 이 현상들을 어디까지 막는지로 구분됩니다.

isolation level	dirty read	non-repeatable read	phantom read
READ UNCOMMITTED	허용	허용	허용
READ COMMITTED	차단	허용	허용
REPEATABLE READ	차단	차단	허용
SERIALIZABLE	차단	차단	차단

이 표는 SQL 표준 기준이고 PostgreSQL은 표준보다 엄격합니다. READ UNCOMMITTED를 지정해도 READ COMMITTED로 동작해서 dirty read는 어느 level에서도 발생하지 않고, REPEATABLE READ는 phantom read까지 막습니다. 기본값은 SHOW default_transaction_isolation;으로 확인하며, 결과는 read committed입니다. 앞의 lost update 실습도 이 기본값에서 실행되었습니다. 대부분의 서비스는 READ COMMITTED에 원자적 UPDATE나 FOR UPDATE를 더하는 것으로 충분합니다. level을 올리면 database가 막아주는 현상이 늘어나는 대신 충돌한 transaction이 serialization failure(직렬화 실패) 오류로 취소되는데, 이는 높은 level에서 충돌이 감지될 때 database가 내는 오류입니다. 취소된 transaction의 재시도 로직은 애플리케이션이 감당해야 합니다.

deadlock 재현과 예방

두 transaction이 서로가 잡은 lock을 기다리면 어느 쪽도 진행할 수 없는데, 이 상태가 deadlock입니다. 두 row를 서로 다른 순서로 고쳐서 재현합니다. terminal A에서 글 1번의 lock을 잡습니다.

BEGIN;
UPDATE posts SET likes = likes + 1 WHERE id = 1;

terminal B에서 글 2번의 lock을 잡습니다.

BEGIN;
UPDATE posts SET likes = likes + 1 WHERE id = 2;

이어서 terminal A에서 UPDATE posts SET likes = likes + 1 WHERE id = 2;로 글 2번을 고치려 하면 B가 잡은 lock 때문에 대기합니다. 다시 terminal B에서 UPDATE posts SET likes = likes + 1 WHERE id = 1;로 글 1번을 고치려 하면 A는 B를, B는 A를 기다리는 순환이 만들어집니다.

lock 대기가 deadlock_timeout(기본 1초)을 넘으면 PostgreSQL이 대기 관계를 검사해서, 순환이 확인되면 한쪽 transaction을 강제로 취소합니다. 취소되는 쪽은 보통 마지막 UPDATE를 실행한 terminal이고, 아래 오류가 출력되면서 남은 쪽의 UPDATE는 그 즉시 완료됩니다.

ERROR:  deadlock detected
DETAIL:  Process 161 waits for ShareLock on transaction 771; blocked by process 168.
Process 168 waits for ShareLock on transaction 770; blocked by process 161.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "posts"

오류를 받은 terminal은 ROLLBACK으로 transaction을 정리하고, 남은 terminal은 COMMIT합니다. 예방 원칙은 두 가지입니다. 여러 row를 고치는 transaction은 항상 같은 순서로 lock을 잡고(예를 들어 id 오름차순), transaction을 짧게 유지해서 lock을 잡고 있는 시간을 줄입니다. deadlock을 재현하고 예방법을 설명하는 것은 0편에서 말한 도달 기준의 세 번째 항목입니다. 실습이 끝났으므로 likes column을 삭제해서 표준 schema로 되돌립니다.

ALTER TABLE posts DROP COLUMN likes;

MySQL 차이

MySQL 편에서 다룬 MySQL 8.4의 기본 storage engine(table 데이터를 디스크에 읽고 쓰는 모듈)인 InnoDB는 기본 isolation level이 REPEATABLE READ입니다. PostgreSQL의 READ COMMITTED와 다른 대표적인 기본값 차이이고, SELECT @@transaction_isolation;으로 확인합니다. deadlock을 감지해서 한쪽 transaction을 취소하는 동작은 같지만, InnoDB는 대기 시간 없이 lock 요청 시점에 바로 감지하고 취소된 transaction을 자동으로 롤백합니다. 오류 메시지는 “Deadlock found when trying to get lock; try restarting transaction”입니다. UPDATE posts SET likes = likes + 1 형태의 원자적 UPDATE와 SELECT … FOR UPDATE 패턴도 동일하게 동작합니다.

다음 편

다음 7편에서는 애플리케이션 코드가 driver와 connection pool, ORM으로 database를 사용하는 방법을 다룹니다.