[DB] 트랜잭션(2) - Isolation Level

 

<SQL BOOSTER>의 내용을 바탕으로 작성된 개인 공부를 위한 기록용 포스트입니다.

---

들어가기 전에

트랜잭션 Isolation Level은 하나의 트랜잭션에서 작업 중인 데이터가 다른 트랜잭션에 영향을 받지 않는 정도 또는 하나의 트랜잭션에서 작업 중인 데이터를 다른 트랜잭션이 어느 정도까지 접근할 수 있는지에 대한 정도를 말한다. 그렇다면 이 Isolation Level을 왜 사용하는가? 서로 다른 트랜잭션에서 동시에 변경 작업을 수행하게 됐을 때 비정상적으로 데이터가 처리되는 경우가 있기 때문에 이러한 상황을 막기 위해 Isolation Level을 설정한다.

 

Isolation Level은 단계 별로 나뉘어져 있는데 가장 낮은 단계로 설정하면 한 트랜잭션에서 변경 중인 데이터를 다른 트랜잭션에서 접근할 수 있다. 반대로 가장 높은 단계로 설정하면 조회 작업을 할 때도 다른 트랜잭션에서 격리된다. 낮게 설정했을 때는 동시성이 좋아지지만 데이터의 정확성을 확보할 수 없고, 높게 설정했을 때는 동시성이 나빠지지만 데이터의 정확성을 확보할 수 있기 때문에 트레이드 오프가 발생한다.

 

중요한 것은 데이터의 정확도 뿐만 아니라 동시성도 고려해야 할 점이라는 것이다. 그러므로 적절한 단계의 Level을 사용해서 데이터의 정확도를 어느 정도 확보하면서 동시성을 높여야 한다. 트랜잭션에서 불필요한 작업을 제거해서 간결하게 만들고 SQL을 최적화해서 구현한다면 데이터의 정확성과 동시성 모두 확보할 수 있다.

 

SQL 표준에서 정의한 Isolation Level에는 다음과 같은 단계가 있다. 낮은 단계부터 높은 단계 순이다.

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE READ

대부분의 DB가 이 네 가지를 지원한다. 그렇다고 획일화된 방법을 사용하지는 않고 각각의 RDBS에 따라 지원하는 방법이 조금씩 다르기 때문에 공식 문서를 확인해서 적절한 Isolation Level을 선택해야 한다.

 

오라클의 경우에는 READ UNCOMMITTED는 지원하지 않으며 READ COMMITED가 기본 설정값이고,  MySQL의 InnoDB는 REPEATABLE READ가 기본으로 사용된다. SQL server에서는 특이하게도 네 가지 외에도 Snapshot이라는 Level을 하나 더 추가했는데 트랜잭션이 시작될 때 Snapshot을 따로 저장해놓고 데이터의 일관성을 관리하는 방법이다.

 

Isolation Level을 살펴보기 전에 트랜잭션의 이상 현상부터 살펴보도록 하겠다.

 

 

Database anomalies

데이터 불일치가 일어나는 이상 현상을 말한다. 표준에서 정의된 이상 현상 이외에도 여러 가지들이 있지만 이 포스팅에서는 다루지 않는다.

 

1. Dirty Read

COMMIT되지 않은 데이터의 값을 읽었을 때 생기는 문제를 말한다.

1. 트랜잭션 A는 테이블에 행을 삽입한다.
2. 트랜잭션 B는 새 행을 읽는다
3. 트랜잭션 A가 롤백한다.

트랜잭션 B가 트랜잭션 A에서 수행한 결과로 무언가 작업을 했다면 유효하지 않은 데이터를 바탕으로 작업한 것이기 때문에 문제가 발생할 수 있는 것이다. 

 

 

2. Non-repeatable Read(=Fuzzy Read)

하나의 트랜잭션 내에서 같은 데이터를 두 번 읽었을 때 다른 결과를 얻는 문제를 말한다.

1. 트랜잭션 A는 행을 읽는다.
2. 트랜잭션 B가 행을 변경한다.
3. 트랜잭션 A는 동일한 행을 두 번 읽고 새로운 결과를 얻는다.

 

3. Phantom Read

하나의 트랜잭션 내에서 같은 조건으로 두 번 조회했을 때 존재하지 않던 데이터를 얻는 문제를 말한다.

1. 트랜잭션 A는 SQL 쿼리에서 WHERE 절을 충족하는 모든 행을 읽습니다.
2. 트랜잭션 B는 WHERE 절을 만족하는 추가 행을 삽입합니다.
3. 트랜잭션 A는 WHERE 조건을 재평가하고 추가 행을 선택합니다.

 

이러한 현상들을 일부 허용하거나 허용하지 않거나에 따라 Isolation Level이 나뉘어지게 되는 것이다. 

출처:&nbsp;https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/

 

 

Isolation Levels

1. READ UNCOMMITED

한 트랜잭션에서 다른 트랜잭션이 아직 커밋하지 않은 변경사항을 읽을 수 있다.

 

예를 들어, 첫 번째 세션에서 계좌 테이블의 'ACC1' 계좌의 잔액을 2500원에서 5000원으로 변경하고 COMMIT을 하지 않았다고 하자. 두 번째 세션에서 조회를 한다면 어떤 결과가 조회될까? COMMIT되지 않았으나 변경된 잔액 값인 5000원이 조회된다. Dirty Read가 가능한 것이다.

 

나머지 이상 현상에 대해서도 제한하지 않았으므로 위의 표와 같이 세 가지 이상 현상이 모두 발생할 수 있다.

 

2. READ COMMITTED

