[DB] MySQL과 pgSQL의 트랜잭션 격리 수준과 이상 현상

이상 현상

이름	설명
Dirty Read	다른 트랜잭션에서 Flush되지 않은 변경사항이 반영되는 현상
Non-Repeatable Read	같은 조회 쿼리 사이에 다른 트랜잭션의 커밋된 내용이 반영되어 조회 결과가 달라지는 현상
Phantom Reads	다른 트랜잭션에서 추가된 행이 반영되는 현상
Lost Update	현재 트랜잭션에 의해 다른 트랜잭션의 커밋된 변경사항이 사라지는 현상

Dirty Read

sequenceDiagram
    participant T1
    participant T2
    T1->>DB: UPDATE value=100 (커밋 안 함)
    T2->>DB: SELECT value (100 읽음, 실제 커밋 전 데이터)
    T1->>DB: ROLLBACK

T1에 의해 value가 100으로 변경되었는데, 이 커밋되지 않은 변경 내용을 T2에서 읽을 경우 100이 읽히는 것을 말한다. 만약 이상태에서 T1이 롤백을 하게 되면 , T2는 이상한 값을 읽은 꼴이 된다.

Non-repeatable Read

sequenceDiagram
    participant T1
    participant T2
    T1->>DB: SELECT value (100)
    T2->>DB: UPDATE value=200
    T2->>DB: COMMIT
    T1->>DB: SELECT value (200, 결과 달라짐)

T1에서 첫 조회 시에는 100이 읽혔지만, 다른 트랜잭션에 의해 값이 바뀌면 언제든지 값이 달라질 수 있는 문제이다.

Phantom Read

sequenceDiagram
    participant T1
    participant T2
    T1->>DB: SELECT * WHERE level>50 (2건 조회)
    T2->>DB: INSERT INTO table VALUES (level=60)
    T2->>DB: COMMIT
    T1->>DB: SELECT * WHERE level>50 (3건 조회, 행이 추가됨)

T2에 의해 삽입된 행이 T1 조회 시에도 읽히는 문제를 말한다.

Lost Update

sequenceDiagram
    participant T1
    participant T2
    T1->>DB: SELECT value (50)
    T2->>DB: SELECT value (50)
    T2->>DB: UPDATE value=150 (커밋)
    T1->>DB: UPDATE value=100 (덮어씀, T2의 변경 사라짐)
    T1->>DB: COMMIT

격리 수준

트랜잭션 격리 수준이란 RDBMS에서 여러 트랜잭션이 동시에 처리될 때, 각 트랜잭션이 서로의 변경 내용이나 데이터를 볼 수 있도록 허용할지 말지를 정하는 규칙히다.

PostgreSQL

1️⃣ SERIALIZABLE

• 특정 트랜잭션이 사용 중인 모든 행을 다른 트랜잭션 접근 못하도록 잠금 (가장 엄격)
• 마치 순차적으로 실행하는 것처럼 보여, 성능이 떨어진다.
• 단순한 SELECT를 하더라도 락이 걸리기 때문에 불필요한 대기가 발생할 수 있다.
• pgSQL에선 Serializable 격리 수준을 위해 Serializable Snapshot Isolation을 구현했다.

예시

# 순차적으로 실행

# 트랜잭션A
begin transaction isolation level serializable;
# 트랜잭션B
begin transaction isolation level serializable;

# 트랜잭션A
update test set t='b' where t='a';
# 트랜잭션B
update test set t='a' where t='b';

# 트랜잭션A
commit;
#트랜잭션B
commit;
ERROR: could not serialize access due to read/write dependencies among transactions

2️⃣ REPEATABLE READ

• 트랜잭션 시작 전에 커밋된 데이터만 조회한다. 트랜잭션 실행 중 변경된 데이터(커밋 여부 상관없이)는 볼 수 없다.
• 특정 행 조회시 항상 같은 데이터 응답하도록 보장
• 트랜잭션 시작 지점의 스냅샵을 보기 때문에 Serializable anomaly를 제외한 모든 이상 현상을 방지

