MySQL의 Transaction Isolation Level (Lock에 관하여) by 주식회사 브랜디

편집자 주
문맥에 따라 ‘Transaction’과 ‘트랜잭션’으로 영어와 한글을 혼용함.
문맥에 따라 ‘LOCK’과 ‘lock’으로 대문자와 소문자를 혼용함.

Overview

MySQL DB는 일반적인 운영환경에서 뛰어난 성능을 제공합니다. 특히 적은 양의 자료가 빈번하게 교류되는 환경에서는 더욱 빛을 발하죠. 국내에서는 주로 작은 규모의 웹사이트를 구축할 때 MySQL을 사용합니다. 그런데 문제는 사이트의 규모가 커지면서부터 생긴다는 것이죠. 조금씩 느려지는 Query가 생기면 원인도 파악하고, Query를 튜닝하고, 설계도 변경하지만 MySQL의 특징적인 문제를 곧 만나게 됩니다.

테이블을 복제(CREATE SELECT)하거나 다른 테이블로 옮기면(INSERT SELECT) 작업을 하는 동안 SELECT 절에 있는 테이블들이 Lock이 걸립니다. 게다가 다른 Session에서 해당 테이블을 수정(UPDATE / DELETE)하면 복제와 이동을 마칠 때까지 대기 상태로 있어야 한다는 것입니다. 이러한 문제는 시스템을 구축하고 자료가 일정량 쌓이기 전까지는 알 수 없습니다. 또한 Oracle과 같은 DB를 사용하던 사용자가, MySQL을 사용하면 이와 같은 문제가 있을 것이라고 생각하기도 어렵습니다.

이러한 특징을 가진 MySQL의 Transaction Isolation Level을 알아보고자 합니다. Transaction Isolation Level 은 Transaction의 경리 수준을 말합니다. 트랜잭션 처리 시 다른 트랜잭션에서 접근해 자료를 수정하거나 볼 수 있도록 하는 수준입니다.

Transaction Isolation Level의 종류와 특성

Transaction Isolation Level에는 READ UNCOMMITTED, READ COMMIITED, REPEATABLE READ, SERIALIZE 네 가지 종류가 있습니다. 1)

READ UNCOMMITTED

1) COMMIT 되지 않은 데이터에 다른 트랜잭션에서 접근할수 있다.
2) INSERT, UPDATE, DELETE 후 COMMIT 이나 ROLLBACK에 상관없이 현재의 데이터를 읽어온다.
3) ROLLBACK이 될 데이터도 읽어올 수 있으므로 주의가 필요하다.
4) LOCK이 발생하지 않는다.

READ COMMIITED

1) COMMIT 된 데이터에 다른 트랜잭션에서 접근할 수 있다.
2) 구현 방식이 차이 때문에 Query를 수행한 시점의 데이터와 정확하게 일치하지 않을 수 있다.
3) LOCK이 발생하지 않는다.
4) MySQL에서 많은 양의 데이터를 복제하거나 이동할 때 이 LEVEL을 추천한다.

REPEATABLE READ

1) Default LEVEL이다.
2) SELECT시 현재 시점의 스냅샷을 만들고 스냅샷을 조회한다.
3) 동일 트랜잭션 내에서 일관성을 보장한다.
4) record lock과 gap lock이 발생한다.
5) CREATE SELECT, INSERT SELECT시 lock이 발생한다.

SERIALIZE

1) 가장 강력한 LEVEL이다.
2) SELECT 문에 사용하는 모든 테이블에 shared lock이 발생한다.

LOCK과 테이블, 어떻게 해결할 수 있을까?

지금부터는 관련된 내용을 확인해보겠습니다. 우선 현재의 경리 수준부터 알아보겠습니다.

mysql> SHOW VARIABLES WHERE VARIABLE_NAME='tx_isolation'; +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec)

다음으로 TEST 테이블을 만듭니다. 이때 SELECT절의 테이블을 UPDATE할 경우, 대기 상태로 빠지는 것을 확인해보겠습니다. 테이블을 만들고 상태를 확인합니다.

