참고링크
트랜잭션의 격리 수준(isolation Level)이란?
nesoy.github.io
- 이성욱 , 『Real MySQL』 , 위키북스
- 아직 1년도 안된 개발자라.. 틀린 내용이 있을 수 있습니다.
- 틀린 내용이나 지적할 부분이 있다면 robin00q@naver.com 혹은 댓글을 달아주세요.^_^
Four Transaction Isolation Level
Transaction Isolation Level 은 크게 4가지로 나뉩니다.
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
이에 대해서 알아보겠습니다.
1. READ UNCOMMITTED
- 해당 격리 수준에서는 트랜잭션의 변경 내용 (Transaction 으로 INSERT/UPDATE/DELETE 등 무슨 행위를 하였을 때) 이
- COMMIT 여부에 상관 없이 다른 트랜잭션에 보여집니다.
- 트랜잭션 이란?
- 데이터베이스에 대해 CRUD를 하는 행위로서, 데이터베이스를 읽거나, 조작하여 데이터를 갖고 일을 수행하는 것이라고 볼 수 있습니다.
- 하나의 트랜잭션이 시작하기 직전 BEGIN이 수행되며 이는 트랜잭션이 시작함을 말합니다.
- 하나의 트랜잭션이 정상적으로 끝나면 COMMIT이 수행되며,
- COMMIT 이 끝난 트랜잭션은 작업이 끝난 것 (종료됨)이라고 말할 수 있습니다.
- 만약 트랜잭션 내부 (BEGIN 과 COMMIT 사이) 에서 에러가 발생하거나 한다면??
- Rollback 이 수행되며 해당 데이터는 반영되지 않고 begin 상태 이전으로 롤백됩니다.
- 예전에 내가 갖고 있었던 의문
- 아니 트랜잭션은 어차피 한건의 데이터를 입력하는건데 중간에 오류가 좀 나도, 하나가 반영 되거나 안되거나 아닌가?
- INSERT INTO TEMP_TABLE (ID, NAME) VALUES (1, '이석준'), (2, '준석이'), (3, '석준이') 또한 하나의 트랜잭션입니다.
- 하나의 트랜잭션이라고 하나의 데이터만 입력하는게 아니었네요 ㅜ.ㅜ(저도 여태껏 몰랐음)
- INSERT INTO TEMP_TABLE (ID, NAME) VALUES (1, '이석준'), (2, '준석이'), (3, '석준이') 또한 하나의 트랜잭션입니다.
- 아니 트랜잭션은 어차피 한건의 데이터를 입력하는건데 중간에 오류가 좀 나도, 하나가 반영 되거나 안되거나 아닌가?
위의 그림에 대해서 한번 봐보겠습니다.
| Transaction1 | Transaction2 |
| BEGIN을 수행하며, 이제 트랜잭션이 시작됩니다. BEGIN을 하는 시점에는 데이터베이스에 1개의 데이터 [111 | Seoul] 가 들어있는 상태입니다. |
|
| INSERT를 수행하여 [222 | Busan] 을 데이터베이스에 입력했습니다. READ UNCOMMITTED의 특성상 COMMIT을 하지 않았음에도(트랜잭션이 아직 정상적으로 종료될지, 안될지 모르지만) 데이터베이스에 반영합니다. |
|
| // BEGIN이 생략되었지만, 있다고 생각하시면 됩니다. SELECT 를 수행하여 222번 값을 찾습니다. |
|
| 아직 1번 트랜잭션이 끝나지 않았지만, READ UNCOMMITTED 는 데이터를 바로 반영하는 격리수준이므로, [222 | BUSAN] 데이터를 얻게됐습니다. | |
| COMMIT을 수행하며, 트랜잭션을 종료합니다. |
해당 격리수준 (READ_UNCOMMITTED) 는 아직 다른 트랜잭션이 종료되지 않았는데도, 데이터를 조회할 수 있게됩니다.
- 이를 Dirty Read 라고 부릅니다.
뭐가 문제?
- Transaction1이 1000개의 데이터를 넣는다는 가정하에 501번째에서 에러가 났다면??
- 만약 501번 째에서 에러가 나기 이전
- Transaction2 는 그 전에 데이터 (예를 들어 10번째 데이터) 를 조회해서 찾았지만
- 501번 째에서 에러가 난 이후
- Transaction3 은 에러가 난 후, 같은 데이터 (10번째 데이터) 를 조회했으나, 에러가 났기 때문에 롤백이돼서 찾을 수가 없네요,,?
- 만약 501번 째에서 에러가 나기 이전
- 데이터가 있었다가 없었다가 하는 문제가 생겨, 개발자와 사용자에게 많은 혼란을 줄 수 있습니다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | 발생 | ? | ? |
| READ COMMITTED | |||
| REPEATABLE READ | |||
| SERIALIZABLE |
2. READ COMMITTED
- COMMIT 이 완료된 데이터에 대해서만 다른 트랜잭션에서 조회할 수 있다.
그렇다면 dirty read가 발생할까요????
- 발생하지 않습니다.
- 아직 다른 트랜잭션이 종료되지 않았지만, 해당 데이터를 조회할 수 있는 것이 Dirty Read 의 정의입니다.
- 위에서 트랜하지만 트랜잭션이 종료된 (트랜잭션이 commit 된 이후) 후에 데이터가 반영되므로 이제는 발생하지 않습니다.
조금 더 부연설명
- MySQL의 경우는 이렇습니다.
원래의 데이터는 [ 80점 | '이석준' ] 이었다고 가정해보겠습니다.
UPDATE student
SET score=90
WHERE name='이석준';- READ COMMITTED 의 상황에서
- 하나의 트랜잭션이 위의 데이터를 아직 커밋하지 않아도, 사실은 데이터베이스에 반영됩니다.
- 하지만 Undo 영역 (변경 이전의 데이터를 보관하는 영역) 에 [ 80점 | '이석준' ] 이 저장되어 있기때문에, 다른 트랜잭션이 중간에서 조회해도 Undo 영역을 조회해서 가져가게됩니다.
- RollBack의 경우에 Undo 영역을 기반으로 롤백을 하게 됩니다.
- 하나의 트랜잭션이 위의 데이터를 아직 커밋하지 않아도, 사실은 데이터베이스에 반영됩니다.
| Transaction1 | Transaction2 |
| BEGIN을 수행하며, 이제 트랜잭션이 시작됩니다. BEGIN을 하는 시점에는 데이터베이스에 2개의 데이터 [111 | Seoul], [222 | Busan] 이 들어있는 상태입니다. |
|
| UPDATE를 수행하여, 222번의 데이터 (Busan) 을 Jeju로 변경합니다. | |
| // BEGIN이 생략되었지만, 있다고 생각하시면 됩니다. SELECT 를 수행하여 222번의 데이터를 조회합니다. |
|
| 아직 Transaction1 이 커밋하지 않았기 때문에 [222 | Busan] 에 대해서 얻게됩니다. |
|
| COMMIT을 수행하며, 트랜잭션을 종료합니다. |
이정도면 완벽한거 아닌가?
- NON-REPEATABLE-READ
현재 Transaction2 하나의 트랜잭션 내에서
- 같은 222 번 데이터를 두 번 조회합니다.
- 하지만 첫 번째는 BUSAN 을 가져오고
- 두 번째에서는 JEJU 를 가져오네요
어떤 문제가?
- 0원이었던 계좌에 대해서 오늘 하루의 입금액과 출금액을 집계한다고 가정 (현재 입출금이 매우 활발한 상황) 해보겠습니다.
- 집계하는 트랜잭션은 입, 출금에 대해서 집계합니다.
- 입금에 대해서 먼저 집계하였는데 이때 집계된 입금액이 [100억] 이라고 가정하겠습니다.
- 그 이후 출금에 대해서 집계하는데
- 이 입금과 출금 사이에 누군가 200억을 입금하고 150억을 출금한 뒤, 커밋을 하여 트랜잭션을 종료했다면??
- 집계된 출금액이 [원래 출금액 + 150억] 이 되는 상황이 발생합니다.
- 0원 계좌에서 입금이 100억인데 출금이 150억 이상인 문제가 생길 수 있겠죠?
NON-REPEATABLE-READ
- 하나의 트랜잭션 내에서 다른 값이 조회될 수 있다.
- 항상 같은 값이 조회된다는 보장이 없다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | 발생 | 발생 | ? |
| READ COMMITTED | 발생 안함 | 발생 | ? |
| REPEATABLE READ | |||
| SERIALIZABLE |
3. REPEATABLE READ
데이터베이스에는 Undo 영역이 존재한다.
- 아래의 그림을 보면 초록색으로 Undo 라고 쓰여있는 것을 볼 수 있다.
- Undo 영역은 변경 이전의 내용을 갖고있는다. (트랜잭션이 실패하면 롤백하기 위해서, 데이터베이스 Lock 을 막기 위해서 ...)
Repeatable Read는 변경이 있는 row라면
- 변경은 Transaction ID (순차적으로 증가하는 값) 로 감지할 수 있다. (데이터베이스 내부적으로 알아서 생성됨)
- 현재 실행중인 Transaction ID 보다 작은 값을 갖고 있는 데이터를 조회한다. (실제 데이터 영역과, Undo 영역을 모두 본 뒤)
그림 설명
- 가정 상황 : Transaction ID가 6번인 Transaction이 [ 111 | Seoul ] , [ 222 | Busan ] 을 만들어놨다.
| Transaction1 | Transaction2 |
| BEGIN을 통해 트랜잭션을 시작합니다. Transaction ID는 10번으로 시작합니다. |
|
| 222 데이터를 조회합니다. [222 | Busan]의 Transaction ID 가 6번입니다. 자신(Transaction2) 의 Transaction ID는 10번입니다. 6 < 10 (자신의 트랜잭션이 시작하기 전에 변경된 데이터) 이므로, 해당 데이터를 가져옵니다. |
|
| BEGIN을 통해 트랜잭션을 시작합니다. Transaction ID는 12번으로 시작합니다. |
|
| Update 를 통해 222 번을 [222 | Busan] -> [222 | Jeju] 로 변경합니다. 이 과정에서, 222번의 Transaction ID 가 12번으로 변경됩니다. - 변경한 Transaction ID를 갖게됩니다. 변경에 대해서 롤백이 필요할 수 있거나, 이러한 상황을 위해서 Undo 영역에 변경 이전의 내용 (6번의 TransactionID를 가진 [222 | Busan]을 저장해놓습니다. |
|
| COMMIT을 수행하며, 트랜잭션을 종료합니다. | |
| 222 데이터를 조회합니다. 실제 데이터베이스를 보니 12번 Transaction ID를 갖고있는 데이터가 있습니다. 자신의 Transaction ID(10) < 실제 데이터 Transaction ID(12) 로 실제 데이터의 Transaction ID가 더 크므로, Undo 영역을 봅니다. Undo 영역을 보니, Transaction ID 6번인 222를 갖고있는 데이터가 존재하므로, 해당 데이터를 반환합니다. |
|
| [222 | Busan] 이 반환됨. |
PHANTOM READ
- 트랜잭션 수행에 따라서 레코드가 생겼다가 없어졌다가 하는 일
- MySQL InnoDB에서는 일반적인 SELECT 의 경우에는 발생하지 않으나 (Undo 영역을 가져오기 때문)
- SELECT ... FOR UPDATE 의 경우에는 발생합니다.
- SELECT ... FOR UPDATE???
- 어떤 데이터를 갱신 / 삭제 하는 행위를 하기 위해서는 조회가 선행되어야합니다.
- 이를 위한 쿼리로써, 해당 데이터에 LOCK을 걸고 수행되며, 실제 데이터에 접근하게 됩니다.
- 어떤 데이터를 갱신 / 삭제 하는 행위를 하기 위해서는 조회가 선행되어야합니다.
- 실제 데이터 베이스에 접근합니다. (UNDO 영역에 접근하지 않음)
- SELECT ... FOR UPDATE???
- 새로 추가된 행에 대해서 SELECT ... FOR UPDATE는 현재 데이터의 내용을 갖고오게 되어
- 처음에는 2개
- 나중에는 3개의 데이터를 갖고오게 됩니다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | 발생 | 발생 | 발생 |
| READ COMMITTED | 발생 안함 | 발생 | 발생 |
| REPEATABLE READ | 발생 안함 | 발생 안함 | 발생 |
| SERIALIZABLE |
4. SERIALIZABLE
- 보통 어떤 데이터를 SELECT 하는 문장은 LOCK을 걸지않고 수행하나, SERIALIZABLE은 SELECT 조차 LOCK을 걸고 수행한다.
- 다른 쿼리들 또한 LOCK을 걸고 수행한다.
- 해당 격리 수준은 모두 one by one 으로 수행한다고 생각하면 편하다.
- 따라서 성능은 좋지않다.
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
| READ UNCOMMITTED | 발생 | 발생 | 발생 |
| READ COMMITTED | 발생 안함 | 발생 | 발생 |
| REPEATABLE READ | 발생 안함 | 발생 안함 | 발생 |
| SERIALIZABLE | 발생 안함 | 발생 안함 | 발생 안함 |
'DataBase' 카테고리의 다른 글
| Index 요약 in MySQL (0) | 2020.12.03 |
|---|
