주로 사용하는 trasaction isolation 에는 read-commited / repeatable-read 가 있습니다.

그 중 repeatable-read 는 한 트랜잭션에서 수행하는 select 에 대해 항상 동일한 결과값을 보장하는 트랜잭션 레벨인데 

이를 위해 repeatable read 에는 두가지 특성이 있습니다.

1) MVCC - 트랜잭션에서 수행한 select 결과값들을 undo 에 백업하고 트랜잭션에 대해 번호를 부여함. 

트랜잭션 안에서 실행 된 모든 select 쿼리는 트랜잭션 번호가 작은 트랜잭션에서 변경된 값만 볼 수 있음

2) gap lock , next key lock - 변경 하는 데이터들의 레코드 사이사이에 거는 lock , 갭락 범위 직전, 직후의 레코드에 거는 lock

이번 글에서는 repeatable read 의 gap lock 과 next key lock을 살펴보겠습니다.

 

-1. data 준비

MariaDB [mysql]> delimiter ;;
MariaDB [mysql]> create procedure test.lock_test()
    -> begin
    -> declare i int default 1;
    -> declare j int default 1;
    -> create table test.lock_test_no_pk ( key int, value varchar(10) );
    -> while i < 30 do
    -> insert into test.lock_test_no_pk values ( i, concat('a0',j ) );
    -> set i:=i+1;
    -> set j:=j+1;
    -> end while;
    -> end
    -> ;;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> delimiter ;
MariaDB [(none)]>
MariaDB [(none)]> call test.lock_test;
Query OK, 29 rows affected (1.042 sec)

 

 

-2. 테스트 환경 확인

 

MariaDB [(none)]> select * from test.lock_test_no_pk;

+------+-------+

| num  | value |

+------+-------+

|    1 | a01   |

|    2 | a02   |

|    3 | a03   |

|    4 | a04   |

|    5 | a05   |

|    6 | a06   |

|    7 | a07   |

|    8 | a08   |

|    9 | a09   |

|   10 | a010  |

|   11 | a011  |

|   12 | a012  |

|   13 | a013  |

|   14 | a014  |

|   15 | a015  |

|   16 | a016  |

|   17 | a017  |

|   18 | a018  |

|   19 | a019  |

|   20 | a020  |

|   21 | a021  |

|   22 | a022  |

|   23 | a023  |

|   24 | a024  |

|   25 | a025  |

|   26 | a026  |

|   27 | a027  |

|   28 | a028  |

|   29 | a029  |

+------+-------+

29 rows in set (0.001 sec)

 

MariaDB [(none)]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)


MariaDB [(none)]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.001 sec)


MariaDB [(none)]> select * from mysql.innodb_index_stats where table_name='lock_test_no_pk';
+---------------+-----------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name      | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+-----------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | lock_test_no_pk | GEN_CLUST_INDEX | 2019-06-02 12:01:56 | n_diff_pfx01 |         29 |           1 | DB_ROW_ID                         |
| test          | lock_test_no_pk | GEN_CLUST_INDEX | 2019-06-02 12:01:56 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | lock_test_no_pk | GEN_CLUST_INDEX | 2019-06-02 12:01:56 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+-----------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.001 sec)

=>  테이블 생성 시 primary key를 생성하지 않으면 내부적으로 임의의 pk 를 생성함

 

 

-3. TEST - PK 없을 때

 

 --- A session ---

 

MariaDB [(none)]>
MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.016 sec)


MariaDB [(none)]> update test.lock_test_no_pk set value = 'test' where num between 10 and 20;
Query OK, 11 rows affected (0.003 sec)
Rows matched: 11  Changed: 11  Warnings: 0

 

 

 

--- B session ---

 

MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.000 sec)


MariaDB [(none)]> update test.lock_test_no_pk set value='test2' where num=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [(none)]>
MariaDB [(none)]> update test.lock_test_no_pk set value='test2' where num=25;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [(none)]> insert into test.lock_test_no_pk values (30,'a030');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [(none)]> insert into test.lock_test_no_pk values (40,'a040');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

=> PK 가 없으면 A session 에서 수행한 10~20 까지의 레코드에 대해서만 락이 걸리는 게 아니라 전체 record 들이 락 걸려버림

 

 

-4. TEST - PK 있을 때

 

 

MariaDB [(none)]> select * from mysql.innodb_index_stats where table_name='lock_test_pk';
+---------------+--------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | lock_test_pk | PRIMARY    | 2019-06-02 12:17:13 | n_diff_pfx01 |         29 |           1 | num                               |
| test          | lock_test_pk | PRIMARY    | 2019-06-02 12:17:13 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | lock_test_pk | PRIMARY    | 2019-06-02 12:17:13 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+--------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.001 sec)

 

 

 

--- A session ---

 

MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.002 sec)


MariaDB [(none)]> update test.lock_test_pk set value='test' where num between 10 and 20;
Query OK, 11 rows affected (0.001 sec)
Rows matched: 11  Changed: 11  Warnings: 0

 

 

--- B session ---

 

MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.000 sec)


MariaDB [(none)]> update test.lock_test_pk set value='test2' where num=3;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0
=> A session 에서 변경한 10~20 레코드들만 락이 걸리기 때문에 PK 없을 때와는 달리 변경 가능

MariaDB [(none)]> update test.lock_test_pk set value='test2' where num=25;
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [(none)]> update test.lock_test_pk set value='test2' where num=21;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
=> 10~20 직후 레코드인 21에는 next key lock 

MariaDB [(none)]> update test.lock_test_pk set value='test2' where num=15;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
=> 15에는 record lock

MariaDB [(none)]> insert into test.lock_test_pk values (30,'a030');
Query OK, 1 row affected (0.000 sec)