[InnoDB] 인덱싱되지 않은 컬럼으로 UPDATE 시 데드락이 발생한다

결제 서비스를 만들고 테스트하던 중 인덱싱 되지 않은 컬럼을 검색 조건으로 활용한 Update에서, 단건의 요청 처리에 대해 문제가 없었으나 다수의 요청 동시 처리 시 데드락이 발생하는 상황을 경험했다.

 

---

 

 

1. 데드락 확인

 

org.springframework.dao.TransientDataAccessResourceException: execute; SQL [UPDATE payment_orders
SET payment_order_status = :status, updated_at = CURRENT_TIMESTAMP
WHERE order_id = :orderId]; Deadlock found when trying to get lock; try restarting transaction

 

스프링을 통해 어떤 로직을 테스트하던 중, 인덱싱되지 않은 컬럼을 검색 조건으로 UPDATE 쿼리가 동시에 다수 발생할 때 데드락으로 인해 실패하는 것을 확인했다.

 

 

mysql> show engine innodb status;

 

데드락이 발생한 트랜잭션과 쿼리의 상세 정보를 확인하기 위해 DB에 접속하여 InnoDB의 상태를 출력하였다.

 

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-07-07 07:18:17 281472619503424
*** (1) TRANSACTION:
TRANSACTION 5431, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 22, OS thread handle 281472562806592, query id 123 192.168.65.1 root updating
UPDATE payment_orders
SET payment_order_status = 'UNKNOWN', updated_at = CURRENT_TIMESTAMP
WHERE order_id = 'ce6d18a4-5ac0-4c27-b032-aba1c6225695'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `test`.`payment_orders` trx id 5431 lock mode S locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000bf; asc         ;;
...
 14: len 5; hex 99b3ce7490; asc    t ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000c0; asc         ;;
...
 14: len 5; hex 99b3ce7490; asc    t ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `test`.`payment_orders` trx id 5431 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000b5; asc         ;;
...
 14: len 5; hex 99b3ce6ddc; asc    m ;;


*** (2) TRANSACTION:
TRANSACTION 5427, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1128, 13 row lock(s), undo log entries 4
MySQL thread id 21, OS thread handle 281472567033664, query id 122 192.168.65.1 root updating
UPDATE payment_orders
SET payment_order_status = 'UNKNOWN', updated_at = CURRENT_TIMESTAMP
WHERE order_id = 'f4f0c58e-dfad-4b03-89f1-2114f3e4e9b7'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `test`.`payment_orders` trx id 5427 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000b5; asc         ;;
...
 14: len 5; hex 99b3ce6ddc; asc    m ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000b6; asc         ;;
...
 14: len 5; hex 99b3ce6ddc; asc    m ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000b7; asc         ;;
...
 14: len 5; hex 99b3ce6ea9; asc    n ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000b8; asc         ;;
...
 14: len 5; hex 99b3ce6ea9; asc    n ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `test`.`payment_orders` trx id 5427 lock_mode X waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 8; hex 80000000000000bf; asc         ;;
...
 14: len 5; hex 99b3ce7490; asc    t ;;

*** WE ROLL BACK TRANSACTION (1)

 

Transaction 5427과 Transaction 5431에서 각각 아래 쿼리를 실행하는 도중 데드락이 발생했다는 것을 볼 수 있다.

 

-- TX 5427
SET payment_order_status = 'UNKNOWN', updated_at = CURRENT_TIMESTAMP
WHERE order_id = 'f4f0c58e-dfad-4b03-89f1-2114f3e4e9b7'

-- TX 5431
SET payment_order_status = 'UNKNOWN', updated_at = CURRENT_TIMESTAMP
WHERE order_id = 'ce6d18a4-5ac0-4c27-b032-aba1c6225695'

 

 

로그 중 아래와 같은 부분들을 통해 실제 어떤 row에 대한 record lock인지도 특정할 수 있다. 각 줄은 TX 정보부터 해당 row의 각 컬럼이 무엇인지를 내포한다.

 

 0: len 8; hex 80000000000000b6; asc         ;;
...
 6: len 30; hex 64336433323034342d653432622d343833342d396338392d666662633562; asc d3d32044-e42b-4834-9c89-ffbc5b; (total 36 bytes);
...
 14: len 5; hex 99b3ce6ddc; asc    m ;;

 

실제 db에 저장된 값들(UUID d3d32044-e42b-4834-9c89-ffbc5b) 등이 보인다.

 

서버에서 관련 예외를 이미 받았으므로 데드락은 자동 감지되어 처리됨을 예상 가능, show processlist 명령으로 프로세스 및 스레드를 확인하면 이미 트랜잭션을 처리하는 스레드는 보이지 않음도 확인하였다.

 

2.  원인 분석

 

TX 5427은 heap no 2, 3, 4, 5에 대한 X lock을 가지고 heap no 12에 대한 X lock을 얻지 못해 대기,

TX 5431은 heap no 12, 13에 대한 S lock을 가지고 heap no 2에 대한 X lock을 얻지 못해 대기하는 중이다.

 

1. 같은 컬럼에 대한 검색 조건이 다른데 Record 락 경합이 발생한다는 점,

2. 테스트 도중 저장되어 있는 데이터의 수가 적고 쿼리가 단순한데 비교적 많은 레코드들을 로드하고 락을 건다는 점

 

두 가지 근거로 고민해보니 원인은 너무 단순했다.

아직 테스트 중인 DB로 검색 조건인 order_id에 대한 인덱싱을 하지 않아 Full Scan을 수행, 다른 TX가 겹치며 문제가 발생한 것.

 

https://medium.com/@im_zero/non-unique-index%EC%99%80-next-key-lock-b314379d9b0f

 

이미 실험해보신 분이 있었는데, non-indexed column에 대한 업데이트 쿼리 시 풀 스캔 후 수정 과정에서 모든 레코드에 X lock이 걸리게 된다. X lock 취득은 레코드 단위로 순차적으로 일어나는데, 먼저 시작된 TX 5427이 레코드에 대한 X lock을 거는 도중 TX 5431가 이미 S lock을 잡고 있어 수행되지 못했고, 마찬가지로 TX 5431도 쓰기 작업을 위해 X lock을 취득을 시도할 때 데드락이 발생한 것.

(공식 문서에서 찾진 못했으나 UPDATE 쿼리의 경우 읽기 단계에서 S lock을, 이후 X lock으로 재취득하는 과정이 이뤄지는 듯)

 

 

https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html

 

관련 근거는 위의 공식 문서에서 일부 확인 가능했다.

 

1. A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement.
2. If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table.

locking read, update, delete는 SQL에 의해 스캔되는 모든 index record에 record lock을 건다. 구문에 사용될 적절한 index가 없다면, MySQL은 테이블의 모든 row에 대한 lock (record)를 걸어 다른 사용자의 insert를 금지한다.

 

 

3. 해소

데드락 자체는 MySQL에서 알아서 감지하고 해소하여 수동으로 프로세스를 처리할 필요는 없었고,

인덱스를 추가하여 문제가 해소됨을 확인하였다.

 

---

 

검색 조건에 인덱싱을 하는 건 너무 당연한 일이다.

다만 인덱싱의 부재가 성능적 문제 외에 InnoDB의 특성으로 데드락에 걸릴 수 있다는 사실이 제법 흥미로웠다.

과거에 인덱싱되지 않은 컬럼으로 검색 시 모든 레코드에 락이 걸린다는 내용을 어렴풋이 본 것 같은데.. 실제로 테스트하며 마주친 덕분에 innodb의 동작도 다시 살펴볼 수 있었다.