📒

이전 포스팅에서는 MVCC란 무엇인지, MVCC가 적용된 RDBMS에서 isolation level마다 어떻게 동작하는 지에 대해서 살펴보았습니다.

그리고 이전 포스팅에서는 한 트랜잭션에서 같은 데이터에 대한 read를 두 번 했을 때 read committed, repeatable read level에서 MySQL, PostgreSQL이 어떻게 동작하는 지를 살펴보았습니다.

오늘은 MVCC가 적용된 MySQL, PostgreSQL에서 트랜잭션이 같은 데이터에 write를 하는 동작에서 발생할 수 있는 문제를 각각 isolation level마다 그리고 RDBMS마다 어떻게 처리하는지 알아보겠습니다.

✔️ MVCC에서 lost update 문제

DB 시리즈 포스팅에서 lost update에 대해서 살펴본 적이 있습니다.

lost update가 발생하는 이유는 서로 다른 두 트랜잭션이 같은 데이터에 대해 write 작업을 하기 때문에 발생했습니다. 한 트랜잭션이 update한 결과가 사라지는 것입니다.

x=50, y=10

tx1. x가 y에 40을 이체한다. read committed

tx2. x에 30을 입금한다. read commited

read(tx1,x) - write(tx1,x=10) - read(tx2,x) - write(tx2,x=80)(block) - read(tx1,y) - write(tx1,y=50) - commit1 - write(tx2,x=80) - commit2

tx1에서 x의 값을 읽습니다. commit된 데이터를 읽어야 하니까 x=50을 읽어옵니다. 그리고 40을 빼야하니까 x=10 write 작업을 합니다. 당연히 수행하기 전에 write-lock을 획득합니다.

tx1에서 x=10 write 작업은 바로 디스크에 적용되는 것이 아닌 snapshot으로 데이터 변경이 저장됩니다.

tx2에서 x의 값을 읽습니다. commit된 데이터를 읽어야 하니까 x=50을 읽어옵니다. 그리고 30을 더해야하니까 x=80 write 작업을 하려고 하지만, 이미 tx1이 x에 대해 write-lock을 점유하고 있는 상태이기 때문에 write-lock을 획득하지 못하고 block 됩니다.

tx1은 계속 진행해서 y=10을 읽고 y에 40을 더해 y=50 write를 하기 위해서 y에 대한 write-lock을 획득하고 write를 진행합니다. 마찬가지로 snapshot으로 저장합니다.

그리고 tx1은 commit을 합니다.

이 후에야 tx2는 x에 대한 write-lock을 획득하여 x=80을 snapshot으로 저장하고 commit합니다.

그럼 결과적으로 tx1이 저장한 x=10, y=50이 아닌 x=80, y=50이 디스크에 저장됩니다.

tx1이 진행한 update가 사라진 lost update가 발생했습니다.

이 문제를 어떻게 해결할 수 있을까요?

📌PostgreSQL에서 lost update 해결

👉tx2의 isolation level을 repeatable read로 변경

tx2의 isolation level을 repeatable read로 변경합니다.

앞의 schedule과 똑같습니다. 하지만 tx1이 먼저 commit했기 때문에 tx2가 write(x=80)을 하려고 하면 실패하고 tx2는 rollback됩니다.

왜냐하면 PostgreSQL에서는 같은 데이터에 대해서 먼저 update한 트랜잭션이 commit 되면 나중 트랜잭션은 rollback하는 규칙이 있습니다. 이런 특징을 first-updater-win이라고 합니다.

먼저 update한 트랜잭션이 rollback하는 경우는 상관 없습니다.

그래서 결국 tx1만 성공하기 때문에 x=10, y=50이 디스크에 적용됩니다.

🚨그럼 tx1은 read committed로 둬도 괜찮을까요?

결론만 말하면 아닙니다. tx1도 repeatable read로 바꿔줘야 합니다.

tx2가 먼저 시작하는 schedule을 가정해봅시다. 현재 tx1은 read committed입니다.

read(tx2,x) - read(tx1,x) - write(tx2,x=80) - write(tx1,x=10)(block) - commit2 - write(tx1,x=10) - read(tx1,y) - write(tx1,y=50) - commit1

read committed에서는 먼저 update한 트랜잭션이 commit시 나중 트랜잭션은 rollback한다는 규칙이 적용되지 않기 때문에 똑같이 lost update가 발생할 수 있습니다.

즉, PostgreSQL에서 lost update를 해결하기 위해서는 관련있는 트랜잭션의 isolation level을 모두 repeatable read로 바꿔줘야 합니다.

📌MySQL에서 lost update 해결

MySQL에서도 마찬가지로 MVCC가 적용되었더라도 read committed level일 때 lost update가 발생합니다.

MySQL도 PostgreSQL처럼 두 트랜잭션을 모두 repeatable read로 변경하면 해결될까요?

안타깝게도 MySQL에서는 repeatable read에 first-updater-win이라는 개념이 없습니다.