CREATE TABLE test.TB_PROD_BAS_TEST (  PRIMARY KEY (PROD_ID) ) SELECT  T101.PROD_ID  ,T101.PROD_NM  ,T101.PROD_EN_NM  ,T101.PROD_MEMO FROM test.TB_PROD_BAS T101 ;  -- 생성시 INFORMATION_SCHEMA.PROCESSLIST 로 상태를 확인합니다.  mysql> SELECT  -> *  -> FROM INFORMATION_SCHEMA.PROCESSLIST  -> WHERE USER = 'hansj'  -> AND COMMAND <> 'Sleep'  -> \G *************************** 1. row ***************************  ID: 11004  USER: hansj  HOST: 192.168.1.150:50711  DB: test COMMAND: Query  TIME: 5  STATE: Sending data  INFO: CREATE TABLE test.TB_PROD_BAS_TEST (  PRIMARY KEY (PROD_ID) ) SELECT  T101.PROD_ID  ,T101.PROD_NM  ,T101.PROD_EN_NM  ,T101.PROD_MEMO FROM test.TB_PROD_BAS T101 1 row in set (0.00 sec)

다음으로 테이블 생성 시 UPDATE를 해 대기 상태로 빠지는지 확인해보겠습니다.

UPDATE test.TB_PROD_BAS SET PROD_MEMO = 'TEST' WHERE PROD_ID = 1 ;  mysql> SELECT  -> *  -> FROM INFORMATION_SCHEMA.PROCESSLIST  -> WHERE USER = 'hansj'  -> AND COMMAND <> 'Sleep'  -> \G *************************** 1. row ***************************  ID: 11004  USER: hansj  HOST: 192.168.1.150:50711  DB: test COMMAND: Query  TIME: 24  STATE: Sending data  INFO: CREATE TABLE test.TB_PROD_BAS_TEST (  PRIMARY KEY (PROD_ID) ) SELECT  T101.PROD_ID  ,T101.PROD_NM  ,T101.PROD_EN_NM  ,T101.PROD_MEMO FROM test.TB_PROD_BAS T101 *************************** 2. row ***************************  ID: 11006  USER: hansj  HOST: 192.168.1.150:50719  DB: test COMMAND: Query  TIME: 22                 *****이부분 중요합니다.******  STATE: updating          *****이부분 중요합니다.******  INFO: UPDATE test.TB_PROD_BAS SET PROD_MEMO = 'TEST' WHERE PROD_ID = 1 2 rows in set (0.00 sec)

위의 TIME을 보면 테이블이 생성될 때까지 대기하고, UPDATE 문의 상태가 updating 으로 표시됩니다. 하지만 이렇게 나올 경우 건수가 많으면 실제 UPDATE 중인지 대기상태인지 확인하기가 어렵습니다. LOCK이 걸린 테이블을 확인하려면 INNODB LOCK 테이블로 정확하게 알 수 있습니다. 아래 세 가지 테이블로 확인해보겠습니다. 보다 자세한 설명은 MySQL 홈페이지를 확인합니다.

information_schema.INNODB_TRX
LOCK을 걸고 있는 프로세스 정보
information_schema.INNODB_LOCK_WAITS
현재 LOCK이 걸려 대기중인 정보
information_schema.INNODB_LOCKS
LOCK을 건 정보

위의 각 항목마다 테이블 생성 및 UPDATE 시 정보가 어떻게 나타나는지 확인해보겠습니다.

1.information_schema.INNODB_TRX

