-
2024.08.08 index scan lockTIL 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: 01번 실행 시 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: 03번까지 실행 시 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)'TIL' 카테고리의 다른 글
2024.08.12 (0) 2024.08.12 2024.08.09 index seek (0) 2024.08.11 2024.08.07 gap lock (0) 2024.08.07 2024.08.06 Intention lock (0) 2024.08.06 2024.08.05 Locks Set by SQL 3, INSERT INTO T SELECT ... FROM S WHERE.. (0) 2024.08.05