💡 Postgres는 MySQL과 다르게 REPEATABLE READ으로 적용하면, 매번 select를 해도 같은 결과를 가져옴 (유령 읽기 방지)

🔥 Repeatable Reads는 발생할 수 있는 이상현상들을 감지하기 위해 모니터링 기술이 들어갔다. 반면에 Serializable은 이상현상을 방지하기 위해 트랜잭션을 가능한 모두 직렬로 실행한다는 차이가 있다.

예시

id	t
1	b
2	b
3	a
4	a

# 순차적으로 실행

# 트랜잭션A
begin transaction isolation level repeatable read;
# 트랜잭션B
begin transaction isolation level repeatable read;

# 트랜잭션A
update test set t='b' where t='a';
# 트랜잭션B
update test set t='a' where t='b';

# 트랜잭션A
commit;
#트랜잭션B
commit;

id	t
1	a
2	a
3	b
4	b

트랜잭션 생성 시점의 스냅샷을 기준으로 변경하기 때문에 위 코드의 경우 아래와 같이 결과가 발생한다. 그러나 만약 T1이 T2가 변경한 행을 수정할 경우, Lost Update가 발생하기 때문에 DB가 강제로 롤백시킨다.

"ERROR:  could not serialize access due to concurrent update"

3️⃣ READ COMMITTED (Default)

• 커밋 완료된 트랜잭션 변경사항만 다른 트랜잭션에서 조회하도록 허용
• 한 트랜잭션이 아직 커밋하지 않았다면, 해당 변경사항은 다른 트랜잭션에서 볼 수 없습니다
• 만약 UPDATE, DELETE, SELECT FOR UPDATE/SHARE 같은 명령어를 통해 다른 트랜잭션이 변경 후 커밋을 하지 않은 상태라면, 해당 변경점들이 커밋될 때까지 대기한다.
• 자신이 커밋하지 않은 변경 점은 조회가 가능하다

예시

# 트랜잭션A
begin transaction isolation level read committed;
SELECT SUM(value) FROM purchases; -- 결과: 1600

# 트랜잭션B
begin transaction isolation level read committed;
INSERT INTO purchase (value) VALUES (400);
COMMIT;

# 트랜잭션A
SELECT SUM(value) FROM purchases; -- 결과: 2000

위 예시는 Non-Repeatable Read가 발생하는 예시이다. 당연하게도 커밋된 내용을 실시간으로 조회하기 때문에 다른 트랜잭션에서 커밋된 내용으로 인해 이전과 결과가 다를 수 있다.

MySQL

MySQL과 pgSQL 모두 MVCC를 지원하기 때문에 격리 수준에서 크게 차이나지 않는다. 다만 Repeatable Read에서 팬텀 리드를 어떻게 방지하는지에 따라 차이가 발생한다.

1️⃣ SERIALIZABLE

pg와 동일하게 순차적으로 처리하므로 성능이 매우 떨어진다.
단순 읽기 쿼리에서도 대상 레코드에 Next Key Lock을 공유 락으로 걸기 때문에, 해당 레코드에 대한 추가/수정/삭제가 불가능하다.

2️⃣ REPEATABLE READ

PG와 마찬가지로 MVCC를 통해 동일 트랜잭션 내에서의 같은 조회를 보장한다.그러나 새로운 레코드가 추가되는 것을 허용한다. 즉 다른 트랜잭션에서 추가로 생성된 레코드에 대해선 Phantom Reads가 발생할 수 있다.
MySQL에선 이를 Gap Lock과 비관적 락을 통해 해결할 수 있다.

이 내용은 추후 포스팅으로 남기겠다.

격리 수준과 이상 현상의 관계

	SERIALIZABLE	REPEATABLE READ	READ COMMITTED	READ UNCOMMITTED
Dirty Read	X	X	X	O
Non-repeatable Read	X	X	O	O
Phantom Read	X	O	O	O
Lost Update	X	O	O	O