2024. 12. 31. 03:37ㆍ프로그래밍/MySQL
개요
비즈니스 로직 작성 중 bulk insert 이후 모든 레코드에 대해 update를 수행해야 할 필요가 생겼다.
개별 데이터에 대해 단일 쿼리문을 실행하면 로직 작성은 편하겠으나 성능이 떨어질 거 같았다. 따라서 Bulk Insert 이후에 id값을 활용하여 update를 해야하는데 여기서 삽입된 레코드들에 대한 Auto increment의 값이 과연 순차적임을 보장하는지에 대한 궁금증에서 비롯된 실험을 공유하고자 한다.
우선 필자의 MySQL은 별도의 튜닝없이 default setting만 가지며 엔진은 InnoDB 를 사용했음을 고려하고 봐줬으면 한다.
MySQL의 Auto increment를 핸들링하는 세가지 방법
https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html
위 공식 문서에서 말하고 있듯 MySQL에는 innodb_autoinc_lock_mode 변수의 값을 설정할 수 있다. traditional(=0), consecutive(=1), interleaved(=2)와 같이 Auto increment 옵션에 대해 세가지 선택권을 제공한다. 기본값은 2인 interleaved이다.
이 세가지에 대해 간단하고 명료하게 설명하고 interleaved 옵션의 경우의 실험 결과를 마지막에 분석해보겠다.
1. traditional
이 설정은 모든 Insert 문에 대하여 table-level lock이 걸리며 auto increment의 값이 순차적으로 증가함을 보장한다고 한다. 그러나 이는 transaction 단위가 아닌 statement 단위, 즉 쿼리 단위로 보장한다는 것을 뜻한다. 가령 TR1에서 첫번째 쿼리로 bulk insert 10개를 수행해서 id = 1~10을 가졌고 TR2에서 마찬가지로 10개의 bulk insert를 수행한 이후 다시 TR1에서 insert를 수행 시 id가 11부터 시작하지 않고 21부터 시작한다는 뜻이다.
2. consecutive
이 옵션은 bulk insert 시에만 table-level lock이 걸리며 이는 INSERT INTO .. .SELECT 와 같이 소스와 타겟 테이블이 다른 경우도 해당한다고 한다.
CREATE TABLE sample1(
id int primary key auto_increment,
title varchar(255)
);
CREATE TABLE sample2(
id int primary key auto_increment,
content varchar(255)
);
INSERT INTO sample1(title) SELECT content FROM sample2;위와 같은 테이블 구조를 가진다고 했을 때 아래의 INSERT문을 실행하는 케이스를 살펴보자. 이런 경우 해당 소스 테이블(sample2)에 대한 공유 lock이 걸린 후 타겟 테이블(sample1)에 lock이 걸린다고 한다. sample1에 대량 데이터 삽입 과정에서의 데이터 일관성을 유지하기 위해서이다.
만약 타겟과 소스가 같은 테이블이라면(like BULK INSERT) 해당 테이블의 모든 행에 대한 lock이 걸린 후 Auto increment lock이 걸리게 된다. 또한 table-lock은 한번에 하나의 statement만 유지할 수 있다고 한다. 즉 특정 테이블에서 테이블 lock이 걸려있을 경우 다른 세션에서는 bulk insert를 수행하지 못한다는 뜻이 된다.
3. interleaved
위에서 언급했듯이 MySQL InnoDB의 default setting이다. 얘는 위 설정값들과 다르게 auto increment의 값이 순차적임을 보장하지 않는다. 또한 bulkinsert시에 gap이 발생할 수 있다고 한다. 단 이는 INSERT SELECT, INSERT INTO VALUES(),(),().. 와 같은 대량 삽입 작업에만 해당한다고 한다.
실험
뭐 사실 위 문서만 봐도 답은 나왔으나 직접 눈으로 봐야 직성이 풀리는 경향이 있어서 테스트 해봤다.
CREATE PROCEDURE BULK_INSERT1()
BEGIN
DECLARE i INT DEFAULT 1; -- 반복 카운터 초기값
DECLARE lo INT DEFAULT 1500000; -- 최대 값 설정
START TRANSACTION;
WHILE i <= lo DO
INSERT INTO sample1(title) VALUES
(i), (i+1), (i+2), (i+3), (i+4),
(i+5), (i+6), (i+7), (i+8), (i+9);
SET i = i + 10; -- 10씩 증가
END WHILE;
COMMIT;
END
CREATE PROCEDURE BULK_INSERT2()
BEGIN
DECLARE i INT DEFAULT 1; -- 반복 카운터 초기값
DECLARE lo INT DEFAULT 1500000; -- 최대 값 설정
START TRANSACTION;
WHILE i <= lo DO
INSERT INTO sample2(title) VALUES
(CONCAT('test', i)),
(CONCAT('test', i + 1)),
(CONCAT('test', i + 2)),
(CONCAT('test', i + 3)),
(CONCAT('test', i + 4)),
(CONCAT('test', i + 5)),
(CONCAT('test', i + 6)),
(CONCAT('test', i + 7)),
(CONCAT('test', i + 8)),
(CONCAT('test', i + 9));
SET i = i + 10; -- 10씩 증가
END WHILE;
COMMIT;
END;위와 같이 대량 삽입을 위해 두개의 프로시저를 생성했다. 하나의 INSERT statement로 BULK INSERT를 동시에 두개 실행해도 되지만 PROCEDURE로 코드를 작성하기 까다롭고 비즈니스 로직에서 작성하자니 귀찮아서 다음과 같이 진행하기로 한다.
1. sample1에 150만개의 데이터 삽입
2. sample2에 150만개의 데이터 삽입
3. sample 테이블에 INSERT ... SELECT를 이용해 동시(정확히 동시는 아님)에 sample1과 sample2의 데이터를 sample에 삽입
결과
공식 문서에서 얘기한대로 두가지 문제점이 있다.
1. bulk insert시 gap 발생
2. 값이 순차적임이 보장되지 않음
시작 id는 분명 0으로 세팅하였음에도 300만개의 데이터가 삽입됐는데 id는 7000개 가량 gap이 발생했다.
또한 sample1을 먼저 insert했음에도 sample2 insert 작업과 auto_inc값이 겹쳐서 id가 250만임에도 sample1의 데이터는 아직 145만을 가르키고 있다.
정리
그렇다면 트랜잭션을 사용한다면 auto_inc의 값이 순차적임이 보장될까?
위에서도 언급했듯 table-level lock이 걸리는 것은 0과 1의 경우 뿐이기 때문에 이 역시 보장되지 않는다.
그래서 필자는 id의 값이 순차적인것과 관계없이 Isolation Level을 활용하여 MySQL의 기본값인 Repetable Read의 일관된 읽기로 bulk insert된 데이터의 insert_id를 활용하여 순차처리하는 것으로 해결했다.
출처 : https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html
bulk insert 코드 참조 : https://gngsn.tistory.com/149