[MySQL] 트랜잭션 격리 수준(isolation level)

경쟁상태 (Race Condition)

여러 클라이언트가 같은 데이터에 동시 접근 시 생기는 문제

트랜잭션의 격리성(Isolation) 이란?

여러 Transaction은 동시에 수행될 수 있다. 이때 각 Transaction은 다른 Transaction의 연산 작업이 끼어들지 못하도록 보장하여 각각의 트랜젝션은 서로 간섭 없이 독립적으로 수행되어야 한다.
현재 수행 중인 트랜잭션이 완료될 때까지 다른 트랜잭션들이 중간 연산 결과에 접근할 수 없음을 의미한다.
따라서 동시에 수행되는 transaction이 동일한 data를 가지고 충돌하지 않도록 제어해줘야 한다. 이를 동시성제어(concurrency control) 라고한다.
즉, 격리성을 보장하기 위해서는 여러 트랜잭션이 동시에 수행되더라도 마치 순서대로 하나씩 수행되는 것처럼 정확하고 일관된 결과를 얻을 수 있도록 제어하는 기능이 필요하다.

트랜젝션은 기본적으로 원자성, 일관성 그리고 지속성을 보장해준다. 하지만 격리성의 경우에는 완전히 보장하려면 각각의 트랜젝션을 순서대로 처리해야한다.

하지만 이처럼 모든 트랜잭션을 순서대로 처리하는 것은 동시성 처리 성능을 매우 나쁘게 한다. 따라서 격리성의 수준을 여러 단계로 나눠놓았다.

트랜잭션 격리 수준(isolation level)

트랜잭션 격리수준(isolation level)이란 동시에 여러 트랜잭션이 처리될 때, 트랜잭션끼리 얼마나 서로 고립되어 있는지를 나타내는 것이다.

즉, 간단하게 말해 특정 트랜잭션이 다른 트랜잭션에 변경한 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다.

격리수준은 크게 아래의 4개로 나뉜다.

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

아래로 내려갈수록 트랜잭션간 고립 정도가 높아지며, 성능이 떨어지는 것이 일반적이다.

일반적인 온라인 서비스에서는 READ COMMITTED나 REPEATABLE READ 중 하나를 사용한다.
(oracle = READ COMMITTED, mysql = REPEATABLE READ)

트랜잭션이 작업을 수행하다 보면 오손 읽기, 반복불가능 읽기, 유령데이터 읽기와 같은 문제를 직면할 수 있다.

그래서 Lock보다는 완화된 방법으로 트랜잭션을 동시에 실행시키면서, 발생하는 문제를 해결하기 위해 DBMS가 제공하는 명령어가 바로 트랜잭션 고립 수준 명령어(Transaction Isolation Level Instruction)이다.

READ UNCOMMITTED - 사실상 사용 X

READ UNCOMMITTED는 커밋하지 않은 데이터 조차도 접근할 수 있는 격리 수준이다. READ UNCOMMITTED에서는 다른 트랜잭션의 작업이 커밋 또는 롤백되지 않아도 즉시 보이게 된다.

고립 수준이 Level 0으로 가장 낮은 명령어로, 자신의 데이터에 아무런 공유락을 걸지 않는다.
즉, 다른 트랜잭션에서 커밋하지 않은 데이터를 읽을 수 있다.

READ UNCOMMITTED는 자신의 데이터에 아무런 공유락도 걸지 않지만 배타락은 데이터의 갱신손실 문제 때문에 걸어주어야 한다. 또한 다른 트랜잭션에 공유락과 배타락이 걸린 데이터를 대기하지 않고 읽는다.

예를 들어 사용자 A의 트랜잭션에서 INSERT를 통해 데이터를 추가했다고 하자. 아직 커밋 또는 롤백이 되지 않은 상태임에도 불구하고 READ UNCOMMITTED는 변경된 데이터에 접근할 수 있다. 이를 그림으로 표현하면 다음과 같다.

이렇듯 어떤 트랜잭션의 작업이 완료되지 않았는데도, 다른 트랜잭션에서 볼 수 있는 부정합 문제를 Dirty Read(오손 읽기)라고 한다. Dirty Read는 데이터가 조회되었다가 사라지는 현상을 초래하므로 시스템에 상당한 혼란을 주게 된다. 만약 위의 경우에 사용자 A가 커밋이 아닌 롤백을 수행한다면 어떻게 될까?

