MySQL Transaction 격리 수준

후니팍·2023년 8월 24일

Database mysql transaction

이전까지는 mysql로 쿼리 짜고, 테이블 만들고, 데이터베이스 만드는 정도로만 사용할 줄 알았습니다.
요즘 조금 더 심화적인 데이터베이스의 중요성에 대해 깨달아 관련 공부를 시작했습니다.

그 첫 주제로 데이터베이스의 격리 수준에 대해 정리해보고자 합니다.

트랜잭션

논리적 작업 단위를 말합니다.

더 이상 나눌 수 없는 작업 단위인데요. 계좌 이체를 예로 들겠습니다.

계좌 이체를 처리하려면

1. 사용자 A의 잔액을 20만원 줄이는 데 성공한다.

2. 사용자 B의 잔액을 20만원 더하는 데 성공한다.

두 가지 모두 성공해야합니다.

쿼리는 2개를 실행하더라도 논리적 작업 단위는 하나이기 때문에 이것을 트랜잭션이라고 합니다.

트랜잭션의 특징

데이터베이스에서 트랜잭션의 특징이 있는데요. ACID 라고 합니다.

총 4가지로 Atomicity(원자성), Consistency(일관성), Isolation(독립성), Durability(지속성) 이 있습니다.

하나하나 살펴보도록 하겠습니다.

Atomicity(원자성)

트랜잭션이 데이터베이스에 모두 다 반영되거나, 모두 다 반영되지 않아야 한다는 특징입니다.

여러 개의 쿼리 중에 일부만 성공했다고 해서 일부만 정상 처리하게 된다면 데이터 정합성이 깨지기 때문에 무조건 모두 다 성공해야 commit을 하고, 그렇지 않으면 rollback을 실행한다는 원칙입니다.

Consistency(일관성)

트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 한다는 특징입니다.

예를 들어 트랜잭션이 완료되면 계좌 잔액의 자료형이 Long에서 String으로 변하지 않는다는 것을 보장한다는 뜻입니다.
저는 너무 당연한 말이라고 생각해 100% 완벽하게 이해한 것이 맞는지 잘 모르겠습니다...

Isolation(독립성)

트랜잭션은 다른 트랜잭션이 존재하지 않는 것처럼 서로 간섭 없이 수행되어야 한다는 것을 의미합니다.
즉, 하나의 트랜잭션이 커밋되기 전까지 다른 트랜잭션은 특정 트랜잭션의 결과를 참조할 수 없습니다.

Durability(지속성)

트랜잭션 커밋 후에는 시스템이 중단되거나 장애가 발생해도 데이터가 그대로 유지되어야 한다는 특징입니다.
작업 도중에 에러가 발생하더도 커밋된 데이터는 유지된 상태로 데이터가 변하지 않아야 한다는 뜻입니다.

Commit, Rollback

Commit

트랜잭션이 성공적으로 마무리되어, 하나의 트랜잭션이 끝났다는 것을 알려주기 위해 사용하는 연산입니다.
저는 자바의 try-catch 구문에서 try 부분이 성공했다는 표현으로 생각했습니다!

Rollback

트랜잭션이 실패했고, 이전 상태로 롤백한다는 것을 알려주기 위해 사용하는 연산입니다.
저는 자바의 try-catch 구문에서 catch 부분에 도달했을 때 이전 연산을 원래 상태로 돌리는 표현으로 생각했습니다.

InnoDB 스토리지 엔진

Mysql 스토리지에 memory, MyISAM 등이 있는데 이 중에서 가장 많이 사용되는 스토리지 엔진입니다.

다른 엔진들과 달리 InnoDB는 트랜잭션을 지원한다고 합니다.

원자성으로 예를 들어보겠습니다.

CREATE TABLE inno_db_test (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
) ENGINE=InnoDB;

INSERT INTO inno_db_test VALUES (2, 'ditoo');
INSERT INTO inno_db_test VALUES (1, 'hongsil'), (2, 'ditoo'), (3, 'ethan');
SELECT * FROM inno_db_test; -- inno_db 결과?

