[Database] DB MVCC와 PostgreSQL, MySQL의 동작 비교

기존 Lock based Concurrency Control

	read-lock	write-lock
read-lock	O	X
write-lock	X	X

 

위 표는 각 read와 write lock의 호환 관계를 나타낸 표이다.

예를 들어 두 Transaction이 모두 read-lock + read-lock을 가지고 있을 때는 block이 될 필요 없지만, 한 Transaction이 read-lock, 다른 Transaction은 write-lock을 가지고 있을 경우 둘 중 하나만 실행이 가능하며, 다른 한쪽은 unlock 할 때까지 기다려야 한다.

 

이러한 호환성 문제를 개선하기 위해서 MVCC(Multiversion Concurrency Control)가 등장한다.

MVCC (Multiversion Concurrency Control)

다중 버전 동시성 제어(MVCC)는 전통적인 Lock 메커니즘과 달리 성능 병목 현상과 잠재적인 교착 상태를 초래하지 않으면서 여러 거래가 동시에 데이터베이스에 액세스할 수 있도록 한다.

MVCC의 핵심 개념은 각 데이터 항목의 여러 Version을 유지하는 것이다.

이는 Transaction이 데이터를 읽거나 수정할 때 다른 Transaction이 동시에 동일한 데이터를 수정하더라도 특정 시점의 일관된 데이터베이스 SnapShot을 볼 수 있도록 한다. 

	read-lock	write-lock
read-lock	O	O
write-lock	O	X

Lock에 관점에서 보면 MVCC는 read-lock과 write-lock 이 동시에 Transaction이 실행될 수 있도록 구현한다.

MVCC의 특징

• 데이터를 read할 때 특정 시점 기준으로 가장 최근에 commit 된 데이터를 읽는다.
• 데이터 변화(write) 이력을 관리한다.(이는 공간적 비용을 더 필요로 한다.)
• read 할 때 Lock을 사용하지 않아서 read 와 write는 서로를 block하지 않는다. (이는 성능적 측면에서 큰 장점이 있다.)
• MVCC는 commit된 데이터만 읽는다.
• 그리고 commit을 하면 writelock을 unlock을 한다. 이것은 recoverability를 위함이다.

PostgreSQL에서 Lost Update 문제와 해결

Lost Update가 발생하는 시나리오 

상황

• Transaction 1: x가 y에 40을 이체한다.
• Transaction 2: x에 30을 이체한다.
• DB에는 x = 50, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 40, y = 50 이어야 한다.
• Transaction 1은 read committed Isolation level이 적용되어 있다.
• Transaction 2은 read committed Isolation level이 적용되어 있다.

read committed

read 하는 시간을 기준으로 가장 최근에 commit 된 데이터를 읽는다.

repeatable read

transaction 시작 시간 기준으로 가장 최근에 commit 된 데이터를 읽는다.

PostgreSQL에서는 같은 데이터에 먼저 update한 transaction이 commit 되면 나중에 read 한 transaction은 rollback 된다.

 

시나리오

Transaction 1 (x가 y에 40을 이체한다.)	Transaction 2 (x에 30을 이체한다.)
read(x) = 50
write-lock(x), write(x) = 10	Transaction 2 Start
	read(x) = 50
	write-lock(x), write(x) = 80 (Block)
read(y) = 10	Block
write-lock(y), write(y) = 50	Block
commit, write-unlock(x), write-unlock(y)
	write-lock(x), write(x) = 80
	commit, write-unlock(x)

결과적으로 잘못된 데이터인 x = 80, y = 50을 저장하게 된다.

Isolation Level 수정으로 Lost Update 해결

상황

• Transaction 1: x가 y에 40을 이체한다.
• Transaction 2: x에 30을 이체한다.
• DB에는 x = 50, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 40, y = 50 이어야 한다.
• Transaction 1은 read committed Isolation level이 적용되어 있다.
• Transaction 2은 repeatable read Isolation level이 적용되어 있다.

