Last Updated on 2월 15, 2021 by 태랑(정현호)
Shared lock by CTAS/Ins Select
CTAS/Insert Select 사용시 공유락(Shared Lock) 문제 와 발생 원인 이유
MySQL 의 트랜잭션 모델은 REPEATABLE READ Isolation Level 를 사용하는 RDB 이며, Repeatable Read는 Read Uncommitted, Read Committed 레벨과는 달리 Phantom Row가 발생하지 않는다는 장점이 있습니다.
또한 DML시 Gap Lock와 Next-Key Lock을 이용하여 레코드 사이의 Gap 레코드 나 Next Key 에 대한 잠금을 사용하는 Transaction Locking을 사용 합니다
일반적인 조회에서는 REPEATABLE READ Isolation Level 으로 조회 하지만 Row에 Lock을 사용하지 않고 조회를 합니다 하지만 이렇게 읽어 들인 결과에(Result Set) 으로 테이블이나 데이터 적재(Insert Select) Operation 시 Shared Lock 을 사용하게 됩니다
CTAS/Insert Select 시 공유락(Shared Lock) 을 사용함에 따라 Select 는 허용 되나 변경은 허용되지 않게 됩니다 그래서 원본(Source Table)테이블이 읽고 있는 Row 에 대해서 U/D 시 Lock Wait 를 대기를 하게 됩니다 보통은 이렇게 설명이 되어있습니다.
그런데 Oracle 이나 다른 DBMS 를 경험한 사용자 입장에서 보면 이 부분이 가장 이해가 않되는 부분 입니다 보통 왜 인지 이유에 대한 설명이 없고 Oracle 같은 경우 CTAS/Insert Select 시 Source Table에 대한 DML Locking 이 되지 않기 때문입니다.
얼마전에 커뮤니티에서 왜 Select 시에는 Lock이 없이 수행되지만 CTAS/Insert Select 시에는 공유락이 발생 되는지에 대한 질문에 저도 다시 고민하게 되었던 부분 이었습니다
테스트 대상 버전 : MySQL 5.7.31
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에서 구현되는 방식이고 Mysql의 InnoDB 에서도 Multi-Version 데이터가 구현되어 있기 때문에 select 에 대한 일관된 읽기는 잠금이 필요하지 않다고 알고 있지만 실제로 CTAS/Insert Select 를 수행시 tab_source 테이블에 공유락을 사용하게 됩니다.
Why use shared lock?
그 이유로는 복구 및 복제의 데이터 정합성을 보장하기 위한 것입니다.
Lock을 사용하지 않고 위의 명령문(CTAS/Insert Select) 을 실행하는 동안 다른 트랜잭션이 tab_source 을 업데이트하면 데이터 복구 결과나 Replication 결과가 잘못 될 수 있습니다.
내용을 확인 하기 위해서 다시 테스트 해보겠습니다.
다시 테스트 할때 차이점은 "innodb_locks_unsafe_for_binlog" 파라미터 값을 변경 하여 MySQL을 재시작 후 테스트를 진행 해야 합니다.
[참고] innodb_locks_unsafe_for_binlog
InnoDB의 gap lock 과 index-row locking 두 가지를 포함하는 next-key locking 관련된 파라미터로 default 는 0(off) 이고 default 로 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 을 수행하지 않게 됩니다.
다른 부분으로 binlog_format 방식을 row 방식으로 설정 후 innodb_locks_unsafe_for_binlog 을 1로 설정 하거나 transaction_isolation 을 READ-COMMITTED 를 조정하는 등을 고민 해볼 수는 있습니다.
이와 같이 MySQL은 Replication 사용이 보편적이고 Next-Key Lock 이라는 기능에 의해 다른(가령 Oracle) DB와 다르게 처리 되기 주의나 신경써야 할 부분이 존재 하게 됩니다
ref link
MySQL Locks Use Details
dev.mysql.com-innodb_locks_unsafe_for_binlog
dev.mysql.com-set-transaction
연관 된 다른글
Senior DBA(Mysql, Oracle) - 현재 위메프에서 많은 새로움을 경험중입니다
At WeMakePrice / Previous - Oracle Korea ACS Support / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io