MySQL 데드락(deadlock)을 발생시켜보자

dominic·2022년 6월 5일
Databasemysql
0
post-thumbnail

데드락은 서로 다른 트랜잭션들이 서로에 대한 락을 소유한 상태로 대기 상태가 되어 더 이상 요청에 대한 응답을 수행하지 못하는 상황을 말합니다.

여기서는 데드락을 발생시켜보고 MySQL에서 제공하는 정보들을 확인해 보고자 합니다.

환경 구성

  • MySQL Version: 도커로 구동한 8.0 버전의 MySQL

  • 테이블: real mysql 서적에서 사용되는 예제 테이블

    CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `last_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `gender` enum('M','F') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `ix_hiredate` (`hire_date`),
  KEY `ix_gender_birthdate` (`gender`,`birth_date`),
  KEY `ix_firstname` (`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci STATS_PERSISTENT=0;

  • innodb_deadlock_detect 설정: Off

    set global innodb_deadlock_detect=off;

  • 클라이언트 A: Workbench

  • 클라이언트 B: DBeaver

  • 모니터 클라이언트: 트랜잭션 상태 조회를 위한 별도 세션

    # 사용되는 쿼리
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.INNODB_TRX;

데드락 테스트

  1. 클라이언트 A에서 트랜잭션을 시작하고 select ... for update 문장으로 락을 걸어줍니다.
    # 클라이언트 A
BEGIN TRANSACTION;
SELECT * FROM employees WHERE gender='M' AND birth_date='2020-07-30' FOR UPDATE;
  2. 클라이언트 B에서 트랜잭션을 시작하고 '1'과 같은 쿼리지만 검색 조건을 다르게 하여 수행합니다.
    # 클라이언트 B
BEGIN TRANSACTION;
SELECT * FROM employees WHERE gender='F' AND birth_date='2020-07-31' FOR UPDATE;
  3. 모니터 클라이언트에서 각 트랜잭션의 상태를 조회할 수 있습니다.
    SELECT * FROM information_schema.INNODB_TRX;
    현재는 클라이언트 A(trx_id:6413), 클라이언트 B(trx_id:6415) 모두 RUNNING 상태입니다.
  4. 클라이언트 A에서 '2'에 사용된 쿼리로 Lock을 걸어줍니다. 하지만 클라이언트 B로 인해 이미 락이 걸려 있어 대기 상태가 됩니다.
    # 클라이언트 A
SELECT * FROM employees WHERE gender='F' AND birth_date='2020-07-31' FOR UPDATE;
  5. 모니터 클라이언트에서 상태를 확인하면 클라이언트 A(trx_id:6413)의 Lock이 대기중임을 알 수 있습니다.
  6. 클라이언트 B에서 '1'에 사용된 쿼리로 락을 걸어줍니다. 이때, 클라이언트 A로 인해 이미 Lock 걸려 있어 대기 상태가 되고 클라이언트 A, B 모두 대기 상태에 빠지게 됩니다.(※ 진행 과정에서 '1'의 과정부터 다시 수행하게 되어 trx_id가 변경되었습니다.)

데드락 감지 기능 활성

MySQL은 데드락을 감지하면 트랜잭션을 롤백해 데드락 상태에서 빠져나갈 수 있도록 기능을 제공합니다. 앞선 테스트에서는 데드락을 발생시키고 락의 상태를 확인하기 위해 기능을 비활성화했지만 이번에는 활성화된 상태에서 데드락이 발생하는 경우를 확인해 보려 합니다.

  • innodb_deadlock_detect 설정: On
    set global innodb_deadlock_detect=on;

기능을 활성화하고 위에서 진행한 내용을 동일하게 진행하면 데드락이 발생되는 쿼리에서 에러를 보여줍니다.
이렇게 데드락이 감지된 경우에는 InnoDB 모니터 정보를 통해 내용을 확인할 수 있습니다. (1)과 (2)는 각 트랜잭션을 나타내고 각 트랜잭션의 락 정보 등이 담겨 있으며, 어떤 트랜잭션을 롤백 하여 데드락 상태에서 빠져나갔는지 확인이 가능합니다.

SHOW ENGINE INNODB STATUS;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-06-05 16:59:29 139921995212544
*** (1) TRANSACTION:
TRANSACTION 6422, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 629, OS thread handle 139921418274560, query id 6566 172.17.0.1 root executing
select * from employees where gender='F' and birth_date='2020-07-31' 
LIMIT 0, 1000
for update

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 20 page no 1122 n bits 1272 index ix_gender_birthdate of table `employees`.`employees` trx id 6422 lock_mode X
Record lock, heap no 1203 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 01; asc  ;;
 1: len 3; hex 8fc8fe; asc    ;;
 2: len 4; hex 8007a120; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20 page no 1320 n bits 768 index ix_gender_birthdate of table `employees`.`employees` trx id 6422 lock_mode X waiting
Record lock, heap no 695 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 3; hex 8fc8ff; asc    ;;
 2: len 4; hex 8007a121; asc    !;;


*** (2) TRANSACTION:
TRANSACTION 6423, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 561, OS thread handle 139921426728704, query id 6568 172.17.0.1 root executing
/* ApplicationName=DBeaver 21.1.2 - SQLEditor <Script-4.sql> */ select * from employees where gender='M' and birth_date='2020-07-30' for update

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 20 page no 1320 n bits 768 index ix_gender_birthdate of table `employees`.`employees` trx id 6423 lock_mode X
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 695 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 3; hex 8fc8ff; asc    ;;
 2: len 4; hex 8007a121; asc    !;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20 page no 1122 n bits 1272 index ix_gender_birthdate of table `employees`.`employees` trx id 6423 lock_mode X waiting
Record lock, heap no 1203 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 01; asc  ;;
 1: len 3; hex 8fc8fe; asc    ;;
 2: len 4; hex 8007a120; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
profile
dominic
이전 포스트

Jenkins를 사용해 CI/CD 환경 구축해 보기(2)

다음 포스트

Mybatis column mapping 과정에서 발생한 name conflict 해결 일지

0개의 댓글