MySQL 트랜잭션 격리수준 확인/변경

본 포스트에서는 MySQL 트랜잭션 격리수준을 확인하는 방법과 변경하는 방법에 대해 정리한다.

     

    MySQL 버전에 따른 사용법 차이

    예전에는 tx_isolation이 쓰였는데, MySQL 5.7.20에서 transaction_isolation이 alias로 추가되었고, 8.0에 이르러서는 tx_isolation이 더 이상 사용되지 않기 때문에 변수를 사용하여 트랜잭션 격리수준을 확인하거나 설정할 때, 이를 참고하여 활용하면 된다.

    • MySQL 5.7.2 이하: tx_isolation 사용가능
    • MySQL 8.0 이상: transaction_isolation 사용

     

    트랜잭션 격리수준 확인하기

    현재 트랜잭션 격리 수준이 어떻게 설정되어 있는지는 간단히 쿼리로 확인할 수 있다.

    SELECT @@GLOBAL.transaction_isolation;
    SELECT @@SESSION.transaction_isolation;

    현재 세션의 트랜잭션 격리 수준을 확인하기 위해 다음의 쿼리로 대체하여 사용할 수도 있다.

    SHOW VARIABLES LIKE '%isolation';

    만약, 트랜잭션에 read only 설정이 되어 있는지를 확인하려면 다음과 같이 확인할 수 있다.

    SELECT @@GLOBAL.transaction_read_only;
    SELECT @@SESSION.transaction_read_only;

    MySQL 트랜잭션 격리수준의 기본값은 REPEATABLE READ이다. 즉, 별도의 설정을 변경하지 않는다면 항상 REPEATABLE READ로 동작한다는 뜻이다.

    mysql> show variables like 'transaction_isolation';
    +-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+
    1 row in set (0.08 sec)
    
    mysql> select @@session.transaction_isolation;
    +---------------------------------+
    | @@session.transaction_isolation |
    +---------------------------------+
    | REPEATABLE-READ                 |
    +---------------------------------+
    1 row in set (0.01 sec)

     

    트랜잭션 격리수준 변경하기

    트랜잭션 격리수준을 필요에 따라 변경하려면 다음의 방법들을 선택하여 설정할 수 있다.

    서버 띄울 때 설정하기

    mysql.conf 또는 mysql.ini 등의 설정 파일에 다음과 같이 명시한다. 단, 이 방법은 기본 설정을 변경하는 것이므로 영향도가 클 수 있다.

    [mysqld]
    transaction-isolation = REPEATABLE-READ
    transaction-read-only = OFF

    문장으로 설정하기

    MySQL에서 트랜잭션 변경은 다음과 같이 할 수 있다.

    SET [GLOBAL | SESSION] TRANSACTION
        transaction_characteristic [, transaction_characteristic] ...
    
    transaction_characteristic: {
        ISOLATION LEVEL level
      | access_mode
    }
    
    level: {
         REPEATABLE READ
       | READ COMMITTED
       | READ UNCOMMITTED
       | SERIALIZABLE
    }
    
    access_mode: {
         READ WRITE
       | READ ONLY
    }

    위와 같이 안내하고 있는데, 하나씩 대입해 보면 어렵지 않게 사용할 수 있다. 아래의 예를 보면 어렵지 않게 문법을 유추할 수 있다.

    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    변수로 설정하기

    UPDATE @@SESSION.transaction_isolation = 'read-uncommitted';
    UPDATE @@SESSION.transaction_isolation = 'read-committed';
    UPDATE @@SESSION.transaction_isolation = 'repeatable-read';
    UPDATE @@SESSION.transaction_isolation = 'serializable';

    설정 변경 및 확인 예

    변수 방식과 문장 방식을 혼용하여 적용 및 확인한 결과이다.

    현재 세션 트랜잭션 격리수준 변경하기 - 변수 방식

    mysql> set @@session.transaction_isolation = 'READ-COMMITTED';
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | REPEATABLE-READ                |
    +--------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select @@session.transaction_isolation;
    +---------------------------------+
    | @@session.transaction_isolation |
    +---------------------------------+
    | READ-COMMITTED                  |
    +---------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show variables like 'transaction_isolation';
    +-----------------------+----------------+
    | Variable_name         | Value          |
    +-----------------------+----------------+
    | transaction_isolation | READ-COMMITTED |
    +-----------------------+----------------+
    1 row in set (0.09 sec)

    현재 세션 트랜잭션 격리수준 변경하기 - 문장 방식

    mysql> set session transaction isolation level READ UNCOMMITTED;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> select @@global.transaction_isolation, @@local.transaction_isolation;
    +--------------------------------+-------------------------------+
    | @@global.transaction_isolation | @@local.transaction_isolation |
    +--------------------------------+-------------------------------+
    | REPEATABLE-READ                | READ-UNCOMMITTED              |
    +--------------------------------+-------------------------------+
    1 row in set (0.01 sec)

     

    디폴트 설정 재확인

    위와 같이 변경 작업을 해봤지만, 커넥션을 끊고 재접속해서 확인해보면 원래의 디폴트 설정(repeatable read)이 적용됨을 확인할 수 있다.

    mysql> select @@global.transaction_isolation, @@local.transaction_isolation;
    +--------------------------------+-------------------------------+
    | @@global.transaction_isolation | @@local.transaction_isolation |
    +--------------------------------+-------------------------------+
    | REPEATABLE-READ                | REPEATABLE-READ               |
    +--------------------------------+-------------------------------+
    1 row in set (0.00 sec)

     

    scope 명시 효과와 적용 시점

    문장 방식을 쓰거나, 변수 방식을 쓰거나 동일한데 scope을 명시할 때의 시점을 알고 써야 한다.

    1. GLOBAL 명시: GLOABL
    2. SESSION 명시: 현재 세션
    3. GLOBAL 또는 SESSION 생략: 다음 트랜잭션 한정

     

    참고

     

    MySQL :: MySQL 8.0 Reference Manual :: 13.3.7 SET TRANSACTION Statement

    13.3.7 SET TRANSACTION Statement SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] ... transaction_characteristic: { ISOLATION LEVEL level | access_mode } level: { REPEATABLE READ | READ COMMITTED | READ UNCOMMIT

    dev.mysql.com

    • 본 테스트를 위해, Docker에 MySQL을 설치한 것을 활용한다.
     

    Docker 기반 MySQL 설치하기

    Docker를 사용하여 MySQL을 설치하는 방법을 정리한다. docker-compose.yml YAML 파일의 기본 골격을 다음과 같이 작성한다. 파일내 변수로 선언한 내용은 직접 yml 파일에 그대로 기록하거나, 외부 파일로

    luran.me

    Docker contianer에 접속하기 위해 다음과 같이 실행한다.

    $ docker exec -it mysql_db bash

    다음과 같이 MySQL에 접속하면 된다.

    $ mysql -u사용자ID -p

    댓글(0)

    Designed by JB FACTORY