PostgreSQL: 트랜잭션, 락, 격리 수준, MVCC, VACUUM

PostgreSQL 심층 분석: 트랜잭션, 잠금, 직렬화 격리 수준 완벽 이해

개발자들은 PostgreSQL에서 종종 예측하기 어려운 쿼리 동작을 마주합니다. 예를 들어, 멈춤 현상, 동시성 작업 중 예상치 못한 UPDATE 결과, 또는 트랜잭션 실패 등이 있습니다. 격리 수준, 잠금, 그리고 VACUUM 작동 방식과 같은 내부 메커니즘을 이해하는 것은 안정적이고 고성능의 애플리케이션을 구축하는 데 필수적입니다. 이 글은 이러한 개념들을 실제 SQL 예시를 통해 심층적으로 분석하고, 최근 PostgreSQL의 개선 사항들을 강조합니다.

트랜잭션 내 시간: `now()` vs `clock_timestamp()`

PostgreSQL은 현재 시간을 가져오는 두 가지 주요 함수를 제공하며, 이들의 동작은 트랜잭션 컨텍스트 내에서 다릅니다. now() 함수는 현재 트랜잭션의 시작 시간을 반환하며, 트랜잭션이 실행되는 동안 변경되지 않습니다. 이는 예를 들어 created_at 및 updated_at을 설정할 때 단일 트랜잭션 내의 작업에 대한 타임스탬프 일관성을 보장합니다.

BEGIN;
SELECT now();
-- ... 다른 작업들 ...
SELECT now(); -- 첫 번째 호출과 동일한 시간을 반환합니다.
SELECT clock_timestamp(); -- 다를 수 있습니다.
COMMIT;

now()와 달리 clock_timestamp() 함수는 실제 현재 실행 시간을 제공하며, 이는 단일 쿼리나 트랜잭션 내에서도 변경될 수 있습니다. 이 차이를 이해하는 것은 시간 기반 데이터를 다룰 때 오류를 피하는 데 도움이 됩니다.

PostgreSQL의 트랜잭션 격리 수준

PostgreSQL은 다른 RDBMS에 비해 단순화된 격리 수준 모델을 제공하며, 세 가지 수준만 지원합니다:

Read Committed (읽기 커밋됨): 기본 격리 수준으로, 트랜잭션 내의 각 명령은 데이터의 새로운 스냅샷을 봅니다. PostgreSQL에서는 Dirty Read(더티 읽기)가 근본적으로 불가능하므로, Read Uncommitted(읽기 커밋되지 않음)는 Read Committed와 동일합니다.
Repeatable Read (반복 가능 읽기): 트랜잭션 내의 모든 읽기 작업이 첫 번째 쿼리 시점의 고정된 데이터 스냅샷을 보도록 보장합니다. 이는 Phantom Read(팬텀 읽기)와 Non-Repeatable Read(반복 불가능 읽기)를 방지합니다.
Serializable (직렬화 가능): 가장 엄격한 수준으로, 동시 실행되는 트랜잭션들이 마치 순차적으로 실행된 것처럼 동일한 결과를 보장합니다. 이는 직렬화 가능 스냅샷 격리(SSI) 메커니즘을 통해 달성됩니다.

PostgreSQL의 중요한 특징 중 하나는 트랜잭션 내에서 DDL(데이터 정의 언어)을 지원한다는 것입니다. 이를 통해 데이터 구조를 생성, 변경 또는 삭제하고 필요한 경우 롤백할 수 있어 마이그레이션 및 복잡한 스키마 변경의 안전성을 높입니다.

잠금 메커니즘 및 동시성 제어

MVCC(다중 버전 동시성 제어)를 사용함에도 불구하고, PostgreSQL은 쓰기 작업에 적극적으로 잠금을 사용합니다. 두 트랜잭션이 동일한 행을 업데이트하려고 시도하면, 두 번째 트랜잭션은 첫 번째 트랜잭션이 완료될 때까지 대기합니다. 다음 예시를 살펴보세요:

-- 세션 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... 커밋하지 않음 ...

-- 세션 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- 이 쿼리는 세션 1이 완료될 때까지 대기합니다.

아래 표는 PostgreSQL의 주요 잠금 유형, 목적 및 충돌 모드를 보여줍니다:

| 잠금 유형 | 획득 시점 | 충돌 모드 |

| :-------------------- | :------------------------------------------ | :---------------------------- |

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

| ROW SHARE | SELECT FOR UPDATE/SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE 이상 |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE 이상 |

| SHARE | CREATE INDEX | ROW EXCLUSIVE 이상 |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (일부) | ROW SHARE 이상 |

| EXCLUSIVE | 읽기만 허용 | ROW SHARE 이상 |

| ACCESS EXCLUSIVE | DROP TABLE, ALTER TABLE, VACUUM FULL | 모든 잠금 |

잠금이 엄격할수록 표에서의 번호가 높고 충돌하는 작업의 범위가 넓어집니다. 충돌하는 잠금을 가진 작업은 동일한 객체에서 동시에 실행될 수 없으며, 하나는 대기해야 합니다. 표준 CREATE INDEX와 달리 CREATE INDEX CONCURRENTLY는 DML 작업을 차단하지 않고 인덱스를 구축할 수 있다는 점에 유의해야 합니다.

