📒

지난 시간에는 isolation이 지켜지지 않고 트랜잭션이 겹쳐서 실행될 때 일어날 수 있는 이상 현상들에 대해서 알아보았습니다.

SQL 표준에서는

• dirty read: commit 되지 않은 데이터를 읽었을 때 발생하며, 데이터를 읽은 이후에 이 데이터를 rollback하게 되는 경우 문제가 발생하는 현상
• non-repeatable read: 한 트랜잭션에서 같은 데이터를 두 번 읽었는데 두 데이터의 결과가 다른 현상
• phantom read: 한 트랜잭션에서 같은 조건으로 데이터를 두 번 읽었는데 두 데이터의 결과가 다른 현상

이렇게 세 가지 이상 현상 개념에 대해서 다루고 이에 대한 isolation level을 아래와 같이 정의했습니다.

• read uncommited: 세 가지 이상 현상을 다 허용
• read commited: dirty read가 발생하는 schedule은 허용 X
• repeatable read: non-repeatable read까지 발생하는 scehdule은 허용 X
• serializable: 가장 엄격한 isolation level로 모든 이상 현상이 발생하는 schedule 허용 X

하지만 1995년 이 SQL 표준을 비판하는 논문이 등장하였고 이 논문에서는 SQL 표준에서 언급한 이상 현상 외에 더 많은 이상 현상이 존재하며, 다룬 이상 현상도 더 넓은 개념에서 바라보아야 한다고 비판하였습니다.

그렇게 소개한 새로운 이상 현상은 다음과 같습니다.

• read skew: 한 트랜잭션에서 관련 있는 서로 다른 데이터를 읽었는데 데이터 일관성이 깨지는 현상
• write skew: 두 트랜잭션에서 관련 있는 서로 다른 데이터에 쓰기 작업을 진행했는데 데이터 일관성이 깨지는 현상
• lost update: 한 트랜잭션이 abort혹은 commit되면서 다른 트랜잭션이 commit하여 업데이트한 결과가 사라지는 현상
• dirty write: 한 트랜잭션이 commit 되지 않은 다른 트랜잭션이 write한 데이터를 write 작업을 하면서 나타나는 이상 현상

그리고 더 넓은 개념으로 바라보아야 하는 이상 현상

• dirty read의 확장: commit 되지 않은 데이터를 읽었을 때, 그 데이터가 rollback 되는 경우에만 발생하지 않음
• phantom read의 확장: 같은 조건으로 데이터를 여러 번 읽는 경우가 아니더라도 phantom read가 발생

이렇게 두 가지를 비판하였습니다.

✔️snapshot isolation

1995년 등장한 논문에서 비판하는 내용에서 또 한 가지가 있었는데,

상업적인 DBMS에서 사용되는 방법을 반영해서 isolation level을 구분하지 않았다는 것입니다.

snapshot isolation은 어떻게 concurrency control을 구현할 것인지, 이 구현 방식에 기반해서 정의된 isolation level입니다.

x=50, y=50

다음과 같은 트랜잭션이 존재합니다.

tx1. x계좌에서 y계좌로 40을 이체한다.

A. read(x) x->50
B. write(x) x=10
C. read(y) y->50
D. write(y) y=90
tx2. x와 y를 읽는다.

tx2. y계좌에 100을 입금한다.

A. read(y) x->50
B. read(y) y=150

다음과 같은 schedule이 있습니다.

tx1(A) - tx1(B) - tx2(A) - tx2(B) - commit2 - tx1(C) - tx1(D)
x->50    x=10     y->50    y=150              y->50    y=90

snapshot 특정 시점에서의 형상이고, 트랜잭션을 시작하는 시점에서의 형상을 다룹니다. (RDBMS마다 snapshot을 뜨는 시점이 다를 수 있습니다)

tx1 snapshot
x=50

x를 읽고 x에 10을 쓰는 동작을 tx1이 진행합니다. 그런데 여기서 x=10으로 write하는 동작을 DB에 쓰는 것이 아닌 snapshot에다가 씁니다.

따라서 tx1 snapshot에서 x=10이 됩니다.

tx1 snapshot
x=10
y=50

tx2 snapshot
x=50
y=50

그리고 tx2가 시작해서 y를 읽고 y에 100을 더하여 y=150으로 write 하는 작업을 진행합니다. 여기서도 마찬가지로 DB에 쓰는 것이 아닌 snapshot에다가 씁니다.

tx2 snapshot
x=50
y=150

