AWS RDS INSERT문 중복 실행으로 인한 DeadLock 파악

상황

---

서버 장애로 인해서 클라이언트로부터 요청받은 API의 응답 시간이 길어지는 상황이 발생했습니다.
 
응답 시간이 길어짐에 따라 여러가지 이슈가 발생했는데,

그중, 몇몇 유저로부터 참여형 서비스에서 신규로 참여할 수 없다는 cs가 인입되었습니다.
 
참여형 서비스에 신규로 참여를 하게되면
mysql에 INSERT 문을 실행해 유저가 참여했다는 데이터를 저장합니다.
 
참여가 안된다는 cs를 토대로 AWS RDS에 어떠한 장애가 발생했음을 짐작했습니다.

AWS RDS 지표를 통해 문제 발생 시점에 교착 상태가 발생하고 있다는 것을 확인하였으며,
 
어떤 DeadLock 이 발생하고 있는지 확인해 보았습니다.
 
 
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-23 03:48:01 2af8f4e04700
*** (1) TRANSACTION:
TRANSACTION 8653313357, ACTIVE 46 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 376, 3 row lock(s)
MySQL thread id 28811050, OS thread handle 0x2af9a8caf700, query id 465798082 172.31.15.80 .....

update { INSERT QUERY }
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 681 page no 2954 n bits 182 index `PRIMARY` of table ...... trx id 8653313357 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 182 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
....

---

*** (2) TRANSACTION:
TRANSACTION 8653321201, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 376, 3 row lock(s)
MySQL thread id 28815387, OS thread handle 0x2af961299700, query id 465806112 172.31.0.158 .....

update  { INSERT QUERY }

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 681 page no 2954 n bits 182 index `PRIMARY` of table ..... trx id 8653321201 lock mode S locks gap before rec
Record lock, heap no 182 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
....

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 681 page no 2954 n bits 182 index `PRIMARY` of table ...... trx id 8653321201 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 182 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
....
 
 

원인

---

응답 시간이 길어짐에 따라, 앱상에서 유저가 참여하기 버튼을 눌러도 즉시 처리가 되지 않았고,
응답을 받기전에 유저가 참여하기 버튼을 연타하여 동일한 PK의 INSERT 문이 여러번 실행되었습니다.
 
일반적으로 동일한 PK를 사용한 INSERT문이 여러번 실행되면 Duplicated key error가 발생하지만,
 
서로 다른 transaction에서 INSERT문이 실행되었고, 특정 하나의 transaction이 rollback이 되면 DeadLock이 발생할 수 있다고 합니다.
 

---

 
InnoDB에서는 Reocord INSERT문을 실행하기 전에 gap lock의 일종인 Insert Intention Lock 획득합니다.

Insert Intention Lock 이란 ?

INSERT가 실행될 때 InnoDB 엔진 내부적으로 획득하는 특수한 형태의 Gap Lock 입니다.
일반적으로 Gap Lock은 Locking Read를 위한 SELECT 구문이 실행될때 발생하지만,
이 Lock은 INSERT를 위한 Exclusive Lock (X-Lock) 을 획득하기 전에 발생합니다.

여러개의 트랜잭션들이 Gap 안의 서로 다른 위치에 INSERT를 동시 수행할 때 기다릴 필요가 없도록 하는 것이 목적이며 Insert Intention Lock들 간에는 충돌이 발생하지 않습니다.

 

[DeadLock 상황]

1. 서로 다른 transaction에서 동일한 PK를 사용하는 INSERT 문이 세번 실행되었다고 가정하겠습니다. 편의상 transaction A, B, C라고 부르겠습니다.
2. transaction A에서 INSERT를 위해 X-Lock을 획득합니다.
3. B와 C에서도 INSERT문을 실행합니다. 이때, 이미 A에 의해 INSERT하려는 PK(row)에 X-Lock이 걸려있으므로 B,C는 대기상태에 빠집니다.
4. A가 Rollback을 합니다.
5. B, C가 X-Lock을 획득하려고 경쟁합니다.
6. B, C 가 실행하려는 INSERT문의 PK가 동일하므로 Duplicated key error 발생.
7. INSERT 문의 경우 Duplicated key error 가 발생하면, 해당 PK(인덱스 레코드)에 대해 일단 S-Lock을 획득하는 특성이 있습니다.
8. 따라서 B, C는 S-Lock을 먼저 획득 한 후 서로, X-Lock을 획득하려고하는데, 다른 transaction에서 이미 S-Lock을 획득한 상태이기 때문에 X-Lock을 획득할 수 없습니다.
9. 이때 DeadLock이 발생합니다.
10. 둘 중 늦게 실행된 transaction이 DeadLock처리되어 Rollback되면, 남은 transaction이 X-Lock을 획득하여 실행합니다.

 

대응

---

클라이언트에서 최초에 API 요청을 했을 때, 서버로부터 응답을 받기 전까지 버튼을 비활성화 하는 방향으로 대응했습니다.
 
서버에서는 어떻게하면 막을 수 있을지 고민해볼 필요가 있을 것 같습니다.
 
 
 
참조 사이트
https://steemit.com/kr/@yjiq150/mysql-innodb-lock-and-deadlock