말그대로 COMMIT된 데이터만 읽을 수 있는 것을 말한다. 프로그램 각각 따로 띄워서 다른 세션으로 동일한 테이블에 접근을 해서 과정을 살펴보겠다.

 

1) UPDATE - SELECT 테스트

우선 첫 번째 세션에서 계좌 테이블의 'ACC1' 계좌의 잔액을 2500원에서 5000원으로 변경하고 COMMIT이나 ROLLBACK은 하지 않았다고 해보자.

UPDATE m_acc T1
SET T1.bal_amt = 5000
WHERE T1.acc_no = 'ACC1';

 

두 번째 세션에서 잔액을 확인하면 변경된 잔액이 나올까? 아니다. 첫 번째 세션에서 아직 COMMIT을 하지 않았기 때문에 COMMIT을 하기 전까지는 계속해서 2500원이라는 잔액을 확인할 수 있을 것이다. Dirty Read를 허용하지 않는 것을 의미한다.

 

 

2) UPDATE - UPDATE 테스트

두 세션이 동시에 같은 데이터를 UPDATE 한다면 어떻게 될까?

 

첫 번째 세션에서 'ACC1' 계좌의 잔액을 5000원에서 4500원으로 변경했다. 그리고 두 번째 세션에서 잔액을 확인하고 데이터를 변경하는 작업을 수행하려고 한다.

SELECT * FROM m_acc T1
WHERE T1.acc_no = 'ACC1';

UPDATE m_acc T1
SET T1.bal_amt = T1.bal_amt - 500
WHERE T1.acc_no = 'ACC1';

우선 SELECT 문을 먼저 보겠다. 첫 번째 세션에서 아직 COMMIT을 안 했기 때문에 변경하기 이전의 값인 5000원이 조회될 것이다. 그리고 UPDATE 문은 첫 번째 세션에 막혀서 진행되지 못한다. 이때 쿼리문은 계속 실행 중인 상태를 유지하게 되는데 이것을 '대기 상태'라고 한다. 먼저 실행되고 있던 트랜잭션이 데이터를 변경하고 있어서 그 다음으로 실행되는 트랜잭션이 데이터에 접근하지 못하고 마냥 기다리게 되는 것을 말하는데, 이 상태가 길어지면 데이터베이스의 동시성이 나빠진다.

 

첫 번째 세션이 COMMIT을 하면 COMMIT 동작이 완료되자마자 두 번째 세션의 UPDATE 문 실행이 완료될 것이다. 이렇게 대기 상태에 접어들었다가 작업을 추후에 완료하는 것은 Dirty Write를 허용하지 않는다는 것을 의미한다.

 

 

3) INSERT - INSERT 테스트

만약 'ACC4'라는 계좌 정보를 두 트랜잭션에서 모두 INSERT한다고 가정해보자. 계좌 번호 컬럼은 PK로 지정된 컬럼이기 때문에 두 번째 세션은 대기 상태에 빠졌다가 첫 번째 세션에서 COMMIT을 하면 중복 에러가 발생하고, ROLLBACK을 하면 INSERT 작업이 성공한다.

 

또 다른 테스트로 첫 번째 세션에서는 'ACC5', 두 번째 세션에서는 'ACC99'을 INSERT한다면 어떨까? 첫 번째 세션에서 COMMIT을 하지 않았더라도 두 번째 세션의 작업은 대기 상태에 빠지지 않고 바로 처리된다. 앞선 트랜잭션의 데이터와 중복되지 않기 때문이다. 이때 만약 중복된 'ACC5'를 INSERT하려고 한다면 그때 앞의 테스트처럼 대기 상태에 빠질 것이다.

 

4) 특징

• 한 트랜잭션이 변경 중인 데이터는 다른 트랜잭션에서 변경 전 데이터만 조회할 수 있다.
  • 변경 중 데이터 : UPDATE, DELETE, INSERT 후에 COMMIT이나 ROLLBACK 되지 않은 데이터
• 한 트랜잭션이 변경 중인 데이터는 다른 트랜잭션에서 동시에 변경할 수 없다.
  • 늦게 변경 작업을 시도한 세션은 대기 상태에 빠지게 된다.
• UPDATE가 대기 상태에 빠지면 선행 트랜잭션의 처리에 따라 UPDATE 결과가 다르다.
• 같은 키(Primary Key와 Unique Key) 값을 가진 데이터가 동시에 입력되면 후행 트랜잭션은 대기 상태에 빠진다.
  • 선행 트랜잭션의 처리에 따라 후행 트랜잭션은 중복 에러가 발생할 수 있다.
• 에러가 발생해도 트랜잭션 전체가 자동 ROLLBACK 되지 않는다.

 

3. REPEATABLE READ

트랜잭션이 시작되기 전에 COMMIT된 내용에 대해서만 조회할 수 있다. 하나의 트랜잭션 안에서는 동일한 결과를 보여주도록 해서 READ COMMITTED에서 발생하는 Non-repeatable Read를 방지할 수 있다. Undo log에 저장된 데이터 중에 실행 중인 트랜잭션의 선행 트랜잭션에서 변경한 데이터만 보여주기 때문이다.

 

 

4. SERIALIZABLE READ

다른 트랜잭션으로 인한 이상 현상이 일어나지 않도록 완전히 격리한 것을 말한다. 한 트랜잭션이 조회나 변경 작업을 하는 동안 다른 트랜잭션은 아예 접근하지 못하기 때문에 앞서 말한 동시성이 나빠진다.  

 

 

 

 

참고

15.7.2.1 Transaction Isolation Levels

Transaction Isolation Levels

[쉬운 코드] transaction isolation level

Understanding Isolation Levels in a Database Transaction

[데이터베이스] MySQL 트랜잭션 격리 수준