Transaction Isolation - MySQL과 Postgresql 비교

Transaction Isolation Level / read phenomena

ACID 4요소 중 하나가 isolation.

• 가장 높은 수준의 isolation -> all concurrent txn will not affect each other.
• 반대로 말하면, txn이 다른 txn에게 영향을 줄 수 있는 정도가 구분된다는 뜻. 이걸 Read Phenomenon이라 부른다.

1. Dirty Read : uncommitted txn값을 읽어들이는 것. txn이 commit일지 rollback일지 알 수 없기 때문에 incorrect data를 사용하게 될 가능성이 높음
2. Non-Repeatable Read : 동일한 쿼리 요청을 했는데 응답값이 달라지는 경우
3. Phantom Read : 2와 비슷하지만, 하나의 row가 아니라 여러 개의 row를 읽어들일 때 발생하는 것을 말함. 예컨대 조건절 걸어서 table을 두 번 조회했는데 값이 다른 경우라던가.
   1. DB에 새 row가 추가되거나 기존 row를 삭제하는 등 조건절에 영향을 주는 연산이 발생했을 경우 가능함.
4. Serialization Anomaly

4개의 Isolation Level

• Read Uncommitted : Dirty Read 포함해서 4개 Read Phenomena 전부 발생가능
• Read Committed : Dirty Read 해결
• Repeatable Read : Non-repeatable read / Phantom Read 해결
• Serializable : 네 개 다 해결.
  • concurrent txn을 적절히 order했을 때, final result가 반드시 동일하도록 하는 경우의 수가 하나 이상은 존재한다는 뜻

Mysql 8과 Postgres 12로 실험.

• Mysql의 경우 select @transaction_isolation; 으로 확인 가능하며, 디폴트 설정은 Repeatable Read.
• global isolation level을 체크하려면 select @global.transaction_isolation; 으로 확인. 마찬가지로 디폴트는 Repeatable Read

  Mysql

Read Uncommitted

Mysql 8 버전으로 두 개의 mysql 콘솔 열어서 테스트.

• mysql 두 개의 콘솔을 열고 각각의 isolation level을 read uncommitted로 설정한 후 진행
• 두 개의 txn을 순차적으로 열었을 때, 왼쪽의 txn에서 account의 balance 100 에 -10 해준 상태.
• 왼쪽 txn을 commit하지 않았음에도 오른쪽 txn에서 데이터를 조회하면 100이 아니라 90을 읽는다.

READ Committed

• 왼쪽 Txn에서 balance에 다시 10을 빼서 값이 80인 상황
• 오른쪽 txn에서 balance 조회하면 80이 아니라 90을 읽는다. uncommited된 row는 읽지 않음.

그러나 왼쪽의 txn을 commit한 뒤 오른쪽 txn에서 조건절을 걸면, row가 90이 아니라 80으로 바뀌어 있는 걸 볼 수 있다.

• 하나의 txn 내에서 같은 row를 두 번 읽었는데, 값이 다른 현상. Phantom Read 발생.

REPEATABLE READ

• repeatable read를 설정하면, 왼쪽 txn을 commit한 후에도 오른쪽 txn에서는 값이 변경되지 않은 걸 확인할 수 있다. Phantom Read 방지.

그러나 오른쪽 txn에서 만약 값을 변경했을 경우 문제가 생긴다.

• txn 2에서 10을 빼면, 80 - 10이므로 70을 리턴하는 것이 오른쪽 txn 입장에서 자연스럽다.
• 하지만 실제 row는 왼쪽 txn에서 먼저 commit한 70이므로, 70 - 10인 60이 된다. 오른쪽 txn이 연산 마치고 다시 조회할 경우 60이 리턴된다.
• isolation이 완벽하게 지켜지지는 않은 경우로, 사용자 입장에서 합리적인 전개방식은 80 - 10이 60이 된 걸 보여주는 것보다는 에러를 리턴하는 것이 좋아 보임

Serializable

• serializable일 경우, mysql 자체적으로 select 문을 전부 select for share 로 변경함.
• select for share 옵션이 들어갈 경우 txn에서 read는 가능하지만 update / delete에서는 lock을 필요로 한다.
• 거의 동시에 txn이 시작됐다고 가정하면, 한쪽에서 update 요청할 경우 다른 쪽 txn이 정상적으로 commit이나 rollback되기 전까지는 lock 걸려있는 상태가 된다.

