MySQL 트랜잭션 격리 수준, 그 차이와 특징 한눈에 보기

데이터베이스를 운영하다 보면 여러 트랜잭션이 동시에 실행될 때, 데이터 정합성과 동시성 제어를 위해 격리 수준이 중요해집니다. MySQL은 다양한 격리 수준을 제공하여 각 상황에 맞게 데이터의 일관성을 보장하는데요. 오늘은 SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED 네 가지 격리 수준의 특징과 장단점을 살펴보겠습니다.

1. SERIALIZABLE – 가장 엄격한 격리, 하지만 속도는 느리다

SERIALIZABLE은 이름 그대로 모든 트랜잭션을 순차적으로 실행하는 것처럼 동작합니다.

• 특징:
  • 여러 트랜잭션이 동일한 레코드에 동시에 접근하는 것을 원천 차단하여, 데이터 부정합(불일치)이 발생하지 않습니다.
  • 순차적으로 처리되므로 동시 작업의 성능은 크게 저하됩니다.
• 잠금 방식:
  • SELECT FOR SHARE 또는 SELECT FOR UPDATE를 사용하면 대상 레코드에 읽기 또는 쓰기 잠금을 걸지만, 보통 순수 SELECT는 잠금 없이 실행됩니다.
  • 단, SERIALIZABLE 수준에서는 순수 SELECT에도 넥스트 키 락을 걸어, 다른 트랜잭션이 해당 데이터를 수정하지 못하도록 합니다.

안전성은 최고지만, 동시성 요구가 높은 시스템에서는 사용하기 어려운 격리 수준입니다.

2. REPEATABLE READ – 한 번 읽은 데이터는 변하지 않는다

REPEATABLE READ는 MySQL의 기본 격리 수준으로, 트랜잭션 내에서 같은 데이터를 여러 번 읽어도 결과가 일관되게 유지됩니다.

• MVCC (다중 버전 동시성 제어):
  • 트랜잭션이 시작되면 고유 번호가 부여되고, 데이터 변경 전의 상태를 언두 로그에 백업합니다.
  • 이를 기반으로 트랜잭션은 자신이 시작되기 전의 커밋된 데이터만 조회하므로, 중간에 다른 트랜잭션이 데이터를 변경해도 처음 읽은 데이터가 그대로 유지됩니다.
• 유령 읽기(Phantom Read):
  • 기존 레코드의 일관성은 보장되지만, 트랜잭션 실행 중 다른 트랜잭션이 새 레코드를 추가하면 조회 결과에 변화가 생길 수 있습니다.
  • MySQL은 갭 락(gap lock)을 사용해, SELECT FOR UPDATE 같은 잠금이 걸린 경우 유령 읽기를 방지합니다.

즉, REPEATABLE READ는 한 번 읽은 데이터의 일관성을 보장하는 대신, 새 레코드의 추가로 인한 변화는 완벽히 막지 못할 수 있습니다.

3. READ COMMITTED – 커밋된 데이터만 보여준다

READ COMMITTED는 이름 그대로, 오직 커밋된 데이터만 읽어들이는 격리 수준입니다.

• 주요 특징:
  • 다른 트랜잭션의 변경 사항이 커밋되기 전에는 보이지 않으므로, Dirty Read(오손 읽기) 문제를 방지합니다.
  • 단, 한 트랜잭션 내에서 여러 번 SELECT을 실행하면, 중간에 다른 트랜잭션이 데이터를 변경해 커밋된 경우 이전과 다른 결과가 나타날 수 있습니다.
    → 이는 Non-Repeatable Read(반복 불가능 읽기)나 유령 읽기(Phantom Read) 현상으로 이어질 수 있습니다.
• 사용 시 고려사항:
  • READ COMMITTED는 트랜잭션 없이 실행되는 SELECT와 트랜잭션 내에서 실행되는 SELECT 간의 결과 차이가 거의 없기 때문에, 단순 조회 작업에서는 일관된 커밋 데이터를 볼 수 있습니다.
  • 다만, 동일 트랜잭션 내에서 데이터가 변경될 가능성이 있으므로, 절대적인 일관성이 필요한 경우라면 REPEATABLE READ나 SERIALIZABLE 같은 수준을 고려해야 합니다.

READ COMMITTED는 많은 DBMS에서 기본적으로 채택하는 격리 수준으로, 안전성과 성능의 균형을 맞춘 선택지라고 할 수 있습니다.

4. READ UNCOMMITTED – 가장 낮은 격리, 위험한 선택

READ UNCOMMITTED는 아직 커밋되지 않은 데이터까지 읽어들일 수 있는 격리 수준입니다.

• 특징 및 위험성:
  • Dirty Read 현상이 발생하여, 다른 트랜잭션의 변경 사항이 커밋 전에 조회될 수 있습니다.
  • 만약 해당 트랜잭션이 롤백된다면, 이미 읽어들인 데이터와 실제 데이터 사이에 불일치가 생겨 시스템에 심각한 문제가 발생할 수 있습니다.

이러한 이유로 READ UNCOMMITTED는 데이터 정합성 측면에서 매우 취약하여, 실무에서는 거의 사용되지 않는 격리 수준입니다.

