Real MySQL 8.0 5장(2) - 트랜잭션의 격리수준

트랜잭션의 격리 수준(isolation level)이란?

여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회중인 데이터를 볼수있게 할지, 못보게 할지를 결정하는 것

 

격리수준의 종류와 3가지 문제

위 표에서 아래로 갈수록 격리 수준이 높아지고,

READ UNCOMMITED 의 경우 DIRTY READ 문제가 발생하기 때문에 일반적인 데이터베이스에서는 거의 사용하지 않는다.

SERIALIZABLE 의 경우도 동시 처리 성능이 낮기 때문에 동시성 처리가 중요한 데이터베이스에서는 거의 사용되지 않는다.

오라클 같은 DBMS에서는 주로 READ COMMITTED 수준을 많이 사용하며, MySQL에서는 REPEATABLE READ 수준을 많이 사용한다.

트랜잭션 격리수준을 테스트 하기 위해서는 AUTOCOMMIT을 OFF로 설정 해 두어야 테스트 할 수 있다.

SET autocommit=OFF;

1. READ UNCOMMITED : Dirty Read 문제 발생

각 트랜잭션에서의 변경 내용이 아직 COMMIT이나 ROLLBACK 되지 않은 상태에서도 다른 트랜잭션에서 조회가 가능한 격리수준.

Dirty Read 문제 란?

이렇게 어떤 트랜잭션에서 처리한 작업이 아직 완료 되지 않았는데도 다른 트랜잭션에서 볼수 있는 현상을 더티 리드(Dirty Read)라고 한다.

Dirty Read 문제가 발생하기 때문에 조회 할때마다 조회 데이터가 실제 DB에 있는 데이터와 결과가 다르거나 사라지는 문제가 생길 수 있다.

READ UNCOMMITED의 경우 RDBMS 표준에서 트랜잭션의 격리수준으로 인정하지 않을 정도로 정합성에 많은 문제가 있다.

 

2. READ COMMITED : NON-REPEATABLE READ 정합성 문제 발생

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

어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

새로운 변경사항이 들어오면, 이전 데이터는 언두로그에 기록되고 변경사항이 커밋 되기 전 까지 다른 트랜잭션에서의 요청에 언두로그에 있는 이전 데이터를 데이터를 반환한다.

NON-REPEATABLE READ 정합성 문제 란?

하나의 트랜잭션 안에서 여러번의 똑같은 SELECT 쿼리로 데이터를 요청했을 때, 각 SELECT 쿼리는 항상 같은 결과를 가져와야하는데, 이렇지 못한 상황을 NON-REPEATABLE READ 문제라고 한다.

중간에 다른 트랜잭션에서 커밋한 데이터 때문에 한 트랜잭션 안에서 같은 SELECT문의 실행 결과가 달라지는 문제 발생

일반적인 웹 프로그램에서는 크게 문제가 되지 않을 수 있지만, 금전적인 문제를 다루는 경우 문제가 될 수 있다. 오늘 입금된 금액의 총합을 조회하는 상황에서 NON-REPEATABLE READ 문제가 발생 할 경우, 총합을 계산하는 SELECT 쿼리가 실행 될 때 마다 다른 결과를 가져올 수 있기 때문이다.

cf. READ COMMITED 격리 수준에서 트랜잭션 없이 실행되는 SELECT VS 트랜잭션 내에서 실행되는 SELECT 차이

READ COMMITED 격리 수준에서는 SELECT 문을 트랜잭션 내에서 사용하던, 없이 실행하던 차이가 없다. 하지만 아래 나올 REPEATABLE READ 격리 수준같은 경우, 트랜잭션 내에서만 SELECT 문이 같은 결과를 보장한다

3. REPEATABLE READ

 

InnoDB에서 기본으로 사용되는 격리 수준

트랜잭션 Id 를 기준으로 자신보다 이후에 발생한 트랜잭션에서의 변경사항은 읽지 않는다.

InnoDB에서는 일반적인 SELECT 상황에서는 Phantom Read 문제가 발생하지 않는다.

사용자 B의 트랜잭션이 실행되는 도중 사용자 A의 트랜잭션에서 데이터의 변경이 일어났다. REPEATABLE READ에서는 한 트랜잭션 내에서의 같은 SELECT는 항상 같은 결과를 내야 하므로, 사용자 B의 트랜잭션 이후에 BEGIN 된 사용자 A의 COMMIT내용을 읽지 않고, 언두로그에 기록된 데이터를 대신 읽는다.

이때 이후에 시작된 변경사항인지 판별하기 위해 트랜잭션 ID(순차적으로 증가하는 값)를 사용한다.

PHANTOM READ 문제

SELECT … FOR UPDATE 또는 SELECT … FOR SHARE 문은 언두 레코드에 잠금을 걸 수 없다는 특성이 있다. 사용 시, 다른 트랜잭션에서 수행한 변경 작업에 영향을 받아 Phantom Read 문제가 발생 할 수 있다.

하지만 일반적인 SELECT 상황에서는 자신 이후에 발생된 트랜잭션에서의 데이터는 읽지 않고 언두 로그에서 데이터를 읽을 수 있기 때문에 문제가 되지 않는다.

4. SERIALIZABLE

InnoDB에서의 순수한 SELECT 읽기 작업의 경우 잠금이 필요없는 일관된 읽기를 지원하기 때문에 아무런 레코드 잠금도 없이 실행된다.

하지만 트랜잭션의 격리수준을 SERIALIZABLE로 설정하게 되면 읽기 작업도 잠그게 되고, 다른 트랜잭션에서 읽기 작업을 수행하기 위해서는 다른 트랜잭션의 작업이 완료 되어 잠금을 반환 할때까지 기다려야한다.

PHANTOM READ 문제가 발생하지 않는다는 장점이 있지만, InnoDB에서는 레코드 기반의 갭락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ 문제가 발생하지 않기 때문에 굳이 동시처리 성능이 떨어지는 SERIALIZABLE 로 격리 수준을 설정 할 필요성이 없다.