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

Transaction (트랜잭션)

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

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

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

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

 

트랜잭션 범위

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

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

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

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

 

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

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

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

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

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

---

트랜잭션 격리 수준

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

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

 

격리 수준을 이야기하면 항상 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는 데이터를 변경하는 트랜잭션은 변경 전 레코드를 언두 로그로 복사하고 변경한다.

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

DIRTY READ는 발생하지 않으며 커밋된 데이터만 다른 트랜잭션에서 조회할 수 있다.

 

 

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

아래는 계좌에 100만원이 있고 대출을 받는 상황이라 가정한다

1. 트랜잭션 1이 계좌 잔액을 조회한다.
2. 대출 승인 기준 금액인 100만원이 확인되어 심사를 기다린다.
3. 트랜잭션 2가 계좌에서 20만원을 출금하고 커밋한다.
4. 최종 승인 전 트랜잭션 1이 같은 계좌를 다시 조회하면 80만원이라는 다른 결과를 보게 된다.
5. 승인 기준 미달로 대출 승인 취소된다.

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

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

 

REPEATABLE READ

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

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

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

 

이 격리 수준에서 DIRTY READ와 NON REPEATABLE READ는 발생하지 않는다.

InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성에 대비해 데이터가 변경되기 전

레코드를 언두 공간에 백업해두고 실제 데이터를 변경한다.

이러한 변경 방식을 MVCC라고 한다.

REPEATABLE READ는 이 MVCC를 위해 언두 영억에 백업된 이전 데이터를 이용해

동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

 

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

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

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

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

 

READ COMMITTED와 REPEATABLE READ의 스냅샷 TRX_ID 활용 차이

READ COMMITTED는 매 SELECT마다 새로운 스냅샷을 생성한고 현재 언두 로그의 가장 최신의 데이터를 읽는다.

SELECT시점마다 새로운 스냅샷을 생성하고 현재 레코드의 트랜잭션 ID(TRX_ID)가 스냅샷에 있는지 확인한다.

있다면(아직 커밋 전) 언두 로그에서 가장 최신의 데이터를 읽는다.

없다면(이미 커밋됨) 현재 저장된 데이터를 읽는다.

즉, TRX_ID를 커밋 여부 확인용으로 사용해 매번 새로운 스냅샷으로 최신 커밋된 데이터 확인한다.

 

REPEATABLE READ는 첫 SELECT에서 스냅샷 생성, 이후 트랜잭션 끝까지 유지한다.

트랜잭션 ID(TRX_ID)를 기준으로 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 언두 로그 데이터를 읽는다.

조회 시 레코드의 TRX_ID가 자신의 TRX_ID보다 작은지 확인하고 TRX_ID보다 크다면 해당 레코드는 읽지 않는다.

조회 레코드의 TRX_ID가 자신의 TRX_ID보다 작다면 스냅샷에 있는지 확인한다.

스냅샷에 조회 레코드의 TRX_ID가 없다면 이미 커밋된 데이터이므로 해당 버전의 레코드를 읽는다.

만약 스냅샷에 조회 레코드의 TRX_ID가 있다면 커밋되지 않은 레코드이므로 해당 TRX_ID보다 더 이전의 TRX_ID를 읽는다.

 

 

 

즉, 트랜잭션 ID 비교와 동일한 스냅샷의 활성 트랜잭션 목록 확인에 사용해 트랜잭션 시작 시점 기준의 일관성 유지한다.

 

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

REPEATABLE READ 격리 수준에서는 NON REPEATABLE READ 문제는 방지 하지만

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

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

 

InnoDB는 팬텀 리드 문제를 MVCC의 구현 요소인 TRX_ID를 사용하여 방지한다.

위에서도 설명했지만 사용자 2의 TRX_ID는 사용자 1의 TRX_ID보다 작기 때문에 읽지 않는다.

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

 

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

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

 

주의할 점은 언두 로그를 활용 못하는 상황이다.

FOR UPDATE 같은 락을 명시적으로 사용하는 경우에는 언두 로그를 읽지 않고 항상 최신 데이터를 읽는다.

또한 이 상황에서도 TRX_ID를 부여하지 않는다.

아마 락은 해당 레코드를 수정하려는 의도를 나타내는 것이기 때문에

언두 로그를 읽게 되면 다른 트랜잭션의 변경사항을 놓칠 수 있기 때문이 아닐까라 생각이 든다.

이 상황에서 잠겨있는 레코드가 age = 20일 경우 age가 21인 레코드가 추가될 수 있어 PHANTOM READ가 발생할 수 있다.

InnoDB는 '넥스트 키 락'을 사용해 이 상황에서 발생하는 PHANTOM READ를 방지한다.

이 부분에 대해서는 아래 포스팅에서 설명한다.

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

 

정리하면, InnoDB의 REPEATABLE READ 격리 레벨은 대부분의 읽기 작업 상황에서

MVCC를 사용해 NON REPEATABLE READ, PHANTOM READ를 방지한다.

하지만 언두 로그를 활용 못하는 상황 처럼 완벽히 PHANTOM READ를 방지하기 위해 '넥스트 키 락'을 지원해 완벽히 방지한다.

 

SERIALIZABLE

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

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

SERIALIZABLE은 PHANTOM READ를 방지하지만

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

---

참고자료

• Real MySQL 8.0