PSQL 격리 수준과 관련한 Q&A

Questions - REPEATABLE READ

Isolation 이란? 트랜잭션들이 linear 하게 실행했을 때와 같은 결과를 보장하는 것.

쉽게 예측하기 힘든 결과를 내거나 SERIALIZABLE 없이는 격리가 깨지는 쿼리 패턴을 소개하려고 함.

 

Question 1.

 

query-1)

SELECT * FROM apps WHERE id = '1' --- (1) 

UPDATE apps SET title = "cuteTitle" WHERE app.id = '1'  --- (4)

 

query-2)

SELECT * FROM apps WHERE id = '1' --- (2)

UPDATE apps SET title = "fancyTitle" WHERE app.id = '1' --- (3)

• Answer
  • 락 경합 이후 한쪽은 commit 에 성공하고, 다른 쪽은 concurrent update 로 인해 fail
  • update 시점에 다른 트랜잭션이 로우에 대해 쓰기를 진행했는지 알 수 있기 때문
  • InnoDB 에서는 이거 안된다! 무조건 FOR UPDATE 걸어야 함

 

Question 2.

 

workers

id	state
camel	at_office
hoo	at_office
ted	dead

 

query-1)

START TRANSACTION --- (1)

existingWorkers = SELECT * FROM **workers** FOR UPDATE
										 WHERE state = 'at_office' --- (6)

if len(existingWorkers) > 1:
	UPDATE workers SET state = 'out_of_office' 
		WHERE work_state.id = 'camel' AND state = 'at_office'  --- (7)
	
COMMIT --- (8)

 

query-2)

START TRANSACTION --- (2)

existingWorkers = SELECT * FROM **workers** FOR UPDATE 
										WHERE state = 'at_office' --- (3)

if len(existingWorkers) > 1:
	UPDATE worker**s** SET state = 'out_of_office' 
		WHERE work_state.id = 'hoo' AND state = 'at_office'  --- (4)
	
COMMIT --- (5)

• Answer: 
  • FOR UPDATE 가 있는 경우: 한쪽이 실패! 
  • FOR UPDATE 가 없는 경우: 양쪽 모두 성공해 정합성 깨짐
• FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

 

Question 2-1. 그럼 INSERT 는..?

 

query-1)

START TRANSACTION --- (1)

existing = SELECT * FROM workers 
						WHERE state = 'at_office' FOR UPDATE ; --- (6)

if len(existing) < 1:
	INSERT INTO **workers** VALUES('velmont', 'at_office') -- (7)
	
COMMIT --- (8)

 

query-2)

START TRANSACTION  --- (2)

existing = SELECT * FROM worker**s** 
						WHERE state = 'at_office' FOR UPDATE --- (3)

if len(existing) < 1:
	INSERT INTO **workers** VALUES('camel', 'at_office') --- (4)
	
COMMIT --- (5)

• Answer
  • psql 은 SELECT FOR UPDATE, UPDATE 쿼리도 스냅샷을 본다
  • 둘 다 된다..! → FOR UPDATE 와 무관하게 비지니스 규칙이 깨진다
    

 

Question 3.

colors

name color