CREATE TABLE myisam_test (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
) ENGINE=MyISAM;

INSERT INTO myisam_test VALUES (2, 'ditoo');
INSERT INTO myisam_test VALUES (1, 'hongsil'), (2, 'ditoo'), (3, 'ethan');
SELECT * FROM myisam_test; -- myisam 결과?

위의 코드에서 inno_db의 결과는 아래와 같습니다.

id	name
2	ditoo

그리고 myisam의 결과는 아래와 같습니다.

id	name
2	ditoo
1	hongsil

차이는 transaction의 지원 여부에 있습니다.

inno_db의 경우 트랜잭션을 지원하기 때문에 insert 문을 실행할 때 자동으로 트랜잭션 처리가 되어 여러 value 중 하나라도 잘못되면 전체 롤백에 됩니다. 그래서 조회 결과에 id = 2 인 데이터밖에 조회되지 않은 것입니다.

반면에 myisam의 경우 트랜잭션을 지원하지 않기 때문에 insert 문에 여러 value를 넣었을 때 성공하는 insert까지 데이터베이스에 반영됩니다. 즉, 2번째 insert에서 id = 1 까지 성공하고 id = 2 인 것부터 pk가 겹치기 때문에 실패했기 때문에 id = 1 인 것까지 데이터베이스에 반영이 된 것입니다.

mysql 5.5.5부터 inno db를 디폴트 스토리지 엔진으로 사용하고 있다고 합니다. (왜 그전까지는 myisam을 썼는지 이해되지 않음...)

트랜잭션의 격리 수준

트랜잭션에 대해 짧게 살펴보았으니, 조금 더 심화적인 부분인 트랜잭션 격리 수준에 대해 알아보고자 합니다.

Real MySQL 8.0에 따르면

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

라고 합니다.

즉, 서로 다른 두 개 이상의 트랜잭션이 동시에 같은 데이터를 처리할 때 어떤 방식으로 처리해야 하는지에 대해 다룬 것입니다.

격리 수준	dirty read	non-repeatable read	phantom read
read uncommitted	o	o	o
read committed	x	o	o
repeatable read	x	x	o
serializable	x	x	x

위 표와 같이 격리 수준을 나타내곤 하는데요. 하나하나 살펴보도록 하겠습니다.

읽기 부정합

트랜잭션의 격리 수준에 따라 발생할 수 있는 문제점들입니다.
이 문제점들을 기준으로 격리 수준을 나눌 수 있습니다.

Dirty Read

위 그림에서 Transaction 2가 Y를 80으로 바꾸었습니다. 하지만 커밋되지 않고 롤백이 되었죠.
Transaction 1은 Transaction 2가 롤백하기 전의 Y를 읽었습니다. 따라서 X는 100으로 변했고 커밋되었습니다.
여기서 모순이 발생합니다.

X에 Y를 더하면 20 + 30이 계산되어 50이 되어야 하는데, Transaction 1이 롤백하기 전의 Y를 읽어 X가 100으로 변한 것을 확인할 수 있습니다.
이런 현상을 Dirty Read 라고 합니다.

Non-Repeatable Read

X를 두 번 읽는 Transaction 1의 작업에서 두 번 읽은 X의 값이 서로 달랐습니다.
Transaction 2가 중간에 X의 값을 변경했기 때문인데요.
이렇게 같은 Transaction에서 같은 데이터를 읽는데 값이 서로 다르게 읽히는 현상을 Non-Repeatable Read 라고 합니다.

Phantom Read

Non-Repeatable Read의 일종입니다. 한 필터로 조회한 데이터들의 묶음이 달라지는 현상을 말합니다.

위의 그림에서 Transaction 2가 t2의 v를 10으로 변경하여 Transaction 1의 두 번의 조회값이 서로 달라졌습니다.
앞서 살펴본 Non-Repeatable Read와 매우 유사합니다.

격리 수준 (Isolation Level)

격리 수준은 총 4단계로 read uncommitted -> read committed -> repeatable read -> serializable 순으로 격리 수준이 높습니다.

