(8) 트랜잭션 격리 수준

1. 격리 수준(Isolation level)이란?

1-1) 정의

동시에 수행되는 트랜잭션 thread/작업들에서 일관성을 해칠 수 있는 데이터 작업을 허용하도록 하는 수준

 

1-2) 필요성

ACID 특성을 지키면서 트랜잭션이 수행되도록 하는데

보통 격리수준과 성능이 반비례 관계에 있게 됨

ACID 참조 : https://korshika.tistory.com/171

(7) DB Transaction

 

> Trade-off

1) Locking을 높여 수많은 트랜잭션을 순서대로 처리하면 DB 성능감소

2) Locking을 낮춰 동시수행을 처리한다면 consistency가 보장이 안되며 데이터가 잘못될 수 있음

→ 최대한 효율적인 locking이 필요함

 

 

2. 격리 수준 종류(Isolation Levels)

> [Lock에 대한 용어설명 ▼]

※ Read 할때의 Lock

SELECT 문을 사용하면 걸림, Shared / Exclusive 두종류

※ DB - shared lock & exclusive lock

---

Exclusive lock과 Shared lock

운영체제에서 잠금(Lock)과 유사하게 데이터베이스에서도 잠금(Lock)이 있다.

멀티 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하려면 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화 장치가 필요하다.

예를들어 한 명이 도서관의 좌석을 예약하는 중에 다른 한 명이 같은 좌석을 예약할 수 없게하여 정확히 한 명만 좌석을 배정받을 수 있게 한다.

이런 이유로 이런 기능을 하는 Lock 이라는 기술이 등장했다.

다양한 격리수준이 존재하고 그에 따른 lock들이 많이 있지만 크게 두 가지로 정리한다.

---

Exclusive lock (배타적 잠금)

쓰기 잠금(Write lock)이라고도 불린다.

어떤 트랜잭션에서 데이터를 변경하고자 할 때(ex . 쓰고자 할 때) 해당 트랜잭션이 완료될 때까지 해당 테이블 혹은 레코드(row)를 다른 트랜잭션에서 읽거나 쓰지 못하게 하기 위해 Exclusive lock을 걸고 트랜잭션을 진행시키는  것이다.

=> exclusive lock에 걸리면 shared lock을 걸 수 없다. (shared lock은 아래에서 설명)

=> exclusive lock에 걸린 테이블,레코드등의 자원에 대해 다른 트랜잭션이 exclusive lock을 걸 수 없다.

---

Shared lock (공유 잠금)

읽기 잠금(Read lock)이라고도 불린다.

어떤 트랜잭션에서 데이터를 읽고자 할 때 다른 shared lock은 허용이 되지만 exclusive lock은 불가하다.

쉽게 말해 리소스를 다른 사용자가 동시에 읽을 수 있게 하되 변경은 불가하게 하는 것이다.

=> 어떤 자원에 shared lock이 동시에 여러개 적용될 수 있다.

=> 어떤 자원에 shared lock이 하나라도 걸려있으면 exclusive lock을 걸 수 없다.

* Lock은 DBMS가 자동으로도 적용하기도 하고 수동으로도 줄 수 있다.

* Lock은 잠금 비용과 동시성비용을 고려해야한다.

만약 lock을 걸어야할 페이지가 많다면, 그럴바에 테이블 전체에 lock을 걸어버리는 편이 한번에 처리하니까 잠금 비용에 낮아져 효율적이다.

하지만 lock의 범위가 넓어질수록 동시에 접근할 수 없는 자원이 많아지므로 동시성 비용이 높아져 효율이 떨어진다.

* Lock이 엄청나게 다양하다.

row lock / table lock 부터 시작해서 그 안에 RX, RS, S, SRX, X등이 있다.

* 격리수준에 따라 Lock종류가 많아진다. 예를 들면 SQL select문에서 Shared lock을 걸지 않게하는 것도 있다.

SELECT * FROM TABLENAME WITH (READUNCOMMITTED) WHERE PK = 5

---

출처: https://jeong-pro.tistory.com/94 [기본기를 쌓는 정아마추어 코딩블로그]

---

---

※ Row-level lock

가장 기본적인 lock은 테이블의 row마다 걸리는 row-level lock이다. 여기에는 크게 shared lock과 exclusive lock의 두 종류가 있다.

