MySQL의 트랜잭션과 격리 수준 이해하기

Transaction (트랜잭션)

트랜잭션은 데이터의 정합성을 보장해주는 RDBMS에 아주 필수적인 기술이다.

InnoDB 스토리지 엔진은 트랜잭션을 보장하고 레코드 단위의 잠금을 제공하지만

MyISAM, MEMORY 스토리지 엔진은 이 트랜잭션을 지원하지 않고 테이블 단위의 잠금을 제공한다.

이 이유로 InnoDB 스토리지 엔진이 가장 많이 사용되는 것이라 추측된다.

 

트랜잭션 범위

트랜잭션은 범위 또한 아주 중요하다.

범위가 크다면 범위 내의 작업들의 정합성이 지켜진다는 점이 좋지만

정합성이 중요하지 않은 작업이 끼어있다면 오히려 커넥션을 점유하고 있는 시간이 길어지는데

이는 여유 커넥션의 개수가 줄어진 다는 것을 의미하고 TPS 수치 또한 떨어질 것이다.

 

특히 어떤 작업들이 트랜잭션 처리에 좋지 않을까

• 정합성이 중요하지 않은 작업 (약간의 오차는 허용되는 로직)
• 네트워크를 통해 원격 서버와 통신하는 작업 (장애 전파 위험과 네트워크 통신 연결로 인한 오버헤드)
• 실패해도 크게 문제되지 않는 작업
• 읽기 전용 작업(readOnly = true)의 내부 로직의 처리 시간이 긴 경우 ⇒ db 작업에 대해서만 트랜잭션을 사용하는게 오히려 나음

이 말고도 더 다양한 상황이 있을 수 있다.

구현하고자하는 서비스의 성격에 따라 크게 달라질 수 있으니 항상 고민하고

트랜잭션의 범위가 불필요하게 크다면 DMS 서버의 부하를 줄 수 있어 항상 인지할 필요가 있다

---

트랜잭션 격리 수준이란? 그리고 3가지 부정합 문제

격리 수준이란 여러 트랜잭션이 동시에 처리될 때

특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 테이블을 볼 수 있게 허용할지 말지를 결정하는 것이다.

 

격리 수준을 이야기하면 항상 3가지 부정합의 문제가 언급된다.

격리 수준의 레벨에 따라 부정합 발생 여부를 표로 나타내면 다음과 같다.

	DIRTY READ	NON REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	O	O	O
READ COMMITTED	X	O	O
REPEATABLE READ	X	X	O (InnoDB에선 X)
SERIALIZABLE	X	X	X

• DIRTY READ : 한 트랜잭션이 아직 커밋되지 않은 다른 트랜잭션의 데이터를 읽는 현상
• NON REPEATABLE READ : 한 트랜잭션 내에서 같은 데이터를 두 번 읽었을 때 그 결과가 다른 현상
• PHANTOM READ : 한 트랜잭션 내에서 같은 쿼리를 실행했을 때 이전에 없던 레코드가 나타나거나 있던 레코드가 사라지는 현상

 

이제 각각의 격리 레벨에 대해 알아보자

---

READ UNCOMMITTED

가장 낮은 수준의 격리 수준으로 다른 트랜잭션이 커밋하지 않은 데이터를 읽을 수 있다.

데이터 정합성은 매우 낮지만, 동시 처리 성능은 가장 높다.

DIRTY READ 외에도 3가지 부정합 문제가 전부 나타날 수 있어 정합성에 문제가 아주 많은 격리 수준이다.

---

READ COMMITTED

오라클 DBMS에서 기본으로 사용되는 격리 수준으로 온라인 서비스에서 가장 많이 선택된다고 한다.

 

READ COMMITTED부터는 MVCC를 사용하기 시작한다.

데이터를 변경하는 트랜잭션은 변경 전 레코드를 언두 로그로 복사하고 변경한다.

SELECT 시점마다 새로운 스냅샷을 생성해 활성화된 트랜잭션이 있는지 없는지 여부를 확인하고

다른 트랜잭션은 선행 트랜잭션이 커밋하기 전 까지는 언두 로그를 보고 변경 전인 레코드를 읽어

커밋된 데이터만 다른 트랜잭션에서 조회할 수 있다.

 

하지만 NON REPEATABLE READ가 문제가 된다.

다음의 경우를 보자.

 

한 트랜잭션 내에서 같은 데이터를 두 번 읽었을 때 그 결과가 달라졌다.

이는 하나의 트랜잭션 내에서 동일 데이터를 여러 번 읽는 작업이 금전적인 처리와 연결되면 큰 문제가 될 수 있다.

---

REPEATABLE READ

InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준으로

바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

(바이너리 로그에 기록되는 변경사항의 정확성과 복제의 신뢰성을 보장을 위해)

 

READ COMMITTED와 REPEATABLE READ는 MVCC를 이용해 일관된 읽기를 제공하지만

스냅샷 생성 주기와 언두 로그에 저장된 읽어야 할 버전을 결정하는 기준이 다르다.

여기서 말하는 스냅샷이란 활성 트랜잭션 목록을 저장하고 있다.