결론

각 격리 수준은 데이터 정합성을 유지하는 방식과 성능에 있어 서로 다른 트레이드오프를 가지고 있습니다.

• SERIALIZABLE은 최고의 안전성을 보장하지만, 동시성이 크게 떨어집니다.
• REPEATABLE READ는 트랜잭션 내에서 읽은 데이터의 일관성을 유지해주지만, 새 레코드의 추가로 인한 변화는 완전히 막지 못할 수 있습니다.
• READ COMMITTED는 오직 커밋된 데이터만 읽어 Dirty Read를 방지하면서도, 다소 유연한 데이터 조회를 가능하게 합니다.
• READ UNCOMMITTED는 성능은 좋을 수 있으나, 데이터 정합성에 치명적인 위험을 내포하고 있습니다.

시스템의 요구 사항과 데이터의 중요도에 따라 적절한 격리 수준을 선택하는 것이 MySQL을 효율적으로 운영하는 핵심입니다.

# 계정 확인
select user, host from mysql.user;

# 권한 부여
GRANT ALL PRIVILEGES ON [DB 이름].* TO '[계정 이름]'@'[계정 host]';
FLUSH PRIVILEGES;

# 권한 확인
SHOW GRANTS FOR '[계정 이름]'@'[계정 host]';

*MySQL에서 bulk insert를 할 때 DB에서 받아 줄 수 있는 최대치를 구하기 위함

*MySQL은 최대 용량으로 DB가 받아 줄 수 있는 양을 결정(개인적으로 확인했을 때)

# 확인 방법
SHOW VARIABLES LIKE 'max_allowed_packet';


# 테스트
INSERT INTO cm_max VALUES ( REPEAT('A', 10000000), REPEAT('가', 10000000));
# 10000000 은 10MB 정도, 만약 max_allowed_packet가 10MB 이상이면 max_allowed_packet 을 줄여서 테스트
# -> SQL Error [1301] [HY000]: (conn=257936) Result of repeat() was larger than max_allowed_packet (16777216) - truncated

# max_allowed_packet 줄이는 방법
SET GLOBAL max_allowed_packet = 128 * 1024 * 1024;

EXISTS 사용 방법

EXISTS는 먼저 쿼리를 조회해서 하나의 레코드를 가지고 오고 나서 서브쿼리를 실행하여 서브쿼리에 대한 결과가 존재하는지를 확인해서 있다면 레코드를 출력해줍니다.

여기서 가장 중요한 것은 서브쿼리에 대한 결과가 NULL이여도 결과가 존재한 것이기 때문에 결과가 True가 되어 레코드를 출력하게 됩니다.

TEST

테스트 해볼 사이트: https://sqltest.net/

SQL Script

CREATE TABLE mysql_test_a ( 
id VARCHAR(30),
name VARCHAR(30) NOT NULL
); 


INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('1', 'abab');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('2', 'John');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('3', 'dd');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('4', 'bb');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('5', 'aa');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('2', 'omokoe');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('6', 'John');

SQL Query

SELECT * FROM mysql_test_a a
where exists (
    select 1 from mysql_test_a b
    where a.id = b.id
    and b.name = 'John'
);

결과

'2', 'John'
'2', 'omokoe'
'6', 'John'

왜 '2', 'omokoe' 가 결과 값에 포함되어 있을까?

앞서 말했드이 먼저 쿼리를 조회하고 나서 서브쿼리를 조회 하는데, 서브쿼리 b 전체 행을 조회합니다. b 테이블에 id 값이 2인데 name에 값이 John인 레코드가 있으면 return True가 되고 레코드에 출력되는 것입니다. 그래서 '6', 'John'도 출력되는 것입니다.

Join 속도 개선

Join 속도 개선 전에 먼저 현재 작성한 쿼리가 어떤지 파악해야합니다.

Mysql기반 EXPLAIN을 통해 쿼리에 대해 분석해보겠습니다.

** 추후 다른 DBMS에서 확인하는 방법을 추가 업데이트 하도록 하겠습니다.

EXPLAIN 사용방법

EXPLAIN
SELECT * FROM 테이블명 WHERE 조건

결과

type 설명

• ALL, eq_ref: 조인시 기본 키나 고유키를 사용하여 하나의 값으로 접근(최대 1행만을 정확하게 패치).
• ref: 여러 개의 행을 패치할 가능성이 있는 접근.

컬럼 extra는 옵티마이저가 동작에 대한 힌트입니다.

extra 설명

EXPLAIN을 통해 분석하여 검색 범위 및 옵티마이저 동작에 문제가 없는데도 속도가 느리다면 아래 사항에 대해서 검토 해보면 좋습니다.

- left outer join 등 보다는 inner join 속도가 빠릅니다.

- 테이블에서 행의 순서를 가져올 때 분리해서 가져오는것도 생각해봐야합니다.(여러 테이블과 조인한 상태에서 테이블에 행을 가져오는건 속도 저하를 유발합니다.)

- 스칼라 서브 쿼리는 지양해야합니다.(실무에서 쿼리짜는 도중 스칼라 서브 쿼리를 사용했을 때 속도 차이가 많이 났습니다.)