주로 사용하는 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)