개발관련/삽질
MySQL insert, Lock wait timeout exceeded 트러블슈팅
동팡
2022. 2. 22. 00:52
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 transaction5. 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)