사용자 B의 트랜잭션은 id = 51인 데이터를 계속 처리하고 있을 텐데, 다시 데이터를 조회하니 결과가 존재하지 않는 상황이 생긴다. 이러한 Dirty Read 상황은 시스템에 상당한 버그를 초래할 것이다

그래서 READ UNCOMMITTED는 RDBMS 표준에서 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다. 따라서 MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용해야 한다.

오손읽기(Dirty Read)

읽기 작업을 하는 트랜잭션 1이 쓰기 작업을 하는 트랜잭션2가 작업한 중간 데이터를 읽기 때문에 발생하는 문제
작업중인 트랜잭션 2가 작업을 Rollback한 경우 트랜잭션 1은 무효가 된 데이터를 읽게 되고 잘못된 결과를 도출한다.

아래의 그림과 같이 T1이 T2가 Rollback되기 전의 데이터 21 읽었고, T2가 Rollback이 되면 T1이 의미가 없는 값을 갖게 되므로 문제가 발생한다. 그리고 이러한 무효가 된 데이터를 읽게되어 발생하는 문제를 현황 파악 오류(Dirty Read)라고 한다.

READ COMMITTED

어떤 트랜잭션의 변경 내용이 COMMIT 되어야만 다른 트랜잭션에서 조회할 수 있다. 따라서 Dirty Read는 발생하지 않는다.

하지만 READ COMMITTED는 REPEATABLE READ에서 발생하는 Phantom Read에 더해 Non-Repeatable Read(반복 읽기 불가능) 문제까지 발생한다.

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

고립 수준이 Level 1인 명령어로, Dirty Page의 참조를 피하기 위해 자신의 데이터를 읽는 동안 공유락을 걸지만 트랜잭션이 끝나기 전에라도 해지 가능하다.

예를 들어 사용자 A가 트랜잭션을 시작하여 어떤 데이터를 변경하였고, 아직 커밋은 하지 않은 상태라고 하자. 그러면 테이블은 먼저 갱신되고, 언두 로그로 변경 전의 데이터가 백업되는데, 이를 그림으로 표현하면 다음과 같다.

이때 사용자 B가 데이터를 조회하려고 하면, READ COMMITTED에서는 커밋된 데이터만 조회할 수 있으므로, REPEATABLE READ와 마찬가지로 언두 로그에서 변경 전의 데이터를 찾아서 반환하게 된다. 최종적으로 사용자 A가 트랜잭션을 커밋하면 그때부터 다른 트랜잭션에서도 새롭게 변경된 값을 참조할 수 있는 것이다.

하지만 READ COMMITTED는 Non-Repeatable Read(반복 읽기 불가능) 문제가 발생할 수 있다.

예를 들어 사용자 B가 트랜잭션을 시작하고 name = “Minkyu”인 레코드를 조회했다고 하자. 해당 조건을 만족하는 레코드는 아직 존재하지 않으므로 아무 것도 반환되지 않는다.

그러다가 사용자 A가 UPDATE 문을 수행하여 해당 조건을 만족하는 레코드가 생겼다고 하자. 사용자 A의 작업은 커밋까지 완료된 상태이다. 이때 사용자 B가 다시 동일한 조건으로 레코드를 조회하면 어떻게 될까? READ COMMITTED 는 커밋된 데이터는 조회할 수 있도록 허용하므로 결과가 나오게 된다.

READ COMMITTED에서 반복 읽기를 수행하면 다른 트랜잭션의 커밋 여부에 따라 조회 결과가 달라질 수 있다. 그리고 이러한 데이터 부정합 문제를 Non-Repeatable Read(반복 읽기 불가능)라고 한다.

Non-Repeatable Read는 일반적인 경우에는 크게 문제가 되지 않지만, 하나의 트랜잭션에서 동일한 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 생길 수 있다.

