DEV ℧ Developer Diary

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

25 Feb 2023

트랜잭션의 격리 수준 (Isolation Level)

이전 포스트에서 트랜잭션의 특징중 격리수준에 대해 정리해 보도록 하자

트랜잭션의 격리수준

트랜잭션의 격리수준이란, Transaction 에서 정리했던 특징중 I에 해당하는 독립성의 정도를 나타낸다.

원칙상 트랜잭션의 격리수준은 높아야 하지만, 현실적으로 성능 문제로 인해 유연하게 조절 할 수 있도록 격리 수준을 구분해 놓은 것을 의미한다.

격리 수준이 낮아질 수록 수준은 향상되지만, 동시성 제어(Race Condition)이 되지 않아 문제가 발생 할 수 있다.

DBMS에서는 다양한 Lock 기법을 활용해 트랜잭션의 격리수준에 맞춰 데이터 접근을 통제한다.

트랜잭션의 격리수준에 따라 발생할 수 있는 현상을 표로 정리하면 아래와 같다.

트랜잭션 격리 수준 Dirty Read Unrepeatable Read Phantom Data Read
READ UNCOMMITED O O O
READ COMMITTED   O O
REPEATABLE READ     O
SERIALIZABLE      
  • Dirty Read : 트랜잭션이 아직 커밋되지 않은 데이터를 읽을 때 발생하며, 예를 들어 트랜잭션 1이 행을 업데이트한다고 가정할 시 트랜잭션 2는 트랜잭션 1이 업데이트를 커밋하기 전에 업데이트 된 행을 읽을 수있다.
  • Unrepeatable Read : 트랜잭션이 동일한 행을 두번 읽지만 매번 다른 데이터를 가져오는 경우 해당 현상이 발생할 수 있다. 예를 들어 트랜잭션 1이 행을 읽는다고 가정할 때, 트랜잭션 2는 해당 행을 업데이트하거나 삭제하고 업데이트 또는 커밋을 한다. 만약 트랜잭션 1이 행을 다시 읽는 경우 다른 행 값을 검색하거나 해당 행을 찾을 수 없는 현상을 말한다.
  • Phantom Data Read : 검색조건과 일치하지만 처음에는 표시되지 않는 행입니다. 예를 들어 트랜잭션 1이 일부 검색조건을 충족하는 행 집합을 읽을때, 트랜잭션 2가 트랜잭션 1과 검색 조건이 일치하는 새로운 행을 생성할 경우 트랜잭션 1이 행을 읽는 쿼리를 다시 실행할때 다른 행 집합을 가져오게 되는 현상을 말한다.

트랜잭션의 격리수준을 실제 쿼리의 결과를 확인해보며 정리해보자.

테스트

환경 : MySql 8.0.32 IDE : Datagrip

테스트를 진행할 테이블은 다음과 같다.

CREATE TABLE STOCK (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    quantity INT(3)
);

INSERT INTO STOCK (id, name, quantity) values (1, 'A', 10);
INSERT INTO STOCK (id, name, quantity) values (2, 'B', 5);

DB1

Database에서 Transaction의 격리수준은 아래의 쿼리를 통해 확인 할 수 있다.

SHOW VARIABLES LIKE 'transaction_isolation';

READ UNCOMMITED

트랜잭션에서 처리 중이거나, 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.

READ UNCOMMITED 예시

Transaction1

UPDATE STOCK
   SET quantity = 11
 WHERE id = 1;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

Transaction1

ROLLBACK;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

Transaction1에서 Commit이나 Rollback을 진행하지 않아도, Transaction2에서 UPDATE로 변경된 쿼리가 접근이 가능하다.

  1. Commit 되지 않은 데이터도 조회가 가능하다(dirty read O)
  2. 동일한 트랜잭션에서 동일한 SELECT 쿼리의 결과가 다를 수 있다.(non-repeatable read O)
  3. 이전에 없던 SELECT 쿼리의 결과가 생길 수 있다.(Phantom read O)

잘 사용되지 않는 격리수준으로 알아만 두도록 하자.

READ COMMITTED

트랜잭션은 다른 트랜잭션이 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회가 가능하다. 오라클의 기본 격리 수준이며, 현재 많이 사용되고 있는 격리 수준이다.

READ COMMITTED 예시

Transaction1

SELECT 'TRANSACTION1', id, name, quantity FROM STOCK;

DB1

UPDATE STOCK
   SET quantity = 11
 WHERE id = 1;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

Transaction1

COMMIT;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

  1. Commit 되지 않은 데이터는 조회가 불가능하다(dirty read X)
  2. 동일한 트랜잭션에서 동일한 SELECT 쿼리의 결과가 다를 수 있다.(non-repeatable read O)
  3. 이전에 없던 SELECT 쿼리의 결과가 생길 수 있다.(Phantom read O)

가장 많이 사용되는 격리수준이다.

REPEATABLE READ

READ COMMIT와 차이점은 UNDO 영역에 백업된 레코드의 여러 버전 가운데 몇번째 이전 버전까지 찾아 들어가냐가 다르다. Mysql innodb의 기본 격리 수준이다.

REPEATABLE READ 예시

Transaction1

SELECT 'TRANSACTION1', id, name, quantity FROM STOCK;

DB1

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

Transaction1

UPDATE STOCK
   SET quantity = 11
 WHERE id = 1;

COMMIT;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

COMMIT;

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

  1. Commit 되지 않은 데이터는 조회가 불가능하다(dirty read X)
  2. 동일한 트랜잭션에서 동일한 SELECT 쿼리의 결과는 동일하다.(non-repeatable read X)
  3. 이전에 없던 SELECT 쿼리의 결과가 생길 수 있다.(Phantom read O)

가장 대중적인 격리 수준이다.

SERIALIZABLE

가장 엄격한 격리수준이다. 하지만 동시처리성능이 가장 떨어진다.

SERIALIZABLE 예시

Transaction1

SELECT 'TRANSACTION1', id, name, quantity FROM STOCK;

DB1

UPDATE STOCK
   SET quantity = 15
 WHERE id = 1;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

basic> SELECT 'TRANSACTION2', id, name, quantity FROM STOCK
[2023-02-25 17:46:19] 0 rows retrieved in 50 s 38 ms (execution: 50 s 22 ms, fetching: 16 ms)
[2023-02-25 17:46:19] [40001][1205] Lock wait timeout exceeded; try restarting transaction

UPDATE 쿼리에 대한 COMMIT이 없자 Transaction2에서는 LOCK이 걸려 쿼리가 실행되지 않은 것을 볼 수 있다.

Transaction1

COMMIT;

Transaction2

SELECT 'TRANSACTION2', id, name, quantity FROM STOCK;

DB1

  1. Commit 되지 않은 데이터는 조회가 불가능하다(dirty read X)
  2. 동일한 트랜잭션에서 동일한 SELECT 쿼리의 결과는 동일하다.(non-repeatable read X)
  3. 이전에 없던 SELECT 쿼리의 결과가 생길 수 있다.(Phantom read X)

하지만 동시처리성능이 제일 떨어지는 격리 수준이다.

참고 : https://learn.microsoft.com/ko-kr/sql/odbc/reference/develop-app/transaction-isolation-levels?view=sql-server-ver16

Related posts