트랜잭션 동시성 처리 #1 (feat. 오라클 vs MySQL vs PostgreSQL)

들어가며

트랜잭션 동시성은 단순히 “락을 건다”로 끝나지 않는다.
DB마다 읽기 일관성(snapshot)을 구현하는 방식이 다르고, 그 결과 데이터가 메모리와 디스크 사이를 흐르는 구조도 다르다.

Oracle은 버퍼 캐시, redo log buffer, undo, background process를 함께 사용해 읽기/쓰기 동시성을 처리한다.

PostgreSQL은 테이블 내부의 tuple version을 쌓고 vacuum으로 정리하며,

InnoDB는 undo log와 read view를 이용해 과거 버전을 보여준다.

Oracle

Oracle 아키텍처를 동시성 관점으로 봐보자

Oracle 인스턴스에서 이 발표에 가장 중요한 구성요소는 네 가지이다.

• Shared Pool: SQL 파싱 결과, 실행 계획, 데이터 딕셔너리 정보 캐시
• Database Buffer Cache: 데이터파일에서 읽어온 데이터 블록 사본 저장
• Redo Log Buffer: 변경 내역을 설명하는 redo entry 저장
• Undo: 롤백과 read consistency를 위해 변경 전 정보를 유지

→ 여기서 Undo 영역은 Datafiles안의 Undo Tablespace에 있다고 보면됨. Undo 정보가 보통 Undo Tablespace의 Undo Segment 형태로 데이터파일에 저장된다. 여러 개의 Undo Segment들을 묶어서 관리하는 논리적인 큰 그릇이 바로 Undo Tablespace.

 

Oracle은 데이터를 바로 datafile에 덮어쓰는 구조가 아니라, 먼저 메모리에서 변경을 처리하고, redo로 영속성을 확보한 뒤, 나중에 datafile에 반영하는 구조다. LGWR는 redo를 online redo log에 기록하고, DBWR는 더티 버퍼를 datafile에 기록한다.

 

Oracle에서 SELECT는 어떻게 흐를까?

조회는 보통 락으로 해결하지 않는다. Oracle은 Read Consistency로 해결한다.

• 클라이언트가 SQL 전송
• 서버 프로세스가 Shared Pool에서 SQL parse/plan 확인
• Buffer Cache에 필요한 블록이 있으면 바로 읽음
• 없으면 Datafile에서 블록을 읽어 Buffer Cache에 적재
• 현재 블록이 내 조회 시점보다 너무 최신이면 Undo를 이용해 과거 시점 이미지를 복원
• 그 결과를 사용자에게 반환

Oracle은 기본적으로 READ COMMITTED에서 statement-level read consistency 를 제공한다. 즉 기본 격리 수준에서 SELECT는 “트랜잭션 시작 시점”이 아니라 “각 쿼리 시작 시점” 기준으로 일관된 데이터를 본다.

→ 일반적으로 SELECT가 다른 세션의 UPDATE 때문에 멈추지 않는다. 현재 블록이 너무 최신이다? 그러면 Undo를 참조해서 내가 봐야하는 시점의 데이터를 재구성 한다.

Oracle에서 Update는 어떻게 흐를까?

변경은 Buffer Cache에서 먼저 발생한다. 그리고 동시에 Undo와 Redo가 함께 움직인다.

• UPDATE 요청 도착
• 대상 블록을 Buffer Cache에서 찾음
• 없으면 Datafile에서 읽어옴
• 변경 전 정보는 Undo 쪽에 남길 수 있도록 undo 정보 생성
• 현재 블록은 Buffer Cache에서 수정되어 dirty buffer가 됨
• 변경 사실은 Redo Log Buffer에 redo entry로 기록
• 같은 row에 대해서는 row lock으로 쓰기 충돌을 제어
• COMMIT 시 LGWR가 redo를 online redo log에 기록
• 이후 DBWR가 적절한 시점에 dirty block을 datafile에 기록

Oracle의 redo log buffer는 변경을 설명하는 redo entry를 담고, buffer cache는 데이터파일 블록의 사본을 저장한다. Oracle은 multiversion consistency와 locking을 같이 사용한다.

Oracle Commit의 진짜 의미?

Q. COMMIT = datafile 저장 완료 를 의미할까?

정답 : 아니다.

 

Oracle에서 커밋의 핵심은 redo가 안전하게 기록되었는가이다.
커밋 시 LGWR가 redo를 online redo log에 기록하고, 그 뒤 클라이언트에 commit 성공을 반환한다. datafile 반영은 DBWR가 나중에 할 수 있다. 즉 영속성 보장의 기준은 datafile 즉시 반영이 아니라 redo 기록이다.

→ “커밋 = 디스크의 테이블 파일에 즉시 저장 완료” 방식이 아니라는 것, 먼저 redo를 안전하게 남긴 후, datafile 반영은 나중에 할 수도 있음.