MySQL innoDB의 디폴트 격리 수준은 3단계인 repeatable read 라고 합니다.

그럼 하나하나 살펴보도록 하겠습니다.

Read Uncommitted

가장 낮은 단계의 격리 수준입니다. read uncommitted에서는 dirty read, non-repeatable read, phantom read 모두 허용합니다.

-- 이하 1번 커넥션

START TRANSACTION;  # 2
INSERT INTO inno_db_test VALUES (1, 'test');  # 5
ROLLBACK ;  # 7

-- 이하 2번 커넥션

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  # 1

START TRANSACTION;  # 3
SELECT * FROM inno_db_test;  # 4
SELECT * FROM inno_db_test;  # 6
ROLLBACK;  # 8

위의 주석 순서로 실행하게 되면 2번 커넥션의 첫 select에서는 아래와 같이 나옵니다.

id	name

그리고 두 번째 select에서는 아래와 같이 데이터가 생긴 것을 확인할 수 있습니다.

id	name
1	test

이로 격리 수준이 read uncommitted 인 경우 커밋되지 않은 데이터까지 읽는다는 것을 확인할 수 있습니다.

Read Committed

두 번째 단계의 격리 수준입니다. read uncommitted와 달리 커밋된 데이터만 읽을 수 있습니다.

하지만 non-repeatable read를 허용하기 때문에 한 트랜잭션에서 같은 데이터를 두 번 읽을 때 서로 다른 값이 나올 수 있습니다.

우선 커밋된 데이터를 읽는지 확인해 보겠습니다.

-- 이하 1번 커넥션

START TRANSACTION;  # 3
INSERT INTO inno_db_test VALUES (1, 'test');  # 4
COMMIT;  # 6

-- 이하 2번 커넥션

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  # 1

START TRANSACTION;  # 2
SELECT * FROM inno_db_test;  # 5
SELECT * FROM inno_db_test;  # 7
ROLLBACK;  # 8

해당 주석 순서로 실행했을 때, 2번 커넥션의 첫 select에서는 아무 데이터도 읽지 않는다는 것을 확인할 수 있습니다.

id	name

1번 커넥션을 커밋하고 나면 7번 주석의 select를 실행할 때 데이터가 삽입되어 있는 것을 확인할 수 있습니다.

id	name
1	test

그렇다면 non-repeatable read는 올바르게 동작할까요?

-- 이하 1번 커넥션

START TRANSACTION;  # 3
UPDATE inno_db_test SET name = 'updated' WHERE id = 1;  # 4
COMMIT;  # 6

-- 이하 2번 커넥션

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  # 1

START TRANSACTION; # 2
SELECT * FROM inno_db_test; # 5
SELECT * FROM inno_db_test; # 7
ROLLBACK; # 8

해당 주석을 실행하기 전 데이터베이스 상태는 아래와 같습니다.

id	name
1	test

위 주석 순서로 실행했을 때, 5번 주석을 실행하면 아래와 같이 나옵니다. 데이터가 바뀌지 않았죠.

id	name
1	test

1번 커넥션을 커밋하고 7번 주석을 실행하면 아래와 같이 데이터가 업데이트 되었다는 것을 확인할 수 있었습니다.

id	name
1	updated

이 결과로 non-repeatable read 현상이 발생한 것을 확인할 수 있었습니다.

Repeatable Read

위의 read committed 수준에서 한 단계 더 강화된 격리 수준입니다.

방금 살펴본 non-repeatable read가 발생하지 않도록 격리시킵니다.

그럼 non-repeatable read가 정말 발생하지 않는지 앞선 실험과 같은 데이터로 실험해 보도록 하겠습니다.

-- 이하 1번 커넥션

START TRANSACTION;  # 3
UPDATE inno_db_test SET name = 'updated' WHERE id = 1;  # 4
COMMIT;  # 6

-- 이하 2번 커넥션

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  # 1

START TRANSACTION; # 2
SELECT * FROM inno_db_test; # 5
SELECT * FROM inno_db_test; # 7
ROLLBACK; # 8