읽기 이상 현상: 팬텀 및 반복 불가능 데이터

Read Committed 격리 수준에서는 읽기 이상 현상이 발생하여 일관성 없는 결과를 초래할 수 있습니다:

Phantom Reads (팬텀 읽기): 한 트랜잭션이 동일한 쿼리를 두 번 실행하고, 두 쿼리 사이에 다른 커밋된 트랜잭션에 의해 새로운 행이 삽입되어 두 번째 쿼리에서 새로운 행을 볼 때 발생합니다.

```sql

-- 세션 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- 예: 330

-- 세션 2 (동시에):

INSERT INTO t_test2 VALUES (1, 30); COMMIT;

-- 세션 1:

SELECT sum(value) FROM t_test2; -- 이제 360, 새로운 행이 나타남.

COMMIT;

```

Non-Repeatable Reads (반복 불가능 읽기): 한 트랜잭션이 동일한 데이터를 두 번 읽고, 두 번째 읽기에서 다른 커밋된 트랜잭션에 의해 데이터가 수정된 것을 볼 때 발생합니다.

```sql

-- 세션 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- 행을 반환

-- 세션 2 (동시에):

UPDATE t_test2 SET value = 15 WHERE class = 1 AND value = 10; COMMIT;

-- 세션 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- 비어있음! 데이터가 변경됨.

COMMIT;

```

이러한 이상 현상을 방지하려면 Repeatable Read 격리 수준을 사용합니다. 이 수준에서는 트랜잭션이 다른 트랜잭션에 의해 변경된 내용과 관계없이 완료될 때까지 변경되지 않는 고정된 데이터 스냅샷에서 작동합니다.

직렬화 가능 격리 수준의 특이점

Serializable 수준은 최대 격리를 제공하여 순차적인 트랜잭션 실행을 시뮬레이션하도록 설계되었습니다. 그러나 PostgreSQL이 실행 순서를 위반할 수 있는 잠재적 충돌을 감지하면 직렬화 오류(ERROR: could not serialize access)가 발생할 수 있습니다. 이러한 경우, 트랜잭션 중 하나는 중단되고 재시도해야 합니다.

Serializable의 흥미로운 측면은 술어 잠금(SSI)으로 인해 발생하는 거짓 충돌입니다. PostgreSQL은 이러한 잠금을 개별 행이 아닌 페이지 수준에서 저장합니다. 두 트랜잭션이 논리적으로 다른 데이터셋으로 작업하지만, 이 데이터가 물리적으로 동일한 디스크 페이지에 위치하는 경우(작은 테이블에서 자주 발생), 논리적으로 충돌이 없음에도 불구하고 직렬화 충돌이 발생할 수 있습니다. 데이터가 다른 페이지에 분산된 큰 테이블에서는 이러한 충돌이 덜 일반적입니다.

동시성 문제 해결

고전적인 데이터 경쟁 문제를 고려해 봅시다: 두 트랜잭션이 동시에 오래된 읽기 값을 기반으로 데이터를 수정하려고 합니다. 예를 들어, 앨리스와 밥이 당직 중인데, 각자 최소 한 명의 당직자가 남아있다면 퇴근하고 싶어 합니다. 두 트랜잭션 모두 Repeatable Read를 사용하고 두 명의 당직자가 있음을 확인한 후 성공적으로 커밋하면, 결국 아무도 당직을 서지 않게 됩니다.

이러한 문제를 해결하기 위해 다음 접근 방식이 사용됩니다:

Serializable: 두 번째 트랜잭션은 직렬화 오류로 실패하며, 이후 재시도할 수 있습니다. 이는 올바른 결과를 보장하지만, 애플리케이션에서 오류 처리가 필요합니다.
SELECT ... FOR UPDATE: 읽기 시 FOR UPDATE를 사용하면 선택된 행을 명시적으로 잠가, 현재 트랜잭션이 완료될 때까지 다른 트랜잭션이 해당 행을 수정하지 못하도록 합니다. 두 번째 세션은 첫 번째 세션이 완료될 때까지 대기한 후 업데이트된 데이터를 보게 됩니다.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- 행을 잠금

-- ... 추가 작업 ...

COMMIT;

```

원자적(Atomic) 작업: 조건 확인과 동작을 단일 쿼리로 결합합니다. UPDATE 시점에 조건이 충족되지 않으면 쿼리는 어떤 행에도 영향을 미치지 않아 경쟁 상황을 제거합니다.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

AND (SELECT count(*) FROM d_test WHERE on_call = true) > 1;

```

자문 잠금 (Advisory Locks)

PostgreSQL은 테이블이나 행에 직접 연결되지 않은 임의의 리소스에 대한 동시 접근을 제어할 수 있는 자문 잠금 메커니즘(pg_advisory_lock, pg_advisory_xact_lock)을 제공합니다. 이 잠금은 정수 값을 기반으로 작동하며 프로세스 간 조정을 위해 사용될 수 있습니다.