mysql> SELECT  -> T101.TRX_ID  -> ,T101.TRX_STATE  -> ,T101.TRX_STARTED  -> ,T101.TRX_REQUESTED_LOCK_ID  -> ,T101.TRX_WAIT_STARTED  -> ,T101.TRX_WEIGHT  -> ,T101.TRX_MYSQL_THREAD_ID  -> ,T101.TRX_ISOLATION_LEVEL  -> ,SUBSTR(T101.TRX_QUERY,1,10)AS TRX_QUERY  -> FROM information_schema.INNODB_TRX T101  -> ;  +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------+------------+  | TRX_ID  | TRX_STATE | TRX_STARTED         | TRX_REQUESTED_LOCK_ID | TRX_WAIT_STARTED    | TRX_WEIGHT | TRX_MYSQL_THREAD_ID | TRX_ISOLATION_LEVEL | TRX_QUERY  |  +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------+------------+  | 8771591 | LOCK WAIT | 2019-05-27 16:15:53 | 8771591:70031:4:306   | 2019-05-27 16:15:53 |          2 |               11006 | REPEATABLE READ     | UPDATE tes |  | 8771586 | RUNNING   | 2019-05-27 16:15:51 | NULL                  | NULL                |    1538969 |               11004 | REPEATABLE READ     | CREATE TAB |  +---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+---------------------+------------+ 2 rows in set (0.00 sec)

TRX_ID_STATE
트랜잭션의 상태를 나타냅니다. 실행 중인지 LOCK WAIT 상태인지 알 수 있습니다.

TRX_MYSQL_THREAD_ID
PROCESSLIST 의 ID를 나타냅니다.

TRX_ISOLATION_LEVEL
ISOLATION LEVEL을 나타냅니다.

따라서 위의 내용을 보면 CREATE TABLE이 실행 중인 것과, UPDATE가 LOCK WAIT인 것, 그리고 관련된 PROCESSLIST의 ID까지도 알 수 있습니다

2.information_schema.INNODB_LOCK_WAITS

mysql> SELECT  -> *  -> FROM information_schema.INNODB_LOCK_WAITS T101  -> ;  +-------------------+---------------------+-----------------+---------------------+  | requesting_trx_id | requested_lock_id   | blocking_trx_id | blocking_lock_id    |  +-------------------+---------------------+-----------------+---------------------+  | 8771591           | 8771591:70031:4:306 | 8771586         | 8771586:70031:4:306 |  +-------------------+---------------------+-----------------+---------------------+ 1 row in set (0.01 sec)

requesting_trx_id
LOCK WAIT 인 TRX_ID

blocking_trx_id
LOCK 을 건 TRX_ID

현재 LOCK이 걸린 TRX_ID와 LOCK을 걸어둔 TRX_ID를 알 수 있습니다.

3.information_schema.INNODB_LOCKS

mysql> SELECT  -> *  -> FROM information_schema.INNODB_LOCKS  -> ;  +---------------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+  | lock_id             | lock_trx_id | lock_mode | lock_type | lock_table           | lock_index | lock_space | lock_page | lock_rec | lock_data |  +---------------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+  | 8771591:70031:4:306 | 8771591     | X         | RECORD    | `test`.`TB_PROD_BAS` | PRIMARY    |      70031 |         4 |      306 | 1         |  | 8771586:70031:4:306 | 8771586     | S         | RECORD    | `test`.`TB_PROD_BAS` | PRIMARY    |      70031 |         4 |      306 | 1         |  +---------------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+ 2 rows in set (0.01 sec)

lock_trx_id
LOCK 과 관련된 TRX_ID

lock_mode
X 쓰기, S 읽기 2)

어떤 테이블이 LOCK을 걸고 있는지 알 수 있습니다.

위의 내용들을 통해 REPEATABLE READ에서 CREATE SELECT시 SELECT 테이블에 LOCK이 걸려 UPDATE가 대기하게 되는 것을 알 수 있습니다. 이번에는 Transaction Isolation Level 을 READ COMMIITED로 변경하고 CREATE SELECT 및 UPDATE를 진행해보겠습니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  SHOW VARIABLES WHERE VARIABLE_NAME='tx_isolation';  +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | READ-COMMITTED  | +---------------+-----------------+  1 row in set (0.00 sec)  UPDATE 문은 다음과 같이 수행됩니다. mysql> UPDATE test.TB_PROD_BAS  -> SET PROD_MEMO = 'TEST'  -> WHERE PROD_ID = 1  -> ; Query OK, 0 rows affected (0.04 sec) Rows matched: 1 Changed: 0  Warnings: 0

