InnoDB 엔진의 잠금(Lock)

Lock

동시성을 제어하기 위한 기능으로, 스토리지 레벨에서의 lock 과 mysql 엔진 레벨에서의 lock 으로 나뉜다.

InnoDB 엔진의 잠금 (Lock)

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

InnoDB 엔진은 레코드 단위의 잠금 방식을 제공하여 동시성 처리가 가능하다.

performance_schema 데이터베이스에 있는 data_locks, data_lock_waits 테이블을 통해 어떤 트랜잭션이 어떤 락을 점유하고 있고 대기하는지 조회할 수 있으며, 트랜잭션을 강제로 종료할 수도 있다.

InnoDB 는 락을 생성할 때 메모리 공간을 효율적으로 쓰기 때문에, 테이블 내에 모든 레코드 대상으로 락을 획득한다고 해서 락 에스컬레이션(lock escalation)이 발생하지 않는다.

💡

Lock Escalation

좁은 범위의 락이 다수 잡힐 경우 DBMS에서 해당 락의 범위를 상승시키는 현상이다. (레코드 또는 페이지 단위의 락을 테이블 단위의 락으로 바꾸는 현상.)

좁은 범위로 락을 여러개 잡는 것보다 큰 범위로 적은 수의 락을 잡는게 메모리 측면에서 더 효율적이기 때문이다.

Record (row-level) Lock

InnoDB 엔진에서 record lock 이란 인덱스를 잠그는 것이다.

row-level lock == index lock

레코드를 탐색하는 과정에서 스캔된 secondary 인덱스 및 레코드를 가리키는 clustered index 를 잠근다.

테이블 내에 clustered index 가 없으면 hidden clustered index 를 만들고 해당 인덱스를 잠근다.

💡

clustered index

테이블 내 레코드들을 정렬하고, 정렬된 레코드들을 가라키는 유니크 인덱스

Locking 대상이 될 clustered index 를 결정하는 순서

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

1) PK

PK 컬럼에는 기본적으로 인덱스가 붙는다.

테이블 내에 PK가 있으면 PK에 붙은 인덱스가 락의 대상이 된다.

2) unique key

테이블 내에 PK 가 없으면 테이블 내에 가장 첫번째 (첫번째의 기준이 뭔지는 모르겠음) not null unique 컬럼에 붙은 유니크 인덱스가 락의 대상이 된다.

3) hidden clustered index

테이블 내에 PK도 없고 not null unique key 도 없으면 InnoDB 엔진은 row ID 를 저장하는 가상의 컬럼에 GEN_CLUST_INDEX 라는 이름의 인덱스를 만든다. (row ID 는 엔진이 레코드 대상으로 부여한다.)

row ID 를 기준으로 레코드들을 정렬하고, GEN_CLUST_INDEX 가 락의 대상이 된다.

예)

PK 없고 유니크 인덱스도 없는 테이블 생성 (cn_without_unique_index 컬럼에 secondary 인덱스는 있음)


mysql> create table test_gap_lock (
  cn_without_unique_index INT NOT NULL DEFAULT 0, 
  cn_name VARCHAR(128)
);
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE test_gap_lock ADD INDEX `idx_to_cn_without_unique_index` (cn_without_unique_index);

row 생성