예를 들어 어떤 트랜잭션에서는 오늘 입금된 총 합을 계산하고 있는데, 다른 트랜잭션에서 계속해서 입금 내역을 커밋하는 상황이라고 하자. 그러면 READ COMMITTED에서는 같은 트랜잭션일지라도 조회할 때마다 입금된 내역이 달라지므로 문제가 생길 수 있는 것이다. 따라서 격리 수준이 어떻게 동작하는지, 그리고 격리 수준에 따라 어떠한 결과가 나오는지 예측할 수 있어야 한다.

READ COMMITTED 수준에서는 애초에 커밋된 데이터만 읽을 수 있기 때문에 트랜잭션 내에서 실행되는 SELECT와 트랜잭션 밖에서 실행되는 SELECT의 차이가 별로 없다.

반복불가능 읽기(Non-repeatable Read)

트랜잭션 1이 데이터를 읽고 트랜잭션 2가 데이터를 쓰고(Update) 트랜잭션 1이 다시 한번 데이터를 읽을 때 생기는 문제
트랜잭션 1이 읽기 작업을 다시 한 번 반복할 경우 이전의 결과와 다른 결과가 나오는 현상

아래의 그림과 같이 T1이 먼저 값을 읽었고 T2가 값을 갱신(Update)하였다고 하자. 그리고 T1이 다시 값을 읽으려고 하면 바로 전에 읽은 값이 달라지는데 이를 Non-Repeatable Read라고 한다.

REPEATABLE READ

특정 행을 조회시 항상 같은 데이터를 응답하는 것을 보장하는 격리 수준이다. 즉, 트랜잭션이 시작되기 전에 커밋된 내용에 대해서만 조회할 수 있다. 한번 조회한 데이터는 트랜젝션 내에서 다시 조회해도 같은 데이터가 나오는게 보장된다.

고립 수준이 Level 2인 명령어로, 자신의 데이터에 설정된 공유락과 배타락을 트랜잭션이 종료될 때까지 유지하여 다른 트랜잭션이 자신의 데이터를 갱신(Update)할 수 없도록 한다.

일반적인 RDBMS는 변경 전의 레코드를 언두 공간에 백업해둔다. 그러면 변경 전/후 데이터가 모두 존재하므로, 동일한 레코드에 대해 여러 버전의 데이터가 존재한다고 하여 이를 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어)라고 부른다. MVCC를 통해 트랜잭션이 롤백된 경우에 데이터를 복원할 수 있을 뿐만 아니라, 서로 다른 트랜잭션 간에 접근할 수 있는 데이터를 세밀하게 제어할 수 있다. 각각의 트랜잭션은 순차 증가하는 고유한 트랜잭션 번호가 존재하며, 백업 레코드에는 어느 트랜잭션에 의해 백업되었는지 트랜잭션 번호를 함께 저장한다. 그리고 해당 데이터가 불필요해진다고 판단하는 시점에 주기적으로 백그라운드 쓰레드를 통해 삭제한다.

REPEATABLE READ는 MVCC를 이용해 한 트랜잭션 내에서 동일한 결과를 보장하지만, 새로운 레코드가 추가되는 경우에 부정합이 생길 수 있다. 이러한 REPEATABLE READ의 동작 방식을 자세히 살펴보도록 하자.

예를 들어 트랜잭션을 시작하고, id = 50인 레코드를 조회하면 1건 조회되는 상황이라고 하자. 아직 트랜잭션은 종료되지 않았다. 참고로 트랜잭션을 시작한 SELECT와 그렇지 않은 SELECT의 차이는 뒤에서 다시 살펴보도록 하겠다.

그리고 이때 다른 사용자 A의 트랜잭션에서 id=50인 레코드를 갱신하는 상황이라고 하자. 그러면 MVCC를 통해 기존 데이터는 변경되지만, 백업된 데이터가 언두 로그에 남게 된다.

이전에 사용자 B가 데이터를 조회했던 트랜잭션은 아직 종료되지 않은 상황에서, 사용자 B가 다시 한번 동일한 SELECT 문을 실행하면 어떻게 될까? 그 결과는 다음과 같다.

사용자 B의 트랜잭션은(T-ID = 10) 사용자 A의 트랜잭션(T-ID = 12)이 시작하기 전에 이미 시작된 상태다.

