MySQL Isolation Level 정리

MySQL Isolation level은 동시성 제어를 위한 설정 값으로, 동시에 실행되는 여러 트랜잭션 간에 데이터 일관성을 유지하기 위해 사용된다. 값에 따라서 다른 트랜잭션에서 commit을 했더라도 볼 수 있을수도 있고, 없을 수도 있다. Isolation Level을 설정할 때 고려해야 할 점은 데이터 일관성과 성능 사이의 트레이드오프다. Isolation Level이 높을수록 데이터 일관성은 유지되지만 성능이 저하된다. 반면에 Isolation Level이 낮을수록 성능은 좋아지지만 데이터 일관성이 보장되지 않는다. 따라서, 요구사항에 따라서 적절한 Isolation Level을 선택하는 것이 중요하다. 

값의 종류는 아래와 같이 4가지로 나누어져 있다.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

간략하게 isolation level 값을 확인하고 설정하는 방법은 아래와 같습니다.

MySQL Isolation level 확인

# session
SELECT @@transaction_isolation;

# global
SELECT @@global.transaction_isolation;

 

MySQL Isolation level 설정

SET session TRANSACTION ISOLATION level REPEATABLE READ

SET global TRANSACTION ISOLATION level REPEATABLE READ

 

Read Uncommitted

Read Uncommitted는 이름 그대로 아직 다른 트랜잭션에서 COMMIT 되지 않은 데이터들을 읽어올 수 있는 level이다. 이 때 읽어온 데이터들이 결국 COMMIT 된다면 더 빨리 데이터들을 읽어오는것은 문제가 없다. 하지만, 만약 COMMIT이 되지 않고 ROLLBACK이 된다면 데이터베이스에 없는 데이터를 읽어오게 된다. 이렇게 신뢰할 수 없는 데이터를 Dirty Read라고 부른다.

Read Commited Test

DB connection을 두 개를 생성한 후에 테스트를 해보면 다음과 같다.

한 쪽에서 commit 하지 않은 데이터를 읽어온것을 확인할 수 있다. 그리고 commit 하지 않은 채 종료하면 정상적으로 다시 데이터를 가져오는 것 까지 확인할 수 있다.

READ UNCOMMITTED isolation level에서는 다음과 같은 세가지 현상이 모두 발생한다.

1. 아직 COMMIT 되지 않은 신뢰할 수 없는 데이터를 읽어옴 (dirty read)
2. 한 트랜잭션에서 동일한 SELECT 쿼리의 결과가 다름 (non-repeatable read)
3. 이전의 SELECT 쿼리에 없던 row가 생김 (phantom read)

 

Read committed

Read Committed 는 다른 트랙잭션에서 Commit된 데이터만 읽어올 수 있는 isolation level이다. 예상 할 수 있는 것 처럼 commit을 한 데이터만 가져오게 된다. DB connection을 두 개를 생성한 후에 테스트를 해보면 다음과 같다.

다른 트랜잭션에서 트랜잭션을 Commit 하기 전에 현 트랜잭션에서 데이터가 변경되지 않음.

Commit 한 후에는 변경된 데이터를 가져옴.

Read Committed isolation level에서는 두 가지 현상이 나타난다.

1. 한 트랜잭션에서 동일한 SELECT 쿼리의 결과가 다름 (non-repeatable read)
2. 이전의 Select 쿼리에 결과에 없던 row가 생김 (phantom read)

 

Repeatable Read

Repeatable Read는 MySQL InnoDB의 기본 isolation level이다. MySQL에서 지정한 기본 isolation level인 만큼 동시성과 안전성의 균형을 갖춘 level이라고 할 수 있다,

결과부터 이야기하면 우리가 그 동안 이야기 해왔던 dirty-read, non-repeatable read, phantom read 모두 발생하지 않는다.

그 이유는 MySQL의 공식문서에서 확인할 수 있다.

MySQL에서는 Repeatable READ와 READ COMMITTED level에 대해서 한 트랜잭션에서 SELECT 쿼리로 데이터를 읽어올 때 lock을 걸지 않고, 해당 시점의 snapshot을 구축하여 거기서 데이터를 읽어온다.

앞에서는 이야기 하지 않았지만, READ COMMITTED에서 각각의 SELECT 쿼리는 그때 그때 최신의 snapshot을 구축하여 데이터를 읽어온다. 따라서 한 트랜잭션이지만 SELECT 쿼리의 결과가 다를 수 있다.

이와 다르게 REPEATABLE READ는 한 트랜잭션에서 처음 데이터를 읽어올 때 구축한 snapshot에서 모두 데이터를 읽어온다. 따라서 매번 SELECT 쿼리의 결과들이 항상 처음과 일치하게 된다.

REPEATABLE READ에서 한 가지 흥미로운 점은 다른 트랜잭션에서 추가된 row에 대해서 SELECT 에서 가져올 때 데이터는 나타나지 않지만 update나 delete를 할 수 있고, 해당 sql을 실행후에 select 쿼리를 사용하면 최신의 snapshot  에서 데이터를 가져올 수 있다.

• COMMIT 해도 SELECT 쿼리 결과가 바뀌지 않음.

다른 트랜잭션에서 COMMIT 해도 SELECT 쿼리 결과가 바뀌 지 않음.

해당 transaction내에서 update, delete를 할 경우 해당 트랜잭션에서는 최신 snapshot으로 받아올 수 있다.

 

SERIALIZABLE

SERIALIZABLE는 동시성을 상당부분 포기하고 안정성에 큰 비중을 둔 isolation level이다. SERIALIZABLE은 한 트랜잭션 안에서 SELECT 쿼리를 사용하더라도, 모두 SELECT … FOR SHARE 으로 변환된다. 해당 변환된 쿼리로 읽어온 row들에는 shared lock(or read lock)을거는 쿼리이다.

쉽게 설명하면 SELECT 쿼리를 한 트랜잭션이 COMMIT 하기 전까지는 update나 delete등이 불가능하다.

Lock timeout이 발생하는 것을 확인할 수 있다.