시나리오

Transaction 1 (x가 y에 40을 이체한다.)	Transaction 2 (x에 30을 이체한다.)
read(x) = 50
write-lock(x), write(x) = 10	Transaction 2 Start
	read(x) = 50
	write-lock(x), write(x) = 80 (Block)
read(y) = 10	Block
write-lock(y), write(y) = 50	Block
commit, write-unlock(x), write-unlock(y)
	write-lock(x), write(x) = 80 (Fail!)
	rollback

repeatable Isolation level은 같은 데이터에 먼저 update한 transaction이 commit 되면

나중에 read 한 trasnaction은 rollback 된다.

따라서 Transaction 2는 재시도를 해야 한다.

이와 같은 시나리오를 PostgreSQL의 first-updatater-win라고 한다.

한쪽만 Repeatable Read인 경우?

상황은 위 예제와 같다.

시나리오

Transaction 1 (x가 y에 40을 이체한다.)	Transaction 2 (x에 30을 이체한다.)
	read(x) = 50
read(x) = 50
	write-lock(x), write(x) = 80 (Block)
write-lock(x), write(x) = 10 (Block)
Block	Commit, write-unlock(x)
write-lock(x), write(x) = 10
read(y) = 10
write-lock(y), write(y) = 50
Commit

결과적으로 x = 10, y = 50으로 저장되어 잘못된 데이터가 저장되었다.

따라서 한 Transaction의 Isolation level만 조정한다면, 문제가 해결되지 않는다.

양쪽의 Isolation Level 수정으로 Lost Update 해결

상황

• Transaction 1: x가 y에 40을 이체한다.
• Transaction 2: x에 30을 이체한다.
• DB에는 x = 50, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 40, y = 50 이어야 한다.
• Transaction 1은 repeatable read Isolation level이 적용되어 있다.
• Transaction 2은 repeatable read Isolation level이 적용되어 있다.

시나리오

Transaction 1 (x가 y에 40을 이체한다.)	Transaction 2 (x에 30을 이체한다.)
	read(x) = 50
read(x) = 50
	write-lock(x), write(x) = 80
write-lock(x), write(x) = 10 (Block)
Block	Commit, write-unlock(x)
write-lock(x), write(x) = 10 (Fail!)
Rollback

마찬가지로 repeatable read Isolation level은 같은 데이터에 먼저 update 한 transaction이 commit 되면

나중에 read 한 trasnaction 1은 rollback 된다.

 

정리하자면, PostgreSQL은 Lost Update현상을 MVCC를 적용하여 해결하려면 Isolation level을 Repeatable read로 해야 한다.

MySQL의 Lost Update 해결

MySQL은 Locking read 기반으로 Lost Update를 해결할 수 있다.

Locking read: Read Lock을 얻는 동시에, Write Lock도 같이 얻는다.

SELECT balance FROM accout WHERE id = 'x'
FOR UPDATE

위 쿼리문에서 FOR UPDATE는 Read Lock을 얻는 동시에, Write Lock도 같이 얻는 실행문이다.

Locking read는 가장 최근에 commit 된 데이터를 읽는다.

Locking Read를 사용한 Lost Update 해결

상황

• Transaction 1: x가 y에 40을 이체한다.
• Transaction 2: x에 30을 이체한다.
• DB에는 x = 50, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 40, y = 50 이어야 한다.
• Transaction 1은 repeatable read Isolation level이 적용되어 있다.
• Transaction 2은 repeatable read Isolation level이 적용되어 있다.

시나리오

Transaction 1 (x가 y에 40을 이체한다.)	Transaction 2 (x에 30을 이체한다.)
	write-lock(x) (Locking read) + read(x) = 50
write-lock(x) (Locking read but Block!) + read(x) = 50
Block	write(x) = 80
Block	Commit, write-unlock(x)
read(x) = 80 (not 50)
write-lock(x), write(x) = 40
write-lock(y) (Locking read) + read(y) = 10
write-lock(y), write(y) = 50
Commit, write-unlock(y)

