[MySQL] 트랜잭션 격리 수준

들어가기 전 

이번 포스팅에서는 데이터베이스 트랜잭션의 격리 수준에 대해서 알아보겠습니다.

 

트랜잭션 격리 수준

트랜잭션 격리 수준은 여러 트랜잭션이 동시에 실행되는 상황에서 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 정하는 것입니다.

 

트랜잭션 격리 수준의 종류는 아래와 같습니다.

• READ_COMMITTED : 다른 트랜잭션에서 커밋된 내용을 참조 가능
• READ_UNCOMMITTED : 다른 트랜잭션에서 커밋되지 않은 내용 참조 가능
• REPEATABLE_READ  : 트랜잭션 진입 이전에 커밋된 내용만 참조 가능
• SERIALIZABLE : 트랜잭션에 진입하게 되면 락을 걸어 다른 트랜잭션에서 참조 불가능

Oracle의 기본 격리 수준은 READ_COMMITTED, MySQL의 기본 격리 수준은 REPEATABLE_READ입니다.

 

*참고*

격리 수준에 대한 모든 예제는 AutoCommit을 비활성화한 상태에서 진행됩니다.

 

AutoCommit 비활성화

 

아래 쿼리를 실행 시키면 AutoCommit을 비활성화 할 수 있습니다.

 

SET AUTOCOMMIT = 0; // 1은 활성화 상태

 

 

트랜잭션 격리 수준과 락을 설명하기 앞서 데이터 초기 세팅을 먼저 진행하겠습니다.

 

초기 세팅

 

테이블 생성 및 데이터 삽입

 

create table member(
    id bigint auto_increment primary key ,
    name varchar(255),
    age int,
    birth date,
    nickname varchar(255)
);

insert into member(name, age, birth, nickname)
values
    ('테스터1', 25, '2000-11-15', '테스터 닉네임'),
    ('hoestory', 27, '1998-10-15', 'hoestory blog');

 

 

현재 실행되고 있는 쿼리의 격리 수준 확인하는 방법

 

SELECT trx_isolation_level FROM information_schema.innodb_trx;

 

처음에 아무 설정을 하지 않고 조회하게 되면 MySQL의 기본 격리 수준인 Repeatable Read가 조회됩니다.

 

 

 

트랜잭션 격리 수준 변경하는 방법

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

 

지금까지 예제를 설명하기 위해 필요한 세팅에 대한 설명을 하였습니다.
이제 본격적으로 예제를 통해서 격리 수준에 대해서 알아보겠습니다.

 

READ_UNCOMMITTED

READ UNCOMMITTED은 각 트랜잭션의 결과가 커밋 또는 롤백과 상관없이 다른 트랜잭션에서 볼 수 있습니다.

각 트랜잭션에서 완료되지 않은 작업을 다른 트랜잭션에서 볼 수 있는 현상을 "Dirty Read"라고 합니다.

 

이제 READ UNCOMMITTED를 예제로 알아보겠습니다.

 

트랜잭션 격리 수준 변경

 

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

 

 

1번 작업

트랜잭션 2번에서 트랜잭션을 시작하고 아래 쿼리를 통해 member의 id, name 컬럼의 데이터 값을 조회하고 있습니다.

 

SELECT id, name FROM member WHERE id = 2

 

쿼리의 결괏값을 id는 2, name은 hoestory가 나온 것을 확인할 수 있습니다.

 

2번 작업

트랜잭션 1번에서는 트랜잭션을 새로 시작하고 아래 쿼리를 통해 name을 수정한 뒤 commit을 하지 않습니다.

 

UPDATE member SET name = '호이스토리' WHERE id = 2;

 

 

3번 작업

트랜잭션 2번이 "1번 작업"에서 수행했던 동일한 쿼리를 실행시키면 수행결과가 달라지는 것을 확인할 수 있습니다.

이유는 READ_UNCOMMITTED 특성상 "2번 작업"에서 Update 쿼리의 수행 결과가 반영된 "3-1"에 접근하기 때문에 Dirty Read가 발생합니다.

 

SELECT id, name FROM member WHERE id = 2;

 

 

 

4번 작업

 

트랜잭션 1번을 커밋을 하여 데이터베이스에 반영합니다.

3번 작업을 통해 READ UNCOMMITTED가 커밋 또는 롤백이 되지 않더라도 반영된 데이터를 조회할 수 있는 것을 확인할 수 있습니다.

 

READ_COMMITTED

 

READ COMMITTED는 READ UNCOMMITTED와 달리 다른 트랜잭션에서 커밋된 내용만 참조합니다.

이러한 특성 때문에 READ COMMITTED는  NON-REPEATABLE-READ가 발생하여 데이터의 정합성에 문제가 발생할 수 있습니다.

 

트랜잭션 격리 수준 변경

 

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

 

 

Commit 하지 않았을 경우

 

 

READ UNCOMMIT과 달리 커밋되지 않았을 경우에는 언두 로그*에서 백업된 데이터 값을 가져옵니다.

그래서 "3번 작업"에서 백업된 데이터에 접근하여 조회합니다.

 

언두 로그란?
SELECT를 제외한 INSERT, DELETE, UPDATE 쿼리가 발생했을 때 쿼리 실행 전의 데이터를 백업해 두는 공간입니다.

