[Database] isolation이 안될 때 나타날 수 있는 여러 현상

Isonlation이 안될 때 발생할 수 있는 이상한 현상들

Dirty Read

commit 되지 않은 변화를 읽어서 이상한 값이 반영되는 상황을 Dirty Read 라고 한다.
예시를 살펴보자.

1. Transaction 1 Begins: A가 어떤 계좌에 $100 달러를 입금하려 한다. (Transaction 1) 현재 계좌 잔액은 $500 달러이다.
2. Transaction 1 Modifies Data: Transaction 1은 새 잔액을 계산한다.($500 + $100 = $600) 하지만 commit은 하지 않는다.
3. Dirty Read Occurs: 또 다른 transaction이 시작하여 계좌에 $500 달러를 read 한다. (Transaction 2)
4. Transaction 2 Reads Uncommitted Data: Transaction 2는 Transaction 1이 입금 중인 것을 알아차리지 못한다. 따라서 처음 계좌에 있던 잔액인 $500 달러를 읽고 $50 달러를 출금하려 한다.
5. Transaction 1 Commits (or Rolls Back): Transaction 1은 $600 달러로 업데이트한 사항을 commit 한다.
6. Transaction 2 abort: Transaction 2는 Transaction 1이 커밋하기 전 잔액을 읽었기 때문에, 자신의 update 사항을 commit하려 할 때 abort가 되고 rollback을 해야한다.

위와 같은 상황에서 정상적인 상황이 되려면, Transaction 1이 계좌 잔액을 $600 달러로 업데이트하여 commit한 후에 Transaction 2가 계좌 잔액을 읽어야 한다.

Nonrepeatable Read

같은 Transaction에서 데이터의 값을 여러번 읽었을 때, 값이 달라지는 현상을 Nonrepeatable Read라고 한다.

1. Transaction 1 Begins: A가 어떤 계좌에 잔액을 확인한다.($500)
2. Nonrepeatable Read Occurs: 또 다른 transaction이 시작하여 (Transaction 2) 계좌에 $500 달러를 read 하고, $600 달러로 수정하여 commit 한다.
3. Transaction 1 Reads (or Rolls Back): Transaction 1은 다시 계좌에 잔액을 확인한다. ($600)

위와 같은 상황에서, 하나의 Transaction 1 에서 read 2번 했을 때 $500 -> $600 으로 결괏값이 바뀌는 Nonrepeatable Read 현상이 발생한다.

Phantom Read

같은 데이터의 값을 여러번 읽었을 때, 없던 데이터가 생긴 현상을 Phantom Read라고 한다.

1. Transaction 1 Begins: 도서관 사서가 판타지 장르의 소설 목록을 read 한다. (Transaction 1)
2. Transaction 2 Inserts New Data: 이 때 Transaction 2가 시작하여 판타지 장르 소설 목록에 새로운 책을 insert 하여 commit 한다.
3. Transaction 1 Re-reads (Phantom Read): Transaction 1은 다시 판타지 장르의 소설 목록을 read 했을 때 없던 책이 목록에 생겼다.

위와 같은 상황에서 Transaction 1은 같은 Transaction 내에서 read 요청을 두 번 보냈는데 처음 read 했을때 없던 책 데이터가 생기는 Phantom Read 현상이 발생한다.

이상한 현상들을 모두 발생하지 않게 만들 수 있지만, 제약사항이 많아져 동시 처리 가능한 Transaction의 수가 줄어들게 된다.

결국 DB의 전체 처리량(throughput)이 하락하게 된다.

이를 해결하기 위해 일부 이상한 현상을 허용하는 Isolation Level을 만들어서 개발자가 필요에 따라 선택할 수 있도록 하는 선택지가 생긴다.

Isolation Level

세 가지 현상을 정의하고 어떤 현상을 허용하는지에 따라서 각각의 isolation level이 구분된다.

애플리케이션 설계자는 isolation level을 통해 전체 처리량(throughput)과 데이터 일관성 사이에서 어느 정도의 거래를 할 수 있다.

Isolation	Dirty Read	Non-repeatable read	Phantom read
Read uncommitted	O	O	O
Read committed	X	O	O
Repeatable read	X	X	O
Serializable	X	X	X

특히 Serializable은 위 세 가지 현상 이외에도, 다른 모든 이상한 현상을 제한하는 Isolation level 이다.

 

Standard SQL 92 비판

하지만 이를 비판하는 논문이 제기되는데, 위 세 가지 현상 이외에 이상한 현상이 많기 때문이다.

이상한 현상에 대해 몇 가지 더 제시해보겠다.

Dirty Write