그리고 tx2가 commit을 하게 되면 그 때 DB에 이 작업을 반영합니다. 이제 이 이후로 시작되는 트랜잭션에서 데이터를 읽는 작업은 이제 DB에 적용된 데이터를 snapshot을 찍어서 사용하겠죠

그리고 나머지 tx1의 작업들을 수행합니다. y값은 DB가 아닌 snapshot으로 찍은 값을 읽고 40을 더해 y=90으로 write하는 작업을 snapshot에 씁니다.

tx1 snapshot
x=10
y=90

이제 tx1을 commit을 하려고 하는데, tx1을 커밋하면 tx2가 y를 업데이트 한 작업이 사라지게 됩니다.

그래서 snapshot isolation은 write-write conflict가 발생했을 때 먼저 commit한 트랜잭션에서 작업한 내용만 인정해줍니다.

그래서 뒤에 commit을 시도하려고 한 tx1은 abort 처리가 되고 tx1 snapshot은 폐기됩니다.

여기서 rollback과 헷갈리시면 안 되는 것이 애초에 DB에 반영된 적이 없으니 되돌리는 개념 자체가 없습니다. snapshot을 폐기하는 겁니다.

이렇게 동작하는 것이 snapshot isolation입니다.

각 트랜잭션마다 시작점을 기준으로 snapshot을 찍어 각각의 버전을 가지고 operation을 진행하는 것이 snapshot isolation이고 snapshot isolationd은 MVCC의 한 종류입니다.

정리하자면, 트랜잭션 시작 전에 commit된 데이터만 보이고 write-wirte conflict가 발생하는 경우 먼저 commit한 트랜잭션만 인정해주며, 그 이후 commit하는 트랜잭션은 abort처리하도록 동작하는 isoaltion level이 snapshot isolation입니다.

✔️실무에서 사용하는 RDBMS의 Isolation level

📌MySQL

MySQL은 SQL 표준에서 다룬 Isolation Level과 동일하게 isolation level을 정의하고 있습니다.

기본으로 사용하는 isolation level은 repeatable read입니다.

📌Oracle

read uncommitted는 제공하지 않으며, repeatable read나 serializable을 사용하려면 isolation level을 serializable로 설정하라고 나와있습니다.

기본으로는 read commited를 사용합니다.

결국 Oracle에서 주로 사용하는 isolation level은 serializable과 read commited이며, oracle에서의 serializable은 snapshot isolation으로 동작합니다.

📌SQL Server

SQL Server에서는 다음과 같이 isolation level을 정의하고 있습니다.

isolation level	dirty read	non-repeatable read	phantom read
read uncommited	yes	yes	yes
read commited	no	yes	yes
repeatable read	no	no	yes
snapshot	no	no	no
serializable	no	no	no

📌PostgreSQL

isolation level	dirty read	non-repeatable read	phantom read	serialiation anomaly
read uncommited	yes	yes	yes	yes
read commited	no	yes	yes	yes
repeatable read	no	no	yes	yes
serializable	no	no	no	no

PostgreSQL에서는 repeatable read가 snapshot isolation에 해당합니다.

대부분의 RDBMS가 표준에서 정의한 이상 현상에 대해서 isolation level을 정의했는데 postgreSQL은 표준에서 정의한 이상 현상 외에 하나 더 추가하여 isolation level을 정의했습니다.

✔️정리

snapshot isolation level은 서로 다른 트랜잭션에서 write-write conflict가 발생한 경우, 먼저 commit한 트랜잭션만 인정해주며, 트랜잭션이 시작하는 시점의 snapshot을 가지고 operation을 진행하는 isolation level입니다.

실무에서 사용하는 주요 RDBMS들은 대부분 SQL 표준에서 정의한 이상 현상들과 isolation level에 기반해서 isolation level을 정의하고 있습니다.

하지만 RDBMS마다 제공하는 isolation level이 다르며, 같은 isolation level이라도 RDBMS마다 동작하는 방식이 다를 수 있습니다.

보통은 RDBMS가 기본적으로 적용하는 isolation level을 따라도 별 문제가 없지만,

예상치 못한 이상 현상이나 퍼포먼스를 위해 튜닝을 진행해야 하는 경우

사용하고 있는 RDBMS의 isolation level이 어떻게 동작하는지 잘 파악하여 적절한 isolation level을 사용할 수 있어야 합니다.

지금까지 살펴본 isolation level 말고도 serializable snapshot isolation 등 더 많은 isolation level이 존재합니다.

또한 serializable을 제외하고는 serializability를 제대로 보장하지 않으며, read committed부터는 커밋된 데이터만 읽기 때문에 recoverability는 보장됩니다.