따라서 repeatable read로 변경해도 트랜잭션은 실패하지 않고 쭈욱 진행해 lost update가 결국 발생합니다.

즉, MySQL의 MVCC는 lost update를 repeatable read로 바꾸는 것만으로는 lost update를 해결할 수가 없습니다.

그렇다면 어떻게 해야 할까요?

두 트랜잭션은 지금 repeatable read level입니다.

read(tx2,x) - read(tx1,x)(block) - write(tx2,x=80) - commit2 - read(tx1,x) - write(tx1,x=10) - read(tx1,y) - write(tx1,y=50) - commit1

MySQL에서는 read 작업을 할 때 lock을 취득할 수 있도록 해야합니다. 이를 MySQL에서는 locking read라고 합니다.

그래서 tx1, tx2 모두 x에 대한 read 작업을 할 때 write-lock을 취득해야 합니다.

read 작업을 할 때 wirte-lock을 취득하기 위해서는 select SQL문에 FOR UPDATE를 추가해주어야 합니다. 그러면 read를 하더라도 해당 데이터에 대한 write-lock을 취득할 수 있습니다.

그럼 schedule을 살펴보면 tx2가 x를 읽기 위해 write-lock을 취득하고 read를 진행했습니다.

tx1도 x를 읽기 위해 write-lock을 취득하려 하지만 이미 tx2가 점유하고 있어서 취득할 수 없고 기다려야 합니다.

tx2는 계속 진행해서 x=80으로 업데이트하고 commit합니다.

tx1은 이제 lock을 획득할 수 있고 read 작업을 진행합니다. 지금 tx1은 repeatable read level이라서 트랜잭션이 시작한 시점에서 가장 최근에 commit된 데이터를 읽습니다. 따라서 x=50을 읽어야 정상입니다.

하지만 x=80을 읽게 됩니다. 그 이유는 locking read는 가장 최근에 commit된 데이터를 읽기 때문에 read 시점에서 가정 최근에 commit된 데이터는 tx2가 commit한 데이터이기 때문입니다.

MVCC가 적용된 MySQL의 locking read는 isolation level과 상관 없이 read를 이렇게 한다는 것을 꼭 기억해야 합니다.

그 후에 tx1가 x에 40을 빼야하므로 x=50 write 작업을 하고 read(y) 작업을 하는데 이 때도 locking read로 읽는 것이 좋습니다.

다른 트랜잭션이 해당 데이터를 가지고 write 작업을 할 수도 있기 때문입니다.

어찌됐든 이렇게 진행이 되면 결국 x=40, y=50으로 정상적인 결과를 디스크에 저장할 수 있습니다.

그래서 결론적으로 MySQL에서는 repeatable read를 쓰는 것만으로는 해결되지 않고 locking read를 사용해야 lost update를 방지할 수 있습니다. 물론 serializable level을 사용한다면 repeatable level 만으로도 가능할 것입니다.

PostgreSQL에서 서로 다른 두 트랜잭션 중 하나의 트랜잭션만 repeatable read로 바꾸는 것이 아닌 관련있는 트랜잭션도 repeatable read를 사용해야 lost update를 방지할 수 있다고 했습니다.

마찬가지로 MySQL에서도 관련있는 다른 트랜잭션에서도 같은 데이터에 대해 locking read(write-lock)을 해주어야 합니다.

👉locking read 종류

MySQL에 locking read도 종류가 있습니다.

select ... for update;
select ... for share;

for update의 경우에는 exclusive lock을 획득하게 되는 것이고, for share의 경우에는 shared lock을 획득하게 되는 것입니다.

이런 for update, for share 같은 문법은 MySQL 말고 PostgreSQL에서도 존재합니다. 그런데 동작방식이 조금 다릅니다.

✔️MVCC에서 write skew 문제

x=10, y=10

tx1. x와 y를 더해서 x에 쓴다.

tx2. x와 y를 더해서 y에 쓴다.

이 두 개의 트랜잭션이 각각 동작하도록 수행되면 정상적인 결과는 x=20,y=30 혹은 x=30,y=20이 되어야 합니다.

그런데 MVCC에서 repeatable read level로 트랜잭션이 동작하게 되면 이상한 현상이 발생할 수 있습니다.

다음과 같은 schedule로 진행된다고 생각해봅시다

read(tx1,x) - read(tx2,x) - read(tx1,y) - read(tx2,y) - write(tx1,x=20) - write(tx2,y=20) - commit1 - commit2

순서대로 따라가보면 결과가 x=20, y=20이 디스크에 저장됩니다. 정상적인 결과가 아닌 다른 결과가 나오면서 데이터 일관성이 깨졌습니다.

이렇게 서로 관련있는 데이터에 대해 쓰기 작업을 하고 나서 데이터 일관성이 깨지는 현상을 write skew라고 한다고 이전 포스팅에서 살펴보았습니다.

📌MySQL에서 write skew 해결

아까 lost update를 해결했던 것처럼 locking read를 사용하면 됩니다.

