[MariaDB][ADMIN] Deadlock found when trying to get lock; try restarting transaction 장애 원인 및 처리 방법

1.데드(Dead) 락 

  - 다중 문 트랜잭션을 사용 할 때 커밋을 하지 않을 때 발생하는 락을 말한다. 

2. 장애 상황

  - 두 개 이상의 트랜잭션이 서로 락을 해제할 때까지 기다리면서 전체적인 작업이 전혀 진행되지 못하는 상태를 데드락이라고 한다.

  - MariaDB INNODB는 데드락 감지기를 갖고 있으며, 트랜잭션 중 하나를 롤백하고 에러를 출력하여 데드락에 빠지는 것을 방지 한다.

3. 테스트 

#테스트 환경

  - CentOS 7

  - MariaDB 10.3.8 

3.1 트랜잭션 락 발생 

1. 테스트 테이블 및 데이터 생성 
MariaDB [test]> create table deadlock( no int(10) unsigned not null auto_increment, primary key(no));
Query OK, 0 rows affected (0.608 sec)

MariaDB [test]> insert into deadlock values();
Query OK, 1 row affected (0.002 sec)

MariaDB [test]> insert into deadlock select null from deadlock;
Query OK, 1 row affected (0.001 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> insert into deadlock select null from deadlock;
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

2.데드락 발생 
#session 1 

  - AUTO COMMIT = ON일 경우 즉시 커밋이 되기 때문에 테스트를 위해서 OFF로 변경한다.
  - 트랜잭션을 시작(BEGIN)하고 insert문을 실행 한 후 트랜잭션 종료를 하지 않는다. 

MariaDB [test]> set session autocommit = OFF;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

MariaDB [test]> BEGIN;

Query OK, 0 rows affected (0.000 sec)

  - 값 8을 삽입 한다.
MariaDB [test]> insert into deadlock values();
Query OK, 1 row affected (0.001 sec)

MariaDB [test]> select * from deadlock;
+----+
| no |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  7 |
|  8 |
+----+

#session 2 

  - AUTO COMMIT = ON일 경우 즉시 커밋이 되기 때문에 테스트를 위해서 OFF로 변경한다.
  - 트랜잭션을 시작(BEGIN)하고 insert문을 실행 한 후 트랜잭션 종료를 하지 않는다. 

MariaDB [test]> set session autocommit = OFF;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> insert into deadlock values();
Query OK, 1 row affected (0.000 sec)

  - 값 10을 삽입 한다.
MariaDB [test]> select * from deadlock;
+----+
| no |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  7 |
| 10 |
+----+
7 rows in set (0.000 sec)

#session 1 
  - deadlock 테이블의 no값이 8번을 10번으로 업데이트 하자.
  
MariaDB [test]> update deadlock set no = 10 where no = 8;

#session 2 
  - deadlock 테이블의 no값이 10번을 8번으로 업데이트 하자.
  - session 2에서 deadlock이 발생했다는 에러를 반환하고 실패 한다.
MariaDB [test]> update deadlock set no = 8 where no = 10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

#session 1
  - 업데이트를 성공 했다.
  - no값이 10인 로우는 session2에서 트랜잭션 진행 중이였기 때문에 대기하다가 
    deadlock에러 발생후 트랜잭션이 종료 되어 업데이트에 성공 했다.

MariaDB [test]> update deadlock set no = 10 where no = 8;
Query OK, 1 row affected (2.202 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from deadlock;
+----+
| no |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  7 |
| 10 |
+----+
7 rows in set (0.000 sec)

MariaDB [test]> commit;
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> show engine innodb status\G;

  - innodb가 데드락을 어떻게 탐지하고 처리 했는지 자세한 내용을 확인해 보자.
  - session 2에서 발생한 트랜잭션을 롤백(ROLLBACK)하면서 데드락을 종료 시켰다. 

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-21 17:17:20 0x7fed807ad700
*** (1) TRANSACTION:
TRANSACTION 125390168, ACTIVE 566 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 861802, OS thread handle 140676419942144, query id 169973223 localhost root Updating
update deadlock set no = 10 where no = 8
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 68400 page no 3 n bits 80 index PRIMARY of table "test"."deadlock" trx id 125390168 lock mode S locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 00000779516b; asc    yQk;;
 2: len 7; hex 290000032e14e0; asc )   .  ;;

*** (2) TRANSACTION:
TRANSACTION 125391211, ACTIVE 489 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 861988, OS thread handle 140658039510784, query id 169973512 localhost root Updating
update deadlock set no = 8 where no = 10
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 68400 page no 3 n bits 80 index PRIMARY of table "test"."deadlock" trx id 125391211 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 00000779516b; asc    yQk;;
 2: len 7; hex 290000032e14e0; asc )   .  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 68400 page no 3 n bits 80 index PRIMARY of table "test"."deadlock" trx id 125391211 lock mode S locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 00000008; asc     ;;
 1: len 6; hex 000007794d58; asc    yMX;;
 2: len 7; hex 630000033617ae; asc c   6  ;;

*** WE ROLL BACK TRANSACTION (2)
  

4. 해결방법 및 결론

  - 2개의 세션에서 트랜잭션 중에 각 각 다른 세션에서 추가 된 행을 업데이트 하려고 할 때 서로 lock이 해제 될때만을 가디리며 교착상태에 빠진다.

  - 오토커밋을 사용 할 경우, 반대쪽 세션에서 즉시 값을 업데이트 할 수 있기 때문에 교착상태에 빠지지 않는다.

  - INNODB에서 데드락을 감지하여 한쪽 세션에 대해서 롤백을 진행하여 교착상태에 빠지진 않지만 언제 어떤 쿼리가 롤백 될지 모르기 때문에 데드락이 발생하지 않기 쿼리를 작성하는 것이 중요하다.