commit이 안된 데이터를 write하여 이상한 값이 들어가는 것을 의미한다.

1. x = 0 인 데이터에 Transaction 1이 x = 10으로 write를 한다.
2. Transaction 1 commit 이전에 x = 10을 읽은 Transaction 2가 x = 100으로 바꾼다.
3. 그런데 Transaction 1이 abort를 하여 x = 0으로 roll back을 한다.
4. 그리고 Transaction 2도 abort를 하여 x = 10으로 roll back 한다.

위 시나리오는 결국 Transaction 1, Transaction 2 모두 roll back을 했음에도 초기 데이터를 유지하지 못하는 현상이 발생한다.

이를 Dirty Write라고 한다.

이를 막기 위해 roll back 시 정상적인 recovery는 매우 중요하기 때문에 모든 isolation level에서 dirty write를 허용해선 안된다.

Lost Update

Transaction이 동시에 실행될 때 다른 Transaction의 update를 덮어쓰기하여 사라지는 현상을 말한다.

1. Transaction 1 Begins: x = 0인 데이터를 Transaction 1이 읽는다.
2. Transaction 2 writes New Data: 이 때 Transaction 2가 시작하여 x = 0인 데이터를 읽고, x = 200으로 write한 후 commit 한다.
3. Transaction 1 occurs Lost update: Transaction 1은 x = 100을 write 하고 commit 한다.
4. 결국 Transaction 2의 commit은 Transaction 1의 commit에 덮어씌워져 없어진다.

또 다른 Dirty Read

Dirty Read는 마치 Abort가 발생해야만 문제가 생기는 것 처럼 보여도, abort가 발생하지 않아도 Dirty read가 발생할 수 있다.

1. $500달러가 있는 M이란 계좌와 $100달러가 있는 N이란 계좌가 있다.
2. Transaction 1 Begins: Transaction 1이 M 계좌에 $100 달러를 입금하려 한다.
3. Transaction 1 Modifies Data: Transaction 1은 M 계좌에 100($500 + $100 = $600) 달러를 입금하여 write한다. 이 때 M = 600, N = 100 총 $700 달러를 가지고 있다. 아직 commit은 하지 않는다.
4. Transaction 2 Reads Uncommitted Data: Transaction 2는 Transaction 1 write한 M = 600 달러를 읽고 N = 100을 읽는다.
5. Transaction 1 Modifies Data and Commits (Dirty Read occur): Transaction 1은 이번엔 N 계좌에 $50 달러를 입금한 후 총 변경 사항을 commit 한다.
6. 결과적으로 Transaction 1은 총 M = 600, N = 150 총 750 달러를 확인하지만, Transaction 2는 M = 600, N = 100을 읽어 총 700 달러를 확인하게 된다. 이는 데이터 정합성을 위반한다.

Read skew

Inconsistent한 데이터를 읽었을 때 발생하는 현상이다.

1. $500달러가 있는 M이란 계좌와 $100달러가 있는 N이란 계좌가 있다.
2. Transaction 2 Begins: Transaction 2가 M 계좌에 $500 달러를 읽는다.
3. Transaction 1 Modifies Data: Transaction 1은 M 계좌에 100 달러를 입금($500 + $100 = $600)하여 write한다. 그리고 N 계좌에 $50 달러를 입금($100 + $50 = $150)하고 commit 한다.
4. Transaction 2 Reads committed Data: Transaction 2는 이번엔 Transaction 1이 write한 N = 150 달러를 읽는다.
5. 결과적으로 Transaction 1은 총 M = 600, N = 150 총 750 달러를 확인하지만, Transaction 2는 M = 500, N = 150을 읽어 총 650 달러를 확인하게 된다. 이는 데이터 정합성을 위반한다.

Write skew

서로 다른 데이터를 썼음에도, inconsistent한 데이터를 쓴 경우를 말한다.

1. $500달러가 있는 M이란 계좌와 $100달러가 있는 N이란 계좌가 있다. 단, 이 계좌는 모두 마이너스 통장이 되어선 안 된다.
2. Transaction 1 Modifies Data: Transaction 1가 M 계좌를 600 달러를 출금하여 M = $-100로 write 한다.
3. Transaction 2 Modifies Data: Transaction 2가 N 계좌에 150 달러를 출금($100 - $150= $-50)하여 write한다.
4. Transaction 1 commit: Transaction 1은 변경 사항을 commit한다.
5. Transaction 2 commit: Transaction 2는 변경 사항을 commit한다.
6. 결과적으로 M, N 모두 마이너스 통장이 되어선 안 된다는 제약 사항을 어기게 되지만, 각각 commit 되기전 다른 계좌를 바꿨기 때문에 DB는 조건을 위배해도 막지못한다.