이때 REPEATABLE READ는 트랜잭션 번호를 참고하여 자신보다 먼저 실행된 트랜잭션의 데이터만을 조회한다. 만약 테이블에 자신보다 이후에 실행된 트랜잭션의 데이터가 존재한다면 언두 로그를 참고해서 데이터를 조회한다.

따라서 사용자 A의 트랜잭션이 시작되고 커밋까지 되었지만, 해당 트랜잭션(T-ID = 12)는 현재 트랜잭션(T-ID = 10)보다 나중에 실행되었기 때문에 조회 결과로 기존과 동일한 데이터를 얻게 된다. 즉, REPEATABLE READ는 어떤 트랜잭션이 읽은 데이터를 다른 트랜잭션이 수정하더라도 동일한 결과를 반환할 것을 보장해준다.

앞서 설명하였듯 REPEATABLE READ는 새로운 레코드의 추가까지는 막지 않는다고 하였다. 따라서 SELECT로 조회한 경우 트랜잭션이 끝나기 전에 다른 트랜잭션에 의해 추가된 레코드가 발견될 수 있는데, 이를 유령 읽기(Phantom Read)라고 한다. 하지만 MVCC 덕분에 일반적인 조회에서 유령 읽기(Phantom Read)는 발생하지 않는다. 왜냐하면 자신보다 나중에 실행된 트랜잭션이 추가한 레코드는 무시하면 되기 때문이다. 이러한 상황을 그림으로 표현하면 다음과 같다.

그렇다면 언제 유령 읽기가 발생하는 것일까?

바로 잠금이 사용되는 경우이다. MySQL은 다른 RDBMS와 다르게 특수한 갭 락이 존재하기 때문에, 동작이 다른 부분이 있으므로 일반적인 RDBMS 경우부터 살펴보도록 하자.

마찬가지로 사용자B가 먼저 데이터를 조회하는데, 이번에는 SELECT … FOR UPDATE를 이용해 쓰기 잠금을 걸었다. 여기서 SELECT … FOR UPDATE 구문은 베타적 잠금(비관적 잠금, 쓰기 잠금)을 거는 것이다. 읽기 잠금을 걸려면 LOCK IN SHARE MODE 구문을 사용해야 한다. 락은 트랜잭션이 커밋 또는 롤백될 때 해제된다.

그리고 사용자 A가 새로운 데이터를 INSERT하는 상황이라고 하자. 일반적인 DBMS에서는 갭락이 존재하지 않으므로 id = 50인 레코드만 잠금이 걸린 상태이고, 사용자 A의 요청은 잠금 없이 즉시 실행된다.

이때 사용자 B가 동일한 쓰기 잠금 쿼리로 다시 한번 데이터를 조회하면, 이번에는 2건의 데이터가 조회된다. 동일한 트랜잭션 내에서도 새로운 레코드가 추가되는 경우에 조회 결과가 달라지는데, 이렇듯 다른 트랜젹션에서 수행한 작업에 의해 레코드가 안보였다 보였다 하는 현상을 Phantom Read(유령 읽기)라고 한다. 이는 다른 트랜잭션에서 새로운 레코드를 추가하거나 삭제하는 경우 발생할 수 있다.

이 경우에도 MVCC를 통해 해결될 것 같지만, 두 번째 실행되는 SELECT ~ FOR UPDATE 때문에 그럴 수 없다.

MVCC에서는 데이터를 먼저 테이블에 반영하고, 언두 로그에 백업한다. 즉, SELECT ~ FOR UPDATE로 잠금을 걸어도 테이블에는 반영이 되고, 언두 로그에는 다른 트랜잭션에 의한 데이터가 계속해서 쌓이는 것이다. 만약 먼저 시작된 트랜잭션이 존재하여 작업을 하면 테이블에는 반영되고, 언두 로그에는 이전 트랜잭션의 데이터가 쌓인다. 그러므로 MVCC 만으로 정확한 데이터 제공이 불가능하고 언두 로그에도 잠금을 걸어야 하는데, 언두 로그는 append only 형태이므로 잠글 수 없다. 따라서 SELECT ~ FOR UPDATE나 LOCK IN SHARE MODE로 레코드를 조회하는 경우에는 언두 영역의 데이터가 아니라 테이블의 레코드를 가져오게 되고, 이로 인해 Phaontom Read가 발생하는 것이다.