예를 들어 memeber 테이블에 데이터가 아래와 같이 존재한다고 가정해 보겠습니다.
id = 1, name = 테스터
id = 2, name = hoestory

그리고 아래와 같은 쿼리가 실행되었을 때 언두 로그에는 아래와 같이 데이터를 백업합니다.

UPDATE member SET name = '호이스토리' WHERE id = 2;

언두 로그 백업 데이터
id = 1, name = 테스터
id = 2, name = hoestory

 

 

Commit 한 경우

 

 

트랜잭션 1의 "2번 작업"에서 Update 쿼리를 Commit 한 뒤 트랜잭션 2의 3번 작업에서는 언두 로그에 접근하는 게 아닌 Update 쿼리가 수행된 "3-1" 데이터에 접근하여 결과를 조회합니다.

 

 

위의 예제를 통해 알아봤듯이 READ COMMITTED는 다른 트랜잭션에서 커밋된 내용을 참조할 수 있습니다.

그런데 이와 같은 특성으로 인해 NON-REPEATABLE-READ가 발생하여 데이터의 정합성 문제가 발생할 수 있습니다.

아래에서 REPEATABLE_READ에 대해서 상세히 설명하겠지만 간략하게 설명하자면 같은 트랜잭션에서는 같은 결괏값을 반환해야 합니다.

그런데 위의 예제를 봤을 때 다른 트랜잭션에서 커밋이 일어나면 특정 트랜잭션에서 커밋이 일어나기 전과 후의 결괏값이 달라져 데이터의 정합성 문제가 발생할 수 있습니다.

 

실생활로 예를 들자면 어떤 회사에서 이벤트를 위해 이벤트에 참여한 인원수를 세어봤는데 처음에 셀 때는 10명이어서 보고서에 10명을 작성하였으나 갑자기 1명이 추가로 이벤트에 참여하였습니다.

그런데 보고서를 제출하려고 다시 세어봤는데 11명이 되어 있어 처음에 세었던 인원수와 제출 전 세었던 인원수가 다른 문제가 발생할 수 있습니다.

 

 

REPEATABLE_READ

REPEATABLE READ는 MySQL의 기본 격리 수준이고 트랜잭션에 진입하기 이전의 커밋된 내용들만 참조합니다.

앞서 설명한 READ COMMITTED, READ UNCOMMITTED에는 Phantom Read*가 발생하는데 REPEATABLE READ에서도 발생합니다.

그런데 MySQL InnoDB에서는 특정 상황에서만 발생 Phantom Read*가 발생하여 데이터 정합성이 깨지는 문제가 발생할 수 있습니다.

 

Phantom Read란?
다른 트랜잭션에서 변경한 레코드가 보였다 안보였다 하는 현상

 

트랜잭션 격리 수준 변경

 

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

 

 

 

REPEATABLE READ는 다른 트랜잭션에서 INSERT, UPDATE , DELETE가 발생하더라도 언두 로그에 백업된 데이터를 접근하기 때문에 쿼리가 발생하더라도 발생하기 전과 후에 동일한 데이터를 조회를 해올 수 있어 데이터 정합성 문제가 발생하지 않습니다.

 

그럼 이제 MySQL InnoDB엔진의 격리 수준이  REPEATABLE READ인 상태에서 Phantom Read가 발생하는 경우에 대해서 알아보겠습니다.

 

MySQL InnoDB + REPEATABLE READ에서 Phantom Read가 발생하는 경우

MySQL InnoDB엔진의 격리 수준이  REPEATABLE READ인 상태에서 Phantom Read가 발생하는 경우에는 아래 쿼리를 실행했을 때 발생합니다.

 

SELECT id, name FROM member WHERE age > 25 FOR UPDATE;

또는

SELECT id, name FROM member age > 25 FOR SHARE;

 

위 쿼리를 실행했을 때 발생하는 이유는 데이터를 조회할 때 쓰기 락, 공유 락을 걸기 때문입니다.

언두 로그에는 락이 안 걸리기 때문에 INSERT, UPDATE, DELETE 쿼리가 실행되어 반영된 데이터 접근하여 Phantom Read가 발생하는 것입니다.

 

 

 

지금까지 데드락에 대해서 알아보았습니다. 이제 데이터베이스 락에 대해서 알아보고 데이터베이스 락으로 인해 데드락이 발생할 경우 원인을 파악하는 방법과 해결하는 방법에 대해서 알아보겠습니다. 
락에 대해 설명하기 전에 예제에서 사용할 예제 테이블과 필요한 권한설정에 대해서 알아보겠습니다.

 

 

SERIALIZEABLE

SERIALIZABLE는 트랜잭션에 진입하게 되면 다른 트랜잭션에 참조하지 못하도록 락을 설정합니다.

 

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

 

 

 

1번 작업

트랜잭션 2의 1번 작업에서 트랜잭션을 시작하면서 id가 2번인 데이터를 조회합니다.

 

2번 작업

트랜잭션 1의 "2번 작업"에서 트랜잭션을 시작하면서 Update 쿼리를 수행하려고 하는데 Lock이 걸려서 대기를 합니다.

 

3번 작업

트랜잭션 2의 "3번 작업"에서 Commit을 수행하면 "2번 작업"에서 락이 해제가 되면서 Update 쿼리를 수행합니다.