pg_advisory_lock: 세션 수준 잠금. 명시적으로 해제(pg_advisory_unlock)되거나 연결이 종료될 때까지 유지됩니다.
pg_advisory_xact_lock: 트랜잭션 수준 잠금. COMMIT 또는 ROLLBACK 시 자동으로 해제됩니다.

자문 잠금은 단일 데이터베이스 내에서만 작동한다는 점을 기억하는 것이 중요합니다. 여러 PostgreSQL 인스턴스를 사용하는 분산 시스템의 경우, 외부 솔루션(예: Redis, ZooKeeper, etcd)이 필요합니다.

MVCC에서 VACUUM의 역할

PostgreSQL의 MVCC(다중 버전 동시성 제어)는 UPDATE 또는 DELETE 작업 시 이전 버전의 행이 즉시 제거되지 않고 '죽은 튜플(dead tuples)'로 표시됨을 의미합니다. 이러한 죽은 튜플은 공간을 차지하며, 새로운 데이터를 위한 공간을 확보하고 테이블 비대화(table bloat)를 방지하기 위해 정리되어야 합니다. VACUUM 프로세스가 이 작업을 담당합니다.

기본적으로 VACUUM은 테이블 파일의 물리적 크기를 줄이지 않습니다. 단지 해제된 공간을 재사용 가능한 공간으로 표시할 뿐입니다. 이는 이후의 INSERT 또는 UPDATE 작업이 파일 크기를 늘리지 않고 이 공간을 차지할 수 있음을 의미합니다.

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);
SELECT pg_size_pretty(pg_relation_size('t_test')); -- 약 3.5 MB

UPDATE t_test SET id = id + 1; -- 새로운 행 버전을 생성
SELECT pg_size_pretty(pg_relation_size('t_test')); -- 약 7 MB (크기 두 배)

VACUUM t_test; -- 죽은 튜플을 정리하지만, 파일 크기는 변하지 않음
SELECT pg_size_pretty(pg_relation_size('t_test')); -- 여전히 약 7 MB

테이블 파일의 물리적 크기 감소는 VACUUM이 '꼬리 부분을 잘라낼' 수 있는 경우에만 발생합니다. 즉, 해제된 페이지가 파일의 끝에 있는 경우입니다. 예를 들어, 높은 id를 가진 많은 수의 행을 삭제한 후:

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- 약 3.5 MB (크기 감소)

자동 autovacuum은 테이블의 변경 사항을 지속적으로 모니터링하고 VACUUM을 실행하여 데이터베이스 상태를 유지하는 매우 중요한 백그라운드 프로세스입니다. 현대 PostgreSQL 버전에서 그 결과를 깊이 이해하지 않고 이를 비활성화하는 것은 안티 패턴입니다.

PostgreSQL의 진화: 지난 5년간의 변화

트랜잭션과 잠금의 기본 원칙은 변함없이 유지되지만, PostgreSQL은 성능과 유용성 향상을 목표로 지속적으로 발전하고 있습니다:

VACUUM 최적화: PostgreSQL 13에서는 VACUUM 중 병렬 인덱스 처리가 도입되었고, PostgreSQL 16에서는 이전 실행 이후 변경되지 않은 페이지를 건너뛰는 기능이 추가되었습니다. 이러한 개선 사항은 대규모 테이블에서 VACUUM 작업 속도를 크게 향상시켜 수동 관리의 필요성을 줄였습니다.
REINDEX CONCURRENTLY (PG 12): 테이블의 DML 작업을 차단하지 않고 인덱스를 재구축할 수 있게 하여, 고부하 시스템에 매우 중요합니다.
향상된 잠금 모니터링: pg_stat_activity 및 pg_locks 시스템 뷰에 새로운 필드가 추가되었고, pg_wait_sampling 확장은 상세한 대기 통계를 제공하여 성능 문제 진단을 간소화했습니다.

핵심 요약:

now() vs clock_timestamp(): now()는 일관성을 위해 트랜잭션 시작 시간을 고정하고, clock_timestamp()는 실제 현재 시간을 제공합니다.
격리 수준: PostgreSQL은 Read Committed(기본값), Repeatable Read(읽기 이상 현상 방지), Serializable(직렬화 오류 위험이 있는 최대 일관성)를 제공합니다.
잠금: MVCC에도 불구하고 PostgreSQL은 쓰기 잠금을 사용합니다. 잠금 테이블과 그 충돌을 이해하는 것은 동시성 접근 최적화에 필수적입니다.
Serializable 및 거짓 충돌: Serializable 수준은 페이지 수준의 술어 잠금으로 인해 오류를 유발할 수 있으며, 트랜잭션 재시도가 필요합니다.
VACUUM 및 MVCC: VACUUM은 죽은 튜플을 정리하지만 일반적으로 파일 크기를 줄이지 않고, 공간을 재사용 가능하게 표시할 뿐입니다. 물리적 크기 감소는 해제된 페이지가 파일의 끝에 있을 때 발생합니다.