mysql on duplicate key update(odku) 데드락 정리

[backtony]

jpa -> jdbcTemplate으로 전환하는 과정에서 다건 insert 케이스에서 데드락 현상 발생

현상

# 아래 쿼리로 인한 현상
insert .. values .. on duplicate key update 

# 데드락 로그 확인
show engine innodb status

# 데드락 로그
RECORD LOCKS space id 64 page no 114906 n bits 152 index PRIMARY of table `shofee`.`user_feed_level1` trx id 35529757870 lock_mode X locks gap before rec insert intention waiting

데드락이 발생하면서 vitess tx pool이 모두 소비되어 장애 발생

원인 분석

@transactional을 사용하지 않는데 왜 vitess의 vttablet은 tx pool을 사용했나?

• 단건 insert 시에는 tx 애노테이션을 사용하지 않으면 vttablet은 tx pool을 사용하지 않음.
• 다건 insert 시에는 tx 애노테이션을 사용하지 않아도 vitess가 자체적으로 tx pool을 사용함.
  • vtgate가 요청을 받아서 샤드별로 쿼리를 분배하면서 vttablet에 begin, commit request가 전송되는 것을 보니 vtgate에서 부터 자체적으로 tx pool을 사용하도록 처리되는 것으로 추정

insert 이해

• 일반적인 insert 과정
  • 중복 체크 시에 gap / next-key lock이 사용
    • pk와 uk 모두에 사용됨.
  • 삽입 후 record lock을 사용
  • gap / next - key lock 해제
  • 커밋 시 record lock 해제
• insert .. odku 과정
  • 중복 체크로 gap / next key lock 사용
    • pk와 uk 모두에 사용됨.
  • 중복이면 업데이트용 record lock
  • 중복 아니면 삽입 후 record lock
  • next-key lock이 commit 시점까지 유지되는 경우가 많음.

즉, odku를 사용하면 일반 insert보다 비교적 lock을 더 오래 잡게 된다.

cf) 만약 pk가 auto increment 이고 uk가 없다면 next-key lock은 잡히지 않고 special auto-inc lock이 사용되어 데드락이 거의 없음.(항상 맨 끝으로만 삽입 되므로) uk가 있다면 중복 체크시에 next-key lock이 사용됨.

여기서 pk가 auto increment가 아니고 snowflake 와 완벽한 순차가 아닌(ex. T1 → 180, T2 → 120, T3 → 260) 별도의 id 체계를 따라간다면 pk 에 대해 next-key lock이 추가로 발생한다.

pk 인덱스에 이미 다음과 값들이 있다고 가정
... 100 ----- 200 ----- 300 ...

트랜잭션 A
INSERT INTO table (pk) VALUES (150);
잠금: (100, 200] 발생 - next key lock

트랜잭션 B
INSERT INTO table (pk) VALUES (180);
잠금: (100, 200] - next key lock

PK 값은 다르지만 잠그는 next-key range가 겹치면서 락 경합 / 대기 발생 할 수 있다.

아래와 같은 경우에는 트랜잭션에 values로 다건 삽입을 하는 경우에 데드락이 발생할 수 있다.

트랜잭션 A lock (100,200]	
트랜잭션 B lock (200,300]
트랜잭션 A lock (200,300] ⛔	
트랜잭션 B lock (100,200] ⛔

즉, pk가 auto inc가 아닌 중간 삽입이 발생한다면 PK 값은 안 겹쳐도 InnoDB의 next-key lock 때문에 “PK 사이 구간”이 잠기고, 분산/비순차 채번에서는 트랜잭션마다 락 잡는 순서가 달라져 정렬을 해도 데드락이나 락 경합이 발생할 수 있다.

정리하면 auto-increment가 아닌 snowflake 기반의 uuid를 사용하고 있으므로 완벽한 순차가 아닌 sid가 저장되어있는 sid 사이에 끼워넣어지게 되면서 pk에 대해 next-key lock이 잡혔고 이에 대해 데드락이 발생했다. (odku가 일반 insert보다 lock을 더 길게 잡으므로 이에 대한 영향이 데드락 가능성을 더 높였을 것으로 추정.)

uk에 대해서도 동일하게 발생할 여지가 있을 것으로 보인다.

해결

rewritebatch 옵션을 false로 두면 batch insert가 단건 insert처럼 처리되므로 이를 방지할 수 있다.

교훈

• pk가 auto-inc가 아닌 snowflake 방식인 경우 다건 insert 시에 데드락 발생 포인트 점검 필요
  • odku가 아닌 경우에도 발생할 가능성은 여전히 존재함.
  • uk가 만약 인접한 케이스가 한번에 다량으로 들어가는 경우에도 발생 가능성이 있음. -> 이 케이스는 드물것으로 예상
  • pk를 다른 방식으로 채번한다면 회피할 수도 있을 것으로 보임.