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

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

트랜잭션의 격리 수준(Isolation Level)이란 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 여부를 결정하는 것이다.

트랜잭션 격리 수준은 격리(고립) 수준이 낮은 순서대로 READ UNCOMMITED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE가 존재한다.

 

• READ UNCOMMITED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

 참고로 아래의 예제들은 모두 자동 커밋(AUTO COMMIT)이 false인 상태에서만 발생한다.

 

1️⃣ READ UNCOMMITED (Level 0)

READ UNCOMMITTED는 다른 트랜잭션의 커밋하지 않은 데이터에 접근할 수 있는 격리 수준이다.

READ UNCOMMITTED에서는 다른 트랜잭션의 작업이 커밋 또는 롤백되지 않아도 즉시 보이게 된다.

 

 

1. 월급 수정 트랜잭션에서 김가나 사원의 월급을 250만원에서 300만원으로 수정하는 트랜잭션이 시작됐다.

3. 월급 조회 트랜잭션에서 김가나 사원의 월급을 조회했다.

4. READ UNCOMMITTED에서는 커밋되지 않은 데이터를 확인할 수 있기 때문에 김가나 사원의 월급을 300만원으로 조회했다.

5. 월급 수정 트랜잭션에서 어떠한 문제가 발생해 ROLLBACK 됐다.

6. 조금 뒤에 다시 김가나 사원의 월급 조회를 해보니 250만원으로 조회된다.

 

시각	월급 수정 트랜잭션	월급 조회 트랜잭션
09:37:00	트랜잭션 BEGIN
09:37:01	김가나 사원의 월급을 300만원으로 UPDATE
09:37:02		트랜잭션 BEGIN
09:37:03		김가나 사원 월급 조회: 300만원
09:37:04		트랜잭션 COMMIT
09:37:05	트랜잭션 ROLLBACK

DB에서 김가나 사원의 월급이 300만원이 된 적이 없는데 09시 37분 3초에 월급 조회 트랜잭션은 커밋되지 않은 데이터인 300만원을 읽어갔다.

→ dirty read 발생

 

커밋되지 않은 데이터를 조회해서 생기는 문제와 같이 dirty read한 데이터를 이용해 DB를 UPDATE 했다면 더 큰 문제가 일어날 것이다.

 

이처럼 READ UNCOMMITTED 격리수준은 데이터 정합성 문제를 가지고 있어서 RDBMS 표준에서는 격리 수준으로 인정되지 않는다.

 

2️⃣ READ COMMITTED (Level 1)

READ COMMITTED는 커밋된 데이터만 조회할 수 있다.

이 격리 수준은 Oracle, PostgreSQL 등 많은 RDBMS의 default 격리 수준이다.

READ COMMITTED은 READ UNCOMMITTED 격리수준에서 발생했던 dirty read 문제가 발생하지 않는다.

 

시각	월급 수정 트랜잭션	월급 조회 트랜잭션
09:37:00	트랜잭션 BEGIN
09:37:01	김가나 사원 월급 조회: 250만원
09:37:02	김가나 사원의 월급을 300만원으로 UPDATE	트랜잭션 BEGIN
09:37:03		김가나 사원 월급 조회: 250만원
09:37:04	트랜잭션 COMMIT
09:37:05		김가나 사원 월급 조회: 300만원
09:37:06		트랜잭션 COMMIT

 

월급 조회 트랜잭션에서 김가나 사원의 월급을 조회하는 똑같은 쿼리를 두 번 실행했을 때 첫 번째는 250만원, 두 번째는 300만원이 조회된다.

→ Non-Repeatable Read 발생

이렇게 같은 트랜잭션 내에서 같은 쿼리를 여러번 실행했을 때 같은 row의 값이 서로 다른 것을 Non-Repeatable Read(반복 읽기 불가능)이라고 한다.

 

3️⃣ REPEATABLE READ (Level 2)

REPEATABLE READ는 MVCC를 통해 트랜잭션이 시작되기 전에 커밋된 데이터만 조회할 수 있다.

한 트랜잭션 내에서 한 번 읽은 데이터는 바뀌지 않는것을 보장하기 때문에 Non-Repeatable Read 문제가 발생하지 않는다.

일반적인 RDBMS는 변경 전의 레코드를 undo 공간에 백업해둔다. 그러면 변경 전/후 데이터가 모두 존재하므로, 동일한 레코드에 대해 여러 버전의 데이터가 존재한다고 하여 이를 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어)라고 부른다. MVCC를 통해 트랜잭션이 롤백된 경우에 데이터를 복원할 수 있을 뿐만 아니라, 서로 다른 트랜잭션 간에 접근할 수 있는 데이터를 세밀하게 제어할 수 있다. 각각의 트랜잭션은 순차 증가하는 고유한 트랜잭션 번호가 존재하며, 백업 레코드에는 어느 트랜잭션에 의해 백업되었는지 트랜잭션 번호를 함께 저장한다. 그리고 해당 데이터가 불필요해진다고 판단하는 시점에 주기적으로 백그라운드 쓰레드를 통해 삭제한다.

→ MVCC를 통해 한 트랜잭션 내 동일 결과 보장

 

이 격리 수준은 MySQL(MariaDB)의 default 격리 수준이다.

하지만 REPEATABLE READ는 새로운 레코드의 추가되는 경우에 부정합이 생길 수 있다.