하지만 MySQL에는 갭 락이 존재하기 때문에 위의 상황에서 문제가 발생하지 않는다.

사용자 B가 SELECT ~ FOR UPDATE로 데이터를 조회한 경우에 MySQL은 id가 50인 레코드에는 레코드 락, id가 50보다 큰 범위에는 갭 락으로 넥스트 키 락을 건다. 따라서 사용자 A가 id가 51인 member를 INSERT 시도한다면, B의 트랜잭션이 종료(커밋 또는 롤백)될 때 까지 기다리다가, 대기를 지나치게 오래 하면 락 타임아웃이 발생하게 된다.

따라서 일반적으로 MySQL의 REAPEATABLE READ에서는 Phantom Read가 발생하지 않는다. MySQL에서 Phantom Read가 발생하는 거의 유일한 케이스는 다음과 같다.

사용자 B는 트랜잭션을 시작하고, 잠금없는 SELECT 문으로 데이터를 조회하였다. 그리고 사용자 A는 INSERT 문을 사용해 데이터를 추가하였다. 이때 잠금이 없으므로 바로 COMMIT 된다. 하지만 사용자 B가 SELECT ~ FOR UPDATE로 조회를 했다면, 언두 로그가 아닌 테이블로부터 레코드를 조회하므로 Phantom Read가 발생한다.

하지만 이러한 케이스는 거의 존재하지 않으므로, MySQL의 REPEATABLE READ에서는 PHANTOM READ가 발생하지 않는다고 봐도 된다. 아래는 MySQL 기준으로 정리된 내용이다.

SELECT FOR UPDATE 이후 SELECT: 갭락 때문에 Phantom Read X
SELECT FOR UPDATE 이후 SELECT FOR UPDATE: 갭락 때문에 Phantom Read X
SELECT 이후 SELECT: MVCC 때문에 Phantom Read X
SELECT 이후 SELECT FOR UPDATE: Phantom Read O

마지막으로 트랜잭션 내에서 실행되는 SELECT와 트랜잭션 없이 실행되는 SELECT의 차이를 살펴보도록 하자.

REPEATABLE READ에서는 트랜잭션 번호를 바탕으로 실제 테이블 데이터와 언두 영역의 데이터 등을 비교하며 어떤 데이터를 조회할 지 판단한다.

즉, 트랜잭션 안에서 실행되는 SELECT라면 항상 일관된 데이터를 조회하게 된다.

이때는 언두 로그에서 데이터를 읽어오므로 일관된 데이터가 조회됨.

하지만 트랜잭션 없이 실행된다면, 데이터의 정합성이 깨지는 상황이 생길 수 있다.

트랜잭션 없는 조회면 테이블에서 바로 조회하므로 정합성이 깨질 수도 있음.

커밋된 데이터만을 보여주는 READ COMMITTED 수준에서는 둘의 차이가 거의 없다.

(모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는)를 가지고 있으며, 언두 영역에 백업된 모든 레코드는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다.)

유령데이터 읽기(Phantom Read)

트랜잭션 1이 데이터를 읽고 트랜잭션 2가 데이터를 쓰고(Insert) 트랜잭션 1이 다시 한번 데이터를 읽을 때 생기는 문제
트랜잭션 1이 읽기 작업을 다시 한 번 반복할 경우 이전에 없던 데이터(유령 데이터)가 나타나는 현상

아래의 그림과 같이 T1이 먼저 값을 읽었고, T2가 값을 삽입(Insert)하였다고 하자. 그리고 T1이 다시 값을 읽으려고 하면 (범위, 집합적으로 조회 했을 때) 바로 전에는 없었던 값이 읽히게 되는데 이처럼 결과 집합이 달라지는 것을 Phantom Read라고 한다.

Read Commited 과 Repeatable read의 차이점

전자는, 해당 데이터의 commit을 기준으로 최신의 snapshot을 계속 갱신한다.
후자는, 트랜잭션 자신이 시작한 시간을 기점으로, 그 당시의 가장 최신의 snapshot을 읽는다.

※ 참고