read(tx1,x) - read(tx2,x)(block) - read(tx1,y) - write(tx1,x=20) - commit1 - read(tx2,x) - read(tx2,y) - write(tx2,y=20) - commit2

tx1에서 x를 읽기 위해 locking read로 write-lock을 취득한 후 읽습니다. tx2에서도 x에 대해서 read하려고 하면 lock을 획득하지 못해서 기다려야 합니다. 그래서 tx2는 block됩니다.

tx1은 마저 진행합니다. tx1이 y를 읽으려고 할 때도 마찬가지로 locking read로 y에 대한 write-lock을 취득하고 read 작업을 합니다.

tx1이 x=20 write 작업을 하고 commit을 진행합니다.

tx2는 이제 x에 대한 write-lock을 쥘 수 있게 되고 read(x)를 합니다.

그런데 앞에서 말했듯이 repeatable read에서 locking read로 동작하는 경우 read 시점에서 가장 최근에 commit된 데이터를 읽기 때문에 x=10이 아닌 x=20을 읽어옵니다.

tx2가 y를 읽을 때도 locking read를 진행하고 y=20 write 작업까지 마친 후 트랜잭션이 종료됩니다.

x=20,y=30으로 정상적인 결과가 나옵니다.

그래서 MySQL에서 write skew를 해결하기 위해 lost update처럼 locking read를 사용하면 write-skew를 방지할 수 있습니다.

📌PostgreSQL에서 write skew 해결

PostgreSQL에서도 for update, for share 문법이 존재한다고 했습니다. 그래서 PostgreSQL에도 이 방법을 적용할 수 있는데 MySQL과 동작방식이 좀 다릅니다.

read(tx1,x) - read(tx2,x)(block) - read(tx1,y) - write(tx1,x=20) - commit1 - read(tx2,x) - read(tx2,y) - write(tx2,y=20) - commit2

마찬가지로 똑같은 schedule입니다.

PostgreSQL에서도 for update를 통해서 읽기 작업을 하기 위해 write-lock을 획득할 수 있습니다.

PostgreSQL에서는 repeatable read leve일 때 먼저 update한 트랜잭션이 commit 되면 나중 tx는 rollback 된다고 했습니다.(first-updater-win)

따라서 commit1 이후 tx2가 read(x)하는 작업은 실패하고 tx2는 rollback하게 됩니다.

그래서 결국 최종 결과는 x=20,y=10으로 남게 됩니다.

어찌됐든 tx2가 동작하지 않았기 때문에 위 결과는 정상적인 결과입니다. 그리고 실패한 tx2는 나중에 다시 실행을 해주던가 해야합니다.

for update, for share 둘 다 first-updater-win 규칙이 repeatable read에서 적용이 됩니다.

이번엔 for share를 사용해서 read-lock을 사용하는 경우에 대해서 간단히 살펴봅시다.

write(tx1,x=20) - read(tx2,x)(block) - ... - commit1 - read(tx2,x)(abort) - rollback

tx1이 x에 대해 wrtie 작업을 한 이후에 tx2가 x에 대해 read 작업을 위해 read-lock을 획득하려고 하면 tx2는 block 됩니다.

일반적으로 MVCC는 write-write operation에 대해서만 block이 이루어지지만, read-lock을 사용하고 싶은 경우도 생기는데 이 때 for share 문법으로 read-lock을 사용하려고 하면 read-read만 허용하는 것과 같이 동작하게 됩니다.

그래서 tx2는 block되고 tx1이 진행되다 commit이 이루어지면, tx2는 x에 대한 read-lock을 쥐고 read(x)를 할 수 있는데 first-updater-win 규칙 때문에 read 작업은 실패하고 tx2는 rollback하게 됩니다.

📌repeatable read level에서 write-skew 결론

select...for update;
select...for share;

위 문법으로 repeatable read level일 때도 write skew현상을 해결할 수 있습니다.

이 방법은 MySQL, PostgreSQL에서 모두 사용 가능하지만 둘의 동작 방식에 약간 차이가 있습니다.

📌serializable level로 올려서 write-skew 해결

👉serializable

tx1이 serializable로 동작할 때 결과는 repeatable read일 때와 동일합니다. 하지만 serializable level일 때 MySQL과 PostgreSQL 동작에서 차이가 있습니다.

🚨MySQL

MVCC로 동작하기 보다는 lock으로 동작합니다.

repeatable read와 유사하게 동작하지만, serializable level 트랜잭션의 모든 평범한 select 문은 암묵적으로 select ... for share 처럼 동작합니다.

개발자가 평범한 select문을 썼다 하더라도 DBMS가 알아서 for share를 붙여주는 의미다.

exclusive lock이 아닌 shared lock이기 때문에 deadlock이 발생할 가능성이 더 높습니다.

🚨PostgreSQL

Serializable Snapshot Isolation 기법이 적용된 MVCC로 동작합니다. MVCC로 동작하면서도 모든 이상현상을 막아주는 isolation 기법이라고 합니다.

이 부분은 추가적으로 공부가 필요할 것 같습니다.