MySQL의 Transacion isolation 간단히 살펴보기

MySQL의 Transaction Isolation에 대해

MySQL에는 네가지 transaction isolation 레벨이 있습니다.
transaction isolation 에 따라 쿼리의 결과가 달라지고 Lock의 범위가 달라지므로 간단히라도 꼭 알아둬야 하는 개념인데요
이번 글에서는 MySQL의 transaction isolation에 대해 간단히 알아보겠습니다.
repeatable-read 의 gap lock, next key lock 은 다음에………..

READ-UNCOMMITTED

특징

lock 잡지 않음
dirty read , unrepeatable read, phantom read 발생

테스트

session 1

    mysql> set session transaction_isolation='read-uncommitted';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update tb_test set b='hi' where a=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session 2

    mysql>  set session transaction_isolation='read-uncommitted';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | hi       |
    | 2 | leedubi  |
    | 3 | parkdubi |
    +---+----------+
    3 rows in set (0.00 sec)

=> 다른 세션에서 commit 을 찍지 않아도 변경된 데이터를 조회할 수 있는 dirty read 가 발생함

READ-COMMITTED

특징

dirty read 방지
unrepeatable readd, phantom read 발생

테스트

session 1

    mysql> set session transaction_isolation='read-committed';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update tb_test set b='hi' where a=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

session 2

    mysql> set session transaction_isolation='read-committed';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | kimdubi  |
    | 2 | leedubi  |
    | 3 | parkdubi |
    +---+----------+
    3 rows in set (0.00 sec)

=> 다른 세션에서 commit을 찍지 않으면 변경된 데이터가 보이지 않음, dirty read 방지

REPEATABLE-READ

특징

dirty read, unrepeatable read 방지
phantom read 발생
- phantom read 를 막기 위해 gap lock, next key lock을 사용함
insert ~ select , create table as select ~ 할 때 select 하는 테이블에 lock 걸림

테스트

session 1

    mysql> set session transaction_isolation='repeatable-read';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update tb_test set b='hi' where a=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

session 2

    mysql> set session transaction_isolation='repeatable-read';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | kimdubi  |
    | 2 | leedubi  |
    | 3 | parkdubi |
    +---+----------+
    3 rows in set (0.00 sec)
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | kimdubi  |
    | 2 | leedubi  |
    | 3 | parkdubi |
    +---+----------+
    3 rows in set (0.00 sec)
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | kimdubi  |
    | 2 | leedubi  |
    | 3 | parkdubi |
    +---+----------+
    3 rows in set (0.00 sec)

=> 다른 세션에서 commit 을 찍어도, 트랜잭션이 시작했을 때의 snapshot 을 그대로 유지하여 unrepeatable read를 방지함

phantom read 테스트

session 1

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into tb_test(b) values('choidubi');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

session 2

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | kimdubi  |
    | 2 | leedubi  |
    | 3 | parkdubi |
    +---+----------+
    3 rows in set (0.00 sec)
    
    mysql> update tb_test set b='limdubi' where a=4;
    
    
    
    Query OK, 1 row affected (3.76 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from tb_test;
    +---+----------+
    | a | b        |
    +---+----------+
    | 1 | kimdubi  |
    | 2 | leedubi  |
    | 3 | parkdubi |
    | 4 | limdubi  |
    +---+----------+
    4 rows in set (0.00 sec)

phantom read 테스트 - next key lock

session 1

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

mysql> select * from tb_test for update;
+---+----------+
| a | b        |
+---+----------+
| 1 | kimdubi  |
| 2 | leedubi  |
| 3 | parkdubi |
+---+----------+
3 rows in set (0.00 sec)

session 2

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

mysql> insert into tb_test(b) values('choidubi');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

lock 조회

mysql> SELECT straight_join         dl.THREAD_ID        , est.SQL_TEXT        , dl.OBJECT_SCHEMA        , dl.OBJECT_NAME        , dl.INDEX_NAME        , dl.LOCK_TYPE        , dl.LOCK_MODE        , dl.LOCK_STATUS        , dl.LOCK_DATA      FROM        performance_schema.data_locks dl inner join performance_schema.events_statements_current est on dl.THREAD_ID = est.THREAD_ID      ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;
+-----------+-------------------------------------------+---------------+-------------+------------+-----------+--------------------+-------------+------------------------+
| THREAD_ID | SQL_TEXT                                  | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |
+-----------+-------------------------------------------+---------------+-------------+------------+-----------+--------------------+-------------+------------------------+
|   1532790 | select * from tb_test for update          | test2         | tb_test     | PRIMARY    | RECORD    | X                  | GRANTED     | supremum pseudo-record |
|   1532790 | select * from tb_test for update          | test2         | tb_test     | PRIMARY    | RECORD    | X                  | GRANTED     | 1                      |
|   1532790 | select * from tb_test for update          | test2         | tb_test     | PRIMARY    | RECORD    | X                  | GRANTED     | 2                      |
|   1532790 | select * from tb_test for update          | test2         | tb_test     | PRIMARY    | RECORD    | X                  | GRANTED     | 3                      |
|   1532790 | select * from tb_test for update          | test2         | tb_test     | NULL       | TABLE     | IX                 | GRANTED     | NULL                   |
|   1532788 | insert into tb_test(b) values('choidubi') | test2         | tb_test     | PRIMARY    | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
|   1532788 | insert into tb_test(b) values('choidubi') | test2         | tb_test     | NULL       | TABLE     | IX                 | GRANTED     | NULL                   |
+-----------+-------------------------------------------+---------------+-------------+------------+-----------+--------------------+-------------+------------------------+
7 rows in set (0.00 sec)

=> repeatable read 일때 select ~ for update 사용 시 , gap lock, next key lock으로 인해 팬텀리드가 방지됨

serializable

특징

dirty read, unrepeatable read, phantom read 없음
트랜잭션 수행 시 select 문에 SELECT FOR SHARE LOCK을 걸어 다른 세션에서 DML 수행 불가

테스트

session 1

mysql>  set session transaction_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_test;
+---+----------+
| a | b        |
+---+----------+
| 1 | kimdubi  |
| 2 | leedubi  |
| 3 | parkdubi |
| 4 | limdubi  |
+---+----------+
4 rows in set (0.00 sec)

session 2

mysql>  set session transaction_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into tb_test(b) values('choidubi');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MySQL의 Transaction Isolation에 대해#

READ-UNCOMMITTED#

특징#

테스트#

READ-COMMITTED#

특징#

테스트#

REPEATABLE-READ#

특징#

테스트#

phantom read 테스트#

phantom read 테스트 - next key lock#

serializable#

특징#

테스트#

MySQL의 Transaction Isolation에 대해

READ-UNCOMMITTED

특징

테스트

READ-COMMITTED

특징

테스트

REPEATABLE-READ

특징

테스트

phantom read 테스트

phantom read 테스트 - next key lock

serializable

특징

테스트