DB의 트랜잭션 격리 수준(isolation level)

Hansu Kim·2021년 12월 29일

Database PostgreSQL db isolation level mysql

Database

목록 보기

2/6

MVCC - MultiVersion Concurrency Control

MVCC는 동시성 제어이다. 동시성 제어는 그 자체로도 매우 깊고 다양화되어있지만, 트랜잭션별 데이터 잠금 관점에서 크게 4가지로 분류된다.

Read Uncommitted
Read Committed
Repeatable Read
Serializable

위에서 아래로 갈 수록 트랜잭션 간 isolation level이 높아지며, 성능이 떨어진다.

1. Read Uncommitted

어떤 트랜잭션의 변경내용이 commit이나 rollback에 상관없이 다른 트랜잭션에서 보여진다.

잠재적 문제: Dirty Read 발생 가능

예시

1. A 트랜잭션에서 10번 사원의 나이를 27살에서 28살로 바꿈
2. 아직 커밋하지 않음
3. B 트랜잭션에서 10번 사원의 나이를 조회함
4. 28살이 조회됨
5. A 트랜잭션에서 문제가 발생해 ROLLBACK함
6. B 트랜잭션은 10번 사원이 여전히 28살이라고 생각하고 로직을 수행함

데이터 정합성 문제로 인해, RDBMS 표준에선 격리수준으로 인정하지도 않는다.

2. Read Committed

트랜잭션의 변경 내용이 Commit되어야만 다른 트랜잭션에서 조회할 수 있다. (Oracle DBMS, PostgreSQL의 Default, 가장 많이 선택되는 격리 수준)

잠재적 문제: Non-Repeatable Read 부정합 문제

예시

1. B 트랜잭션에서 10번 사원의 나이를 조회
2. 27살이 조회됨
3. A 트랜잭션에서 10번 사원의 나이를 27살에서 28살로 바꾸고 커밋
4. B 트랜잭션에서 10번 사원의 나이를 다시 조회
5. 28살이 조회됨

이는 Repeatable Read 정합성에 어긋나며, 어플리케이션 레벨에서 해당 문제에 대해 인지하고, 필요한 경우 보완 로직을 추가해주어야 한다.

Repeatable Read 정합성: 하나의 트랜잭션 내에서 같은 SELECT문 수행시, 항상 같은 결과를 반환해야 한다.

3. Repeatable Read

트랜잭션이 시작되기 전에 커밋된 내용에 대해서만 조회할 수 있는 격리 수준이다. (MySQL/InnoDB의 Default)

1. 10번 트랜잭션이 500000번 사원을 조회
2. 12번 트랜잭션이 500000번 사원의 이름을 변경하고 커밋
3. 10번 트랜잭션이 500000번 사원을 다시 조회
4. 언두 영역에 백업된 데이터 반환

잠재적 문제

1) UPDATE 부정합

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

    START TRANSACTION; -- transaction id : 2
    SELECT * FROM Member WHERE name = 'junyoung';
    UPDATE Member SET name = 'joont' WHERE name = 'junyoung';
    COMMIT;
    
UPDATE Member SET name = 'zion.t' WHERE name = 'junyoung'; -- 0 row(s) affected
COMMIT;

이 상황에서 최종 결과는 name = joont가 된다.
REPETABLE READ이기 때문에,
2번 트랜잭션에서 name = joont로 변경하고 COMMIT을 하면 name = junyoung의 내용을 언두로그에 남겨놔야 한다.
그래야 1번 트랜잭션에서 일관되게 데이터를 보는 것을 보장해줄 수 있기 때문이다.
이 상황에서 아래 구문에서 UPDATE 문을 실행하게 되는데, UPDATE의 경우 변경을 수행할 로우에 대해 잠금이 필요하다.
하지만 현재 1번 트랜잭션이 바라보고 있는 name = junyoung 의 경우 레코드 데이터가 아닌 언두영역의 데이터이고,
언두영역에 있는 데이터에 대해서는 쓰기 잠금을 걸 수가 없다.

그러므로 위의 UPDATE 구문은 레코드에 대해 쓰기 잠금을 시도하려고 하지만 name = junyoung인 레코드는 존재하지 않으므로,
0 row(s) affected가 출력되고, 아무 변경도 일어나지 않게 된다.
그러므로 최종적으로 결과는 name = joont가 된다.

2) Phantom Read

한 트랜잭션 내에서 같은 쿼리를 두 번 실행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말한다.
REPETABLE READ 이하에서만 발생하고(SERIALIZABLE은 발생하지 않음), INSERT에 대해서만 발생한다.
아래와 같은 상황에서 재현될 수 있다.

START TRANSACTION; -- transaction id : 1 
SELECT * FROM Member; -- 0건 조회

    START TRANSACTION; -- transaction id : 2
    INSERT INTO MEMBER VALUES(1,'joont',28);
    COMMIT;

SELECT * FROM Member; -- 여전히 0건 조회 
UPDATE Member SET name = 'zion.t' WHERE id = 1; -- 1 row(s) affected
SELECT * FROM Member; -- 1건 조회 
COMMIT;

REPETABLE READ에 에 의하면 원래 출력되지 않아야 하는데 UPDATE 문의 영향을 받은 후 부터 출력된다.
이 시점에 스냅샷을 적용시키는 것 같다.

참고로 DELETE에 대해서는 적용되지 않는다.

START TRANSACTION; -- transaction id : 1 
SELECT * FROM Member; -- 1건 조회

    START TRANSACTION; -- transaction id : 2
    DELETE FROM Member WHERE id = 1;
    COMMIT;

SELECT * FROM Member; -- 여전히 1건 조회 
UPDATE Member SET name = 'zion.t' WHERE id = 1; -- 0 row(s) affected
SELECT * FROM Member; -- 여전히 1건 조회 
COMMIT;

4. Serializable

가장 단순하고 가장 엄격한 격리수준이다.
InnoDB에서 기본적으로 순수한 SELECT 작업은 아무런 잠금을 걸지않고 동작하는데,
격리수준이 SERIALIZABLE일 경우 읽기 작업에도 공유 잠금을 설정하게 되고, 이러면 동시에 다른 트랜잭션에서 이 레코드를 변경하지 못하게 된다.
이러한 특성 때문에 동시처리 능력이 다른 격리수준보다 떨어지고, 성능저하가 발생하게 된다.