InnoDB vs Postgres 격리 수준 비교

RDB 가 보장하는 ACID 속성 중 트랜잭션 격리(Isolation) 는,

트랜잭션이 다른 트랜잭션에 영향을 받지 않고 수행되어야 함을 의미합니다.

'영향을 받지 않는다' 라는 말은 곧 동시성 문제를 일으켜서는 안된다는 뜻입니다.

(Dirty read, Unrepeatable read, Phantom read, Lost update, Write skew 등)

데이터베이스는 이를 보장하기 위해 비관적/낙관적 locking, MVCC (Multi Version Concurrency Control) 등을

이용합니다. 하지만 이러한 격리성 보장이 확실할수록 성능 면에서의 불이익은 더 커집니다.

이를 해결하기 위해 여러 격리 수준이 제공되지만 각 격리 수준마다 보장해 주지 못하는 문제점들이 존재합니다.

따라서, 애플리케이션 트랜잭션에서 어떤 보장이 필요한지 충분히 검토하고, 사용하는 데이터베이스의 구현까지 고려하여 격리 수준/재시도 정책 등을 잘 설정해야 합니다.

InnoDB (Mysql)

InnoDB 는 UPDATE, DELETE, SELECT...FOR UPDATE 쿼리시 인덱스로 검색된 전체 레코드에 대해
index-record lock  을 겁니다.
(다른 조건으로 인해 로우가 걸러져 반환되더라도, 조회된 인덱스는 모두 잠김)

index 에 락을 걸기 떄문에 gap-lock 구현이 가능합니다.

secondary index 를 이용한 질의는 secondary index에 락을 건 후  cluster index 도 찾아 락을 겁니다.

활용할 수 있는 인덱스가 없어 풀스캔을 뜨게 되면, 모든 로우에 락을 걸게 되므로 인덱스 설정에 주의해야 합니다.

 

InnoDB performs row-level locking in such a way that when it searches or scans a table
index, it sets shared or exclusive locks on the index records it encounters.
Thus, the row-level locks are actually index-record locks.
A next-key lock on an index record also affects the “gap” before that index record.

 

READ COMMITED

커밋되지 않은 쓰기를 무시합니다. 명시적인 locking (FOR SHARE, FOR UPDATE, UPDATE, DELETE) 시에 index record lock 만 걸고, gap lock 은 걸지 않습니다. UPDATE 와 DELETE 시에 조회된 레코드에 모두 락을 건 후 WHERE 절에서 필터링(반환되지 않는) 로우에 대해 락을 해제합니다. (데드락 위험을 낮추기 위함인듯)

 

REPEATABLE READ

SELECT 는 락을 걸지 않고 처음 SELECT 가 실행된 시점 (스냅숏) 으로부터 읽어옵니다.(실행 시점에서 진행중인 트랜잭션, 현재 트랜잭션보다 txid 가 큰 트랜잭션, 어보트된 트랜잭션에 의해 쓰여진 값 무시)

팬텀 리드, 쓰기 스큐가 일어날 수 있습니다.

 

 

 

transaction 1 에서의 SELECT 쿼리는 스냅숏으로부터 로우를 읽어옵니다. transaction2 가 조건에 해당되는 데이터를 INSERT 후 커밋했음에도  transaction 1 에서는 보이지 않습니다. 하지만 transaction1 에서 UPDATE, SELECT FOR UPDATE쿼리를 수행했을 때, transaction2 에서 INSERT 한 로우가 보이게 되고, 조회되었던 3개의 로우가 아닌 4개의 로우가 대상으로 검색됩니다. 이런 현상을 Phantom Read 라고 합니다.

 

 

color 컬럼에 인덱스가 지정되어 있다고 가정합니다.
transaction 1 에서는 'BLACK' -> 'WHITE', transaction 2 에서는 'WHITE' -> 'BLACK' 으로 값을 변경하고 있습니다.

직렬적으로 실행한 경우, 모든 값이 WHITE 이거나 BLACK 이여야 합니다.

