[DB] 트랜잭션 격리수준 (Transaction Isolation Level)

1. 트랜잭션 격리 수준 (transaction isolation level)

트랜잭션의 격리 수준이란 동시에 여러 건의 트랜잭션이 수행되는 경우, 트랜잭션들이 서로 얼마나 고립되어 있는지를 나타내는 정도이다. 간단하게 특정 트랜잭션이 다른 트랜잭션으로 인해 변경된 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다.

 

격리 수준은 크게 아래의 4가지로 구분된다.

 

- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE

 

아래로 내려갈수록 고립 정도가 높아지고, 성능이 떨어진다. 일반적인 온라인 서비스에서는 READ COMMITED 나 REPEATABLE READ 를 사용한다.

2. READ UNCOMMITED

READ UNCOMMITED 레벨에서는 어떤 트랜잭션의 변경내용이 COMMIT 이나 ROLLBACK 이 수행되었는지와 상관없이 트랜잭션 작업의 결과가 다른 트랜잭션에서 보여진다. 이 경우 특정 트랜잭션의 작업이 ROLLBACK 되는 경우 ROLLBACK 이전에 데이터에 접근한 트랜잭션에서는 ROLLBACK 되기 전의 데이터를 통해 로직이 수행되면서 트랜잭션 간의 정합성이 맞지 않게 된다.

 

- 잔고: 10000 원

1. A 트랜잭션에서 잔고에서 1000 원을 더한다.
2. A 트랜잭션의 결과가 COMMIT되기 전에 B 트랜잭션에서 잔고를 조회한다.
-> 11000 원이 조회된다. (Dirty Read 라고 한다.)
3. A 트랜잭션이 ROLLBACK 된다.
4. B 트랜잭션은 잔고를 11000 원으로 생각하고 작업을 수행한다.
-> 잔고가 11000 - 2000 = 9000 원 으로 계산된다.

 

격리 수준 READ UNCOOMMITED 의 경우 위와 같이 각 트랜잭션 간의 정합성이 맞지않는 경우가 발생한다.

3. READ COMMITED

READ COMMITED 는 어떤 트랜잭션의 작업 내용이 COMMIT 된 경우 다른 트랜잭션에서 조회할 수 있도록 하는 것이다. 일반적으로 DBMS 에서 기본 설정으로 사용하고 있고, 온라인 서비스에서 가장 많이 쓰이는 격리 수준이다.

 

위의 예제에서 격리 수준이 READ COMMITED 인 경우, B 트랜잭션에서 잔고로 11000 원이 아닌 10000 원이 조회된다. 이로 인해 정합성 문제가 해결된 거 같지만, READ COMMITED 에서는 NON-REPEATABLE READ 정합성 문제가 발생한다.

 

- 잔고: 10000 원

1. A 트랜잭션에서 잔고에 1000 원을 더한다.
2. B 트랜잭션에서 잔고를 조회.
-> 10000 원이 조회된다.
3. A 트랜잭션이 COMMIT 된다.
4. B 트랜잭션에서 잔고를 재조회한다.
-> 11000 원이 조회된다.

 

위의 경우 한 트랜잭션에서 동일한 조건의 SELECT 를 하는 경우 동일한 결과를 반환해야 한다는 REPEATABLE READ 정합성에 어긋난다.

 

일반적인 웹 애플리케이션에서는 문제가 되지 않을 수 있지만, 금융 서비스의 경우에는 문제가 발생할 수 있다. 예를 들어 입출금이 실행되는 트랜잭션 들이 수행중이고, 입금의 합을 구하는 트랜잭션이 있는 경우, 입출금 트랜잭션의 작업에 따라 입금의 합을 구하는 트랜잭션의 SELECT 쿼리의 결과는 실행될 때마다 달라지게 된다.

4. REPEATABLE READ

격리 수준 REPEATABLE READ 은 트랜잭션이 시작되기 전에 커밋된 내용에 대해서만 조회할 수 있는 격리 수준이다. MySQL 에서 기본 격리 수준으로 사용하고 있으며, 이 격리 수준에서는 NON-REPEATABLE READ 정합성 문제가 발생하지 않는다.

 

MySQL 에서는 트랜잭션마다 번호를 두고 자신 보다 더 낮은 번호의 트랜잭션에서 COMMIT 된 결과만 조회하도록 한다.

 