mysql> insert into test_gap_lock(cn_without_unique_index, cn_name) values(1, 'name1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_gap_lock(cn_without_unique_index, cn_name) values(2, 'name2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_gap_lock(cn_without_unique_index, cn_name) values(3, 'name3');
Query OK, 1 row affected (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_gap_lock(cn_without_unique_index, cn_name) values(3, 'name3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_gap_lock(cn_without_unique_index, cn_name) values(4, 'name4');
Query OK, 1 row affected (0.01 sec)

exclusive lock 획득하는 트랜잭션 시작


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_gap_lock where cn_without_unique_index between 1 and 4 for update;
+-------------------------+---------+
| cn_without_unique_index | cn_name |
+-------------------------+---------+
|                       1 | name1   |
|                       2 | name2   |
|                       3 | name3   |
|                       3 | name3   |
|                       4 | name4   |
+-------------------------+---------+
5 rows in set (0.01 sec)

performance_schema.data_locks 조회

→ where cn_without_unique_index between 1 and 4 조건으로 검색하는 과정에서 idx_to_cn_without_unique_index 인덱스가 스캔되었기 때문에 idx_to_cn_without_unique_index 에 락 획득

→ GEN_CLUST_INDEX 라는 clustered index 생성 후, 해당 인덱스 대상으로 record lock(X,REC_NOT_GAP) 획득


mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+--------------------------------+-----------+---------------+-------------+--------------------------------+
| THREAD_ID | OBJECT_NAME   | INDEX_NAME                     | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                      |
+-----------+---------------+--------------------------------+-----------+---------------+-------------+--------------------------------+
|        79 | test_gap_lock | NULL                           | TABLE     | IX            | GRANTED     | NULL                           |
|        79 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | supremum pseudo-record         |
|        79 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | 1, 0x000000000200              |
|        79 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | 2, 0x000000000201              |
|        79 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | 3, 0x000000000202              |
|        79 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | 3, 0x000000000203              |
|        79 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | 4, 0x000000000204              |
|        79 | test_gap_lock | GEN_CLUST_INDEX                | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000200                 |
|        79 | test_gap_lock | GEN_CLUST_INDEX                | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000201                 |
|        79 | test_gap_lock | GEN_CLUST_INDEX                | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000202                 |
|        79 | test_gap_lock | GEN_CLUST_INDEX                | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000203                 |
|        79 | test_gap_lock | GEN_CLUST_INDEX                | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000204                 |
+-----------+---------------+--------------------------------+-----------+---------------+-------------+--------------------------------+
20 rows in set (0.01 sec)

LOCK_MODE

LOCK_MODE	meaning
X	X,REC_NOT_GAP + X,GAP 과 동일. 인덱스 자체에 X lock 획득 + 인덱스 앞의 gap 에 X lock 획득.
X,REC_NOT_GAP	인덱스 자체에 X lock 획득.

다른 LOCK_MODE 는 여기 에서 Record Locks 참고

LOCK_DATA

LOCK_TYPE 이 RECORD 이면 레코드 내 필드들을 보여주고, 아니면 NULL 보여준다.

secondary 인덱스에 락이 걸리면 secondary 인덱스가 걸린 컬럼의 필드값, <some_unique_value> 형식으로 보여준다.

some_unique_value

PK 가 있는 경우

PK 인덱스에 락이 걸리면 PK 컬럼의 필드값을 보여준다.

PK 가 없는 경우

유니크 인덱스가 걸린 컬럼의 필드값을 보여준다.
유니크 인덱스도 없으면 row ID 를 보여준다.

Gap Lock

2개의 인덱스 사이 또는 맨 앞에 있는 인덱스의 앞, 맨 뒤의 인덱스의 뒤를 잠근다.

다른 트랜잭션이 동일한 gap 에 insert 시도하는 것을 방지한다.

격리성 레벨을 READ COMMITED 으로 조정하면 gap lock 은 FK 제약을 검사하거나 duplicate-key 검사를 할 때를 제외하고 만들어지지 않는다. (Mysql 디폴트 격리성 레벨 : Repeatable Read)

예를 들어 아래 쿼리를 수행하면


select cn1 from tb where cn1 between 10 and 20 for update;

위 쿼리를 수행하는 쓰레드는 cn1 값이 10 ~ 20 사이에 있는 레코드들을 대상으로 lock 을 획득하고, 쿼리가 종료될 때 까지 cn1 에 10 ~ 20 사이의 값을 생성하려는 다른 트랜잭션은 대기한다.

(실제로 cn1 값이 10 ~ 20 인 레코드가 존재하는지 여부는 상관없이 gap lock 을 획득하므로 gap 내에는 인덱스가 있을 수도 있고 없을 수도 있다.)

유니크 인덱스를 통해서는 gap lock 을 획득하지 않는다.

예를 들어 아래와 같이 id(PK)를 조건으로 row 를 찾는 쿼리는 gap lock 을 획득하지 않고 id = 1 에 해당하는 레코드의 id(PK)에 붙은 PRIMARY 인덱스 대상으로만 락을 획득한다.


mysql> start transaction;
mysql> select * from tb where id = 1 for update;
+----+
| id |
+----+
|  1 |
+----+

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
|        81 | tb          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        81 | tb          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

유니크 인덱스가 없는 컬럼 대상으로 select ~ for update 쿼리를 수행하면 아래처럼 값이 gap lock 을 획득한다.


mysql> start transaction;
mysql> select * from test_gab_lock where cn_without_unique_index = 1 for update;
+-------------------------+---------+
| cn_without_unique_index | cn_name |
+-------------------------+---------+
|                       1 | name1   |
+-------------------------+---------+
1 row in set (0.01 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+--------------------------------+-----------+---------------+-------------+-------------------+
| THREAD_ID | OBJECT_NAME   | INDEX_NAME                     | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA         |
+-----------+---------------+--------------------------------+-----------+---------------+-------------+-------------------+
|        81 | test_gap_lock | NULL                           | TABLE     | IX            | GRANTED     | NULL              |
|        81 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X             | GRANTED     | 1, 0x000000000200 |
|        81 | test_gap_lock | GEN_CLUST_INDEX                | RECORD    | X,REC_NOT_GAP | GRANTED     | 0x000000000200    |
|        81 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X,GAP         | GRANTED     | 2, 0x000000000201 |
+-----------+---------------+--------------------------------+-----------+---------------+-------------+-------------------+
6 rows in set (0.00 sec)

값이 1 에 해당하는 idx_to_cn_without_unique_index 인덱스에 X (X 에는 X,GAP 포함)

값이 2 에 해당하는 idx_to_cn_without_unique_index 인덱스에 X,GAP

여러 트랜잭션이 동일한 gap 에 락을 획득할 수 있다.

예) 2개 이상의 트랜잭션이 supremum pseudo-record 에 exclusive lock (next-key lock) 획득

트랜잭션 A



mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from tb where id > 4 for update;
Empty set (0.00 sec)


mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
|        77 | tb          | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|        77 | tb          | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
2 rows in set (0.01 sec)

트랜잭션 B


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from tb where id > 4 for update;
Empty set (0.00 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
|        79 | tb          | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|        79 | tb          | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
|        77 | tb          | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
|        77 | tb          | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
+-----------+-------------+------------+-----------+-----------+-------------+------------------------+
4 rows in set (0.01 sec)

Next key Lock

record lock 과 gap lock 의 조합

next-key lock 은 인덱스 내에서 가장 값이 큰 값과 가상의 무한대 레코드 사이의 gap 에 lock 을 획득한다.

예시) 어떤 인덱스에 10, 11, 13, 20 이 있다면 아래처럼 구간이 있을 것이다.

소괄호 : 초과/미만

대괄호 : 이상/이하


(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

위 예시의 경우 next-key lock 은 (20, positive infinity) 에 해당하는 gap 에 lock 을 획득한다. 이 때 가상의 무한대 레코드를 supremum pseudo-record 라고 한다.

격리성 레벨이 REPEATABLE READ 인 경우, phantom row 를 방지하기 위해 인덱스 탐색/스캔할 때 next-key lock 을 사용한다.

💡

infimum pseudo record (가상의 가장 낮은 레코드) 는 없다.

cn_without_unique_index 컬럼에 2 ~ 4 값 들어가있음


mysql> select cn_without_unique_index from test_gap_lock;
+-------------------------+
| cn_without_unique_index |
+-------------------------+
|                       2 |
|                       3 |
|                       4 |
+-------------------------+
3 rows in set (0.01 sec)

next-key lock 생성 (supremum pseudo-record 에 X lock 생성)


mysql> select * from test_gap_lock where cn_without_unique_index > 4 for update;
Empty set (0.00 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+--------------------------------+-----------+-----------+-------------+------------------------+
| THREAD_ID | OBJECT_NAME   | INDEX_NAME                     | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+-----------+---------------+--------------------------------+-----------+-----------+-------------+------------------------+
|        86 | test_gap_lock | NULL                           | TABLE     | IX        | GRANTED     | NULL                   |
|        86 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X         | GRANTED     | supremum pseudo-record |
+-----------+---------------+--------------------------------+-----------+-----------+-------------+------------------------+
2 rows in set (0.00 sec)

infimum pseudo record ? 대상으로 락 생성 시도했으나 그런거 없음


mysql> select * from test_gap_lock where cn_without_unique_index < 1 for update;
Empty set (0.01 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+---------------+--------------------------------+-----------+-----------+-------------+-------------------+
| THREAD_ID | OBJECT_NAME   | INDEX_NAME                     | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA         |
+-----------+---------------+--------------------------------+-----------+-----------+-------------+-------------------+
|        86 | test_gap_lock | NULL                           | TABLE     | IX        | GRANTED     | NULL              |
|        86 | test_gap_lock | idx_to_cn_without_unique_index | RECORD    | X         | GRANTED     | 2, 0x000000000206 |
+-----------+---------------+--------------------------------+-----------+-----------+-------------+-------------------+
2 rows in set (0.01 sec)

Insert Intention Lock

INSERT 구문으로 레코드를 생성하기 전에 획득하는 gap lock

여러 트랜잭션들이 동일한 gap 에 레코드를 생성하더라도, 생성하는 위치가 다르면 서로 대기하지 않고 IX 락을 획득한다.

예시) 어떤 인덱스에 1, 4 가 있다.


mysql> select id from tb;
+----+
| id |
+----+
|  4 |
|  1 |
+----+
2 rows in set (0.01 sec)

2개의 트랜잭션이 각각 2, 3 을 생성하려고 한다.

→ 각 트랜잭션은 (1, 4] 에 해당하는 gap 에 서로를 블로킹하지 않고 insert intention lock 을 획득한다.

트랜잭션 A

id = 2 에 해당하는 row 생성 → 테이블 대상으로 IX(Intension Exclusive) Lock 획득


mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into city(id, name) values(2, 'name2');
Query OK, 1 row affected (0.01 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
|        81 | tb          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set (0.01 sec)

트랜잭션 B

id = 3 에 해당하는 row 생성 → 트랜잭션 A 에 블로킹되지 않고 테이블 대상으로 IX(Intension Exclusive) Lock 획득

트랜잭션 A 에서 생성한 id = 2 에 해당하는 레코드대상으로 exlusive lock 획득 (트랜잭션 A 가 변경할 것을 방지)


mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into city(id, name) values(3, 'name3');
Query OK, 1 row affected (0.01 sec)

mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
|        79 | tb          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        81 | tb          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        79 | tb          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
+-----------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)

AUTO-INC Lock

어떤 컬럼에 auto-increment 가 설정되어 있는 경우, 테이블에 레코드를 생성하기 위해 AUTO-INC lock 을 획득한다.

트랜잭션 유무와 상관없이 어떤 테이블 대상으로 INSERT 구문을 동시에 수행할 때 자동 증가 번호를 가져오기 위해 AUTO-INC lock 을 획득하거나, 다른 쿼리에 의해 이미 획득된다면 락이 해제될 때 까지 기다려야 한다.

innodb_autoinc_lock_mode 시스템 변수가 auto-increment locking 에 관여하고, 변수 설정에 따라 번호의 순차적 증가 보장의 정도와 동시성 간의 trade-off 를 유발한다.

Locking 측면에서 인덱스 설계가 중요한 이유

InnoDB 엔진에서 row-level locking 이란 레코드를 찾기 위해 스캔한 인덱스에 잠금을 거는 것이다.

예)

employee 테이블 생성

first_name 컬럼에 인덱스 생성

emp_no 컬럼에는 유니크 제약조건 생성 (유니크 인덱스 생성)


mysql> create table employee (
    ->   first_name varchar(128) not null,
    ->   last_name varchar(128) not null,
    ->   emp_no varchar(128) not null unique
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> alter table employee add index idx_first_name (first_name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

레코드들 생성 (first_name 은 같지만 last_name 이 다른 임직원 정보들)


mysql> insert into employee (first_name, last_name, emp_no) values('DaEun', 'Kim', 'A1234');
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee (first_name, last_name, emp_no) values('DaEun', 'Jeong', 'B1234');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee (first_name, last_name, emp_no) values('DaEun', 'Choi', 'C1234');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee (first_name, last_name, emp_no) values('JaDu', 'Kim', 'A3456');
Query OK, 1 row affected (0.04 sec)

mysql> insert into employee (first_name, last_name, emp_no) values('JaDu', 'Park', 'B3456');
Query OK, 1 row affected (0.02 sec)

first_name = ‘DaEun’ & last_name = ‘Kim’ 에 해당하는 레코드 대상으로 select for update 수행


mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee where first_name = 'DaEun' and last_name = 'Kim' for update;
+------------+-----------+--------+
| first_name | last_name | emp_no |
+------------+-----------+--------+
| DaEun      | Kim       | A1234  |
+------------+-----------+--------+
1 row in set (0.01 sec)

performance_schema.data_locks 테이블 조회

예상한 것은 first_name = ‘DaEun’ & last_name = ‘Kim’ 에 해당하는 레코드의 인덱스 대상으로만 락을 획득하는 것이지만

first_name 컬럼에 걸어놓은 idx_first_name 인덱스 때문에 first_name = 'DaEun' 조건을 만족하는 모든 레코드 대상으로 불필요하게 X lock 이 생성됨

emp_no 에 걸어놓은 유니크 인덱스에도 X,REC_NOT_GAP 모드의 락 생성.. (유니크 인덱스에도 락이 걸리는 이유는 여기 참고)

→ 인덱스 잘못 설계하면 동시성이 상당히 떨어질 수 있다.


mysql> select THREAD_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA from performance_schema.data_locks;
+-----------+-------------+----------------+-----------+---------------+-------------+------------------+
| THREAD_ID | OBJECT_NAME | INDEX_NAME     | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA        |
+-----------+-------------+----------------+-----------+---------------+-------------+------------------+
|        86 | employee    | NULL           | TABLE     | IX            | GRANTED     | NULL             |
|        86 | employee    | idx_first_name | RECORD    | X             | GRANTED     | 'DaEun', 'A1234' |
|        86 | employee    | idx_first_name | RECORD    | X             | GRANTED     | 'DaEun', 'B1234' |
|        86 | employee    | idx_first_name | RECORD    | X             | GRANTED     | 'DaEun', 'C1234' |
|        86 | employee    | emp_no         | RECORD    | X,REC_NOT_GAP | GRANTED     | 'A1234'          |
|        86 | employee    | emp_no         | RECORD    | X,REC_NOT_GAP | GRANTED     | 'B1234'          |
|        86 | employee    | emp_no         | RECORD    | X,REC_NOT_GAP | GRANTED     | 'C1234'          |
|        86 | employee    | idx_first_name | RECORD    | X,GAP         | GRANTED     | 'JaDu', 'A3456'  |
+-----------+-------------+----------------+-----------+---------------+-------------+------------------+
8 rows in set (0.01 sec)

테이블에 인덱스가 하나도 없다면?

InnoDB 엔진은 GEN_CLUST_INDEX 인덱스를 만들고 해당 인덱스를 풀 스캔 → 모든 레코드에 락이 걸리게 된다.