transaction 2 가 BLACK 으로 변경하고 커밋하지 않은 변경사항을 transaction 1 의 UPDATE 에서 읽고 락 대기에 들어갑니다. (transaction 1 이 exclusive lock 을 걸었기 때문에).

물론 transaction 2 가 abort 되면 transaction 2 에서 수정된 로우는 transaction 1 에 의해 업데이트 되지 않습니다.

 

 

 

위에서는 transaction 1 이 transaction 2 가 업데이트한 인덱스를 읽고 잠금에 걸렸고, 이번에는 transaction 2 에서 값을 BLACK 으로 업데이트 했음에도 불구하고 transaction 1 은 잠금에 걸립니다. 어떻게 된 일일까요?

 

이는 InnoDB 의 secondary index 와 mvcc 의 관리 방식 때문입니다.

 

Records in a clustered index are updated in-place,and their hidden system columns point
undo log entries from which earlier versions of records can be reconstructed. 
Unlike clustered index records, secondary index records do not contain hidden system
columns nor are they updated in-place. 
 When a secondary index column is updated, old secondary index records are delete-marked
, new records are inserted, and delete-marked records are eventually purged.
 When a secondary index record is delete-marked or the secondary index page is updated 
by a newer transaction, InnoDB looks up the database record in the clustered index.
In the clustered index, the record's DB_TRX_ID is checked, and the correct version of 
the record is retrieved from the undo log if the record was modified after the reading
transaction was initiated.

 

 

로우가 업데이트되면 secondary index 를 바로 업데이트하는 것이 아니라, delete-mark 를 해둔 뒤 새로운 인덱스를 삽입합니다. 또, deleted-mark 된 인덱스, 새로 삽입된 인덱스 레코드에 모두 락이 걸리는 듯 합니다.

SELECT 와 UPDATE 쿼리는 모두 delete-mark 된 인덱스 역시 스캔합니다. SELECT 의 경우 찾은 index page 가 더 최근 트랜잭션에 의해 수정되었을 경우 covering index 테크닉을 사용하지 않고, 무조건 clustered index 를 참조(필요하다면 이후 undo 로그를 읽어) 적절한 버전의 로우를 반환합니다. UPDATE, SELECT ... FOR UPDATE 의 경우 clustered index 에서 undo log 를 읽지 않습니다. (조건 일치 여부를 정할때 버전을 고려x)  SELECT 와 UPDATE 사이에 다른 트랜잭션에서 UPDATE 조건에 맞는 로우를 INSERT 후 커밋했을 경우 UPDATE 의 대상이 됩니다.

이러한 결과가 Phantom Read 를 만듭니다.

 

delete-mark 된 인덱스는 undo 로그에서 해당 버전의 레코드가 사라질 때 함께 제거됩니다. (purge 라고 부릅니다)

 

-> 위 2번째 예시에서 transaction 2 가 WHERE color = 'BLACK' 인 인덱스를 delete-mark 하고 color = 'WHITE' 인 인덱스를 삽입합니다. 두 인덱스에 모두 락이 걸리므로, transaction 1 에서 WHERE color = 'WHITE' 로 update 를 수행했을 때, color = 'WHITE' 인 추가된 인덱스를 스캔하고, 락을 걸기 위해 대기하게 됩니다.

 

-> 위 3번째 예시에서, transaction 2가 로우를 업데이트해  color = 'BLACK' 인 인덱스에는 락이 걸리고 delete-mark 됩니다.

 이 인덱스 역시 WHERE color ='BLACK' 으로 조회하는 transaction 1 에 의해 스캔 대상이 되어 락 대기에 걸립니다.

하지만, transaction 2 가 커밋한 후에 transaction 1 에서 조건을 다시 비교하기 때문에 update 는 수행되지 않습니다

 

 

https://d2.naver.com/helloworld/16110

https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

MySQL :: MySQL 8.0 Reference Manual :: 15.3 InnoDB Multi-Versioning

https://medium.com/myinterest/innodb%EC%99%80-postgresql%EC%9D%98-mvcc-4471b721cb31

InnoDB와 PostgreSQL의 MVCC

 

