Last Updated on 2월 20, 2024 by Jade(정현호)
Contents
CTAS/Insert Select 사용시 공유 락(Shared Lock) 문제 와 발생 원인 이유
MySQL 의 기본 트랜잭션 모델은 REPEATABLE READ Isolation Level 를 사용하는 RDB 이며, MySQL의 Repeatable Read는 Read Uncommitted, Read Committed 레벨과는 달리 PHANTOM READ(PHANTOM ROW)가 발생하지 않는다는 장점이 있습니다.
또한 DML시 Gap Lock와 Next-Key Lock을 이용하여 레코드 사이의 Gap 레코드 나 Next Key 에 대한 잠금을 사용하는 Transaction Locking을 사용합니다
일반적인 조회에서는(Isolation Level 이 REPEATABLE READ 일 경우) Row에 Lock을 사용하지 않고 조회를 합니다
하지만 이렇게 읽어 들인 결과(Result Set) 을 테이블에 적재(Insert Select) 작업 시 Shared Lock 을 사용하게 됩니다
CTAS 또는 Insert Select 수행 시 공유 락(Shared Lock) 을 사용함에 따라 Select 는 허용되나 변경은 허용되지 않게 됩니다. 그래서 원본(Source Table)테이블이 읽고 있는 Row 에 대해서 변경 발생시 Lock Wait 으로 대기를 하게 됩니다 보통은 이렇게 설명이 되어있습니다.
그런데 Oracle이나 DBMS 를 경험한 사용자 입장에서 보면 이 부분이 가장 이해가 안 되는 부분입니다. 보통 왜 인지 이유에 대한 설명이 없고 Oracle 같은 경우 CTAS/Insert Select 시 Source Table에 대한 DML Locking 이 되지 않기 때문입니다.
얼마전에 커뮤니티에서 왜 Select 시에는 Lock이 없이 수행되지만 CTAS 또는 Insert Select 시에는 공유 락이 발생되는지에 대한 질문에 저도 다시 고민하게 되었던 부분이었습니다
테스트 대상 버전 : MySQL 5.7
CTAS/Ins-Select 의 공유 락 테스트
1. 테스트 데이터베이스 생성
mysql> create database test character set utf8mb4;
2. 테스트 테이블 생성 및 데이터 생성
mysql> create table `test`.`tab_source`
(`id` int,`name` varchar(10),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> create table `test`.`tab_target`
(`id` int,`name` varchar(10),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> insert into test.tab_source values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g');
mysql> commit;
3. 데이터 조회
mysql> use test;
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+------+
7 rows in set (0.00 sec)
4. Shared Lock 테스트
먼저 CTAS/Insert select 로 Lock을 사용하고 대기하는 부분에 대해서 확인해보겠습니다.
autocommit 은 disable 하고 진행합니다.
=Session 1
mysql> use test;
mysql> set autocommit = 0;
mysql> select * from tab_target;
Empty set (0.00 sec)
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+------+
7 rows in set (0.00 sec)
=Session 2
mysql> use test;
mysql> set autocommit = 0;
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+-----+
7 rows in set (0.00 sec)
=Session 1
mysql> insert into tab_target select * from tab_source;
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
--> tab_source 테이블을 통해 tab_target 테이블에 적재
=Session 2
mysql> update tab_source set name = 'a1' where id=1;
! Lock Waiting
<<---- 세션2는 세션1에 의해서 대기하게 됨
5. Lock 상태 조회
mysql> select *
from information_schema.INNODB_TRX\G
***************** 1. row *************
trx_id: 11305
trx_state: LOCK WAIT
trx_started: 2020-11-26 07:26:42
trx_requested_lock_id: 11305:44:3:9
trx_wait_started: 2020-11-26 07:29:01
trx_weight: 2
trx_mysql_thread_id: 8
trx_query: update tab_source set name = 'a1' where id=1
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
**************** 2. row *************
trx_id: 11304
trx_state: RUNNING
trx_started: 2020-11-26 07:26:31
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 10
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 3
trx_lock_memory_bytes: 360
trx_rows_locked: 8
trx_rows_modified: 7
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql> select *
FROM information_schema.INNODB_LOCKS\G
****************** 1. row **************
lock_id: 11305:44:3:9
lock_trx_id: 11305
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tab_source`
lock_index: PRIMARY
lock_space: 44
lock_page: 3
lock_rec: 9
lock_data: 1
************** 2. row *************
lock_id: 11304:44:3:9
lock_trx_id: 11304
lock_mode: S
lock_type: RECORD
lock_table: `test`.`tab_source`
lock_index: PRIMARY
lock_space: 44
lock_page: 3
lock_rec: 9
lock_data: 1
2 rows in set (0.00 sec)
=Session 1
mysql> commit;
=Session 2
Return Result
Query OK, 1 row affected (40.22 sec) <--- wait time
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
mysql> truncate table tab_target;
--> 다음 테스트를 위해 테이블 truncate 합니다.
이와 같이 CTAS/Insert Select Operation 은 공유 락을 사용하여 진행하기 때문에 운영 중에 매우 큰 테이블을 복제 형태로 CTAS/Insert Select 하게 되면 완료될 때까지 원본테이블에 대해서 DML 수행 시 Lock Wait 하게 됨으로 매우 조심해야 합니다
tab_source 테이블에 대한 잠금 없이 일관된 읽기를 위해 MVCC 기술을 사용하여 구현을 하게 되고 위의 예제에서도 단순하게 tab_source 테이블을 읽는 것은 일반적인 Select 와 동일하게 수행됩니다
여기 까지는 Oracle Database 에서 구현되는 방식이고 MySQL의 InnoDB 에서도 Multi-Version 데이터가 구현되어 있기 때문에 select 에 대한 일관된 읽기는 잠금이 필요하지 않다고 알고 있지만 실제로 CTAS/Insert Select 를 수행 시 tab_source 테이블에 공유 락을 사용하게 됩니다.
그 이유로는 복구 및 복제의 데이터 정합성을 보장하기 위한 것입니다.
Lock을 사용하지 않고 위의 명령문(CTAS/Insert Select) 을 실행하는 동안 다른 트랜잭션이 tab_source 을 업데이트하면 데이터 복구 결과나 Replication 결과가 잘못될 수 있습니다.
내용을 확인하기 위해서 다시 테스트해보겠습니다.
[참고] 테스트 환경은 MySQL 5.7.6 이하 버전으로 즉, binlog_format의 기본값이 statement 이거나, 그 이후 버전에서 binlog_format을 statement 또는 mixed 로 사용 중 일 경우의 테스트 예시입니다.
다시 테스트할 때 차이점은 "innodb_locks_unsafe_for_binlog" 파라미터 값을 변경하여 MySQL을 재시작 후 테스트를 진행해야 합니다.
[참고] innodb_locks_unsafe_for_binlog
InnoDB의 gap lock 과 index-row locking 두 가지를 포함하는 next-key locking 관련된 파라미터로 default 는 0(off) 이고 default 로 0(off)는 next-key lock 을 사용하겠다는 의미 입니다.
1 or on 으로 설정시 next-key lock 을 비활성화 하겠다는 의미입니다.
MySQL 8 버전부터는 해당 파라미터는 제거되었으며, 그 대신 transaction_isolation 를 READ-COMMITTED 로 대체하여 사용할 수 있습니다.
1. 파라미터 변경
[root]# vi /etc/my.cnf
[mysqld]
innodb_locks_unsafe_for_binlog=on
또한 테스트 과정에서 binlog 도 활성화가 필요 함으로 활성화가 되어 있지 않으면 같이 my.cnf 에 설정합니다.
log-bin=binlog
설정 후 MySQL을 재시작 합니다.
2. 테스트 진행
=Session 1
mysql> show variables like '%innodb_locks_unsafe_for_binlog%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON |
+--------------------------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> select * from tab_target;
Empty set (0.00 sec)
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+------+
7 rows in set (0.00 sec)
=Session 2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> select * from tab_target;
Empty set (0.00 sec)
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+-----+
7 rows in set (0.00 sec)
=Session 1
mysql> insert into tab_target select * from tab_source;
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
<-- Insert Select 후 아직 commit 하지 않았습니다.
=Session 2
mysql> update tab_source set name = 'a1';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
<-- tab_source(원본) 테이블을 변경합니다.
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | a1 |
| 3 | a1 |
| 4 | a1 |
| 5 | a1 |
| 6 | a1 |
| 7 | a1 |
+----+------+
7 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-> 세션2에서 Update 문에 대해서 commit 을 먼저 합니다
=Session 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-> 세션1에서 insert select 작업에 대한 commit 을 수행합니다.
mysql> select * from tab_source;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | a1 |
| 3 | a1 |
| 4 | a1 |
| 5 | a1 |
| 6 | a1 |
| 7 | a1 |
+----+------+
7 rows in set (0.00 sec)
mysql> select * from tab_target;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+------+
7 rows in set (0.00 sec)
tab_source 테이블은 변경되었지만 tab_target 테이블은 기본값으로 입력되었습니다.
위에서 볼 수 있듯이 innodb_locks_unsafe_for_binlog=on 했을 경우 tab_source 테이블에 대해서 next-key lock 을 사용하지 않는 것을 확인할 수 있습니다
3. 상세 내역 확인
mysqlbinlog 명령어를 통해서 binlog 내용을 확인해보도록 하겠습니다.
[root]# cd /usr/local/mysql/data
[root]# mysqlbinlog binlog.000003
BEGIN
<skip..>
use `test`/*!*/;
SET TIMESTAMP=1606367169/*!*/;
update tab_source set name = 'a1' <****
/*!*/;
# at 306
#201126 14:07:20 server id 1 end_log_pos 337 CRC32 0x092200b3 Xid = 14
COMMIT/*!*/;
# at 337
#201126 14:03:08 server id 1 end_log_pos 416 CRC32 0x066495f7 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606366988/*!*/;
BEGIN
/*!*/;
# at 416
#201126 14:03:08 server id 1 end_log_pos 537 CRC32 0xa9c4c115 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606366988/*!*/;
insert into tab_target select * from tab_source <****
/*!*/;
# at 537
#201126 14:08:08 server id 1 end_log_pos 568 CRC32 0xb291e86c Xid = 8
COMMIT/*!*/;
<skip..>
binlog에 보면 tab_source 테이블에 대한 update 구문의 위치가 insert..select 구문 보다 이전임을 확인할 수 있습니다.
이 binlog 를 사용하여 데이터를 복원이나 복제하게 되면 실제 원본과는 일치하지 않게 됩니다.
즉 이 binlog 파일을 가지고 slave에 적용하게 되면 master 와 데이터 불일치가 발생하게 됩니다.
Step 1)
update tab_source set name = 'a1'
<-- 에 의해 tab_source 의 name 이 'a1' 이 되게 되고
Step 2)
insert into tab_target select * from tab_source
<-- 그 다음 수행되기 때문에 tab_target 도 name 이 'a1' 인 데이터가 입력되게 됩니다.
4. Master/Slave 데이터 확인
Replication 환경에서 master(원본) 와 Slave 를 조회하면 이미 데이터의 불일치가 발생되게 됩니다.
Master 에서 조회한 tab_target 값
mysql> select * from tab_target;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+------+
Slave 에서 조회한 tab_target 값
myslq> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: acs
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000046
Read_Master_Log_Pos: 916
Relay_Log_File: relay_log.000006
Relay_Log_Pos: 1123
Relay_Master_Log_File: binlog.000046
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
<중략....>
1 row in set (0.00 sec)
myslq> select * from tab_target;
+----+------+
| id | name |
+----+------+
| 1 | a1 |
| 2 | a1 |
| 3 | a1 |
| 4 | a1 |
| 5 | a1 |
| 6 | a1 |
| 7 | a1 |
+----+------+
위의 예제와 binlog의 내용을 보면 CTAS 와 Insert Select 작업 시 Source Table에 대한 Lock 사용에 대한 부분을 이해할 수 있게 됩니다.
Conclusion
결론적으로 MySQL InnoDB 에서는 CTAS/Insert Select 수행 시 기본 Isolation level 인 REPEATABLE-READ 설정에 따라 Next-Key lock에 의하여 잠금을 수행하여 Source Table 에 대한 변경을 방지하게 됩니다.
이러한 부분을 회피하면서 데이터를 적재하는 방법 중 하나로 원본 테이블의 데이터를 select * from tab_source into outfile 를 사용하여 파일로 기록한 후 load data infile 로 적재하는 것도 한가지 방법이 될 수 있으며 이런 식으로 작업하게 되면 일반 조회 이기 때문에 원본 테이블에 Lock 을 수행하지 않게 됩니다.
위의 테스트에서는 Master 서버(Source 서버)에서 binlog_format이 statement 이거나 mixed 일 경우 Slave(Replica)에서 Master 서버와 데이터가 달라지는 불일치 현상이 발생되는 것입니다.
Next-Key Lock에 대한 부분을 회피하면서 데이터를 입력하고 Slave(Replica)에 데이터 정합성을 보장할 수 있는 방법으로 binlog_format을 ROW 로 설정하면서 innodb_locks_unsafe_for_binlog 을 1로 설정하거나 transaction_isolation 을 READ-COMMITTED 로 사용하는 것입니다.
이와 같이 MySQL의 기본 Isolation Level 이 REPEATABLE-READ 인 점과 Replication 구성이 보편적인 사용 환경임으로 Next-Key Lock 이라는 기능에 의해 다른(가령 Oracle) DB와 다르게 처리되기 때문에 주의나 신경 써야 할 부분이 존재하게 됩니다
Reference
Reference link
• MySQL Locks Use Details
• dev.mysql.com-innodb_locks_unsafe_for_binlog
• dev.mysql.com-set-transaction
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
next lock으로 phantom read 현상을 막을 수 있지만...다수 트랜잭션이 발생할 수 있는 거래내역 성격의 Table에서 치명적일 수 있겠군요