REPETABLE READ 격리수준에서는 트랜잭션이 시작된 시점의 데이터를 일관되게 보여주는 것을 보장해야 하기 때문에 한 트랜잭션의 실행시간이 길어질수록 해당 시간만큼 계속 멀티 버전을 관리해야 하는 단점이 있다.

하지만 실제로 영향을 미칠 정도로 오래 지속되는 경우는 없어서… READ COMMITTED와 REPETABLE READ의 성능차이는 거의 없다고 한다.

REPETABLE READ에서 발생할 수 있는 데이터 부정합

1. UPDATE 부정합

START TRANSACTION; -- transaction id : 1
SELECT * FROM Member WHERE name='junyoung';

    START TRANSACTION; -- transaction id : 2
    SELECT * FROM Member WHERE name = 'junyoung';
    UPDATE Member SET name = 'joont' WHERE name = 'junyoung';
    COMMIT;

UPDATE Member SET name = 'zion.t' WHERE name = 'junyoung'; -- 0 row(s) affected
COMMIT;

이 상황에서 최종 결과는 name = joont가 된다.
REPETABLE READ이기 때문에, 2번 트랜잭션에서 name = joont로 변경하고 COMMIT을 하면 name = junyoung의 내용을 UnDo로그에 남겨놔야 한다.
그래야 1번 트랜잭션에서 일관되게 데이터를 보는 것을 보장해줄 수 있기 때문이다.

이 상황에서 아래 구문에서 UPDATE 문을 실행하게 되는데, UPDATE의 경우 변경을 수행할 로우에 대해 잠금이 필요하다.
하지만 현재 1번 트랜잭션이 바라보고 있는 name = junyoung 의 경우 레코드 데이터가 아닌 UnDo영역의 데이터이고,
UnDo영역에 있는 데이터에 대해서는 쓰기 잠금을 걸 수가 없다.

그러므로 위의 UPDATE 구문은 레코드에 대해 쓰기 잠금을 시도하려고 하지만 name = junyoung인 레코드는 존재하지 않으므로, 0 row(s) affected가 출력되고, 아무 변경도 일어나지 않게 된다.

그러므로 최종적으로 결과는 name = joont가 된다.

간단하게 말해 DML 구문은 멀티버전을 관리하지 않는다.

2. Phantom READ

한 트랜잭션 내에서 같은 쿼리를 두 번 실행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말한다.

REPETABLE READ 이하에서만 발생하고(SERIALIZABLE은 발생하지 않음), INSERT에 대해서만 발생한다.
아래와 같은 상황에서 재현될 수 있다.

START TRANSACTION; -- transaction id : 1 
SELECT * FROM Member; -- 0건 조회

    START TRANSACTION; -- transaction id : 2
    INSERT INTO MEMBER VALUES(1,'joont',28);
    COMMIT;

SELECT * FROM Member; -- 여전히 0건 조회 
UPDATE Member SET name = 'zion.t' WHERE id = 1; -- 1 row(s) affected
SELECT * FROM Member; -- 1건 조회 
COMMIT;

위 코드를 보면 1번 트랜잭션의 select 문은 UnDo 영역에서 데이터를 조회하기 때문에 0건이 조회된다.

하지만 UPDATE의 경우 잠금 설정이 필요하고 UnDo 영역은 잠글 수가 없으므로 해당 레코드에 접근하여 데이터를 변경하는 것이다.

그래서 REPETABLE READ 에 의하면 원래 출력되지 않아야 하는데 UPDATE 문의 영향을 받은 후 부터 출력된다.

이 시점에 스냅샷을 적용시키는 것 같다.

참고로 DELETE에 대해서는 적용되지 않는다.

START TRANSACTION; -- transaction id : 1 
SELECT * FROM Member; -- 1건 조회

    START TRANSACTION; -- transaction id : 2
    DELETE FROM Member WHERE id = 1;
    COMMIT;

SELECT * FROM Member; -- 여전히 1건 조회 
UPDATE Member SET name = 'zion.t' WHERE id = 1; -- 0 row(s) affected
SELECT * FROM Member; -- 여전히 1건 조회 
COMMIT;

SERIALIZABLE