REPEATABLE READ 에서는 FOR UPDATE, FOR SHARE, UPDATE, DELETE 에  NEXT KEY LOCK 또는 GAP LOCK 이 적용됩니다.  NEXT KEY LOCK = ROW LOCK + GAP LOCK (로우와 로우 사이 간격을 잠가 INSERT 를 막습니다)
INSERT 의 경우 INSERT INTENTION LOCK 이 걸리는데, 이는 shared gap lock, 즉 INSERT 끼리는 공유할 수 있지만,
UPDATE 가 거는 EXCLUSIVE GAP LOCK 과는 공유될 수 없습니다.

SELECT * FROM user WHERE id = 5 FOR UPDATE -> id=5 인 유저가 없을 땐 해당 위치에 GAP LOCK만 걸립니다

UNIQUE INDEX 인 경우에는 GAP LOCK 이 적용되지 않습니다.

InnoDB 는 가상의 최상단, 최하단 노드를 가지고 락을 걸 수 있습니다.

gap lock 은 인덱스 레코드 사이에 걸리는 락입니다.

 

SERIALIZABLE
모든 SELECT 에 NEXT KEY LOCK (SHARED LOCK) 을 거는 것으로 팬텀 리드, 쓰기 스큐(갱신 손실 포함)을 해결합니다. 

이를 2PL (2 Phase Lock -> Exclusive Lock, Shared Lock) 이라고 합니다. 데드락이 걸릴 확률이 높지만,

데이터베이스에서 데드락을 자동으로 감지하여 문제가 되는 트랜잭션들을 abort 시켜 해결합니다.

 

(NEXT KEY LOCK 이 걸리므로 한번 읽은 레코드 + 범위에 대해 update, insert 불가)

(REPEATABLE READ 에서 팬텀 리드는 SELECT 가 아무런 락을 걸지 않기 때문에 FOR UPDATE, FOR SHARE 같은

명시적인 locking 이 없으면 삽입/수정 후 커밋이 가능. 이상태에서 UPDATE 를 수행할 경우, 스냅숏에 없거나 수정된 로우를 보게 될 수 있다는 걸 Phantom Read 라고 부름)

 

공식 레퍼런스가 가장 설명이 확실하고 잘 되어 있습니다.

참조: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

Postgres

 

먼저, Postgres 에서는 gap lock 이 존재하지 않습니다.

InnoDB 에서는 실질적인 락이 인덱스에 걸리는 반면,

Postgres 에서는 인덱스에 read/write 할때 short-term 락을 걸고 바로 해제합니다.

 

Short-term share/exclusive page-level locks are used for read/write access. 
Locks are released immediately after each index row is fetched or inserted.
These index types provide the highest concurrency without deadlock conditions.

 

Postgres 는 로우가 실제 저장되어 있는 data page 의 record-header 의 정보를 바탕으로 락 여부를 확인합니다

 

PostgreSQL stores information that a row is locked only and exclusively in the row
version inside the data page (and not in RAM).
It means that it is not a lock in a usual sense, but just some indicator.
Actually, the ID of the xmax transaction, along with additional information bits,
serves as the indicator; we will look at how this is organized in detail a little later.

https://postgrespro.com/blog/pgsql/5968005

Locks in PostgreSQL: 2. Row-level locks

 

READ COMMITED

트랜잭션이 시작한 시점에서의 스냅숏을 통해 SELECT 쿼리를 수행합니다.  현재 트랜잭션에서 업데이트된 로우도 보이게 됩니다.

concurrent update 시에는 락 대기에 들어가 경합중인 트랜잭션이 커밋/어보트 되기를 기다린 후, update 조건을 다시 확인하고 실행됩니다. 즉, 다른 트랜잭션이 update/delete 한 값을 보고 갱신/삭제를 수행합니다 -> Phantom read 가 발생할 수 있습니다.

 

https://www.postgresql.org/docs/7.1/xact-read-committed.html

Read Committed Isolation Level

 

 

REPEATABLE READ