따라서 오른쪽 txn이 먼저 row 변경을 시도했다면, 왼쪽 txn에서 update나 delete 로직을 실행하려 할 때 lock이 걸리는 걸 볼 수 있다.

• 단 lock에도 timeout duration이 존재함. lock 잡고 있던 오른쪽 txn이 commit이나 rollback을 하지 않으면, 대기하던 왼쪽 txn에서 에러 리턴함.
• 따라서 Serializable을 사용할 때에는 transaction Retry strategy가 반드시 있어야 한다.

• 만약 왼쪽 txn이 select 후 update 요청해서 lock이 걸려있는 상황에서 오른쪽 txn도 update를 시도할 경우 -> Deadlock 발생.
• Deadlock Strategy도 미리 정의해야 한다.

Postgresql

READ UNCOMMITTED -> 사실상 read commit

Postgres의 경우 show transaction isolation level 명령어로 확인 가능. 디폴트 설정은 Read Committed.

• mysql은 console마다 isolation level을 설정할 수 있지만, postgresql은 txn 내에서만 설정할 수 있음. 따라서 txn의 isolation 설정은 해당 txn에만 영향을 받도록 되어 있다.

문서를 읽어보면 postgres의 read-uncommitted은 실제 동작방식상 read-committed와 동일하다.

• 따라서 위 예시처럼 read-uncommitted로 isolation 설정하고 한쪽 txn에 update를 실행해도, dirty read는 발생하지 않는다.
• 말하자면 postgres는 isolation level을 3가지만 지원하는 셈. 가장 낮은 레벨은 아예 설정할 수 없게 되어 있다고 보면 됨

READ COMMITTED

• Phantom Read 현상이 발생한다.

REPEATABLE READ

왼쪽 txn이 commit까지 완료했지만, 오른쪽 txn은 왼쪽의 commit 값을 반영하지 않는다.

• same query always return the same result. = Phantom Read 방지.
• update를 시도할 경우 'cannot serialize access due to concurrent update'
  • mysql은 컬럼을 그냥 업데이트하지만, postgres는 conccurent update 발생할 경우 에러를 리턴해준다.
  • confusing state를 방지한다는 측면에서는 좀더 나아 보

Serialization anomaly?

예시

1. 왼쪽 txn에서 계정의 모든 balance를 합산한 결과물을 새 row로 추가했다.
   • insert into accounts{owner, balance, concurrency} values {'sum', 270, 'USD'};
2. 동일한 연산을 오른쪽 txn에서도 수행하려고 할 경우
   • 왼쪽 txn에서 연산한 것과 동일하게 수행할 수 있음. 왼쪽에서 추가한 row는 오른쪽 txn이 시작될 때에는 만들어지지 않았으므로
3. 결과: 동일한 값을 가진 두 개의 row가 DB에 등장한다. -> Serialization anomaly

• Serialization Anomaly : 만약 두 개의 연산을 순차적으로 실행할 경우 발생하지 않았을 결과가 발생하는 것. (두 개의 연산 중 무엇이 먼저 실행되었는지는 중요하지 않음)

SERIALIZABLE

• 왼쪽 txn에서 column의 모든 값을 합한 결과를 row에 insert 후 commit
• 동일한 연산을 오른쪽 txn에서도 수행하고 commit할 경우 에러가 발생한다.
  • could not serialize access due to R/W dependencies among transactions 라는 에러 리턴
  • 재시도하면 될 거라는 hint도 같이 제공 ... Serialization anomaly 이슈 해결

postgres는 potential read phenomena를 탐지하기 위해 Read / Write dependency checking mechanism을 사용함.

mysql의 경우 locking mechanism을 사용함. isolation level을 serializable로 설정한 뒤 테스트

• 왼쪽 Txn에서 sum값을 row로 insert.
• 오른쪽 txn에서 select를 요청할 경우 lock이 걸려서 값이 조회되지 않고 대기상태가 된다.
• 왼쪽 txn에서 commit이나 rollback을 수행해야 오른쪽 txn의 select 메소드가 실행됨.