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 생략: 다음 트랜잭션 한정

 

 

참고

• 공식문서는 아래의 링크를 참조한다.
  https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

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

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

Docker 기반 MySQL 설치하기

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

$ docker exec -it mysql_db bash

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

$ mysql -u사용자ID -p