MySQL insert, Lock wait timeout exceeded 트러블슈팅

MySQL 정보

• 버전: 5.7
• 트랜잭션 격리 수준: Default(repeatable read)

장애 현상

• A 트랜잭션에서 T1 테이블에 delete 쿼리 진행할 때 B 트랜잭션에서 T1 테이블에 insert 쿼리가 lock wait 발생

원인-1(첫 번째 분석)

• delete 쿼리의 실행 계획에서 풀 테이블 스캔 발생
• Where 절이 pk로 구성되어 있어도 옵티마이저는 풀 테이블 스캔이 유리하다고 판단(풀 테이블 스캔은 멀티 블록 스캔이고 인덱스는 싱글 블록 스캔이니 일정 분기점 이상 부터는 인덱스 스캔보다는 풀테이블 스캔이 유리함)
• MySQL에서 로우에 락이 잡히는 기준은 스캐닝된 인덱스 레코드[1]

위의 분석으로 알 수 있는 사항은 다음과 같다.

• T1 테이블의 모든 로우는 락이 잡혔으며
• 락이 잡혀있는 동안 다른 세션(트랜잭션)에서 T1 테이블의 모든 로우는 delete/update 불가능하다.

근데 insert는 왜 안돼??? 격리 수준을 read committed 했을 때는 insert가 잘 된다.

원인-2(두 번째 분석)

• 현재 MySQL의 격리 수준은 repeatable read이다.
• delete 쿼리를 진행할 때 Next-Key-Lock이 잡혔으며[2],
• Repeatable read에서 Next-key-lock으로 인해 positive infinity 레코드 까지 락이 걸렸고,
• 해당 락으로 인해 insert에 lock wait이 발생한다.
• 위의 락으로 본의 아니게 팬텀 로우를 방지한다

기타 특이 사항

• delete할 때 풀 테이블 스캔이 아닌 유니크 스캔을 할 때 insert에 lock wait이 발생하지 않는다.
• ㄴ유니크 스캔을 하면 lock wait 발생하지 않는게 웃기지 않나?? Repeatable read에서 팬텀 로우를 막아준다는데 안 막아준다. ㅈㄴ 어불성설이다ㅋㅋㅋㅋㅋㅋㅋㅋ(아 근데 이 부분에서 lock wait 발생하면 동시성 ㅈ됨)
• read committed 격리 수준일 때 되는 이유는 단순하다. gap lock, next-key-lock이 비활성화 되어 있기 때문이다.
• 반대 상황도 Lock이 발생한다. 1) insert 2) delete 풀 테이블 스캔

기타 주의 사항

• 인덱스 조건없이 쿼리하는 경우 테이블 락 발생
• 2개 이상의 세션에서 동일한 인덱스 키를 사용하여 다른 행의 레코드에 엑세스할 때 락 충돌할 수 있다(동일한 스캔 범위가 있는 경우 lock wait 발생)
• 인덱스 효율이 좋지 않으면 락 범위가 넓어진다.
• 아무리 PK/유니크 인덱스를 사용하여도 옵티마이저 판단하에 풀 테이블 스캔으로 테이블 락이 발생할 수 있다(지금 상황).

재연

1. 테이블 생성

mysql> create table test_tt(t1 int primary key auto_increment, t2 int, t3 int); 
Query OK, 0 rows affected (0.09 sec)

2. 테스트 데이터 삽입/조회

mysql> insert into test_tt(t2, t3) values (1,2); Query OK, 1 row affected (0.00 sec) 
... 

mysql> select * from test_tt;
+----+------+------+
| t1 | t2   | t3   |
+----+------+------+
|  1 |    1 |    2 |
|  2 |    1 |    2 |
|  4 |    1 |    2 |
| 31 |    1 |    2 |
| 32 |    1 |    2 |
| 33 |    1 |    2 |
| 34 |    1 |    2 |
| 35 |    1 |    2 |
| 36 |    1 |    2 |
| 37 |    1 |    2 |
| 38 |    1 |    2 |
| 39 |    1 |    2 |
| 40 |    1 |    2 |
| 41 |    1 |    2 |
| 42 |    1 |    2 |
| 43 |    1 |    2 |
| 44 |    1 |    2 |
+----+------+------+
17 rows in set (0.00 sec)

3. 삭제 실행(세션 1, 트랜잭션 시작, 삭제실행)

mysql> explain delete from test_tt where t1 in(31,32,33,34);
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | test_tt | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

4. 삽입 실행(세션2, 트랜잭션 시작)

mysql> insert into test_tt(t2, t3) values (1,2);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

5. DB 락 현황 조회 결과

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+------------------------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+--------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+------------------------+
| 10586:43:3:1 | 10586       | X         | RECORD    | `mysql`.`test_tt` | PRIMARY    |         43 |         3 |        1 | supremum pseudo-record |
| 10581:43:3:1 | 10581       | X         | RECORD    | `mysql`.`test_tt` | PRIMARY    |         43 |         3 |        1 | supremum pseudo-record |
+--------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)
 
mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 10586             | 10586:43:3:1      | 10581           | 10581:43:3:1     |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

 

참고문헌

• [1]MySQL Doc, locking read
• [2]MySQL Doc, InnoDB Locking
• row lock 관련 아티클
• MySQL Doc,Transaction Isolation Levels, read committed 관련