이전과 같이 실행 전 데이터베이스 상태는 아래와 같습니다.

id	name
1	test

그리고 주석 순서대로 진행하여 두 번의 select를 실행했을 때 결과는 모두 아래와 같았습니다.

id	name
1	test

데이터는 변하지 않았고, 이로 인해 non-repeatable read 가 발생하지 않는다는 것을 검증했습니다.

repeatable read 격리 수준에서 phantom read가 발생하는지 확인해 보도록 하겠습니다.

-- 이하 1번 커넥션

START TRANSACTION;  # 2
UPDATE inno_db_test SET name = 'updated' WHERE id = 1;  # 3
COMMIT;  # 6

-- 이하 2번 커넥션

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  # 1

START TRANSACTION;  # 4
SELECT * FROM inno_db_test where name = 'test';  # 5
SELECT * FROM inno_db_test where name = 'test';  # 7
ROLLBACK;  # 8

해당 주석을 실행하기 전 데이터베이스 상태는 아래와 같습니다.

id	name
1	test
2	test

그리고 주석 순서대로 실행시켰을 때 결과는 놀랍게도 두 select의 결과가 같았습니다.

아래와 같이 말이죠.

id	name
1	test
2	test

phantom read가 작동하지 않은 것인데요. 이론 상이라면 repeatable read 수준에서 phantom read가 발생해야 하는데, 왜 그럴까요?

저는 실험에 MySQL 8.0.34 버전을 사용했는데요. MySQL 8.0 부터 Repeatable Read 수준에서도 Phantom Read가 발생하지 않는다고 합니다.

그 이유는 InnoDB 스토리지 엔진의 MVCC (Multi-Version Concurrency Control) 메커니즘 때문입니다.

MVCC에서 각 트랜잭션은 자신의 볼 수 있는 데이터 버전을 가지는데, 트랜잭션이 데이터를 수정하면 해당 데이터의 새 버전이 생성됩니다. 기존 버전은 유지되며 다른 트랜잭션은 이전 버전의 데이터를 볼 수 있습니다.

즉, 트랜잭션이 데이터를 읽을 때 해당 트랜잭션이 시작된 시점에서의 데이터 버전을 사용하여 읽습니다. 위의 실험에서 2번 커넥션이 시작했을 때 1번 커넥션이 커밋되기 전 버전의 데이터만 읽기 때문에 phantom read 가 발생하지 않았던 것입니다. 다른 트랜잭션이나 작업에 의한 데이터 변경으로 인한 영향을 받지 않기 때문에 Serializable 수준을 사용하지 않고도 phantom read 문제를 해결할 수 있었습니다.

Serializable

격리 수준 최종 단계인 serializable 입니다. 이는 설명하길 데이터가 중간에 바뀔 일이 절대 없다고 합니다.

그렇다면 어떻게 phamtom read까지 보장할 수 있는지 살펴보도록 하겠습니다.

-- 이하 1번 커넥션

START TRANSACTION;  # 3
UPDATE inno_db_test SET name = 'updated' WHERE id = 1;  # 5
COMMIT;  # 6

-- 이하 2번 커넥션

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  # 1

START TRANSACTION;  # 2
SELECT * FROM inno_db_test where name = 'test';  # 4
SELECT * FROM inno_db_test where name = 'test';  # 7
ROLLBACK;  # 8

위의 실험에서 주석 순서대로 SQL 문을 실행시키면 5번 주석 부분부터 lock이 걸립니다.
2번 커넥션 트랜잭션을 다 끝내기 전까지 대기 상태에 걸리고, 2번 커넥션이 완료되어야 lock이 풀리고 SQL 문이 실행됩니다.

repeatable read 와 달리 접근을 막아 다른 트랜잭션을 대기 상태로 만듭니다.

이는 자원이 겹치는 경우 성능적으로 문제가 많을 것입니다. MySQL 8.0 부터는 MVCC를 활성화하여 repeatable read 수준에서도 phantom read 문제를 해결할 수 있기 때문에 굳이 성능도 좋지 않은 serializable 수준의 격리를 사용하지 않을 것 같습니다.