기본적으로는 트랜잭션이 시작되기 전에 커밋된 데이터만 조회하지만 새로운 레코드의 추가까지는 막지 않기 때문에 트랜잭션이 끝나기 전에 다른 트랜잭션에 의해 추가된 레코드가 발견될 수 있는데, 이를 유령 읽기(Phantom Read)라고 한다.

 

🤔 언제 Phantom Read가 발생하는 걸까?

 

시각	월급이 300 이상인 직원 조회 트랜잭션	월급 350인 신입사원 row 삽입 트랜잭션
09:37:00	트랜잭션 BEGIN
09:37:01	월급 300 이상 직원 조회: 1명 SELECT WHERE salary >= 300 FOR UPDATE	트랜잭션 BEGIN
09:37:02		박다라 사원 삽입: 월급 300만원
09:37:03		트랜잭션 COMMIT
09:37:04	월급 300 이상 직원 조회: 2명 SELECT WHERE salary >= 300 FOR UPDATE !!! Phantom Read 발생!!!
09:37:05	트랜잭션 COMMIT

 

월급이 300 이상인 직원을 조회할 때 SELECT FOR UPDATE를 이용해 월급이 300 이상인 직원들에 대해 쓰기 잠금을 걸었다.

해당 구문은 베타적 잠금(비관적 잠금, 쓰기 잠금)을 거는 것이다. (나 데이터 수정하려고 SELECT 하는 중이니까 다른사람들은 데이터에 손대지마!!~) 잠금 있는 읽기는 테이블에 변경이 일어나지 않도록 테이블에 잠금을 걸고 undo 로그가 아닌 테이블에서 데이터를 조회한다. 락은 트랜잭션이 커밋 또는 롤백 될 때 해제된다.

 

월급이 350만원인 박다라 사원을 삽입할 때 일반적인 DBMS는 갭락이 존재하지 않으므로 salary >= 300 인 레코드만 잠금이 걸린 상태이다. 따라서 신입사원 삽입 요청은 잠금 없이 즉시 실행된다.

 

그 후 월급 300 이상 직원 조회 트랜잭션이 동일한 쓰기 잠금으로 데이터를 다시 한번 조회하면, 이번에는 2건의 데이터가 조회된다.

이렇듯 다른 트랜잭션에서 수행한 작업에 의해 레코드가 보였다 안보였다 하는 현상을 Phantom Read 라고 한다.

 

대부분의 RDBMS는 REPEATABLE READ 격리 수준에서 Phantom Read 현상이 발생하지만 MySQL(MariaDB)은 다른 RDBMS와 다르게 특수한 Gap Lock이 존재해 동작이 다른 부분이 있어 일반적으로 Phantom Read가 발생하지 않는다.

🤧 MySQL의 Gap Lock

레코드와 레코드 사이의 간격을 잠궈 레코드의 생성, 수정 및 삭제를 제어한다.

예를 들어 현재 성이 J로 시작하는 레코드가 Jo, Joe 2개가 있다고 하자. 그리고 언제든지 다른 데이터들 ex) Jang, Jeong, Jung 이 추가될 수 있다. 따라서 현재 트랜잭션에서 조회를 할 때, 다른 트랜잭션에서 임의의 데이터가 추가되지 않도록 잠그려면 

SELECT * FROM member WHERE last_name LIKE "J%" FOR UPDATE;         // 쓰기 잠금(베타락)
SELECT * FROM member WHERE last_name LIKE "J%" LOCK IN SHARE MODE; // 읽기 잠금(공유락)

위와 같은 쿼리를 실행해야 한다.

여기서 SELECT … FOR UPDATE 구문은 베타적 잠금(비관적 잠금, 쓰기 잠금)을 거는 것이다.

읽기 잠금을 걸려면  LOCK IN SHARE MODE 구문을 사용해야 한다. 락은 트랜잭션이 커밋 또는 롤백될 때 해제된다. 

 

REPEATABLE READ의 Phantom Read와 관련해 도움이 되었던 블로그를 첨부한다.

[MySQL] 스토리지 엔진 수준의 락의 종류(레코드 락, 갭 락, 넥스트 키 락, 자동 증가 락)

 

4️⃣ SERIALIZABLE

SERIALIZABLE은 가장 엄격한 격리 수준으로, 이름 그대로 트랜잭션을 순차적으로 진행시키는 격리수준이다.

 

SELECT ... FOR UPDATE은 특정 row들에만 lock을 거는 반면 SERIALIZABLE에서는 전체에 lock을 건다는 차이점이 있다.

(RDBMS마다 차이가 있어 모든 RDBMS가 SERIALIZABLE 격리 수준일 때 전체 lock을 거는 것은 아니다.)

 

SERIALIZABLE에서 여러 트랜잭션이 동일한 레코드에 동시 접근할 수 없으므로, 어떠한 데이터 부정합 문제도 발생하지 않는다. 하지만 트랜잭션이 순차적으로 처리되어야 하므로 동시 처리 성능이 매우 떨어진다.

 

 

 

 

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/transaction-isolation-levels?view=sql-server-ver16

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

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/

https://mangkyu.tistory.com/299

https://dololak.tistory.com/446

https://skasha.tistory.com/51

https://breakcoding.tistory.com/406