넓은 의미의 Phantom Write

꼭 같은 데이터 유형이 아니더라도, 다른 데이터를 불러왔을 때도 Phantom Write가 발생할 수 있다.

1. 공포 장르의 도서가 하나도 저장되어 있지 않은 도서관 DB가 있다.
2. Transaction 1 Begins: 도서관 사서가 공포 장르의 소설 목록을 read 한다. (Transaction 1) 이 때 아직 공포 장르 도서가 존재하지 않아 아무것도 불러오지 않는다.
3. Transaction 2 Inserts New Data:Transaction 2가 시작하여 공포 장르 소설 목록에 새로운 책을 insert 하여 commit 한다.
4. Transaction 1 Re-reads (Phantom Read): Transaction 1은 공포 장르의 소설의 수를 read 했을 때 1을 읽고 commit 한다.
5. Transaction 1은 공포 장르 도서를 목록을 read 했을 때 아무 데이터를 불러오지 못했는데, 공포 장르 도서의 수를 불러오는 쿼리에는 1을 불러오는 (Transaction 2에서 삽입한 도서) 이상한 현상이 발생하게 된다.

Snapshot Isolation

상업적인 DBMS에서 사용되는 방법을 반영해서 Isolation level을 구분하지 않았다는 비판 또한 하였다.

Read uncommitted, Read committed, Repeatable read, Serializable 네 가지 Isolation level은 얼마만큼 이상 현상을 허용하는가로 나뉘었지만, Snapshot isolation은 어떻게 동시성을 구현하는 가로 구분지었다.

 

Snapshot은 Transaction이 시작하는 시점을 읽는다.

1. $500달러가 있는 M이란 계좌와 $100달러가 있는 N이란 계좌가 있다.
2. Transaction 1 Begins: Transaction 1가 M 계좌에 $500 달러를 읽는다.
   이 때, Snapshot은 M = 500, N = 100 이다.
3. Transaction 1 Modifies Data: Transaction 1은 M 계좌에 100 달러를 출금($500 - $100 = $400)하여 write한다.
   이 때, Snapshot은 M = 400, N = 100 이다.
4. Transaction 2 Begins: Transaction 2는 N 계좌에 $100 달러를 읽는다.
   이 때, Snapshot은 M = 400,  N = 100 이다.
5. Transaction 2 Modifies Data: Transaction 2은 N 계좌에 50 달러를 출금($100 + $50 = $150)하여 write하고 commit 한다.
   이 때, Snapshot은 M = 400, N = 150 이다.
6. Transaction 1 Modifies Data: Transaction 1이 N을 Read하고 $-100($100 - $100 = 0)으로 변경하여 commit하려 한다. 하지만, Transaction 1이 시작한 시점의 Snapshot은 N = 100 이므로 Transaction 2가 commit한 N = 150을 읽지 않는다.  따라서 Transaction 1이 N의 값을 변경하여 commit하면 abort가 작동한다.

Snapshot Isolation은 가장 먼저 Commit한 Snapshot을 반영하고, 이후 Transaction의 Snapshot은 abort 시킨다.

(First-committer win)

실무에서 Isolation level

MySQL (InnoDB)

• Read uncommitted: 지원
• Read committed: 지원
• Repeatable read: 지원
• Serializable : 지원

Oracle

• Read committed: 지원
• Serializable : 지원

SQL server

• READ UNCOMMITTED: This is the lowest level that allows reading data even if it has been modified but not yet committed by other transactions. It offers the least data consistency but the highest concurrency.
• READ COMMITTED (default): This is the default isolation level in SQL Server. Transactions can only read data that has been committed by other transactions. It provides a balance between data consistency and concurrency.
• REPEATABLE READ: This level ensures that a transaction will see the same data on subsequent reads within the transaction, even if other transactions commit changes in the meantime. However, it allows phantom reads.
• SERIALIZABLE: This is the highest isolation level that ensures serializability of transactions. It simulates executing transactions one after another, preventing any anomalies like dirty reads or phantom reads. However, it can significantly impact concurrency.
• SNAPSHOT: This level uses read versioning to provide a consistent view of data as of the transaction start time. It avoids locking but can introduce some overhead.
• READ COMMITTED SNAPSHOT: This is a combination of READ COMMITTED and SNAPSHOT. It reads committed data like READ COMMITTED but uses versioning for better concurrency than READ COMMITTED alone.

PostgreSQL

https://www.postgresql.org/docs/current/transaction-iso.html

참조

• https://youtu.be/bLLarZTrebU?si=urYyhW2d2mAJzWMX
• Google Gemini