이 글을 쓰는 이유
먼저 이 글을 쓰는 이유는 저희 카페인 팀의 혼잡도 저장 및 충전기의 상태를 업데이트하는 로직에서 dead Lock이 발생하여 mysql과 connection을 잃는 에러가 발생했기 때문입니다.
------------------------
LATEST DETECTED DEADLock
------------------------
2023-07-21 01:49:54 281472560787424
*** (1) TRANSACTION:
TRANSACTION 1000560, ACTIVE 373 sec inserting
mysql tables in use 1, Locked 1
Lock WAIT 3 Lock struct(s), heap size 1128, 9 row Lock(s), undo log entries 328
MySQL thread id 860, OS thread handle 281472107409376, query id 2958720 update
INSERT INTO charger_status (station_id, charger_id, latest_update_time, charger_state) VALUES ('ST414511', '01', '2023-07-21 08:27:43', 'CHARGING_IN_PROGRESS') ON DUPLICATE KEY UPDATE latest_update_time = '2023-07-21 08:27:43', charger_state = 'CHARGING_IN_PROGRESS'
*** (1) HOLDS THE Lock(S):
RECORD LockS space id 64 page no 742 n bits 424 index PRIMARY of table `charge`.`charger_status` trx id 1000560 Lock_mode X Locks rec but not gap
*** (1) WAITING FOR THIS Lock TO BE GRANTED:
RECORD LockS space id 64 page no 718 n bits 280 index PRIMARY of table `charge`.`charger_status` trx id 1000560 Lock_mode X Locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 946331, ACTIVE 507 sec inserting
mysql tables in use 1, Locked 1
Lock WAIT 4 Lock struct(s), heap size 1323, 12 row Lock(s), undo log entries 432
MySQL thread id 859, OS thread handle 281472629186528, query id 3017483 update
INSERT INTO charger_status (station_id, charger_id, latest_update_time, charger_state) VALUES ('ST412801', '11', '2023-07-21 10:48:20', 'CHARGING_IN_PROGRESS') ON DUPLICATE KEY UPDATE latest_update_time = '2023-07-21 10:48:20', charger_state = 'CHARGING_IN_PROGRESS'
*** (2) HOLDS THE Lock(S):
RECORD LockS space id 64 page no 718 n bits 208 index PRIMARY of table `charge`.`charger_status` trx id 946331 Lock_mode X Locks rec but not gap
*** (2) WAITING FOR THIS Lock TO BE GRANTED:
RECORD LockS space id 64 page no 742 n bits 424 index PRIMARY of table `charge`.`charger_status` trx id 946331 Lock_mode X Locks rec but not gap waiting
실제 개발 서버에서 발생한 데드락의 로그입니다. 해당 로그는 charger_status에 저장 시 서로 XLock을 획득하지 못하여 생기는 에러입니다.
Mysql Dead Lock이란
그럼 Dead Lock은 왜 생기고 언제 생길까요? 저는 이 Log를 직접 마주하기 전까지는 Dead Lock이 그냥 Lock의 시간이 오래 걸릴 때 생기는 줄 알았습니다. 하지만 그렇게 간단하게 발생하는 것은 아니였습니다.
상호 배제(Mutual Exclusion): MySQL은 기본적으로 트랜잭션 내에서 잠금(Lock)을 사용하여 데이터의 상호 배제를 제어합니다. 따라서 두 개 이상의 트랜잭션이 같은 데이터를 동시에 변경하려고 할 때, 해당 데이터에 대한 잠금이 설정되어 상호 배제 조건이 만족됩니다.
점유와 대기(Hold and Wait): 트랜잭션이 이미 하나 이상의 데이터를 잠근 상태에서 다른 데이터의 잠금을 얻기 위해 대기하고 있는 경우 점유와 대기 조건이 만족됩니다. 즉, 트랜잭션이 자신이 점유한 데이터를 유지한 상태에서 다른 데이터에 대한 잠금을 기다리고 있어야 합니다.
비선점(Non-Preemption): MySQL에서는 기본적으로 트랜잭션이 다른 트랜잭션이 점유한 데이터의 잠금을 강제로 해제할 수 없습니다. 따라서 비선점 조건이 만족됩니다.
순환 대기(Circular Wait): 두 개 이상의 트랜잭션이 각각 서로가 기다리는 데이터의 잠금을 보유해야 순환 대기 조건이 만족됩니다. 예를 들면, 트랜잭션 A가 데이터 X의 잠금을 기다리고, 트랜잭션 B는 데이터 Y의 잠금을 기다리며, 트랜잭션 C는 데이터 Z의 잠금을 기다리는 상태가 발생한다면 순환 대기 조건이 성립합니다.
사실 기본 컴퓨터 시스템의 dead Lock과 유사한 조건입니다. 이 부분을 모두 만족해야 데드락이 발생합니다. 하나씩 알아보겠습니다. 먼저 개발 서버에서 발생한 데드락으로 살펴보겠습니다.
*** (1) TRANSACTION:
TRANSACTION 1000560, ACTIVE 373 sec inserting
mysql tables in use 1, Locked 1
Lock WAIT 3 Lock struct(s), heap size 1128, 9 row Lock(s), undo log entries 328
MySQL thread id 860, OS thread handle 281472107409376, query id 2958720 update
INSERT INTO charger_status (station_id, charger_id, latest_update_time, charger_state) VALUES ('ST414511', '01', '2023-07-21 08:27:43', 'CHARGING_IN_PROGRESS') ON DUPLICATE KEY UPDATE latest_update_time = '2023-07-21 08:27:43', charger_state = 'CHARGING_IN_PROGRESS'
*** (1) HOLDS THE Lock(S):
RECORD LockS space id 64 page no 742 n bits 424 index PRIMARY of table `charge`.`charger_status` trx id 1000560 Lock_mode X Locks rec but not gap
-------------------------------------------------------------------------
*** (2) TRANSACTION:
TRANSACTION 946331, ACTIVE 507 sec inserting
mysql tables in use 1, Locked 1
Lock WAIT 4 Lock struct(s), heap size 1323, 12 row Lock(s), undo log entries 432
MySQL thread id 859, OS thread handle 281472629186528, query id 3017483 update
INSERT INTO charger_status (station_id, charger_id, latest_update_time, charger_state) VALUES ('ST412801', '11', '2023-07-21 10:48:20', 'CHARGING_IN_PROGRESS') ON DUPLICATE KEY UPDATE latest_update_time = '2023-07-21 10:48:20', charger_state = 'CHARGING_IN_PROGRESS'
*** (2) HOLDS THE Lock(S):
RECORD LockS space id 64 page no 718 n bits 208 index PRIMARY of table `charge`.`charger_status` trx id 946331 Lock_mode X Locks rec but not gap
1번 트랜잭션 1000560이 charge_status 테이블에 insert ~ on duplicate key update ~ 쿼리를 발생시키기 위해 space id 64 page no 742 n bits 424 index PRIMARY of table 에 X Lock을 가지고 있습니다
그리고 2번 트랜잭션 946331 도 똑같은 테이블에 비슷한 쿼리를 발생시키려고 합니다. 그리고 해당 트랜잭션도 X Lock을 가지고 있습니다.
저희 팀에 데드락이 발생한 이유
먼저 저희 팀은 공공 API를 통해 전기차 충전소 정보를 cron으로 업데이트 해주고 있습니다.
그리고 충전기의 상태도 주기적으로 업데이트 해주고 있습니다.
충전소 정보를 갱신할 경우 새로 생긴 충전소가 있다면 추가해줘야하고, 새로 생긴 충전소가 있다면 새로운 충전기가 있을테고, 그에따른 충전기 상태도 추가해줘야합니다.
그리고 원래 있던 충전소의 정보가 바뀌는 것에 대해서도 업데이트 해줘야합니다. 이렇게 된다면 여러번의 분기 처리로 application 레벨에서 해결하는 것이 나을지 혹은 mysql의 문법 중 INSERT ~~ ON DUPLICATE KEY UPDATE ~~을 이용할까 고민을 했었는데요.
그 중 후자를 택한 이유를 말씀드리겠습니다. 충전기의 정보는 하루에 공공 API를 요청할 수 있는 key 제한이 있고 지도 기반으로 검색할 수 있는 조건이 없기 때문에 실시간으로 요청할 수 없는 구조입니다. 그래서 요청 key 제한을 넘지 않는 선에서 자주 요청을 해야합니다. 그렇게해야 사용자에게 정보에 대한 신뢰를 줄 수 있습니다. 따라서 자주 요청되는 작업에 Application 레벨에서 구현한다면, findAll() 메서드를 통해 23만건의 충전기 상태 정보를 메모리에 로딩합니다. 그리고 api의 요청을 통해 얻은 23만+n건의 충전기 상태 정보를 메모리에 올립니다.
그리고 해당 정보가 있는지 비교합니다. 해당 정보가 findAll()로 찾아온 list에 없으면 Insert, 해당 정보가 있다면 update를 통해 갱신합니다.
이렇게 한다면 총 batch Insert, Update를 통해 2번의 쿼리 + 46만 n건의 충전기 정보를 메모리에 올려 비교하는 연산이 생길 것 입니다.
하지만 INSERT ~~ ON DUPLICATE KEY UPDATE ~~ 를 사용한다면 batch insert를 통해 1번의 쿼리로 해결 할 수 있습니다. 메모리에 데이터도 적재하지 않고 말이죠.
하지만 보셨던 것과 같이 데드락이 발생했습니다. 이유는 INSERT ~~ ON DUPLICATE KEY UPDATE ~~ 의 특수한 Lock mechanism 때문입니다. 해당 쿼리는
- 먼저 삽입하려는 행이 테이블에 존재하는지 확인합니다.
- 그리고 읽은 record에 대해 S Lock을 설정합니다.
- 그리고 해당 record가 duplicate key라는 조건이라면 X Lock을 설정합니다.
이런 방식으로 작동하기 때문입니다. 이런 방식이 왜 문제가 될 수 있는지 알아보겠습니다. 먼저 자신이 읽은 record에 S Lock을 각각의 트랜잭션이 설정합니다. 그리고 업데이트를 하기위해 record에 X Lock을 설정하려고 합니다. 하지만 각각의 트랜잭션이 서로 S Lock을 설정했기 때문에 S Lock을 반납하고 X Lock을 설정하려고 해도 두 트랜잭션 모두 기다리는 데드락 상황이 발생하기 때문입니다. 이런 상황이 되면 아까 위에서 말씀드렸던 데드락의 조건 4가지가 다 만족하고 데드락이 발생합니다.
해결 방안
해결 방안은 여러가지가 있을 수 있습니다. 그 중 제 수준에서 생각할 수 있는 방법은 2가지가 있습니다.
- 트랜잭션을 작게 분리 트랜잭션을 오래 가지고 있으면 Lock을 가지고 있는 시간이 오래걸립니다. 그래서 트랜잭션을 작게 분리할 수 있습니다. 페이징을 통해 트랜잭션을 작게 분리하다보면 쿼리가 여러번 나가 성능상 문제가 생길 수 있을 것 같습니다.
INSERT ~~ ON DUPLICATE KEY UPDATE ~~사용하지 않기 해당 sql이 아닌INSERT IGNORE을 사용하여 추가된 정보만 넣고, update는 다른 작업으로 분리하기
이런 방법들을 사용하면 될 것 같았습니다. 그 중 저는 현재는 간단하게 2번째 방법이 제일 나을 것 같다는 생각에 쿼리를 수정했습니다.
그리고 문제를 해결했습니다. 해당 문제가 발생하게 되어 좀 더 재밌는 것들을 고민하고 공부할 수 있는 저희 팀에게 감사하고 모르는 키워드를 많이 알려준 누누에게 감사합니다.
아직 배우는 단계라 정확한 정보가 아닐 수 있습니다. 부족한 부분에 대해 많은 지적 부탁드립니다.