기존에 대기했던 것과 다르게 0.04초가 걸렸습니다.

mysql> SELECT  -> *  -> FROM INFORMATION_SCHEMA.PROCESSLIST  -> WHERE USER = 'hansj'  -> AND COMMAND <> 'Sleep'  -> \G *************************** 1. row ***************************  ID: 11004  USER: hansj  HOST: 192.168.1.150:50711  DB: test COMMAND: Query  TIME: 9  STATE: Sending data  INFO: CREATE TABLE test.TB_PROD_BAS_TEST (  PRIMARY KEY (PROD_ID) ) SELECT  T101.PROD_ID  ,T101.PROD_NM  ,T101.PROD_EN_NM  ,T101.PROD_MEMO FROM test.TB_PROD_BAS T101 1 row in set (0.00 sec)  -- 프로세스 정보도 CREATE TABLE 만 진행중임을 알수 있습니다.  mysql> SELECT  -> T101.TRX_ID  -> ,T101.TRX_STATE  -> ,T101.TRX_STARTED  -> ,T101.TRX_REQUESTED_LOCK_ID  -> ,T101.TRX_WAIT_STARTED  -> ,T101.TRX_WEIGHT  -> ,T101.TRX_MYSQL_THREAD_ID  -> ,T101.TRX_ISOLATION_LEVEL  -> ,T101.TRX_QUERY  -> FROM information_schema.INNODB_TRX T101  -> ;  +---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+  | TRX_ID  | TRX_STATE | TRX_STARTED         | TRX_REQUESTED_LOCK_ID | TRX_WAIT_STARTED | TRX_WEIGHT | TRX_MYSQL_THREAD_ID | TRX_ISOLATION_LEVEL | TRX_QUERY                                                                                                                                                       |  +---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+  | 8771856 | RUNNING   | 2019-05-27 17:17:45 | NULL                  | NULL             |    4594347 |               11004 | READ COMMITTED      | CREATE TABLE test.TB_PROD_BAS_TEST  (   PRIMARY KEY (PROD_ID)  )  SELECT   T101.PROD_ID   ,T101.PROD_NM   ,T101.PROD_EN_NM   ,T101.PROD_MEMO  FROM test.TB_PROD_BAS T101 |  +---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

READ COMMITTED LEVEL로 CREATE만 수행 중인 것을 알 수 있습니다.

mysql> SELECT  -> *  -> FROM information_schema.INNODB_LOCK_WAITS T101  -> ; Empty set (0.00 sec)  mysql> SELECT  -> *  -> FROM information_schema.INNODB_LOCKS  -> ; Empty set (0.00 sec)

LOCK을 걸고 걸린 것이 없어 내용도 없습니다.

Conclusion

지금까지 Transaction Isolation Level 을 기준으로 CREATE SELECT 시 SELECT 에 사용되는 테이블도 LOCK이 걸릴 수 있는 것을 확인했고, 그에 따른 해결 방법까지 알아봤습니다.

INSERT INTO SELECT에서도 같은 현상이 나타납니다. 그렇기 때문에 운영 중인 테이블을 복제(CREATE SELECT)하거나 다른 테이블로 옮길 경우(INSERT SELECT) Transaction Isolation Level을 READ COMMITTED 변경하고 작업하기를 권장합니다.

그렇지 않으면 관련된 TABLE은 LOCK이 걸리고, 관련 Query들이 대기 상태로 빠지면서 시스템 장애가 발생할지도 모릅니다.

참고
1)MySQL :: MySQL 5.6 Reference Manual :: 14.7.2.1 Transaction Isolation Levels

2)MySQL :: MySQL 5.6 Reference Manual :: 14.7.1 InnoDB Locking

글
한석종 부장 | R&D 데이터팀
hansj@brandi.co.kr

브랜디, 오직 예쁜 옷만