[장애] innodb Dead Lock 문제

mysql innodb 엔진은 기본적으로 오라클에 버금가는 우수한 퍼포먼스를 가지고 있다고 생각했다.

하지만 default 옵션이라는 것이 오늘 발목을 잡았다.

 

결론적으로 상용서비스에 Dead Lock이 발생하였고 120초 동안 장애가 발생하였다.

다행이도 innodb는 데드락이 발생하면 120초 후에 unlock 하는 기능이 default로 구현되어있다.

 

원인은 mysql innodb 엔진의 트랜잭션 Level 설정으로 발생한 것인데 innodb 엔진의 Default 기본설정인 REPEATABLE-READ이다.
REPEATABLE-READ에서는 현재 Select버전을 보장하기 위해 동일세션 내에 Snapshot을 이용하는데, 이경우 해당 데이터에 관하서 암묵적으로 Lock과 비슷한 효과가 발생한다고 한다.
즉 Select 작업이 종료될 때까지 해당 데이터 변경 작업이 불가능하다는 것까지 확인하였다.

 

다만 select count(*) into value from .. 쿼리에 데드락이 발생한 것은 좀 의외였고

snapshot 기능이 REPEATABLE-READ 설정시 테이블 통쨰로 떠서 동일세션 내에서는 한번뜨고 변경되지 않기 때문에 동일 세션에서 같은 위치에 값을 불러서 변수에 담을 때 데드락이 걸리는 가슴아픈 상황을 맞이하였다.

 

#장애로그
mysql> SHOW ENGINE INNODB STATUS\G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-03-03 12:47:25 0x7f6187421700
*** (1) TRANSACTION:
TRANSACTION 522522335, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 3
MySQL thread id 10194076, OS thread handle 140057084225280, query id 417845128547 172.xx.x.xxx mhpapp statistics
select count(*) into varCnt
  from   TALBE_XXX
  where  TYP_CD='ZZZ'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table TABLE_XXX trx id 522522335 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 43555354; asc CUST;;
 1: len 6; hex 00001f250de6; asc    %  ;;
 2: len 7; hex 4000005c6d1875; asc @  \m u;;
 3: len 4; hex 8022dc6b; asc  " k;;

"*** (2) TRANSACTION:
TRANSACTION 522522086, ACTIVE 10 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 4
MySQL thread id 10200077, OS thread handle 140056857810688, query id 417845128829 172.xx.x.xx mhpapp statistics
select count(*) into varCnt
  from   TALBE_XXX
  where  TYP_CD='YYY'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table `TABLE_XXX` trx id 522522086 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

#Lock 발생 로그부분 확인

-->동일지점을 호출

RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table `TALBE_XXX` trx id 522522335 lock_mode X locks rec but not gap waiting

RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table `TALBE_XXX` trx id 522522086lock_mode X locks rec but not gap

 

해결방법은 isolation level을 READ-COMMITED로 바꾸면 되는데, 상용서비스에 DB의 트랜잭션 레벨을 테스트없이 변경한다는 것은 말도 안되는 것이고, 리플리케이션 관련 binary log 부분도 변경이 필요해서 당장 무중단으로 반영하긴 어렵다는 것을 확인했다.

일단 처음 발생한 것이고 서비스 고객의 홈페이지 or 어플사용량이 증가하면서 대량 트랜잭션이 발생할 때 간혹 발생하는 현상이라는 점과 DEAD LOCK에 빠지더라도 감지해서 자동으로 락을 푸는 기능이 있기 때문에 일단은 그대로 두고

지속적으로 발생하면 READ-COMMITED 으로 변경하고 리플리케이션 binary log도 격리수준에 맞춰서 MIXED에서 Row based로 바꿔야할 거같다.

좀 더 모니터링을 해보고 결정한다.

 

 

#Transaction isolation Level 정리

1) Read uncommitted
- 다름 트랜잭션이 Commit 전 상태를 볼 수 있음
  Binary log가 자동으로 Row Based 로 기록됨 (Statement 설정 불가, Mixed 설정시 자동 변환)

 

2) Read-Committed
- Commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 특정 타 트랜잭션이 Commit을 수행하면 해당 데이터 를 Read할 수 있음
Binary log가 자동으로 Row Based로 기록됨 (Statament 설정 불가, Mixed 설정 시 자동 변환)

 

3) Repeatable Read
- Mysql InnoDB 스토리지 엔진의 Default isolation level
Select 시 현재 데이터 버전의 Snapshot을 만들고, 그 Snapshot으로부터 데이터를 조회
동일 트랜잭션 내에서 데이터 일관성을 보장하고 데이터를 다시 읽기 위해서는 트랜잭션을 다시 시작해야 함

 

4) SERIALIZBLE
- 가장 높은 isolation level 로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared lock이 걸림
가장 트랜잭션에서는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가

 

#REPEATABLE-READ vs READ-COMMITED 차이정리
동일세션 내에 snapshot을 이용하는 것은 같지만 REPEATABLE-READ은 동일세션이라면 한번 뜨고 끝이지만,
READ-COMMITED은 동일세션이라도 이벤트 발생시마다 snatshot을 뜬다.