데이터베이스 질문과 답변
안녕하세요. 항상 구루비에서 많은 정보를 얻고 있습니다.
답변에 미리 감사드리며, 관심 부탁드립니다.
MySQL에서 insert할 때 락이걸리는게 잘 이해가 되지 않아서 질문드립니다.
현재 MySQL을 사용하고 있습니다.
트랜잭션 격리 수준은 디폴트(repeatable read)입니다.
질문
1-1. 아래의 질문 상세에서 전체 로우 락이 됐을 때 insert가 왜 안되는지 궁금합니다.
1-2. insert 되지 않는 이유가 next-key-lock?? 때문에 안되는건가요?
2. 아래의 질문 상세에서 격리 수준을 read committed로 변경하면 아래의 시나리오에서 insert가 잘 됩니다. 혹시 읽기 일관성 보장을 안해도 되니깐 insert 락이 안걸리는건가요?
질문 상세(실제 테이블을 만들어서 테스트했습니다.)
1. 테이블 생성
1 2 | mysql> create table test_tt(t1 int primary key auto_increment, t2 int, t3 int);Query OK, 0 rows affected (0.09 sec) |
2. 테스트 데이터 삽입/조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 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, 트랜잭션 시작, 삭제 실행)
1 2 3 4 5 6 7 | 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, 트랜잭션 시작)
1 2 | mysql> insert into test_tt(t2, t3) values (1,2);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
5. DB 락 현황 조회 결과
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
1-1/1-2번
MySQL Lock 관련 문서에서 좀 긴가민가 적혀있어서 확신을 못 가졌는데요.
다른 블로그에서 명시적으로 언급해줬네요.
next-key-lock 으로 인해 insert 실패한 것 같습니다.
2번
읽기 일관성 보다는...
read committed 격리 수준에서는 gap lock과 next-key-lock이 비활성화 되어 있어서 insert가 가능한 것 같습니다.(링크의 READ COMMITTED 부분 참고).
) 버튼을 클릭하여 작성 하시면 됩니다.