안녕하세요. 항상 구루비에서 많은 정보를 얻고 있습니다.

답변에 미리 감사드리며, 관심 부탁드립니다.

MySQL에서 insert할 때 락이걸리는게 잘 이해가 되지 않아서 질문드립니다.

현재 MySQL을 사용하고 있습니다.

트랜잭션 격리 수준은 디폴트(repeatable read)입니다.

질문 

 1-1. 아래의 질문 상세에서 전체 로우 락이 됐을 때 insert가 왜 안되는지 궁금합니다.

 1-2. insert 되지 않는 이유가 next-key-lock?? 때문에 안되는건가요?

 2. 아래의 질문 상세에서 격리 수준을 read committed로 변경하면 아래의 시나리오에서 insert가 잘 됩니다. 혹시 읽기 일관성 보장을 안해도 되니깐 insert 락이 안걸리는건가요?

질문 상세(실제 테이블을 만들어서 테스트했습니다.)

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)

delete 실행 계획 결과 where 절에 아무리 pk라도, 풀 테이블 스캔이 유리해서 풀 테이블 한것으로 예상되며,

모든 로우가 스캐닝 됐으니까 모든 로우가 락이걸리는 것 까지는 이해가됩니다

그리고 베타락이니까 update/delete 작업을 할 수 없는것은 이해되는데요

근데.. 왜 insert는 lock wait을 하는걸까요???  mysql의 repeatable read는 팬텀 리드(로우)도 막아준다는데 그거랑 관련이 있을까요..?

참고로 유니크 인덱스 스캔을 하는 경우, insert는 잘 됩니다!

 1-1. 전체 로우 락이 됐을 때 insert가 왜 안되는지 궁금합니다.

 1-2. insert 되지 않는 이유가 next-key-lock?? 때문에 안되는건가요?

 2. 격리 수준을 read committed로 변경하면 위의 시나리오에서 insert가 잘 됩니다. 혹시 읽기 일관성 보장을 안해도 되니깐 insert 락이 안걸리는건가요?

글 읽어주셔서 감사드리고 답변 주시면 정말 감사하겠습니다 ^^7