mysql 데드락 확인방법 show engine innodb status

by

1.데드락 확인 명령어

mysql에서 Lock 관련 튜닝 포인트를 확인할 때

show engine innodb status\G;

를 실행하면 가장 최근 데드락이 발생한 트랜젝션을 알 수 있다.

ex) show engine innodb log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-09-12 12:11:43 0x7f29a4980700
*** (1) TRANSACTION:
TRANSACTION 1514937792, ACTIVE 0 sec updating or deleting
mysql tables in use 3, locked 3
LOCK WAIT 509 lock struct(s), heap size 57552, 30576 row lock(s), undo log entries 1
MySQL thread id 6501497, OS thread handle 139816465430272, query id 9856748676 172.31.8.92 mhpapp updating reference tables
			UPDATE xxxx.xxxxx
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3384 page no 860 n bits 1120 index IDX_xxxx_04 of table `xxxx`.`xxxxx` trx id 1514937792 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 47463939; asc GF99;;
 1: len 4; hex 00012cf0; asc   , ;;

*** (2) TRANSACTION:
TRANSACTION 1514937793, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 82
mysql tables in use 3, locked 3
505 lock struct(s), heap size 57552, 29847 row lock(s), undo log entries 1
MySQL thread id 6501498, OS thread handle 139816831813376, query id 9856748686 172.31.8.92 mhpapp updating reference tables
			UPDATE xxxx.xxxxx
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3384 page no 860 n bits 1120 index IDX_xxxx_04 of table `xxxx`.`xxxxx` trx id 1514937793 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;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 47463939; asc GF99;;
 1: len 4; hex 00012b2a; asc   +*;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 47463939; asc GF99;;
 1: len 4; hex 00012b2b; asc   ++;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 47463939; asc GF99;;
 1: len 4; hex 00012b30; asc   +0;;
 ...
 ..
 .

2.데드락 설정 파라미터 값은?

#자동 데드락감지
mysqldb는 데드락을 자동감지해서 my.cnf에 설정된 시간이 지나면 자동 롤백을 진행하고
설정 파라미터는 아래와 같다.

vi /etc/my.cnf
------------------------------------------------
innodb_lock_wait_timeout = 60

default는 120초로 되어있는데 보통 서비스DB는 60초 내외로 설정한다.

3.데드락 확인 로그가 너무 길어서 확인이 어려우면?

보통 gap lock이 발생하여 transaction rollback 되면
건수가 많을 시에는

mysql> show engine innodb status\G;


로 실행해도 열이 길어서 Linux Shell 한 화면에 볼 수가 없다.

#show engine innodb status 로그를 파일로 받기

이럴 때는 파일로 빼서 확인을 해야하는데 명령어는 아래와 같다.

[mysql@xxx ~]$ mysql -uroot -p -e "show engine innodb status\G;" -t > 62_show_status_log.out

4.데드락이 잘 발생하는 구문

데드락은 보통은

  1. update 구분에서 많이 발생하고
  2. procedure나 function에서 값을 받기 위해 변수 (value)를 선언하고
    아래와 같은 select를 실행시 지연이 발생했을 때
    select aaa into value from..
  3. count(*) 쿼리의 fullscan

등에서 많이 발생하는데

아무래 시스템 my.cnf에서 자동 롤백으로 DB를 보호하고 있긴 하지만
자주 발생하면 적절한 튜닝이 필요하다.

끝.

Leave a Comment