[Database] MySQL과 Oracle, 트랜잭션과 잠금은 어떻게 다를까?

<Real MySQL 8.0 - 트랜잭션과 잠금> 파트를 읽고,

MySQL과 Oracle 아키텍처 구조가 완전히 다른데 트랜잭션과 잠금을 처리하는 내부 구현은 어떨까? 라는 궁금증이 생겼다.

 

MySQL은 스토리지 엔진에 따라 동작이 달라지므로 이 글에서는 InnoDB의 경우로 한정하여, 두 DB의 공통점과 차이점을 트랜잭션과 잠금 관점에서 정리해보았다.

 

공통점

MVCC 기반 읽기 일관성

InnoDB와 Oracle 모두 "읽기 작업은 쓰기 작업을 막지 않고, 쓰기 작업은 읽기 작업을 막지 않는다" 는 동시성 대원칙을 지키기 위해 `MVCC(Multi Version Concurrency Control)`를 사용한다.

 

한 트랜잭션이 데이터를 수정하고 있더라도, 다른 트랜잭션은 변경 이전 버전의 데이터를 보관하는 Undo 영역(MySQL: Undo Log / Oracle: Undo Segment)을 바라보게 만든다. 즉, 실제 데이터를 잠그고 대기시키는 대신, "과거 시점의 사본"을 별도로 유지해서 읽기와 쓰기가 부딪히지 않게 한다.

 

 

행 단위 잠금

테이블 전체가 아닌, 개별 행 단위로 잠금을 걸 수 있어서 높은 동시성을 지원한다.

 

 

표준 격리 수준 용어 공유

ANSI SQL 표준에서 정의한 트랜잭션 격리 수준 용어(READ COMMITTED, REPEATABLE READ, SERIALIZABLE 등)를 공통으로 사용한다. (단, 동일한 이름을 쓴다고 해서 내부 동작까지 같지는 않다.)

 

 

차이점

기본 격리 수준 

• Oracle: READ COMMITTED
• MySQL: REPEATABLE READ

Oracle은 커밋된 데이터만 읽도록 허용하며, 한 트랜잭션 안에서 쿼리를 다시 날렸을 때 다른 트랜잭션이 커밋한 내용이 반영되어 데이터가 달라지는 것(Non-Repeatable Read)을 허용한다.

 

MySQL은 한 트랜잭션이 시작되면, 트랜잭션이 끝날 때까지 시작할 때의 데이터를 그대로 유지하겠다는 일관성을 기본으로 제공한다.

 

이렇게 기본 격리 수준이 다른 이유는 무엇일까?

시대적 배경과 철학의 차이 때문이다.

격리 수준을 REPEATABLE READ로 올리면 데이터 일관성은 높아지지만, 리소스 낭비는 심해지며 `ORA-01555` 위험도 늘어난다. 대규모 엔터프라이즈 환경을 타겟으로 발전한 Oracle은 NON-REPEATABLE READ 정도는 대부분의 OLTP 업무로직에서 감수할 만한 수준으로 판단하여, 성능을 끌어올릴 수 있는 READ COMMITTED를 채택했다.

MySQL은 오래전부터 복제를 지원해왔는데, 과거 MySQL의 복제 방식(SBR: Statement-Based Replication)은 REPEATABLE READ 없이 동작하면 원본(master)과 복제본(replica) 사이에 데이터 불일치가 생길 수 있었다. 따라서 트랜잭션 중에 데이터가 절대 끼어들지 못하게 막는 REPEATABLE READ를 기본값으로 설정해야만 했고, 그 전통이 지금까지 이어온 것이다.(현재는 로우 기반 복제를 주로 써서 안전하지만, 하위 호환성을 위해 유지 중이다.)

 

 

읽기 일관성 구현 방식

• Oracle: 쿼리가 시작된 순간의 시스템 시간(SCN)을 기준으로 항상 일관된 스냅샷을 제공한다.
• MySQL: `REPEATABLE READ` 안에서 트랜잭션 시작 시점에 생성한 `Read View`를 통해 일관된 읽기를 구현한다.
  • Phantom Read 방지를 위해 Gap Lock을 추가로 사용한다.

 

Oracle은 격리 수준과 무관하게 모든 SELECT가 반드시 Consistent Read 메커니즘을 거친다.

SELECT 문이 시작되면 언제나 데이터 블록 헤더의 트랜잭션 목록(ITL) 슬롯을 먼저 확인한다. 만약 특정 블록을 어떤 트랜잭션이 수정 중인데 아직 커밋하지 않았다면, Oracle은 그 블록을 확인하지도 않고 즉시 Undo Segment로 가서 예전 데이터 블록을 만들어 낸다.

즉, 읽기 요청이 들어왔을 때, 커밋되지 않은 최신본을 볼 수 있는 방법 자체가 아키텍처 상 존재하지 않기 때문에, `Dirty Read`가 구조적으로 불가능하다.

 

 

Gap Lock 유무

MySQL은 Record Lock과 Gap Lock을 합친 Next-Key Lock을 사용한다. 

Oracle에는 이런 개념이 없고, 대신 SERIALZABLE 격리 수준에서 트랜잭션 시작 시점의 스냅샷을 끝까지 유지하는 방식으로 Phantom Read를 막는다.

 

추가 내용: MySQL의 MVCC vs. Oracle의 MVCC

1️⃣ MySQL(InnoDB) : 레코드 단위의 링크드 리스트 체인

테이블의 실제 데이터 레코드 뒤에 숨겨진 포인터 변수가 붙어 있고, 이것이 과거 이력(Undo Log)을 줄줄이 사탕처럼 엮고 있는 구조

[ 실제 테이블 블록 (Cluster Index) ]
+----+-----------+------------+---------------+
| ID |  Name     | DB_TRX_ID  |  DB_ROLL_PTR  |  <- 현재 테이블 상태 (가장 최신)
+----+-----------+------------+---------------+
|  1 |  이순신    |    tx 200  |       * ------+  
+----+-----------+------------+---------------+       |
                                                      | (롤백 포인터가 과거를 가리킴)
                                                      v
                                        [ Undo Log 영역 (Undo 버퍼/파일) ]
                                        +------------+---------------+-------------+
                                        | 옛날 Name   |  DB_TRX_ID    | DB_ROLL_PTR |
                                        +------------+---------------+-------------+
                                        |  홍길동      |     tx 100    |      * -----+
                                        +------------+---------------+-------------+      |
                                                                                          v
                                                                                   (최초 Insert 시점)