Locking read는 가장 최근에 commit된 데이터를 읽기 때문에, 두 번째 read(x) 때 50이 아닌 80을 읽는다.

PostgreSQL에서는 Rollback을 했지만, MySQL에서는 Lock을 기반으로 진행한다는 차이점이 있다.

Repeatable read에서 write skew 문제 해결

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

상황

• Transaction 1: x와 y를 더해서 x에 쓴다.
• Transaction 2: x와 y를 더해서 y에 쓴다.
• DB에는 x = 10, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 20, y = 30 or x = 30, y = 20 이어야 한다.
• Transaction 1은 repeatable read Isolation level이 적용되어 있다.
• Transaction 2은 repeatable read Isolation level이 적용되어 있다.

시나리오

Transaction 1: x와 y를 더해서 x에 쓴다.	Transaction 2: x와 y를 더해서 y에 쓴다.
read(x) = 10
	read(x) = 10
read(y) = 10
	read(y) = 10
write(x) = 20
	write(y) = 20
commit
	commit

위 결과는 x = 20, y = 20을 저장하게 되어 잘못된 데이터가 저장되었다.

MySQL에서 write skew 해결 (Locking Read사용)

상황

• Transaction 1: x와 y를 더해서 x에 쓴다.
• Transaction 2: x와 y를 더해서 y에 쓴다.
• DB에는 x = 10, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 20, y = 30 or x = 30, y = 20 이어야 한다.
• Transaction 1은 repeatable read Isolation level이 적용되어 있다.
• Transaction 2은 repeatable read Isolation level이 적용되어 있다.

시나리오

Transaction 1: x와 y를 더해서 x에 쓴다.	Transaction 2: x와 y를 더해서 y에 쓴다.
write-lock(x) (Locking read) + read(x) = 10
	write-lock(x) +  read(x) = 10 (block)
write-lock(y) (Locking read) + read(y) = 10	block
write-lock(x), write(x) = 20	block
commit, write-unlock(x)	block
	write-lock(x) (locking read) + read(x) = 20
	write-lock(y) (locking read) + read(y) = 10
	write(y) = 30
	commit, write-unlock(x), write-unlock(y)

PostgreSQL에서 write skew 해결 (Rollback)

상황

• Transaction 1: x와 y를 더해서 x에 쓴다.
• Transaction 2: x와 y를 더해서 y에 쓴다.
• DB에는 x = 10, y = 10이 저장되어 있다.
• 정상적으로 동작한다면 최종 결과는 x = 20, y = 30 or x = 30, y = 20 이어야 한다.
• Transaction 1은 repeatable read Isolation level이 적용되어 있다.
• Transaction 2은 repeatable read Isolation level이 적용되어 있다.

시나리오

Transaction 1: x와 y를 더해서 x에 쓴다.	Transaction 2: x와 y를 더해서 y에 쓴다.
read(x) = 10
	read(x) = 10
read(y) = 10
	read(y) = 10
write-lock(x), write(x) = 20
	write(y) = 20 (block)
commit, write-unlock(x)
	rollback

postgreSQL의 repeatable read는 같은 데이터에 먼저 update한 Transaction이 commit되면 나중 Transaction은 rollback 된다.

Serializable level

MySQL

• Repeatable read와 유사하다.
• Transaction의 모든 평범한 select문은 암묵적으로 select... FOR SHARE 처럼 동작한다.
• FOR UPDATE는 Exclusive lock이라 성능적으로 불리하기 때문이다.
• 그러나 FOR SHARE는 DeadLock 발생 위험이 있다.

PostgreSQL

• SSI(serializable snapshot isolation)로 구현
• first-committer-winner로 동작

참고

• https://youtu.be/wiVvVanI3p4?si=j5_iKBdvGYG-x-GO
• https://youtu.be/-kJ3fxqFmqA?si=oARPxH-X8q8bvTr-