1	black
2	white
3	black
4	white`

 

query-1)

SELECT * FROM colors WHERE color = 'black' FOR UPDATE;

UPDATE colors SET color = 'white' WHERE id IN (ids);

SELECT * FROM colors WHERE color = 'white' FOR UPDATE;

UPDATE colors SET color = 'black' WHERE id IN (ids);

UPDATE colors SET color='white' WHERE color='black'

 

query-2)

UPDATE colors SET color='black' WHERE color='white'

 

• Answer
  • linear 하게 실행된다면 color 는 모두 black 또는 모두 white 여야 함 
  • 하지만 white → black, black → white 로 바뀌어 isolation 이 깨지는 경우가 발생함

 

Why?

 

단조 증가하는 xid

• 트랜잭션 아이디는 wraparound 되기 전까지 단조 증가 ( 항상 비교 가능 )

로우 단위로 xmin, xmax 관리

• 로우의 가시성 범위를 제약함
• xmin ≤ xid < xmax (xmax 가 ongoling xid list 에 있는 경우는 invalidate 되지 않았다고 판단)
• xmin 이 트랜잭션이 시작될 때 실행중인 xid list 에 없어야 함
• → 시작 시점에 커밋하지 않은 로우는 보여서는 안되므로

xmin, xmax 의 업데이트

• xmin은 로우를 업데이트한 트랜잭션 id
• xmax은 로우를 invalidate 한 트랜잭션 id ( update, delete )

row	xmin	xmax
old	1	3
new	3	-

 

쓰기 충돌 감지

• 트랜잭션은 로우 update 시 가시성 범위 밖에 있는 txid 가 새로운 로우를 쓰고 있는 경우
• xmin, xmax 를 통해 이를 알 수 있음
• 해당 트랜잭션에 대기 후 커밋시 cocurrent update 로 실패
• select for update 도 같은 동작 ( repeatable read 이상에서 concurrent update fail )

 

쓰기 충돌 감지가 안되는 경우는?

UPDATE WHERE SET 조건이 교차함

• black -> white, white -> black 으로 바꾼 것처럼 두 UPDATE 의 WHERE 과 SET 이 교차했다
• 아래 케이스 = SELECT & UPDATE 로 풀어 보아도 괜찮다 

 

다른 tx 가 SELECT 한 조건에 대한 insert / update 가 교차했다

• 다른 row 가 read 한 조건을 수정하는 update 가 이루어졌다
• 단, 겹치는 로우에 대한 업데이트가 없었다
• 또는 SELECT FOR UPDATE 걸린 로우에 대한 업데이트가 아닌 INSERT
• 이는 psql 이 gap lock (next key lock) 을 사용하지 않기 때문에 막히지 않는다 
  
  
  

Then?

• Isolation level 은 껍데기만 표준이다. 주의하자
  • 가장 많이 쓰는 InnoDB 랑 Postgres 랑 같은 수준에서 보장하는게 다르다
  • InnoDB 에서 해주는 보장 (gap lock) 등이 오인되는 경우를 많이 봤다.
  • InnoDB 는 UPDATE 쿼리가 스냅숏을 보지 않는다
  • InnoDB 는 REPEATABLE READ에서 FOR UPDATE 미사용 시 lost update 가 있을 수 있다
    • psql 은 concurrent update 를 감지하여 실패시킨다
• 기본 READ COMMITED / REPEATABLE READ 수준에서 정합성 문제를 일으키는 로직에 대한 주의가 필요하다.
  • SELECT & INSERT 패턴 주의 (unique constraint conflict 로 막히는 경우가 많긴 하다)
  • UPDATE 교차는 주의
• REPEATABLE READ 쓰더라도 쓰기 충돌에 대한 optimistic lock fail 이 있을 수 있음에 유의해야 한다
  • 특히 SELECT FOR UPDATE 만 수행되어도 tx 가 동시성 이슈로 실패할 수 있다는건 잘 모른다
• 락 배울때 지겹게 들었곘지만, 낙관적 락은 경합이 적을 때 더 유용하다
  • 경합이 충분히 적고, 성능이 중요하며, 동일 로우에 대한 흔한 SELECT - UPDATE 패턴의 트랜잭션이라면 FOR UPDATE 안쓰고 REPEATABLE READ 로 트랜잭션을 낙관해 보는 것도..?

 

Solution

• 명시적인 application level lock 을 사용한다
• psql 에도 있다: tx_advisory_lock
• transactor 에 아래와 같은 옵션 추가해서 사용하고 있다

func installApp(ctx context.Context, appID string) (*App, error) {
 return tx.Do(func(ctx context.Context) (*App, error) {
   // svc code...
 }, tx.XLock(nameSpaceApp, appID)) // pqsl advisory lock
}

 

• 격리 수준을 Serializable 로 올려 사용한다 
  • 생각보다 안느리다 (SSI)
  • 정말 정합성이 중요한 서비스면 그냥 디폴트로 놔도 될정도