[DB] MySQL과 PostgreSQL의 격리 수준 (w/ 이상 현상)

👻 이상 현상

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

---

PostgreSQL

PG는 MVCC를 통해 격리 수준을 구현한다. MVCC는 특정 시점에 커밋된 데이터를 읽는 것이다. 따라서 내부적으로 read uncommitted를 제공하지 않는다.

1️⃣ SERIALIZABLE

• 특정 트랜잭션이 사용 중인 모든 행을 다른 트랜잭션 접근 못하도록 잠금 (가장 엄격)
• 마치 순차적으로 실행하는 것처럼 보여, 성능이 떨어진다.
• 단순한 SELECT를 하더라도 락이 걸리기 때문에 불필요한 대기가 발생할 수 있다.
• PG에선 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 같은 명령어를 통해 다른 트랜잭션이 변경 후 커밋을 하지 않은 상태라면, 해당 변경점들이 커밋될 때까지 대기한다.
• 가장 많이 사용
• 자신이 커밋하지 않은 변경 점은 조회가 가능하다

---

MySQL

MySQL과 PG 모두 MVCC를 지원하기 때문에 격리 수준에서 크게 차이나지 않는다. 다만 REPEATABLE READ에서 Phantom Reads를 어떻게 방지하는지에 따라 차이가 바생한다.

1️⃣ SERIALIZABLE

pg와 동일하게 순차적으로 처리하므로 성능이 매우 떨어진다.

단순 읽기 쿼리에서도 대상 레코드에 Next Key Lock을 공유 락으로 걸기 때문에, 해당 레코드에 대한 추가/수정/삭제가 불가능하다.

 

2️⃣ REPEATABLE READ

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

MySQL에선 이를 Gap Lock과 비관적 락을 통해 해결할 수 있다.

비관적 락을 통해 수행된 트랜잭션이 있는 경우, 다른 트랜잭션에서의 변경 사항의 Gap Lock에 의해 대기 상태가 된다. 이 상태에서 사용자 B의 트랜잭션이 종료되고 나서야 비로소 변경사항이 반영된다.
만약 비관적 락을 사용하지 않을 경우, 락이 존재하지 않기 때문에 변경 사항들이 바로 Commit된다. 이 상태에서 SELECT FOR UPDATE를 하면, Undo Log가 아닌 Table로부터 조회가 되므로 Phantom Reads가 발생한다.

Read1	Read2	팬텀리드여부	이유
SELECT FOR UPDATE	SELECT	X	Gap Lock
SELECT FOR UPDATE	SELECT FOR UPDATE	X	Gap Lock
SELECT	SELECT	X	MVCC
SELECT	SELECT FOR UPDATE	O	-

---

✅ 요약

PostgreSQL

	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITED	X	O	O
READ COMMITED	X	O	O
REPEATABLE READ	X	X	X
SERIALIZABLE	X	X	X

MySQL

	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITED	O	O	O
READ COMMITED	X	O	O
REPEATABLE READ	X	X	O(거의 없음)
SERIALIZABLE	X	X	X

 

---

🗂️ 출처 및 참고

postgreSQL - 트랜잭션 격리 수준
트랜잭션 격리 수준
MySQL 격리 수준