트랜잭션의 격리 수준(isolation level)이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것입니다.
데이터베이스의 부정합 문제 3가지와 격리 수준 4가지의 관계는 다음과 같습니다.
DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
READ UNCOMMITTED | 발생 | 발생 | 발생 |
READ COMMITTED | 없음 | 발생 | 발생 |
REPEATABLE READ | 없음 | 발생 | 발생 (InnoDB는 없음) |
SERIALIZABLE | 없음 | 없음 | 없음 |
현재 트랜잭션 격리 수준을 확인해봅시다. InnoDB의 기본 격리 수준은 REPEATABLE READ입니다.
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
트랜잭션 격리 수준을 변경하려면 SET SESSION 문을 사용합니다.
-- 이 변경은 현재 세션에만 적용되며, 다른 세션에는 영향을 미치지 않습니다.
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- MySQL 8.0.24부터는 SET PERSIST 문을 사용할 수 있습니다.
-- 이 변경은 격리 수준을 전역적으로 영구 설정합니다.
-- 이렇게 설정하면 서버를 다시 시작해도 변경 내용이 유지됩니다.
mysql> SET PERSIST TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
격리 수준 실험을 위해 employees 테이블을 준비합니다.
mysql> CREATE TABLE employees (
emp_no INT PRIMARY KEY,
first_name VARCHAR(50)
);
COMMIT을 수동으로 하기 위해 오토커밋을 OFF로 설정합니다.
-- 오토커밋을 OFF로 설정합니다.
mysql> SET AUTOCOMMIT = 0;
-- 오토커밋 상태를 확인합니다.
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1. READ UNCOMMITTED
이 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보입니다.
1.1. 다른 커넥션으로 MySQL 서버에 접속합니다. (간단하게 터미널 하나 더 띄우시면 됩니다.) 세션 A, 세션 B의 격리 수준을 READ UNCOMMITTED로 변경한 후, 오토커밋을 해제합니다.
-- 트랜잭션 격리 수준을 전역적으로 READ-UNCOMMITTED로 설정합니다.
mysql> SET PERSIST TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 세션 A, 세션 B의 오토커밋을 OFF로 설정합니다.
mysql> SET AUTOCOMMIT = 0;
-- 오토커밋 상태를 확인합니다.
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1.2. 세션 A에서 employees 테이블에 emp_no가 1이고 first_name이 Adam인 데이터를 INSERT하여 새로운 레코드를 추가합니다.
mysql> BEGIN;
mysql> INSERT INTO employees VALUES (1, 'Adam');
1.3. 세션 B에서 같은 데이터를 읽습니다. READ UNCOMMITTED 격리 수준에서는 아직 세션 A에서 커밋되지 않은 Adam 사원의 데이터를 읽을 수 있습니다.
mysql> SELECT * FROM employees WHERE first_name = 'Adam';
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Adam |
+--------+------------+
DIRTY READ 현상의 문제는 세션 A에서 INSERT된 Adam을 롤백한다고 하더라도 여전히 세션 B에서 Adam이 정상적인 사원이라고 생각하고 계속 처리할 것이라는 점입니다. DIRTY READ를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준 입니다. 따라서 MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장합니다.
2. READ COMMITTED
오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 이 수준부터는 DIRTY READ가 발생하지 않습니다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문입니다. 정말 그런지 확인해봅시다.
2.1. 격리 수준을 READ COMMITTED로 변경합니다.
mysql> SET PERSIST TRANSACTION ISOLATION LEVEL READ COMMITTED;
2.2. 세션 A에서 트랜잭션을 시작하고 Adam의 first_name을 Eve로 바꿉니다.
mysql> BEGIN;
mysql> UPDATE employees SET first_name = 'Eve' WHERE first_name = 'Adam';
2.3. 세션 A가 커밋을 수행하기 전에 세션 A에서는 변경된 Eve가 조회되지만, 세션 B에서는 Adam이 조회됩니다. 여기서 세션 B의 SELECT 쿼리 결과는 employees 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것입니다.
-- 세션 A
mysql> SELECT * FROM employees WHERE emp_no = 1;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Eve |
+--------+------------+
-- 세션 B
mysql> BEGIN;
mysql> SELECT * FROM employees WHERE emp_no = 1;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Adam |
+--------+------------+
2.4. 최종적으로 세션 A에서 변경된 내용을 커밋하면 그때부터는 세션 B에서도 백업된 언두 레코드(Adam)이 아니라 새롭게 변경된 Eve라는 값을 참조할 수 있게 됩니다.
-- 세션 A
mysql> COMMIT;
-- 세션 B
mysql> SELECT * FROM employees WHERE emp_no = 1;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Eve |
+--------+------------+
READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ라는 부정합 문제가 있습니다. NON-REPEATABLE READ란, 하나의 트랜잭션 내에서 같은 SELECT 쿼리로 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋나는 현상을 말합니다.
2.5. 세션 B에서 Noel을 조회합니다. 일치하는 결과가 없습니다.
-- 세션 B (Empty set)
mysql> SELECT * FROM employees WHERE first_name = 'Noel';
2.6. 세션 A가 Eve를 Noel로 변경하고 커밋을 실행한 후, 세션 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번엔 Noel이 조회됩니다.
-- 세션 A
mysql> UPDATE employees SET first_name = 'Noel' WHERE first_name = 'Eve';
mysql> COMMIT;
-- 세션 B
mysql> SELECT * FROM employees WHERE first_name = 'Noel';
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Noel |
+--------+------------+
이러한 NON-REPEATABLE READ 현상은 일반적으로 문제가 없어보일 수 있지만, 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수도 있습니다.
예를 들어, 다른 트랜잭션에서 입금과 출금 처리가 계속 진행되는 동안 다른 트랜잭션에서 오늘 입금된 금액의 총액을 조회한다고 가정해봅시다. REPEATABLE READ가 보장되지 않기 때문에, 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져오게 됩니다. 중요한 것은 우리가 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지 정확히 예측하지 못한다는 것입니다.
별로 중요하지 않은 차이처럼 보이지만 이런 문제로 데이터의 정합성이 깨지고 그로 인해 애플리케이션에 버그가 발생하면 찾아내기가 쉽지 않습니다.
3. REPEATABLE READ
MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이며, 이 수준부터는 NON-REPEATABLE READ 부정합이 발생하지 않습니다.
READ COMMITTED 격리 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없습니다. 그러나 REPEATABLE READ 격리 수준에서는 기본적으로 SELECT 쿼리 문장도 트랜잭션 범위 내에서만 작동합니다.
REPEATABLE READ는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장합니다.
모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있습니다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없습니다. REPEATABLE READ 격리 수준이 어떻게 작동하는지 확인해 봅시다.
3.1. 격리 수준을 REPEATABLE READ로 변경합니다.
mysql> SET PERSIST TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3.2. 세션 B에서 트랜잭션을 시작한 후, emp_no가 1인 Noel을 조회합니다. 이전에 Noel이 INSERT됐을 때의 트랜잭션 번호를 6이라고 가정하고, 현재의 트랜잭션 번호를 10이라고 가정합니다. (아쉽게도 InnoDB 엔진에서 트랜잭션 번호에 직접 접근하는 방법은 제공되지 않습니다.)
-- 세션 B (TRX-ID: 10)
mysql> BEGIN;
mysql> SELECT * FROM employees WHERE emp_no = 1;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Noel |
+--------+------------+
3.3. 세션 A에서 Noel의 first_name을 Cain으로 변경합니다. 이 때의 트랜잭션 번호를 12라고 가정합니다. 변경된 내용을 커밋합니다.
-- 세션 A (TRX-ID: 12)
mysql> BEGIN;
mysql> UPDATE employees SET first_name = 'Cain' WHERE first_name = 'Noel';
mysql> COMMIT;
3.4. 세션 B에서 다시 emp_no가 1인 데이터를 조회합니다. 이 때 MVCC로 인해 트랜잭션 번호가 6인 언두 로그의 데이터를 조회하게 됩니다.
세션 A 트랜잭션(TRX-ID: 12)의 변경 사항과 관계 없이 세션 B의 트랜잭션(TRX-ID: 10)에서는 항상 Noel만 조회합니다. 세션 B에서 BEGIN 명령으로 트랜잭션을 시작하면서 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게 됩니다.
-- 세션 B (TRX-ID: 10)
-- TRX-ID가 6이던 언두 로그의 데이터를 조회
mysql> SELECT * FROM employees WHERE emp_no = 1;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Noel |
+--------+------------+
3.5. 세션 B의 트랜잭션을 종료하고 다시 조회하게 되면 변경된 이름인 Cain을 조회할 수 있습니다.
-- 세션 B
mysql> COMMIT;
mysql> SELECT * FROM employees WHERE emp_no = 1;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Cain |
+--------+------------+
한 세션에서 BEGIN으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있습니다. 이렇게 언두에 백업된 레코드가 많아지면 자연스럽게 MySQL 서버의 처리 성능이 떨어질 수 있습니다.
REPEATABLE READ 격리 수준에서도 PHANTOM READ라는 부정합 문제가 있습니다. 세션 A에서 employees 테이블에 INSERT를 하는 도중에 세션 B에서 SELECT ... FOR UPDATE 쿼리로 employees 테이블을 조회했을 때 어떤 결과를 가져오는지 확인해 봅시다.
3.6. 세션 B에서 트랜잭션을 시작한 후, emp_no가 1 이상인 데이터를 조회합니다. 이전에 Noel이 UPDATE됐을 때의 트랜잭션 번호는 12였습니다. 현재의 트랜잭션 번호는 15이라고 가정합니다.
-- 세션 B (TRX-ID: 15)
-- TRX-ID가 12이던 언두 로그의 데이터를 조회
mysql> BEGIN;
mysql> SELECT * FROM employees WHERE emp_no >= 1 FOR UPDATE;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Cain |
+--------+------------+
3.7. 세션 A에서 Abel을 INSERT합니다.
-- 세션 A (TRX-ID: 17)
mysql> BEGIN;
mysql> INSERT INTO employees VALUES (2, 'Abel');
mysql> COMMIT;
3.8. 세션 B에서 같은 SELECT 쿼리를 다시 실행합니다. REPEATABLE READ는 두 번의 SELECT 쿼리 결과가 똑같아야 하는데, SELECT ... FOR UPDATE 쿼리 결과가 서로 다릅니다. 이전에 조회되지 않던 Abel까지 조회가 됩니다.
-- 세션 B
-- 언두 영역이 아닌 현재 레코드를 조회함
mysql> SELECT * FROM employees WHERE emp_no >= 1 FOR UPDATE;
+--------+------------+
| emp_no | first_name |
+--------+------------+
| 1 | Cain |
| 2 | Abel |
+--------+------------+
이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 합니다.
SELECT ... FROM UPDATE 쿼리는 SELECT하는 레코드에 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수가 없습니다. 그래서 SELECT ... FOR UPDATE나 SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것입니다.
4. SERIALIZABLE
가장 단순하면서 가장 엄격한 격리 수준입니다. 그만큼 동시 처리 성능도 다른 수준보다 떨어집니다.
읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경할 수 없게 됩니다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다는 것입니다.
이 수준에서는 PHANTOM READ 현상도 일어나지 않습니다. 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요성은 없습니다.
🎯정리
Dirty Read: 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 말합니다.
Non-Repeatable Read: 하나의 트랜잭션 내에서 같은 SELECT 쿼리로 항상 같은 결과를 가져와야 한다는
REPEATABLE READ정합성에 어긋나는 현상을 말합니다.Phantom Read: 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 말합니다. 여기서 팬텀은 검색 기준과 일치하지만 처음에는 보이지 않는 행입니다.
Oracle의 기본 격리 수준은
READ COMMITTED이고, MySQL InnoDB 스토리지 엔진의 기본 격리 수준은REPEATABLE READ입니다.InnoDB 스토리지 엔진은
REPEATABLE READ격리 수준에서도갭 락과넥스트 키 락덕분에PHANTOM READ가 발생하지 않습니다.