2024.08.05 Locks Set by SQL 3, INSERT INTO T SELECT ... FROM S WHERE..

TIL

2024.08.05 Locks Set by SQL 3, INSERT INTO T SELECT ... FROM S WHERE..

Gisungcu 2024. 8. 5. 23:05

MySQL :: MySQL 8.0 Reference Manual :: 17.7.3 Locks Set by Different SQL Statements in InnoDB

17.7.3 Locks Set by Different SQL Statements in InnoDB A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in

dev.mysql.com

INSERT INTO ... SELECT Performance with Innodb tables.

Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN SHARE MODE or FOR UPDATE, SELECT statements will not lock any rows while running. This is generally cor

www.percona.com

INSERT ... ON DUPLICATE KEY UPDATE 는 기존 insert와는 다르게 duplicate key에러 발생 시 S lock획득이 아닌 x lock을 획득하려합니다.
이로인해 기존 S lock으로 인한 dead lock은 발생하지 않을 거 같습니다.
exclude next key lock을 얻으려고 한다는데 이는 뭔지 모르겠다.
음 생각해보니 record x lock인듯 하다.

INSERT INTO T SELECT ... FROM S WHERE ...문은 select 조건에 next key lock을 얻습니다.
MVCC때문에 일관된 읽기를 하지 않나?라고 생각할 수 있지만 MVCC에서 DML문은 일관된읽기에서 예외입니다.
근데 알고있던 것은 select에 shared lock을 잡는다 였는데 shared next-key locks은 next key lock과 무엇이 다르지? record에 X lock을 잡지 않는다는 것인가?

record lock이라도 shared lock과 exclude lock이 있다.
단순 Lock in share mode를 사용하면 shared lock이고 for update를 하면 exclude lock으로 확인된다.

gap lock은 S lock이라고 볼 수 있고.
그래서 record lock에는 S,X lock이 있고 INSERT INTO T SELECT ... FROM S WHERE에는 S lock기반의 next key lock을 거는 것으로 생각된다.
pk를 사용하면 gap lock은 없고, 값이 없을 때는 gap lock이 있다.
secondary index를 사용하면 gap lock이 있다.
밑의 data.lock을 보면 X lock은 어느 상황에서도 없다. 공식문서에서 말하는 shared next key lock인 거 같다.

실습을 해보면 select 조건에 S lock이 잡힌다.

gap lock은 잡히지 않아서 gap에 대한 insert는 가능하다.
uk,pk를 사용하지 않으면 gap lock은 잡힌다.

CREATE TABLE tb_gaplock (
id INT NOT NULL,
name VARCHAR(50) DEFAULT NULL,
num INT DEFAULT NULL,
PRIMARY KEY (id),
INDEX idx_name (name),
INDEX idx_num (num)
);

INSERT INTO tb_gaplock
VALUES (1, 'Matt', 1),
(3, 'Esther', 3),
(6, 'Peter', 6),
(15, '15', 15),
(20, '20', 20);

	A
	mysql> INSERT INTO tb_gaplock (id, name, num) -> SELECT 2, name, num -> FROM tb_gaplock -> WHERE id = 3; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
		mysql> insert into tb_gaplock(id,name) values (2,'Matt2'); wait
		mysql> insert into tb_gaplock(id,name) values (4,'Matt2'); Query OK, 1 row affected (0.00 sec)

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:1068:139724815651624
ENGINE_TRANSACTION_ID: 3343
            THREAD_ID: 48
             EVENT_ID: 16
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139724815651624
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:1068:139724815651536
ENGINE_TRANSACTION_ID: 3343
            THREAD_ID: 48
             EVENT_ID: 16
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139724815651536
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:2:4:11:139724815648544
ENGINE_TRANSACTION_ID: 3343
            THREAD_ID: 48
             EVENT_ID: 16
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139724815648544
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
3 rows in set (0.00 sec)

그럼 값이 없는 거에 대해서는?

gap lock이 잡힌다.
gap에 대해서 insert가 불가능하다.

	A
	mysql> INSERT INTO tb_gaplock (id, name, num) SELECT 2, name, num FROM tb_gaplock WHERE id = 2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

1번 까지 실행
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:1068:139724815651536
ENGINE_TRANSACTION_ID: 421199868816600
            THREAD_ID: 48
             EVENT_ID: 23
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139724815651536
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:2:4:11:139724815648544
ENGINE_TRANSACTION_ID: 421199868816600
            THREAD_ID: 48
             EVENT_ID: 23
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139724815648544
            LOCK_TYPE: RECORD
            LOCK_MODE: S,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
2 rows in set (0.00 sec)

uk,pk를 사용하지 않으면 gap lock은 잡힌다.

3~6~15 까지 next key lock이 걸리고 insert가 불가능하다.
record에는 S lock이 걸리니 select는 가능하다.
이걸 보면 공식문서에서 말하는 shared next key lock이라고 볼 수 있는 것일까.

	A
	mysql> INSERT INTO tb_gaplock (id, name, num) SELECT 2, name, num FROM tb_gaplock WHERE num = 6; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from tb_gaplock; +----+--------+------+ \| id \| name \| num \| +----+--------+------+ \| 1 \| Matt \| 1 \| \| 3 \| Esther \| 3 \| \| 6 \| Peter \| 6 \| \| 15 \| 15 \| 15 \| \| 17 \| Pee \| 14 \| \| 20 \| 20 \| 20 \| +----+--------+------+ 6 rows in set (0.00 sec)
		mysql> insert into tb_gaplock(id,name, num) values (26,'Matt2',4); wait..
		mysql> insert into tb_gaplock(id,name, num) values (26,'Matt2',14); wait..
		mysql> insert into tb_gaplock(id,name, num) values (26,'Matt2',16); Query OK, 1 row affected (0.00 sec)
		mysql> select * from tb_gaplock where id = 2; Empty set (0.00 sec)

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:1068:139724815651624
ENGINE_TRANSACTION_ID: 3378
            THREAD_ID: 48
             EVENT_ID: 45
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139724815651624
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:1068:139724815651536
ENGINE_TRANSACTION_ID: 3378
            THREAD_ID: 48
             EVENT_ID: 45
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139724815651536
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:2:6:9:139724815648544
ENGINE_TRANSACTION_ID: 3378
            THREAD_ID: 48
             EVENT_ID: 45
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 139724815648544
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: 6, 6
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:2:4:12:139724815648888
ENGINE_TRANSACTION_ID: 3378
            THREAD_ID: 48
             EVENT_ID: 45
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139724815648888
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 6
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:2:6:10:139724815649232
ENGINE_TRANSACTION_ID: 3378
            THREAD_ID: 48
             EVENT_ID: 45
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 139724815649232
            LOCK_TYPE: RECORD
            LOCK_MODE: S,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15, 15
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 139724892105944:2:6:12:139724815649232
ENGINE_TRANSACTION_ID: 3378
            THREAD_ID: 48
             EVENT_ID: 45
        OBJECT_SCHEMA: sample
          OBJECT_NAME: tb_gaplock
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_num
OBJECT_INSTANCE_BEGIN: 139724815649232
            LOCK_TYPE: RECORD
            LOCK_MODE: S,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 6, 2
6 rows in set (0.00 sec)