[MySQL] insert into .. select from table 형태의 쿼리 사용시 주의 사항.

■ 쿼리 형태

insert into table
select *
from table1, table2, table3
where ...

create table as
select *
from table1, table2, table3
where ...

 

위 형태의 쿼리를 사용할때는 주의 하여야 합니다. 보통 위의 쿼리는 똑같은 형태의 테이블을 생성하거나 똑같은 데이터를 특정 테이블에 밀어넣을때 많이 사용합니다.

하지만 위의 쿼리를 실행하게 되면 select를 하게되는 table1,table2,table3에는 read lock이 걸리게 됩니다.

이는 isolation level이 REPATABLE_READ일때 발생하게 됩니다.

이 REPATABLE_READ의 특징은 현재 select의 데이터 정합성과 버전을 보장하기 위해 이 결과값을 snapshot으로 특수한 곳에 저장해 두는데

이 특성때문에 lock이 발생합니다. 또한 이 원인은 MySQL의 GapLock과도 연관이 있습니다.

그래서 이 쿼리가 끝나야지만 다른 세션에서 들어오는 쿼리가 위의 테이블들을 이용할 수 있기 때문에 결과적으로 READ_COMMITED 모드로 작동되게 됩니다.

또한 실제로 잠금 모드에서 읽기를 수행하므로 부분적으로 버전 관리를 무시하고 커밋된 최신행을 검색합니다. 따라서 REPEATABLE-READ 모드에서 작업하는 경우에도 이 작업은

위에서 얘기했듯이 READ-COMMITTED 모드에서 수행되므로 순수한 SELECT가 제공하는 것과 다른 결과를 얻을 수 있습니다. 즉 팬텀 읽기 문제가 발생할 수 있습니다.

 

위의 문제를 해결하기 위해서는 2가지의 방법이 제공됩니다.

 

1. innodb_locks_unsafe_for_binlog 시스템 변수 값을 ON으로 설정.

2. Isolation level을 READ-COMMITTED으로 변경.

 

위의 innodb_locks_unsafe_for_binlog의 기본값은 off 입니다.

이 파라미터를 on으로 설정하면 READ COMMITED 동작으로 바뀌게 됩니다.

대신 팬텀 읽기 문제(데이터를 읽을때 이전과 이후가 다름.)가 발생할 수도 있습니다. 변경전 어플리케이션 영향도를 반드시 체크해야 합니다.

Isolation Level에 대한 설명은 다음 포스트를 참고합니다.

https://myinfrabox.tistory.com/72

 

 

■ 테스트

▶︎ 샘플 테이블 DDL

