저번 글 1/2에서는 NoOffset 전략과 COUNT(*) vs COUNT(column)의 성능 차이, 그리고 그 이유에 대해 살펴보았다. 이번 글에서는 그 연장선으로 몇 가지 유용하게 적용할 수 있는 MySQL 전략에 대해 정리해 보았다.
이 글은 Real MySQL 책을 기반으로 작성되었으며, 보다 깊이 있는 학습을 원한다면 해당 서적이나 MySQL 공식 문서를 병행해서 보는 것을 추천한다.
SELECT … FOR UPDATE
REPEATABLE-READ 격리 수준에서는 RDBMS는 변경 전의 레코드를 언두 공간에 백업해 둔다. 동일한 레코드에 대한 여러 버전이 존재하는 이를 MVCC라고 한다. 각각의 트랜잭션은 순차 증가하는 고유한 트랜잭션 번호가 존재한다. 트랜잭션 번호를 통해서 쿼리에 레코드 값이 변경된다. 트랜잭션 번호가 최신화되지 않았다면 Undo를 통해서 변경 이력을 참조한다.
이 격리 수준과 무관하게 최신 커밋 데이터를 조회하려하는데 이는 최신의 정보 중요한 작업을 사용할 때 SELECT … FOR UPDATE를 사용한다. SELECT … FOR UPDATE을 사용하기 위해서는 베타(Exclusive) 락만 걸기에 트랜잭션 내에서 사용되어야만 효과를 발휘할 수 있다. autocommit 모드에서는 잠금 효과가 거의 없다.
순수한 SELECT 문장은 Non-locking consistent read방식으로 실행되면 항상 SELECT 쿼리의 실행시점 및 트랜잭션 시작 시점 상태의 데이터를 읽는다. 반면에 SELECT … FOR UPDATE는 격리 수준에 상관없이 항상 SELECT가 실행회는 시점의 최종 커밋된 데이터를 반환한다.
테이블 구성
CREATE TABLE bank_account (
account_id INT PRIMARY KEY AUTO_INCREMENT,
account_holder VARCHAR(100),
balance INT NOT NULL,
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
bank_account 테이블은 account_id, account_holder, balance, last_updated로 구성되어 있다.
테이블 컬럼 더미 데이터 생성
INSERT INTO bank_account (account_holder, balance) VALUES
('Alice', 100000),
('Bob', 200000);
초반에 레코드에서 Alice은 100,000의 금액과 Bob은 200,000을 테이블에 저장한다.
세션 A 쿼리 실행
SET autocommit = 0;
BEGIN;
-- 잔액 확인 + 락 획득
SELECT balance FROM bank_account WHERE account_holder = 'Alice' FOR UPDATE;
-- 내부 로직 처리 중...
-- 아직 커밋하지 않음
SET autocommit = 0;이라는 뜻은 autocommit을 사용하지 않는다는 뜻이다. autocommit를 활성화하면 쿼리를 수행할 때 Commit 명령어 없이도 모든 명령을 Commit 하여 반영하는 것을 의미한다. 위 쿼리를 보면 BEGIN으로 쿼리를 시작하고 SELECT문으로 쿼리를 확인한다. 하지만 COMMIT을 하지 않고 락을 세션 A가 가지고 있는 상황이다.
세션 B 쿼리 실행
SET autocommit = 0;
BEGIN;
-- 락 대기 상태
UPDATE bank_account
SET balance = balance + 500000
WHERE account_holder = 'Alice';
다른 세션인 세션 B에서 autocommit을 끈다. 이 상태에서 UPDATE 쿼리에서 bank_account 테이블의 락을 요청한다. 하지만 세션 A에서 락을 가지고 있기 때문에 계속해서 대기를 하게 된다.
세션 A 쿼리 실행
UPDATE bank_account
SET balance = balance - 50000
WHERE account_holder = 'Alice';
COMMIT;
세션 A에서 UPDATE로 쿼리를 실행하고 COMMIT을 날리면 락을 bank_account 테이블에 반환한다.
세션 A의 테이블 값 확인
세션 A에서 실행한 쿼리를 보면 Alice의 balance는 100,000 가지고 있었지만 쿼리가 COMMIT 되고 50,000이 되었다.
세션 B 커밋 확인
세션 B를 보면 bank_account테이블의 락이 쿼리 실행 30초 이전에 락을 가져와서 쿼리가 실행되었다. 만약 30초가 넘어가면 쿼리는 실패하게 된다.
세션 B 테이블 값 확인
세션 B의 실행 쿼리를 보면 세션 A의 쿼리가 실행되고 난 값에 대하여 쿼리가 적용되어 550,000를 반환한다.
실제로 프로젝트를 진행하다 보면 쿼리가 한쪽이 가지게 되는 경우가 있다. 하지만 반대로 30초를 기다리는 유저는 거의 없다. 그래서 락을 다른 세션에서 가진 경우 바로 에러를 반환하는 경우가 필요하다. NOWAIT를 사용하면 바로 에러를 반환한다.
SELECT … FOR UPDATE [NOWAIT | SKIP LOCKED]
선착순으로 쿠폰을 받는 동시성이 높은 작업을 할 때 동시 처리속도를 높이기 위해서 사용한다. NOWAIT와 SKIP LOCKED이 있다.
NOWAIT는 잠금 대기가 발생하는 경우 굳이 대기하지 않고 빠르게 에러를 발생한다. 이때 트랜잭션 내에서 NOWAIT 쿼리를 실행하여 에러가 발생해도, 열어둔 트랜잭션은 그대로 유지된다.
SKIP LOCKED는 잠금 대상 레코드 중 다른 세션에 의해 이미 잠금 되어 있는 레코드는 스킵하고 걸려있지 않은 레코드를 잠그고 반환한다. 어떤 레코드가 반환될지 예측하기 어렵다. 만약 레코드들이 모두 잠금이 걸린 경우 빈 결과를 반환한다.
테이블 구성
CREATE TABLE coupon (
id INT PRIMARY KEY AUTO_INCREMENT,
coupon_code VARCHAR(100) NOT NULL,
is_used BOOLEAN DEFAULT FALSE,
);coupon의 테이블 구성을 보면 id, 쿠폰 번호, 쿠폰 사용 여부 컬럼으로 구성되어 있다.
더미 데이터 생성
INSERT INTO coupon (coupon_code) VALUES
('CPN-001'), ('CPN-002'), ('CPN-003'), ('CPN-004'), ('CPN-005');
레코드에는 쿠폰의 번호와 여부를 저장한다.
시나리오 1: SELECT ... FOR UPDATE NOWAIT
세션 A 쿼리 실행
BEGIN;
-- 쿠폰 락을 걸고 처리 시작
SELECT * FROM coupon
WHERE is_used = FALSE
ORDER BY id
LIMIT 1
FOR UPDATE NOWAIT;
NOWAIT를 통해서 쿼리를 실행한다. 실제로 테이블의 락을 가지고 있는 상황이다.
세션 B 쿼리 실행
BEGIN;
-- 같은 쿼리 실행 시, 에러 발생 (ER_LOCK_NOWAIT)
SELECT * FROM coupon
WHERE is_used = FALSE
ORDER BY id
LIMIT 1
FOR UPDATE NOWAIT;
다른 B세션에서 테이블의 레코드의 접근한다. 하지만 락을 다른 A세션에서 가지고 있기에 바로 에러가 나온다.
시나리오 2: SELECT ... FOR UPDATE SKIP LOCKED
세션 A 쿼리 실행
BEGIN;
-- 이미 락 걸린 쿠폰은 건너뜀
SELECT * FROM coupon
WHERE is_used = FALSE
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
이번에는 SKIP LOCKED로 레코드의 락을 가지는 형태의 쿼리를 날리고 COMMIT 하고 있지 않은 상황이다.
세션 B 쿼리 실행
BEGIN;
-- 이미 락 걸린 쿠폰은 건너뜀
SELECT * FROM coupon
WHERE is_used = FALSE
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
그때 다른 B세션에서 SKIP LOCKED로 접근하면 잠겨져 있지 않은 랜덤 한 레코드를 반환한다. 그리고 랜덤 하게 반환된 레코드의 락을 A세션처럼 가지고 있는다. 이렇게 같은 테이블에 접근은 막지 않지만 실제로 사용되고 있는 레코드의 접근을 막을 수 있다.
시나리오 3: JOIN에서 SELECT ... FOR UPDATE SKIP LOCKED
테이블 구성
CREATE TABLE event (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
end_at DATE
);
CREATE TABLE coupon (
id INT PRIMARY KEY AUTO_INCREMENT,
coupon_no VARCHAR(100),
event_id INT,
is_used CHAR(1) DEFAULT 'N',
FOREIGN KEY (event_id) REFERENCES event(id)
);
테이블 event와 coupon을 구성한다. event 테이블은 이벤트 이름과 끝나는 기간 컬럼으로 구성된다. coupon 테이블은 쿠폰 번호, 이벤트 id, 쿠폰 사용여부 컬럼으로 구성된다.
더미 데이터 생성
INSERT INTO event (name, end_at)
VALUES ('선착순 코딩 이벤트', '2025-06-30');
INSERT INTO coupon (coupon_no, event_id, is_used) VALUES
('2a09147e', 1, 'N'),
('2ae00bdc', 1, 'N'),
('2b2848f2', 1, 'N');
event 레코드는 이벤트 1개를 구성한다. 쿠폰은 event_id=1과 연관된 coupon 레코드 3개를 구성한다.
세션 A 쿼리 실행
SET autocommit = 0;
BEGIN;
SELECT c.coupon_no, e.name
FROM coupon c
INNER JOIN event e ON e.id = c.event_id
WHERE c.event_id = 1 AND c.is_used = 'N'
ORDER BY c.id
LIMIT 1
FOR UPDATE SKIP LOCKED;
e.id = event_id에 대해서 join으로 조회하는 쿼리를 날리는 상황이다. 이것은 SELECT ... FOR UPDATE SKIP LOCKED으로 실행한다.
세션 B 쿼리 실행
SET autocommit = 0;
BEGIN;
-- 위에서 조회한 id를 바탕으로 사용자에게 지급
UPDATE event
SET end_at = '2025-12-24'
WHERE id = 1; -- 조회한 쿠폰 ID
COMMIT;
이때 다른 B세션에서 event 테이블에 관련된 UPDATE 쿼리를 날리면 LOCK을 가지고 있는 A세션에 의해서 쿼리가 실행되지 않는다.
정답 시나리오
세션 A 쿼리 실행
SET autocommit = 0;
BEGIN;
SELECT c.coupon_no, e.name
FROM coupon c
INNER JOIN event e ON e.id = c.event_id
WHERE c.event_id = 1 AND c.is_used = 'N'
ORDER BY c.id
LIMIT 1
FOR UPDATE OF coupon
SKIP LOCKED;
이걸 해결하기 위해서 원래 쿼리에서 [OF 테이블_이름]을 추가로 작성한다. [OF 테이블_이름] 구문을 사용하면 작성된 테이블 이름의 데이터만 락이 걸리게 된다. A세션에서 쿼리를 실행해서 락을 걸리게 하는 상황이다.
세션 B 쿼리 실행
SET autocommit = 0;
BEGIN;
-- 위에서 조회한 id를 바탕으로 사용자에게 지급
UPDATE event
SET end_at = '2025-12-24'
WHERE id = 1; -- 조회한 쿠폰 ID
COMMIT;
B세션에서 event 테이블과 관련된 UPDATE 쿼리를 날려도 바로 실행되어 적용되는 것을 볼 수 있다. [OF 테이블]을 통해서 JOIN 상태에서도 특정 테이블만 조인을 해서 다른 레코드에서 락이 걸리지 않고 실행되게 할 수 있다.
참조
Real MySQL 8.0
'DB' 카테고리의 다른 글
| [MySQL] MySQL 최적화에 대한 공부(1/2) (2) | 2025.05.02 |
|---|---|
| [MySQL] MySQL 아키텍처 구조에 대한 공부 (0) | 2025.04.07 |