가장 단순하고 가장 엄격한 격리수준이다. SERIALIZABLE에서 여러 트랜잭션이 동일한 레코드에 동시 접근할 수 없으므로, 어떠한 데이터 부정합 문제도 발생하지 않는다.

MySQL에서 SELECT~ FOR SHARE/UPDATE는 대상 레코드에 각각 읽기/쓰기 잠금을 거는 것이다. 하지만 순수한 SELECT 작업은 아무런 레코드 잠금 없이 실행되는데, 잠금 없는 일관된 읽기(Non-locking consistent read)란 순수한 SELECT 문을 통한 잠금 없는 읽기를 의미하는 것이다.

하지만 SERIALIZABLE 격리 수준에서는 순수한 SELECT 작업에서도 대상 레코드에 넥스트 키 락을 읽기 잠금(공유락, Shared Lock)으로 건다. 따라서 한 트랜잭션에서 넥스트 키 락이 걸린 레코드를 다른 트랜잭션에서는 절대 추가/수정/삭제할 수 없다.

이러한 특성 때문에 동시처리 능력이 다른 격리수준보다 떨어지고, 성능저하가 발생하게 된다.

고립 수준이 Level 3으로 가장 높은 명령어로, 실행 중인 트랜잭션은 다른 트랜잭션으로부터 완벽하게 분리된다.

반복되는 말이지만 데이터 집합에 범위를 지어 잠금을 설정할 수 있기 때문에 다른 사용자가 데이터를 변경(UPDATE) 또는 삽입(Insert)하려고 할 때 트랜잭션을 완벽하게 분리할 수 있다. 이 명령어는 가장 제한이 심하고 동시성도 낮다. 즉, SELECT 문 사용하는 모든 데이터에 Shared Lock이 걸리므로 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정(UPDATE) 및 입력(INSERT)가 불가능하다.

앞의 Repeatable Read의 경우에는 1에서 10사이의 number에 대한 삽입이 가능하였지만 SERIALIZABLE은 SELECT의 대상이 되는 모든 테이블에 Shared Lock을 설정하는 것과 같아서 나머지 범위에 대한 INSERT가 불가능하다.

SERIALIZABLE은 가장 안전하지만 가장 성능이 떨어지므로, 극단적으로 안전한 작업이 필요한 경우가 아니라면 사용해서는 안된다.

트랜잭션의 격리 수준(Transaction Isolation Level) 요약

앞서 살펴본 내용을 정리하면 다음과 같다. READ UNCOMMITTED는 부정합 문제가 지나치게 발생하고, SERIALIZABLE은 동시성이 상당히 떨어지므로 READ COMMITTED 또는 REPEATABLE READ를 사용하면 된다. 참고로 오라클에서는 READ COMMITTED를 기본으로 사용하며, MySQL에서는 REPEATABLE READ를 기본으로 사용한다.

	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(MySQL은 거의 없음)
SERIALIZABLE	없음	없음	없음

격리 수준이 높아질수록 MySQL 서버의 처리 성능이 많이 떨어질 것으로 생각하는데, 사실 SERIALIZABLE이 아니라면 크게 성능 개선 및 저하는 발생하지 않는다. 그 이유는 결국 언두 로그를 통해 레코드를 참조하는 과정이 거의 동일하기 때문이다.

따라서 MySQL은 갭 락을 통해 Phantom Read까지 거의 발생하지 않고, READ COMMITTED보다는 동시 처리 성능은 뛰어난 REPEATABLE READ를 사용한다.

트랜잭션 간 격리수준을 경험하기

트랜잭션 격리 수준 실습에 대해서는 아래 블로그를 참고하자.

MySQL의 Transaction Isolation Levels

사실 이 글의 목적은 데이터베이스의 Transaction Isolation Levels에 대해 공부한 내용을 쓰기 위함이었는데, MySQL을 예로 사용하며 여러가지 실습해보며 MySQL에서만 적용되는 몇가지 특성이 있음을 알

jupiny.com

참고로, 트랜잭션을 분리하는 방법은 dbeaver 등 툴에서, sql 페이지를 두 개 생성하고 각각의 페이지에서 트랜잭션을 시작하면 된다. 이를 통해 트랜잭션을 분리할 수 있다.

참고