Shared lock(S lock)은 read에 대한 lock이다. 일반적인 SELECT 쿼리는 lock을 사용하지 않고 DB를 읽어 들인다. 하지만 SELECT ... FOR SHARE 등 일부 SELECT 쿼리는 read 작업을 수행할 때 InnoDB가 각 row에 S lock을 건다.

Exclusive lock(X lock)은 write에 대한 lock이다. SELECT ... FOR UPDATE나 UPDATE, DELETE 등의 수정 쿼리를 날릴 때 각 row에 걸리는 lock이다.

S lock과 X lock을 거는 규칙은 다음과 같다 :

• 여러 transaction이 동시에 한 row에 S lock을 걸 수 있다. 즉, 여러 transaction이 동시에 한 row를 읽을 수 있다.
• S lock이 걸려있는 row에 다른 transaction이 X lock을 걸 수 없다. 즉, 다른 transaction이 읽고 있는 row를 수정하거나 삭제할 수 없다.
• X lock이 걸려있는 row에는 다른 transaction이 S lock과 X lock 둘 다 걸 수 없다. 즉, 다른 transaction이 수정하거나 삭제하고 있는 row는 읽기, 수정, 삭제가 전부 불가능하다.

요약하자면, S lock을 사용하는 쿼리끼리는 같은 row에 접근 가능하다. 반면, X lock이 걸린 row는 다른 어떠한 쿼리도 접근 불가능하다. “Shared”와 “exclusive”라는 이름의 의미와 정확히 일치한다.

출처 : https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/

 

 

 

2-1) Read Uncommitted(레벨 0)

(a) 정의

Transaction에서 SELECT 문장이 수행되는 동안 해당 데이터에 Lock이 걸리지 않는 계층

트랜잭션이 처리중이거나 commit이 되지 않은 상황이어도 다른 트랜잭션이 읽을 수 있음

(b) 예시

사용자1이 A라는 데이터를 B라는 데이터로 변경하는 동안 사용자2는 아직 완료되지 않은(Uncommitted) 트랜잭션이지만 데이터B를 읽을 수 있음

---

이 수준에서만 볼 수 있는 문제는 Dirty Read 가 있음
> Transaction 끼리 완료 상태를 체크하지 않기 때문에 "수정중"인 데이터를 도중에 참조해버리는 오류

Dirty Read?

1. UPDATE 반영 전에 읽는 오류
   • 순서 5에서 오류가 생겨 롤백이 되었다고 가정하자. ID = 1은 다시 MIN 이 된다.
   • 그러나 롤백 전인 순서 4번은 여전히 VAL = 'KIM' 으로 인식할 것이다.
2. INSERT 반영 전에 읽는 오류
   • 트랜잭션 1이 특정 데이터를 INSERT한다.
   • 트랜잭션 2가 그 데이터를 읽고 로직을 수행한다.
   • 트랜잭션 1 수행 중 오류가 생겨 롤백된다. INSERT한 데이터가 삭제되었다.
   • 그러나 트랜잭션 2는 이미 로직을 수행한 상태이다.

출처: https://private-space.tistory.com/97 [티끌모아 산을 쌓아보자]

(c) 문제점

데이터베이스의 일관성 유지가 불가능

 

2-2) Read Committed(레벨 1)

(a) 정의

SELECT 문장이 수행되는 동안 해당 데이터에 Lock( Shared lock / S-lock )이 걸리는 계층

Lock을 소유하고 있는 트랜잭션의 SELECT가 수행되고 있는 동안 원본에 접근할 수 없어 대기

만약 Shared lock이어서 read까지 가능하면 조회는 되지만 맞는 데이터는 아님
- Commit 완료 상태의 transaction만 조회 가능 / 그 전은 UNDO에서 조회

- 보통 자동으로 DBMS에서 선택되는 level (Oracle에서 해당)

 

> 왜 commit이전에도 조회가 될까?
- 원본 데이터를 조회할 수 없고, undo 영역을 조회하기 때문

• 어떤 트랜잭션에서 처리한 작업이 commit 이 안되어 있다면 다른 트랜잭션은  undo 영역에 있는 기존 값을 참고하여 보여 주게 됨

(b) 예시

사용자1이 A라는 데이터를 B라는 데이터로 변경하는 동안 사용자2는 해당 데이터에 접근이 불가능함

---

 