• READ COMMITTED : 매 SELECT마다 새로운 스냅샷을 생성한고 현재 언두 로그의 가장 최신의 데이터를 읽음
• REPEATABLE READ : 트랜잭션 첫 SELECT 시점의 스냅샷을 트랜잭션 끝까지 사용, 트랜잭션 ID(TRX_ID)를 기준으로 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 언두 로그 데이터를 읽음

REPEATABLE READ는 다음과 같이 첫 조회 시점을 기준으로 한 번만 스냅샷을 생성해 스냅샷의 일관성 유지한다.

 

 

NON REPEATABLE READ 문제 방지 상황 구체적으로 알아보기

자신의 TRX_ID보다 큰 레코드를 읽지 않는 건 READ COMMITED도 동일 했다.

그럼 어떻게 NON REPEATABLE READ 문제를 방지하는지 좀더 자세히 알아보자.

스냅샷은 처음에 조회 시점에 생성된 스냅샷을 유지하여 활성화된 트랜잭션이 있는지 여부를 확인하고 레코드를 읽기 시작한다.

이러한 차이로 REPEATABLE READ는 일관된 읽기를 보장하여 NON REPEATABLE READ 문제를 방지할 수 있다.

 

InnoDB의 REPEATABLE READ 격리 수준에서 PHANTOM READ 문제의 해결 방법

하지만 다음과 같이 트랜잭션 진행 중 새로운 레코드가 추가되는 상황에서 PHANTOM READ가 발생할 수 있다.

이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 안보였던 레코드가 갑자기 보이는 현상을 PHANTOM READ라고 한다.

InnoDB는 팬텀 리드 문제를 '넥스트 키 락'을 사용해 완벽히 방지한다고 한다.

 

근데 우선 SELECT - INSERT - SELECT 상황에서는 스냅샷과 TRX_ID를 사용하여 방지가 된다.

위에서도 설명했지만 트랜잭션 2의 TRX_ID는 트랜잭션 1의 TRX_ID보다 작기 때문에 읽지 않는다.

따라서 이후에 다른 트랜잭션에서 데이터가 변경되거나 추가되었다하더라도 PHANTOM READ를 방지할 수 있다.

 

참고로 TRX_ID는 트랜잭션 진행 도중 데이터 변경(INSERT, UPDATE, DELETE) 상황에서도 반복적인 읽기가 가능하도록 하는 것이다.

하지만 읽기 전용 트랜잭션(readOnly)의 경우에는 다른 트랜잭션과의 충돌이 거의 없기 때문에 TRX_ID를 부여하지 않는다.

 

넥스트 키 락으로 어떻게 팬텀 리드 문제를 완벽하게 방지하나?

그럼 '넥스트 키 락'으로 팬텀 리드 문제를 완벽하게 방지한다는 것은 무슨 말일까?

위 처럼 사용자 1이 중간에 INSERT가 커밋 된 이후에 SELECT를 하는 경우에는 문제가 되진 않지만

INSERT가 커밋 된 이후에 UPDATE를 수행할 경우 문제가 된다.

 

넥스트 키 락이 없다면 어떤 일이 발생하는지 한번 알아보자.

(아래는 age에 인덱스가 있다고 가정한다.)

 

사용자 1의 UPDATE 커밋 결과는 다음과 같아진다.

• age가 20인 id 2, 3 레코드가 ADULT로 변경됨 !
• TRX_ID도 현재 TRX_ID로 변경됨 !
• 사용자 1은 조회 시 추가하지 않은 데이터가 보이게 됨 !
• 사용자 2은 조회 시 데이터를 추가하자마자 자신이 추가한 데이터와 전혀 다른 데이터가 됨 !

이것을 방지해주는 것이 바로 '넥스트 키 락'이다.

넥스트 키 락은 유니크하지 않은 인덱스에 대해 잠금을 사용할 경우 InnoDB가 자동으로 넥스트 키 락을 걸게 된다.

(참고로 유니크한 index라면 이런 문제가 발생하지 않기 때문에 넥스트 키 락을 자동으로 적용하지 않는다.)

이렇게 '넥스트 키 락'을 사용해 다음 레코드 까지의 GAP을 잠금으로써 PHANTOM READ를 완벽하게 방지한다.

 

왜 꼭 Gap을 사용하는 걸까 ?

이유를 추측하자면 레코드는 존재하지 않는 행에 대해선 잠금을 걸지 못하기 때문에 

동일한 조건의 행이 추가됨을 방지하기 위해 해당 조건의 행과 다음 행 사이의 Gap을 잠그는 것이라고 보인다.

 

이 넥스트 키 락에 대해서는 아래 포스팅에서 설명한다.

2024.12.01 - [◼ DB] - MySQL의 Lock 종류와 동작 방식을 파헤쳐 보자

 

SERIALIZABLE

가장 단순한 격리 수준이면서도 동시에 가장 엄격한 격리 수준이다.

SERIALIZABLE 격리 수준이 설정된 트랜잭션은 읽기 잠금이 되어 동시에 다른 트랜잭션이 접근해 변경할 수 없다.

SERIALIZABLE은 PHANTOM READ를 방지하지만

InnoDB 스토리지 엔진에서는 REPEATABLE READ에서도 넥스트 키 락으로 이 문제를 방지하기 때문에 필요성이 보이진 않는다.

---

참고자료

• Real MySQL 8.0