글을 작성하게 된 계기
회사에서 회원 등급을 조정하는 배치로직을 개발하던 중, INSERT...SELECT 구문에서 테이블 락이 발생하는지 궁금해졌고, 이 과정에서 알게 된 내용을 정리하기 위해 글을 작성하게 되었습니다.
1. 진행 배경
테스트를 진행한 이유는 데이터를 SELECT한 뒤, 같은 트랜잭션 내에서 INSERT를 수행할 때 성능 저하나 락 경합이 발생하지 않는지 확인 하기 위함입니다. INSERT ... SELECT 구문은 일반적으로 애플리케이션에서 데이터를 조회한 뒤 반복적으로 INSERT하는 방식보다 훨씬 빠르고 효율적이지만, 읽기 대상이 되는 주문 테이블은 서비스에서 자주 사용되는 테이블 이기 때문에, 이락이나 경합을 유발하지 않는지 직접 검증할 필요가 있었습니다.
2. 실습 준비
주문 내역을 기반으로 오늘 판매가 발생한 판매자를 중복 없이 저장하는 상황을 가정해보겠습니다. 거래가 기록되는 order_product 테이블의 데이터를 조회하여, 오늘 판매가 발생한 판매자를 seller_daily_record 테이블에 적재하며 INSERT ... SELECT 구문에서 락이 발생하는지를 확인합니다. 먼저 테이블을 생성합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DROP TABLE IF EXISTS order_product;
DROP TABLE IF EXISTS seller_daily_record;
CREATE TABLE order_product (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
seller_id INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
amount INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE seller_daily_record (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
seller_id INT UNSIGNED NOT NULL,
record_date DATE NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_modified_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uq_seller_record (seller_id, record_date)
) ENGINE=InnoDB;
이후 데이터를 삽입해줍니다.
1
2
INSERT INTO order_product (seller_id, created_at, amount)
VALUES (1, NOW(), 1000);
이후 두 개의 쉘을 열어 한 쪽에는 트랜잭션을 시작하고, 다른 한 쪽에서는 INSERT ... SELECT 구문을 실행하여 락이 발생하는지 확인합니다.
1
2
3
4
5
6
7
8
9
-- 왼쪽 쉘 - COMMIT 하지 말고 대기 상태 유지
USE test_db;
START TRANSACTION;
INSERT IGNORE INTO seller_daily_record (seller_id, record_date)
SELECT seller_id, CURDATE()
FROM order_product
GROUP BY seller_id;
1
2
3
4
5
6
-- 오른쪽 - 쉘 COMMIT 하지 말고 대기 상태 유지
USE test_db;
SELECT object_schema, object_name, lock_type, lock_mode, lock_status
FROM performance_schema.data_locks
WHERE object_schema = 'test_db';
3. 결과
INSERT ... SELECT 구문을 실행하면, 데이터를 읽는 테이블(order_product) 과 쓰기 대상 테이블(seller_daily_record) 에 대해 서로 다른 종류의 락이 걸립니다. 이때 order_product에는 IS(의도 공유 락) 과 S(공유 락) 이 걸려, 트랜잭션이 읽기 일관성을 보장받으면서 동시에 다른 트랜잭션의 쓰기 작업(X 락)을 막지 않습니다. 반면, 데이터를 실제로 적재하는 seller_daily_record에는 IX(의도 배타 락) 이 걸려, 해당 테이블의 특정 레코드에 X(배타 락) 을 걸게 됩니다.
1
2
3
4
5
6
7
8
9
+---------------+---------------------+-----------+-----------+-------------+
| object_schema | object_name | lock_type | lock_mode | lock_status |
+---------------+---------------------+-----------+-----------+-------------+
| test_db | seller_daily_record | TABLE | IX | GRANTED |
| test_db | order_product | TABLE | IS | GRANTED |
| test_db | order_product | RECORD | S | GRANTED |
| test_db | order_product | RECORD | S | GRANTED |
+---------------+---------------------+-----------+-----------+-------------+
4 rows in set (0.01 sec)
4. 정리
INSERT ... SELECT 구문은 애플리케이션에서 데이터를 미리 조회한 뒤 반복적으로 INSERT하는 방식보다 훨씬 효율적입니다. 이유는 MySQL이 내부적으로 읽기와 쓰기를 단일 실행 계획 안에서 처리하기 때문에, 클라이언트와 서버 간의 네트워크 왕복이 없고, 중간 결과를 메모리에서 직접 전달하기 때문입니다. 안전한 것을 확인했으니 바로 사용해보겠습니다. 🚀