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을 효율적으로 운영하는 핵심입니다.

DB에서 사용하는 정렬 알고리즘

데이터베이스 시스템은 일반적인 프로그래밍 언어에서 사용하는 정렬 알고리즘 외에도, 대규모 데이터셋과 디스크 I/O를 고려한 최적화된 정렬 방식을 사용합니다.

알고리즘/기법 설명 사용 시점

# postgresql console 접속
$ psql postgres

# 사용자 확인
$ \du

# 사용자 생서
$ CREATE ROLE postgres WITH LOGIN PASSWORD 'postgres'

# 사용자 권한 부여
$ ALTER ROLE postgres CREATEDB;
$ ALTER ROLE postgres CREATEROLE;

# 새로 만든 유저로 접속
$ psql postgres -U postgres
# -> cmd 창을 보면 postgres=# 에서 postgres => 으로 변환 것을 확인 가능
# '#'은 superuser를 의미, '>' 는 superuser가 아니라는 의미

# 데이터 베이스 생성
$ CREATE DATABASE [데이터베이스명];

# 특정 유저에게 DB의 모든 권한 부여
$ GRANT ALL PRIVIELEGES ON DATABASE [데이터베이스명] TO [유저명];

# 데이터 베이스 리스트 보기
$ \list

# 특정 db로 연결
$ \connect [데이터베이스명];

# 계정 확인
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;

sequence(자동 증가) 값 확인

환경

- DBeaver에서 query 실행.

PostgreSQL에서 setval() 함수를 통해서 현재 seq 값을 확인할 수 있다는 글이 많은데 ERROR가 발생했습니다.

last_value() 함수도 ERROR 발생합니다.

현재까지 성공한 seq 성공 방법은 nextval(’seq name’) 밖에 없습니다. 하지만 이 방법을 사용하면 seq 값이 1 증가하기 때문에 사용할 수 없습니다.

**DBeaver 을 사용하면 table 목록을 열면 Sequences 항목이 나오는데 sequences 를 확인 할 수 있습니다.**

성공

last_value 컬럼을 조회하면  sequence 값을 확인할 수 있었습니다.

select last_value from seq이름

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'도 출력되는 것입니다.

Clustered vs NonClustered (index 개념)

Database에서 index의 구조는 크게 Clustered 와 NonClustered로 나눠집니다.

Clustered

Clustered index는 데이터가 테이블에 물리적으로 저장되는 순서를 정의합니다. 즉, Clustered index는 특정 컬럼을 기준으로 데이터들을 정렬시켜줍니다. 테이블 데이터는 오직 한 가지의 방법으로만 정렬되기 때문에 테이블 당 하나의 Clustered index가 존재합니다.

단점으로는 새로운 데이터를 삽입할 때는 많은 비용이 소모되는 담점이 존재합니다.

주로 사용될 때:

• 테이블 데이터가 자주 업데이트 되지 않는 경우
• 항상 정렬 된 방식으로 데이터를 반환해야하는 경우
• 테이블은 정렬되어있기 때문에 ORDER BY 절을 활용해 모든 테이블 데이터를 스캔하지 않고 원하는 데이터를 조회할 경우
• 읽기 작업이 월등히 많은 경우(Read 속도가 빠릅니다.)

NonClustered

NonClustered index는 군집화 되어있지 않은 index를 말합니다. 즉, 테이블에 저장된 순서에 따라 데이터를 정렬하지 않습니다. 또한 NonClustered index는 별도의 장소에 저장되어 사용됩니다. 그러므로 별도의 공간(약 10%)이 필요합니다.

주로 사용될 때:

• where절이나 join 절과 같이 조건문을 활용하여 테이블을 필터링 하고자할 때
• 데이터가 자주 업데이트 될 때
• 특정 컬럼이 쿼리에서 자주사용 될 때