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도 MVCC를 이용해 COMMIT되기 전의 데이터를 보여주지만

언두 영역에 백업된 레코드의 여러 버전 중 몇 번째 이전 버전까지 찾아 들어가야하는 점이 다르다.

• READ COMMITTED : 매 SELECT마다 현재 커밋된 가장 최신의 데이터를 읽음
• REPEATABLE READ : 트랜잭션 ID(TRX_ID)를 기준으로 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 커밋된 데이터를 읽음

이 차이로 인해 REPEATABLE READ는 NON REPEATABLE READ 문제를 방지할 수 있다.

어떤 방식으로 방지가 되는지 그림으로 이해해보자.

 

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

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

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

 

하지만 InnoDB는 해당 격리 레벨에서 트랜잭션 첫 조회시에 한 번 스냅샷을 저장하고

이후에 다른 트랜잭션에서 데이터가 변경되거나 추가되었다하더라도

재 조회시엔 스냅샷이 생성된 시점 이후에 대한 데이터는 읽지 않아 데이터는 추가되지만 팬텀 리드가 방지된다.

데이터 추가도 막기 위해서는 넥스트 키 락이 적용되어야 하는데 이 부분에 대해서는 아래 포스팅에서 설명한다.

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

 

SERIALIZABLE

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

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

SERIALIZABLE은 PHANTOM READ를 방지하지만

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

---

참고자료

• Real MySQL 8.0