데이터 흐름 update & commit 

• 데이터 자체 수정은 Buffer Cache에서 먼저 일어남
• 과거 버전 복원용 정보는 Undo 쪽으로 감
• 복구용 변경 기록은 Redo Log Buffer에 쌓임
• 커밋 시 LGWR가 redo를 online redo log에 씀
• 실제 datafile 저장은 DBWR가 나중에 수행
• 다른 세션의 SELECT는 필요하면 Undo를 이용해 과거 시점 데이터를 봄

 

SELECT 에 대한 consistent read 흐름

여기서, 핵심은 SELECT가 UPDATE를 기다리지 않고, 필요하면 Undo로 과거 버전을 재구성한다는 점이다. Oracle은 이걸 Consistency read로 설명한다.

 

 

Oracle 동시성의 본질 ?

크게 두가지로 보면 된다.

1. 읽기 동시성
   • Undo + SCN 기반으로 과거 시점 데이터 재구성
   • 읽기와 쓰기의 충돌을 줄임
   • 일반 SELECT는 보통 row lock을 걸지 않음
2. 쓰기 동시성
   • 같은 row를 동시에 바꾸려는 경우 row lock으로 조정
   • UPDATE, DELETE, SELECT ... FOR UPDATE 계열에서 충돌 가능

 

Oracle은 읽기 충돌은 Undo 기반 일관성으로 해결하고, 쓰기 충돌은 row lock으로 해결한다.

 

PostgreSQL

PostgreSQL은 Oracle처럼 undo를 따라가서 과거 버전을 복원하는 방식이 아니다.
공식 문서는 PostgreSQL이 MVCC 를 사용하고, 각 row version의 가시성은 트랜잭션 스냅샷과 system column인 xmin, xmax 등에 의해 판단된다고 설명한다. 또한 불필요해진 오래된 row version은 VACUUM이 정리한다.

• SELECT는 snapshot을 기준으로 현재 visible tuple만 읽음
• UPDATE는 기존 row를 직접 덮기보다 새 버전 row 생성
• 기존 row는 더 이상 최신이 아니게 표시
• WAL에 변경 기록
• 나중에 VACUUM/autovacuum이 dead tuple 정리

PostgreSQL은 과거 버전을 undo에서 찾지 않고, 행 자체를 버전으로 계속 남긴다. 그래서 VACUUM이 필수이다.

 

 

MySQL InnoDB

InnoDB는 PostgreSQL보다는 Oracle과 더 닮아 있다.
공식 문서는 InnoDB가 undo log 를 사용해 earlier version을 구성하고, 일반 SELECT는 consistent nonlocking read이며, update undo log는 다른 스냅샷 조회에 더 이상 필요 없을 때까지 유지된다고 설명한다.

• SELECT 시 Read View 기준으로 현재 row가 보이는지 판단
• 현재 row의 변경이 너무 최신이면 DB_ROLL_PTR 계열 체인을 따라 undo에서 과거 버전 복원
• UPDATE 시 현재 row 갱신
• 이전 버전은 undo log에 저장
• redo도 별도로 기록
• purge thread가 더 이상 필요 없는 undo 정리

InnoDB는 현재 row에 숨은 트랜잭션 정보와 undo log를 같이 써서, 트랜잭션마다 자신이 봐야 할 시점의 row를 보여준다.

 

 

항목	Oracle	PostgreSQL	MySQL(InnoDB)
과거 버전 위치	Undo	테이블 내부 old tuple	Undo Log
읽기 기준	SCN 기반 consistent read	Snapshot + tuple visibility	Read View + trx metadata
현재 데이터 변경 위치	Buffer Cache에서 블록 수정	새 tuple version 생성	현재 row 갱신 + undo chain
커밋 후 정리 방식	Undo 관리 중심	VACUUM 필요	Purge thread
일반 SELECT	락 없이 일관성 읽기	락 없이 snapshot 읽기	nonlocking consistent read

 

세 DB 모두 읽기/쓰기 공존을 목표로 하지만, 과거 버전을 어디에 두는가와 어떻게 정리하는가가 다르다. Oracle은 undo와 SCN, PostgreSQL은 tuple version과 vacuum, InnoDB는 undo log와 read view를 중심으로 동작한다.

결론.

• Oracle
  • 변경은 먼저 Buffer Cache에서 발생
  • 과거 버전 복원은 Undo
  • 복구 가능성 확보는 Redo
  • 영속 반영은 LGWR/DBWR가 역할 분담
  • 읽기 충돌은 consistent read, 쓰기 충돌은 row lock으로 처리
• PostgreSQL
  • 행 자체를 버전화
  • snapshot으로 보이는 tuple만 읽음
  • vacuum이 필수
• MySQL(InnoDB)
  • 현재 row + undo log + read view 조합
  • 일반 SELECT는 nonlocking consistent read
  • purge thread가 undo 정리