(c) 문제점

REPEATABLE READ 정합성/Consistency에 어긋남

> 멱등성이 깨진다는 의미

커밋된 데이터만을 읽어오기 때문에 READ UNCOMMITED 에서 롤백되는 경우 발생하는 문제는 생기지 않는다.
이 격리 수준 이하에선 Non-Repeatable Read 문제가 발생한다.

Non-Repeatable Read?

하나의 트랜잭션이 같은 값을 조회할 때 다른 값이 검색되는 현상이다.
이때 검색은 원본이 commit이 안된 상태로 조회할 수 없기 때문에 undo영역에서 바뀐 데이터 저장된 것을 보고
가져오게 된다.
위의 그림에서 트랜잭션 2의 첫 번째 조회엔 MIN이, 두 번째 조회엔 KIM이 검색되고 있다.

출처: https://private-space.tistory.com/97 [티끌모아 산을 쌓아보자]

 

 

2-3) Repeatable Read (레벨 2)

(a) 정의

트랜잭션이 완료될 때까지 SELECT 문장이 사용되는 데이터 모두에 Shared Lock이 걸리고
트랜잭션이 진행되는 동안 다른 트랜잭션이 바뀌고 있는 데이터를 조회할 수 없음
- 트랜잭션이 시작되고 나서 끝날 때 까지 조회 결과가 항상 같은 것

- Repeatable-read와 Read-Committed의 차이는, 둘다 동일하게 UNDO 영역에서 조회를 하지만
   Read-Committed는 가장 최신 UNDO 변경을 돌려준다면 Repeatable-read는 트랜잭션 시작 이전의
   원본값을 가진 UNDO 영역까지 들어가서 그 값을 돌려주는것이 차이
   > VCC를 위해 언두 영역에 백업된 이전 데이터를 이용해
      동일 Transaction 내에서는 동일한 결과를 보여줄 수 있도록 보장
      (Read committed 도 commit 되기 전 데이터를 보여줌)

- MySQL InnoDB 기본 사용되는 격리 수준

(b) 예시

(c) 문제점

UPDATE 한 데이터에 대해서는 정합성을 보장하지만, INSERT/DELETE 는 보장되지 않음

이 때문에 이 격리 수준 이하에서는 Phantom Read 문제가 발생함

Phantom Read?

마치 유령을 보는 것처럼 있던 데이터가 사라지거나 없던 데이터가 생기는 현상을 말한다.

> select .. for update 쿼리의 경우 다른 트랜잭션에서 수행한 변경 작업에 의해
레코드가 보였다가 안보였다가 할 수 있는데 이것을 Phantom Read라고 표현
-> Undo 영역을 lock 할 수 없기 때문에 변경 전 데이터가 아닌 현재 변경된 레코드를 표현

설명 )

바로 위 예제의 순서 7에서 트랜잭션 1이 ID = 2인 데이터를 조회하고 있다.

순서 8에서 트랜잭션이 정상적으로 종료되지 않고, 트랜잭션 2가 롤백되면 (2, 'KIM') 데이터는 사라지게 된다.

그리고 트랜잭션 1이 다시 ID = 2인 데이터를 조회한다면 정상적으로 검색될 수 있을까?

조회했던 데이터가 롤백 처리로 인해 보이지 않게 되는 것이다.

출처: https://private-space.tistory.com/97 [티끌모아 산을 쌓아보자]

 

 

2-4) Serializable (레벨 3)

(a) 정의

트랜잭션이 완료될 때까지 SELECT 문장이 사용되는 데이터 모두에 Shared Lock이 걸리고
읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 됨

한 트랜잭션에서 읽고 쓰는 레코드는 다른 트랜잭션에서는 절대 접근 불가

고로, 수정/입력 불가
완벽한 읽기 일관성 모드를 제공

(b) 특징

Phantom Read 문제가 발생하지 않고

웬만한 DB는 Repeatable Read 까지만 격리해도 문제가 없음

- 성능이 많이 떨어짐

---

참조

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)

http://labs.brandi.co.kr/2019/06/19/hansj.html

MySQL의 Transaction Isolation Level (Lock에 관하여)

https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/

Lock으로 이해하는 Transaction의 Isolation Level

https://hyunki1019.tistory.com/111

[MySQL] isolation level 종류 및 특징

https://private-space.tistory.com/97

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