SELECT 는 처음 SELECT가 실행된 시점 (스냅숏) 으로부터 읽어옵니다. InnoDB 와 달리 UPDATE, DELETE 역시 스냅숏을 기준으로 질의됩니다. 동일한 로우에 대해 동시 업데이트/삭제를 수행할 시, concurrent update, concurrent delete 등의 오류를 출력하고 트랜잭션이 어보트됩니다. 이는 갱신 손실(Lost Update) 를 막아줍니다.
(READ -> MODIFY -> WRITE 를 수행할 때 동시성 문제로 인해 쓰기가 손실되는 문제)
혹자는 갱신 손실 감지 기능이 없는 InnoDB 를 완전한 REPEATABLE READ 격리수준을 제공하지 않는다고 말하기도 합니다.

 

 

 

transaciton 1 에서 SELECT 쿼리를 통해 스냅숏을 생성하고, transaction 2 에서 해당 조건에 부합하는 로우를 삽입합니다.

하지만 Postgres 의 경우 UPDATE 쿼리 역시 생성된 스냅숏을 기준으로 질의하기 때문에 transaction 2 에서는 transaction 1 에서 삽입한 로우가 업데이트 대상이 되지 않습니다. (transaction 2 의 commit 이 transaction 1 의 UPDATE 보다 늦어질 경우에도 transaction 1 은 UPDATE 시에 락 대기에 걸리지 않습니다)

 

 

 

transaction 2 에서 UPDATE 쿼리를 통해 id > 5 인 로우에 대해 exclusive lock 을 건 뒤 커밋하지 않았기 때문에

transaction 1 은 락 대기에 걸립니다. transaction 2 가 성공적으로 커밋될 경우, transaction 1 은 갱신 손실을 감지하고 트랜잭션을 어보트 시킵니다. (transaction 2 에서 DELETE 등을 수행했을 경우, transaction 1 에서 SELECT 없이 UPDATE 만 수행한 경우도 마찬가지입니다). 앞서 말했듯, READ COMMITED 에서는 앞서 커밋된 트랜잭션이 변경한 값을 가지고 다시 UPDATE 를 시도합니다.


SERIALIZABLE

Postgres 의 설계 원칙에서, 모든 격리수준에서 명시적인 잠금 ( SELECT FOR UPDATE 등) 을 쓰지 않는 이상,

쓰기는 읽기를 블러킹하지 않고, 읽기는 쓰기를 블러킹하지 않아야 합니다.

2PL 에서 로우 읽기시에 거는 공유 잠금 (Shared Lock) 은 쓰기시에 걸리는 배타 잠금 (Exclusive Lock) 과 호환되지 않으므로

이 원칙에 위배됩니다. 따라서 Postgres 의 직렬성 격리 수준은 2PL 을 사용하지 않고,  동시성 문제를 일으킬 수 있는 위험한 READ-WRITE 패턴을 감지하여 어보트 시키는, 직렬성 스냅숏 격리(SSI - Serializable Snapshot Isolation)를 사용합니다.

 

 

 

 

두 트랜잭션의 read-write 에서, 한 트랜잭션의 write 가 다른 트랜잭션의 read 에 영향을 끼치는 경우입니다. 이럴 경우 직렬적으로 실행했을 경우와 다른 결과가 나올 수 있습니다. Postgres 는 UPDATE, DELETE 시에도 스냅숏에서 질의하기 떄문에  InnoDB 와 달리 다른 트랜잭션에서 업데이트한 값을 보고 락을 걸지도 않고, 같은 로우에 대한 갱신 손실이 아닌 경우 REPEATABLE READ 의 갱신 손실 감지에 의해 어보트되지 않습니다. 이럴 경우 REPEATABLE READ 에서는 쓰기 스큐가 일어날 수 있고, SERIALIZABLE 격리 수준을 이용하여 두 트랜잭션을 직렬화해야 합니다. 위와 마찬가지로 Optimistic locking 을 사용하여 문제가 되는 트랜잭션을 어보트시킵니다.

 

 

참조-  https://wiki.postgresql.org/wiki/SSI

https://d2.naver.com/helloworld/16110