TIL
2024.08.08 index scan lock
Gisungcu
2024. 8. 8. 22:36
MySQL(InnoDB)이 잠금할 레코드를 고르는 방법
MySQL 을 처음 공부할 때, 레코드 잠금에 관해서 여러가지 궁금한 점들이 많았다. 그동안 궁금했던 세부적인 부분들에 대해서 정리해보고자 한다.MySQL 은 기본적으로 잠금 읽기(locking read), update, de
pius712.tistory.com
Index관련 문서를 보면 index scan에 걸리는 것들은 lock이 걸린다고 합니다.
Where조건에 해당이 안되도 lock에 걸릴 수 있다는 것 입니다. 예로UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
해당 쿼리가 b인덱스만을 사용한다고 해봅시다.Where조건만보면 둘의 조건이 and인 것들만 lock이 걸릴 거 같지만 b=2인 것을은 모두 lock이 걸립니다.
Uk가 아니면 b=2인 row 모두가 lock에 걸리게 됩니다.위의 조건들은 격리수준이 repeatable read이었을 때에 대한 것들이고 격리수준이 read committed이면 where절과 일치하지 않는다면 처음에 예상한 거 처럼 lock을 해제합니다.
음 궁금해졌다.
index scan을 할 때 range로 스캔을 하게될 텐데 앞에 값도 lock을 잡나? 아니면 record에만 lock을 잡나?.
결과는 rocord에 x lock을 잡고 뒤까지 gap lock을 잡지만 gap이 없어서 반영되는 것은 없었다.
순서대로 값이 있을 때
| mysql> select * from tb_gaplock; +----+-------+------+ | id | name | num | +----+-------+------+ | 1 | Matt | 1 | | 2 | Matt2 | 2 | | 3 | msss | 3 | | 4 | Matt2 | 4 | | 6 | Peter | 6 | | 15 | 15 | 15 | | 20 | 20 | 20 | +----+-------+------+ 7 rows in set (0.00 sec) |
||
| mysql> update tb_gaplock set num = 54 where num = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update tb_gaplock set num = 54 where num = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update tb_gaplock set num = 54 where num = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
1번 실행 시
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:1068:140558509075104
ENGINE_TRANSACTION_ID: 5396
THREAD_ID: 48
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140558509075104
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:6:8:140558509072192
ENGINE_TRANSACTION_ID: 5396
THREAD_ID: 48
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 140558509072192
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 3, 3
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:4:11:140558509072536
ENGINE_TRANSACTION_ID: 5396
THREAD_ID: 48
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140558509072536
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:6:28:140558509072880
ENGINE_TRANSACTION_ID: 5396
THREAD_ID: 48
EVENT_ID: 20
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 140558509072880
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 4, 4
4 rows in set (0.01 sec
그럼 중복 값이 있을 때 정말로 lock을 잡나?
잡는다 num이 2인 것에 모두 lock이 잡혔다.
2가 아닌 것은 수정이 가능하다.
range scan을 했을 때 데이터 페이지의 앞에서 부터 scan을 하지 않는 것인가?
음 이건 index seek이라고 부르는 거 같다. 내일 더 알아보자.
| 1 | mysql> select * from tb_gaplock; +----+-------+------+------+ | id | name | num | num2 | +----+-------+------+------+ | 1 | Matt | 1 | 1 | | 2 | Matt2 | 2 | 2 | | 3 | msss | 2 | 3 | | 4 | Matt2 | 2 | 4 | | 6 | Peter | 6 | 6 | | 15 | 15 | 15 | NULL | | 20 | 20 | 20 | NULL | +----+-------+------+------+ 7 rows in set (0.00 sec) |
|
| 2 | mysql> explain update tb_gaplock set num = 36 where num = 2 and num2 = 3; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ | 1 | UPDATE | tb_gaplock | NULL | range | idx_num | idx_num | 5 | const | 3 | 100.00 | Using where; Using temporary | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) |
|
| 3 | update tb_gaplock set num = 36 where num = 2 and num2 = 3; | |
| mysql> update tb_gaplock set num = 54 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
3번까지 실행 시
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:1068:140558509075104
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140558509075104
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:6:27:140558509072192
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 140558509072192
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 2, 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:6:30:140558509072192
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 140558509072192
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 2, 3
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:6:31:140558509072192
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 140558509072192
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 2, 4
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:4:41:140558509072536
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140558509072536
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:4:42:140558509072536
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140558509072536
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:4:43:140558509072536
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140558509072536
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 4
*************************** 8. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140558497443840:2:6:9:140558509072880
ENGINE_TRANSACTION_ID: 5448
THREAD_ID: 48
EVENT_ID: 74
OBJECT_SCHEMA: sample
OBJECT_NAME: tb_gaplock
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 140558509072880
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 6, 6
8 rows in set (0.00 sec)