트랜잭션을 실행하기 전에 변경되기 전의 값을 Undo 영역에 백업해두고 실제 레코드 값을 변경한다. 이때 Undo 영역에 저장되는 데이터는 변경을 발생시킨 트랜잭션의 번호를 같이 저장하여서 이후에 조회 작업 발생 시에 트랜잭션 번호를 비교하여 실제 레코드 데이터 또는 Undo 영역의 데이터를 반환한다.

 

- 잔고: 10000 원

1. 1번 트랜잭션에서 잔고 조회.
-> 10000 원 조회
2. 2번 트랜잭션에서 잔고에 1000 원을 더한다.
3. 1번 트랜잭션에서 잔고를 재조회.
-> 10000 원 조회 (Undo 영역에서 백업된 데이터를 반환한다.)

 

Undo 영역에 백업된 데이터가 많아지는 경우에 DB 서버의 처리 성능이 떨어질 수 있다. 백업된 데이터는 불필요하다고 판단되는 시점에 주기적으로 삭제된다.

- REPEATABLE READ 에서 발생할 수 있는 데이터 부정합

1) UPDATE 부정합 

START TRANSACTION; -- transaction id : 1
SELECT * FROM Member WHERE name='A';

    START TRANSACTION; -- transaction id : 2
    SELECT * FROM Member WHERE name = 'A';
    UPDATE Member SET name = 'B' WHERE name = 'A';
    COMMIT;

UPDATE Member SET name = 'C' WHERE name = 'A'; -- 0 row(s) affected
COMMIT;

 

이 경우 최종 결과로 모든 A 값을 가지는 name 필드는 B 로 변경된다.

2번 트랜잭션이 수행되게 되면 모든 name='A' 인 데이터들은 값이 'B' 로 변경되고, 그 전의 값 name='A' 는 Undo 영역에 저장된다.

 

2번 트랜잭션이 수행된 후에, 1번 트랜잭션의 UPDATE 문이 수행될 때 실제 레코드의 값이 아닌 Undo 영역의 값을 조회하게 된다. 이때 UPDATE 문에서 쓰기를 실행하는데, Undo 영역에서는 쓰기를 실행할 수 없다. 그렇기 때문에 실제 레코드에서 쓰기를 실행하기 위해 시도한다. 하지만 실제 레코드 영역에는 name='A' 인 값이 없기 때문에 0 row(s) affected 가 출력되고, 아무런 변경도 발생하지 않는다.

 

DML 구문은 멀티 비전을 관리하지 않는다.

2) PHANTOM READ

다른 트랜잭션에서 수행한 변경 내용에 대한 레코드가 보였다가 안보였다가 하는 현상이다.

 

START TRANSACTION; -- transaction id : 1
SELECT * FROM Member; -- 0 row(s) affected

    START TRANSACTION; -- transaction id : 2
    INSERT INTO Member VALUES(1, 'A');
    COMMIT;

SELECT * FROM Member; -- 0 row(s) affected
UPDATE Member SET name='C' WHERE id=1; -- 1 row(s) affected : (1, 'A') -> (1, 'C')

SELECT * FROM Member; -- (1, 'C') 조회
COMMIT;

 

한 트랜잭션에서 여러번 SELECT 쿼리를 수행할 때 보이지 않았던 레코드가 다음 쿼리에서는 보이게 된다.

REPEATABLE READ 에서는 조회되지 않아야 하는데, UPDATE 문으로 인하여 2번 트랜잭션에서 추가된 레코드가 변경된 후에는 조회가 된다.

5. SERIALIZABLE

가장 단순하고 가장 엄격한 격리수준이다.

InnoDB에서 기본적으로 순수한 SELECT 작업은 아무런 잠금을 걸지않고 동작하는데, 격리수준이 SERIALIZABLE일 경우 읽기 작업에도 공유잠금을 설정하게 되고, 이러면 동시에 다른 트랜잭션에서 이 레코드를 변경하지 못하게 된다.

이러한 특성 때문에 동시처리 능력이 다른 격리수준보다 떨어지고, 성능저하가 발생하게 된다.

실제 온라인 서비스에서는 많이 사용되지 않는다.

[Reference]

- https://joont92.github.io/db/%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EA%B2%A9%EB%A6%AC-%EC%88%98%EC%A4%80-isolation-level/

[db] 트랜잭션 격리 수준(isolation level)

- https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation

트랜잭션의 격리 수준(isolation Level)이란?