[MySQL] SELECT INSERT 쿼리 에서 DEADLOCK 발생

최근에 로그를 살펴보다가 발견한 Deadlock SQL로그.

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; 
try restarting transaction at ...

 

가장 최근의 deadlock 히스토리를 살펴본다. (가장 최근 히스토리만 저장되고 이전 내역들은 저장되지 않는다)

SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-02-22 11:37:50 0x7fe8106ae700
*** (1) TRANSACTION:
TRANSACTION 107458378, ACTIVE 1 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 7837 lock struct(s), heap size 712912, 391823 row lock(s)
MySQL thread id 2668303, OS thread handle 140634681337600, query id 49984759 10.0.2.15 mydb_2021_4q Sending data
insert into mytable(a, b, c) select 1, 2, 3 from mytable
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 9634 n bits 112 index PRIMARY of table `mydb`.`mytable` trx id 107458378 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 107458381, ACTIVE 1 sec setting auto-inc lock
mysql tables in use 2, locked 2
7835 lock struct(s), heap size 712912, 391822 row lock(s)
MySQL thread id 2667780, OS thread handle 140634684581632, query id 49984783 10.0.2.16 mydb_2021_4q Sending data
insert into mytable(a, b, c) select 1, 2, 3 from mytable
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 9634 n bits 112 index PRIMARY of table `mydb`.`mytable` trx id 107458381 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mydb`.`mytable` trx id 107458381 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)

 

위 내용을 분석해보면,

우선 트랜젝션 별로 내용이 적혀있고,

/** 1번 트랜젝션 내용 **/
*** (1) TRANSACTION:
TRANSACTION 107458378, ACTIVE 1 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 7837 lock struct(s), heap size 712912, 391823 row lock(s)
MySQL thread id 2668303, OS thread handle 140634681337600, query id 49984759 10.0.2.15 mydb_2021_4q Sending data
insert into mytable(a, b, c) select 1, 2, 3 from mytable

/** 1번 트랜젝션이 lock 을 획득하기 위해 대기중인 트랜젝션 정보 **/
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 9634 n bits 112 index PRIMARY of table `mydb`.`mytable` trx id 107458378 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

/** 2번 트랜젝션 내용 **/
*** (2) TRANSACTION:
TRANSACTION 107458381, ACTIVE 1 sec setting auto-inc lock
mysql tables in use 2, locked 2
7835 lock struct(s), heap size 712912, 391822 row lock(s)
MySQL thread id 2667780, OS thread handle 140634684581632, query id 49984783 10.0.2.16 mydb_2021_4q Sending data
insert into mytable(a, b, c) select 1, 2, 3 from mytable

/** 현재 lock이 잡혀있는 트랜젝션 정보 **/
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 9634 n bits 112 index PRIMARY of table `mydb`.`mytable` trx id 107458381 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

/** 현재 lock이 잡혀있는 트랜젝션 정보 **/
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 9634 n bits 112 index PRIMARY of table `mydb`.`mytable` trx id 107458381 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

/** DEADLOCK 현상이 발생하여 2번 트랜젝션을 롤백 했다는 내용  **/
*** WE ROLL BACK TRANSACTION (2)

 

DEADLOCK 현상 발생시, 현상 확인을 위해 DB에서 히스토리를 확인하여 분석한 후,

애플리케이션에서 해당 쿼리 부분을 수정하는 과정이 필요함.

 

위 내용은 애플리케이션의 쿼리가 INSERT ... SELECT 로 짜여져 있었고,

MySQL 에서는 auto_increment 컬럼이 있는 테이블에 insert .. select 로 쿼리 할 경우,

동시다발적으로 해당 쿼리를 실행하게되면 deadlock 현상이 발생하게 되는 문제가 있다.

 

MySQL을 사용한다면 애플리케이션 단에서 쿼리 작성시 INSERT ... SELECT 형식의 쿼리는 지양해야 하고,

단문으로 나눠서 작업하도록 하자.