CREATE TABLE `sbtest3` (
  `id` int(11) NOT NULL DEFAULT '0',
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

▶︎ REPEATABLE READ상에서 테스트

session 1

mysql> insert into sbtest3
    -> select *
    -> from sbtest1
    -> where 1=1
    -> and pad='27572368917-79289374744-53216183389-55723443119-68547965268';

session 2

mysql> update sbtest1 set k=50369 where id=1000;

session 3

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 11
   User: root
   Host: localhost
     db: sbtest
Command: Query
   Time: 16
  State: Sending data
   Info: insert into sbtest3
select *
from sbtest1
where 1=1
and pad='27572368917-79289374744-53216183389-557
*************************** 2. row ***************************
     Id: 17
   User: root
   Host: localhost
     db: sbtest
Command: Query
   Time: 7
  State: updating
   Info: update sbtest1 set k=50369 where id=1000
*************************** 3. row ***************************
     Id: 18
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
3 rows in set (0.01 se

lock 정보 확인.

mysql> SELECT * from innodb_lock_waits;
+---------------------+----------+---------------+--------------------+--------------+-------------+----------------+---------------------+-----------------+-------------------------+---------------------------+-------------+------------------------------------------+-----------------+-------------------+-----------------+--------------+-------------------------------------------------------------------+--------------------------+--------------------+----------------------+------------------+--------------------------+----------------------------+-------------------------+------------------------------+
| wait_started        | wait_age | wait_age_secs | locked_table       | locked_index | locked_type | waiting_trx_id | waiting_trx_started | waiting_trx_age | waiting_trx_rows_locked | waiting_trx_rows_modified | waiting_pid | waiting_query                            | waiting_lock_id | waiting_lock_mode | blocking_trx_id | blocking_pid | blocking_query                                                    | blocking_lock_id         | blocking_lock_mode | blocking_trx_started | blocking_trx_age | blocking_trx_rows_locked | blocking_trx_rows_modified | sql_kill_blocking_query | sql_kill_blocking_connection |
+---------------------+----------+---------------+--------------------+--------------+-------------+----------------+---------------------+-----------------+-------------------------+---------------------------+-------------+------------------------------------------+-----------------+-------------------+-----------------+--------------+-------------------------------------------------------------------+--------------------------+--------------------+----------------------+------------------+--------------------------+----------------------------+-------------------------+------------------------------+
| 2020-07-30 16:35:45 | 00:00:19 |            19 | `sbtest`.`sbtest1` | PRIMARY      | RECORD      | 54166          | 2020-07-30 16:35:45 | 00:00:19        |                       1 |                         0 |          17 | update sbtest1 set k=50369 where id=1000 | 54166:23:18:16  | X                 | 421106243956336 |           11 | insert into sbtest3 select * f ... 83389-55723443119-68547965268' | 421106243956336:23:18:16 | S                  | 2020-07-30 16:35:42  | 00:00:22         |                 11084571 |                          0 | KILL QUERY 11           | KILL 11                      |
+---------------------+----------+---------------+--------------------+--------------+-------------+----------------+---------------------+-----------------+-------------------------+---------------------------+-------------+------------------------------------------+-----------------+-------------------+-----------------+--------------+-------------------------------------------------------------------+--------------------------+--------------------+----------------------+------------------+--------------------------+----------------------------+-------------------------+------------------------------+
1 row in set, 3 warnings (0.08 sec)

위에서 보는바와 같이 session2에서는 쿼리가 수행되지 않고 멈추게 됩니다.

만약 다른 쿼리들이 수행된다면 결국에는 deadlock 같은 상황이 오게 될수도 있습니다.

혹은 lock wait timeout으로 인한 쿼리 종료가 발생하기도 합니다.

 

▶︎ READ-COMMITTED상에서 테스트

isolation level을 READ-COMMITTED으로 바꾼 후 실행해 봅니다.

 

해당 세션만 변경.

session 1

mysql> set tx_isolation = 'READ-COMMITTED';
mysql> insert into sbtest3
    -> select *
    -> from sbtest1
    -> where 1=1
    -> and pad='27572368917-79289374744-53216183389-55723443119-68547965268';

 

session 2

mysql> set tx_isolation = 'READ-COMMITTED';
mysql> update sbtest1 set k=50369 where id=1000;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

바로 수행됩니다.

참고로 테스트시 session 1,2 모두 tx_isolation level을 READ-COMMITTED으로 바꾸어야 합니다. session1만 바꾸면 session2에서는 되지 않습니다. 반대로도 마찬가지입니다.

 

▶︎ 문제가 되는 또다른 샘플

<샘플 1>
insert into table_2
select *
from table1

insert into table_3
select *
from table2

<샘플 2>
create table_2 as
select *
from table_1

위의 방법도 마찬가지로 lock에 걸립니다.

 

■ 변경 방법.

+ 해당 세션만 변경.

위에 언급되었던 방식으로 합니다. 접속된 해당 세션에서만 변경됩니다. 물론 세션 종료시 다시 기존 isolation level로 변경됩니다.

mysql> set tx_isolation = 'READ-COMMITTED';

 

+ 영구 변경 방법.

기본 설정인 Isolation Level인 REPATABLE READ를 READ COMMITTED으로 영구적으로 바꾸기 위해선 다름과 같이 설정해 줍니다.

[mysqld] 섹션에 입력해줍니다.

$ vi /etc/my.cnf
[mysqld]
transaction-isolation = READ-COMMITTED