데드락 로그

SHOW ENGINE INNODB STATUS

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-12-24 14:05:08 140698914563840
*** (1) TRANSACTION:
TRANSACTION 2606386, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 2
MySQL thread id 331528, OS thread handle 140698706732800, query id 19184821 172.18.0.1 koin updating
update access_history set device_id=1223,last_accessed_at='2025-12-20 21:16:19',updated_at='2025-12-24 23:05:08.333' where id=4229

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 273 page no 20 n bits 264 index PRIMARY of table `koin`.`access_history` trx id 2606386 lock mode S locks rec but not gap
Record lock, heap no 173 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 00001085; asc     ;;
 1: len 6; hex 000000277c57; asc    '|W;;
 2: len 7; hex 81000002170110; asc        ;;
 3: SQL NULL;
 4: len 4; hex 69471223; asc iG #;;
 5: len 4; hex 69471223; asc iG #;;
 6: len 4; hex 69471223; asc iG #;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 273 page no 20 n bits 264 index PRIMARY of table `koin`.`access_history` trx id 2606386 lock_mode X locks rec but not gap waiting
Record lock, heap no 173 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 00001085; asc     ;;
 1: len 6; hex 000000277c57; asc    '|W;;
 2: len 7; hex 81000002170110; asc        ;;
 3: SQL NULL;
 4: len 4; hex 69471223; asc iG #;;
 5: len 4; hex 69471223; asc iG #;;
 6: len 4; hex 69471223; asc iG #;;

*** (2) TRANSACTION:
TRANSACTION 2606385, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 2
MySQL thread id 331524, OS thread handle 140698235852544, query id 19184820 172.18.0.1 koin updating
update access_history set device_id=1222,last_accessed_at='2025-12-20 21:16:19',updated_at='2025-12-24 23:05:08.34' where id=4229

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 273 page no 20 n bits 264 index PRIMARY of table `koin`.`access_history` trx id 2606385 lock mode S locks rec but not gap
Record lock, heap no 173 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 00001085; asc     ;;
 1: len 6; hex 000000277c57; asc    '|W;;
 2: len 7; hex 81000002170110; asc        ;;
 3: SQL NULL;
 4: len 4; hex 69471223; asc iG #;;
 5: len 4; hex 69471223; asc iG #;;
 6: len 4; hex 69471223; asc iG #;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 273 page no 20 n bits 264 index PRIMARY of table `koin`.`access_history` trx id 2606385 lock_mode X locks rec but not gap waiting
Record lock, heap no 173 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 00001085; asc     ;;
 1: len 6; hex 000000277c57; asc    '|W;;
 2: len 7; hex 81000002170110; asc        ;;
 3: SQL NULL;
 4: len 4; hex 69471223; asc iG #;;
 5: len 4; hex 69471223; asc iG #;;
 6: len 4; hex 69471223; asc iG #;;

*** WE ROLL BACK TRANSACTION (2)

Device 증발

Device 중복 생성

조회

select * from access_history ah
left join device d
on ah.device_id = d.id
left join access_history_abtest_variable ahb
on ahb.access_history_id = ah.id
left join abtest_variable av
on ahb.variable_id = av.id
where d.id in (1224, 1225);