트랜잭션과 멱등성

게시글 한 행과 일별 통계 한 행은 서로 다른 테이블에 있지만, 하나의 등록 요청에서 함께 바뀝니다. 게시글만 저장되고 통계의 갱신이 실패하면 목록은 멀쩡해도 운영의 지표가 실제보다 작아지고, 반대로 통계만 늘어나면 없는 글을 센 셈입니다. 트랜잭션은 여러 SQL을 묶는 문법이라기보다, 함께 참이어야 하는 상태를 한 번에 확정하는 단위입니다.

네트워크에서 응답을 잃으면 같은 요청이 다시 올 수 있으므로, 원자성에 더해 (author_id, request_key)의 UNIQUE로 같은 요청이 두 번 반영되지 않게 막습니다. 같은 요청을 여러 번 보내도 결과가 한 번 보낸 것과 같은 성질을 멱등성(idempotency)이라고 합니다. 이 글은 누적 모델 board_lab의 members와 posts에 회원별 일별 통계 daily_post_stats를 더해 실습하며, 회원 1이 있고 이 글의 요청 키는 아직 없다고 전제합니다.

posts에는 2장 1편에서 더한 request_key와 UNIQUE uq_posts_author_request가 이미 있습니다. SHOW CREATE TABLE posts;로 확인해 보면 request_key는 NOT NULL이고, ck_posts_request_key가 공백만 있는 키를 막습니다. 그래서 새로 등록하는 글은 모두 키를 가지며, 서비스는 같은 회원의 같은 요청에 같은 키를, 다른 요청에는 새 키를 넣습니다.

자동 커밋에서 생기는 부분 커밋

자동 커밋 상태에서는 문장 하나하나가 각각의 트랜잭션입니다. 게시글의 INSERT 뒤에 통계의 UPDATE에서 오류가 나도, 이미 확정된 게시글은 저절로 되돌아가지 않습니다.

USE board_lab;

CREATE TABLE IF NOT EXISTS daily_post_stats (
  member_id BIGINT UNSIGNED NOT NULL,
  stat_date DATE NOT NULL,
  published_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at DATETIME(6) NOT NULL,
  PRIMARY KEY (member_id, stat_date),
  CONSTRAINT fk_daily_post_stats_member
    FOREIGN KEY (member_id) REFERENCES members (id)
) ENGINE = InnoDB;

SET autocommit = 1;

INSERT INTO posts (
  author_id, title, content, status, view_count,
  created_at, updated_at, published_at, version, request_key
)
VALUES (
  1, '트랜잭션 경계', '게시글과 통계를 함께 저장해야 한다.',
  'PUBLISHED', 0,
  '2026-07-14 09:00:00', '2026-07-14 09:00:00',
  '2026-07-14 09:00:00', 0, 'post-20260714-bad'
);

-- 잘못된 컬럼 이름 때문에 두 번째 구문만 실패한다.
UPDATE daily_post_stats
SET published_count_typo = published_count + 1
WHERE member_id = 1 AND stat_date = '2026-07-14';

ERROR 1054 (42S22): Unknown column 'published_count_typo' in 'field list'

두 번째 문장은 실패했지만 첫 INSERT는 이미 커밋되었습니다. 다음 두 쿼리로 확인하면 게시글은 하나 있고 통계 행은 없습니다.

SELECT COUNT(*) AS bad_posts
FROM posts
WHERE author_id = 1 AND request_key = 'post-20260714-bad';

SELECT COUNT(*) AS stat_rows
FROM daily_post_stats
WHERE member_id = 1 AND stat_date = '2026-07-14';

bad_posts
1

stat_rows
0

예외를 처리하면서 ROLLBACK을 호출해도, 이미 끝난 자동 커밋의 문장에는 아무 효과가 없습니다. 업무의 오류는 SQL의 문법 오류와도 다릅니다. 모든 문장이 성공했더라도 통계가 두 번 늘거나 다른 날짜에 기록되면 불변식은 깨집니다.

부분 커밋은 네 가지로 찾습니다. 게시글의 INSERT와 통계의 UPSERT 가운데 하나만 남아도 되는지 묻고, 두 문장 사이에 일부러 실패를 넣어 커밋된 행을 확인합니다. 공개 게시글의 수와 통계의 건수를 같은 기준 시각으로 비교하고, 같은 요청 키로 다시 실행해 늘어난 행과 집계가 0인지 봅니다.

ACID와 업무의 단위

원자성(Atomicity)은 트랜잭션 안의 변경이 모두 커밋되거나 모두 롤백되는 성질입니다. 일관성(Consistency)은 제약과 업무 규칙이 유효한 상태만 만들도록 설계하는 일이고, 격리성(Isolation)은 동시에 실행되는 트랜잭션끼리의 간섭을 제어하며, 지속성(Durability)은 커밋한 결과가 장애 뒤에도 남는 성질입니다. 네 성질의 머리글자를 따서 ACID라고 부릅니다.

트랜잭션 안에는 같은 데이터베이스에서 짧게 끝나는 변경만 둡니다. 메일 발송이나 HTTP 호출, 메시지 발행을 트랜잭션 안에 넣으면 바깥의 지연과 실패가 데이터베이스의 잠금을 오래 붙잡습니다.

등록의 순서는 이렇게 정합니다. 먼저 (author_id, request_key)의 UNIQUE로 같은 회원의 같은 등록이 한 행만 되게 하고, 제목과 본문, 상태와 시각을 게시글에 저장합니다. 이어서 같은 회원과 날짜의 통계에 공개 건수와 조회수를 더하고, 두 변경이 모두 성공한 뒤에만 COMMIT합니다.

게시글과 일별 통계를 함께 저장한다

통계 행이 없으면 만들고 이미 있으면 늘리는 UPSERT를 씁니다. 아래는 모든 문장이 성공할 때의 경로입니다. INSERT마다 오류가 없는지 확인하고, 오류가 나면 나머지 문장과 COMMIT을 실행하지 말고 곧바로 ROLLBACK합니다. 일반적인 제약 오류는 실패한 문장만 되돌리므로, 트랜잭션을 시작했다고 앞에서 성공한 문장까지 저절로 롤백되지는 않습니다.

START TRANSACTION;

INSERT INTO posts (
  author_id, title, content, status, view_count,
  created_at, updated_at, published_at, version, request_key
)
VALUES (
  1, '멱등한 게시글 등록', '같은 요청은 한 번만 반영한다.',
  'PUBLISHED', 0,
  '2026-07-14 10:00:00', '2026-07-14 10:00:00',
  '2026-07-14 10:00:00', 0, 'post-20260714-001'
);

INSERT INTO daily_post_stats (
  member_id, stat_date, published_count, total_views, updated_at
)
VALUES (1, '2026-07-14', 1, 0, CURRENT_TIMESTAMP(6)) AS new_stat
ON DUPLICATE KEY UPDATE
  published_count = published_count + 1,
  total_views = total_views + new_stat.total_views,
  updated_at = new_stat.updated_at;

COMMIT;

SELECT member_id, stat_date, published_count, total_views
FROM daily_post_stats
WHERE member_id = 1;

member_id | stat_date  | published_count | total_views
1         | 2026-07-14 | 1               | 0

두 문장이 모두 성공해 커밋되었으므로, 새 요청 키의 게시글은 한 행이고 그날의 통계 행이 새로 만들어져 공개 건수가 1이 되었습니다. VALUES (...) AS new_stat은 새로 넣으려던 행에 이름을 붙이는 MySQL 8.0.19 이후의 문법입니다. 예전처럼 VALUES(total_views) 함수로 새 값을 참조하는 방식도 아직 동작하지만, MySQL 8.0.20부터 쓰지 않을 기능으로 지정되어 경고가 납니다.

응답을 잃은 클라이언트가 같은 요청을 다시 보내, 이 트랜잭션을 처음부터 다시 실행했다고 해 봅시다. 게시글의 INSERT가 UNIQUE에 걸립니다.

ERROR 1062 (23000): Duplicate entry '1-post-20260714-001' for key 'posts.uq_posts_author_request'

게시글의 INSERT가 중복 오류를 냈는데 통계의 UPSERT를 계속 실행하면 집계만 다시 늘어납니다. 그래서 호출하는 쪽은 먼저 ROLLBACK한 뒤, author_id와 request_key로 기존 글을 찾아 저장된 요청의 내용이 같은지 확인하고, 같다면 첫 응답의 결과를 돌려줍니다.

SELECT id, author_id, title, status, created_at
FROM posts
WHERE author_id = 1
  AND request_key = 'post-20260714-001';

이 조회는 회원 1의 '멱등한 게시글 등록' 한 행을 돌려주며, id는 앞에서 실행한 INSERT에 따라 다릅니다. 요청 키는 회원의 범위에서 충분히 오래 보존해야 합니다. 키를 너무 빨리 지우면 오래 늦어진 재시도가 새 글을 만들 수 있습니다.

더 깊이: 문장 롤백이 남기는 것과 트랜잭션을 끝내는 문장

MySQL 8.4 매뉴얼에 따르면 중복 키 오류는 IGNORE를 쓰지 않았다면 그 문장만 되돌리고, 테이블스페이스의 공간이 모자라 나는 Table is full 오류나 행이 너무 길다는 오류도 마찬가지입니다. CHECK 위반처럼 저장 엔진보다 위의 MySQL 계층에서 찾아내는 오류도 대부분 해당 문장만 되돌립니다. 그런데 문장 하나를 롤백해도 그 문장이 이미 얻은 잠금은 풀리지 않습니다. 그래서 중복 오류를 받은 뒤 트랜잭션을 정리하지 않고 열어 두면, 같은 행을 쓰려는 다른 연결이 계속 기다리게 됩니다.

트랜잭션을 끝내는 것은 COMMIT과 ROLLBACK만이 아닙니다. DDL처럼 실행하기 전에 암묵적으로 커밋하는 문장이 여럿 있는데, START TRANSACTION과 BEGIN도 그 가운데 하나입니다. 트랜잭션은 중첩되지 않으므로, 열린 트랜잭션 안에서 START TRANSACTION을 다시 실행하면 앞의 변경이 그 자리에서 커밋됩니다. 오류가 난 트랜잭션을 정리하지 않은 채 재시도 코드가 새 트랜잭션을 시작하면, 실패한 줄 알았던 앞의 게시글 INSERT가 확정될 수 있다는 뜻입니다. LOCK TABLES와 SET autocommit = 1(이미 1이 아닐 때)도 같은 효과가 있습니다.

CREATE TEMPORARY TABLE과 DROP TEMPORARY TABLE은 트랜잭션을 커밋하지 않지만, 그렇다고 롤백되지도 않습니다. 트랜잭션 안에서 임시 테이블을 만든 뒤 ROLLBACK해도 테이블은 그대로 남습니다. 이런 예외가 있으므로, 재시도할 때는 언제나 ROLLBACK으로 앞의 상태를 먼저 끝낸 뒤 새 트랜잭션을 시작합니다.

원래 데이터와 통계를 맞춰 본다

집계 테이블은 원래 데이터에서 다시 계산할 수 있는 파생 데이터입니다. 둘이 일치하는지 확인할 때는 같은 날짜의 범위와 같은 공개 상태를 써야 합니다.

WITH source AS (
  SELECT
    author_id AS member_id,
    DATE(published_at) AS stat_date,
    COUNT(*) AS published_count,
    SUM(view_count) AS total_views
  FROM posts
  WHERE status = 'PUBLISHED'
    AND published_at >= '2026-07-14'
    AND published_at <  '2026-07-15'
  GROUP BY author_id, DATE(published_at)
)
SELECT
  s.member_id,
  s.stat_date,
  s.published_count AS source_count,
  d.published_count AS summary_count,
  s.total_views AS source_views,
  d.total_views AS summary_views
FROM source AS s
LEFT JOIN daily_post_stats AS d
  ON d.member_id = s.member_id
 AND d.stat_date = s.stat_date
WHERE d.member_id IS NULL
   OR d.published_count <> s.published_count
   OR d.total_views <> s.total_views;

member_id | stat_date  | source_count | summary_count | source_views | summary_views
1         | 2026-07-14 | 2            | 1             | 0            | 0

원래 데이터에는 7월 14일의 공개 글이 두 개인데 통계는 하나만 셉니다. 앞의 자동 커밋에서 남은 post-20260714-bad가 통계 없이 저장되었기 때문이며, 나중에 성공한 트랜잭션이 그 누락을 고쳐 주지는 않습니다. 이처럼 누락이 나오면 원래 데이터에서 통계의 값을 다시 계산해 바꿀 수 있어야 합니다(연습 문제 3).

이 LEFT JOIN은 원래 데이터에 있는 회원과 날짜의 키를 기준으로 누락과 값의 차이를 찾습니다. 원래 데이터가 없는데 통계에만 있는 행은 찾지 않으므로, 이 쿼리가 0행이라고 양쪽이 완전히 일치한다고 확정할 수는 없습니다.

운영에서는 열린 트랜잭션이 얼마나 오래 유지되는지와 잠금의 대기 시간, uq_posts_author_request가 충돌하는 비율과 기존 결과를 돌려준 비율, 원래 데이터와 통계가 어긋난 건수, 롤백의 비율과 SQLSTATE별 오류의 분포를 함께 봅니다. MySQL의 DDL은 암묵적으로 커밋하므로 업무의 트랜잭션 안에 섞지 않고, 연결 풀에 연결을 돌려주기 전에는 자동 커밋과 격리 수준을 원래 상태로 되돌립니다.

회원 게시판의 불변식이 유지되려면 함께 성공해야 하는 데이터베이스의 변경만, 가장 짧은 단위로 묶습니다. 바깥 호출은 떼어 내고, 요청 키로 결과가 불확실한 요청을 조회할 수 있는 상태로 바꿉니다.

정리

• 자동 커밋에서 실행한 여러 문장은 저절로 하나의 업무가 되지 않습니다.
• 게시글과 함께 참이어야 하는 일별 통계는 한 트랜잭션에서 확정하고, 중간에 오류가 나면 ROLLBACK합니다.
• (author_id, request_key)의 UNIQUE는 중복 등록과 응답을 잃은 재시도를 안전하게 만듭니다.
• 재시도가 중복 오류를 내면 롤백한 뒤 기존 결과를 찾아 돌려줍니다.
• 파생 통계는 원래 데이터에서 다시 계산하고 맞춰 볼 수 있어야 합니다.

연습 문제

1. 게시글과 첫 댓글을 함께 등록하기

게시글과 첫 댓글을 한 요청에서 함께 등록하고, 같은 요청의 재시도는 한 번만 반영해 보세요. 댓글의 INSERT가 실패하면 게시글도 남지 않아야 합니다. 이 연습은 뒤의 장에서 만들 comments와 섞이지 않도록 실습용 tx_comment_demo를 씁니다.

DROP TABLE IF EXISTS tx_comment_demo;
CREATE TABLE tx_comment_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  content VARCHAR(1000) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  CONSTRAINT fk_tx_comment_post
    FOREIGN KEY (post_id) REFERENCES posts (id),
  CONSTRAINT fk_tx_comment_author
    FOREIGN KEY (author_id) REFERENCES members (id)
) ENGINE = InnoDB;

회원 1과 새 요청 키 post-with-comment-1을 씁니다. 성공 경로를 실행하기 전에, 댓글의 내용에 NULL을 넣는 실패 경로의 결과도 먼저 예상해 봅니다.

START TRANSACTION;

INSERT INTO posts (
  author_id, title, content, status, view_count,
  created_at, updated_at, published_at, version, request_key
)
VALUES (
  1, '질문 글', '첫 댓글까지 함께 저장한다.',
  'PUBLISHED', 0, NOW(6), NOW(6), NOW(6), 0, 'post-with-comment-1'
);

SET @post_id = LAST_INSERT_ID();

INSERT INTO tx_comment_demo (post_id, author_id, content, created_at)
VALUES (@post_id, 1, '첫 댓글', NOW(6));

COMMIT;

2. 재시도에서 통계가 두 번 늘어나는 경우

응답을 잃은 재시도에서, 게시글의 INSERT가 1062로 실패했는데도 애플리케이션이 오류를 무시하고 통계의 UPSERT와 COMMIT을 실행했다면 결과는 어떻게 되나요?

3. 어긋난 통계를 바로잡기

본문의 일치 확인에서 찾은 회원 1의 7월 14일 통계를 원래 데이터에서 다시 계산한 값으로 바로잡아 보세요.

START TRANSACTION;

UPDATE daily_post_stats AS d
JOIN (
  SELECT author_id AS member_id, DATE(published_at) AS stat_date,
         COUNT(*) AS published_count, SUM(view_count) AS total_views
  FROM posts
  WHERE status = 'PUBLISHED'
    AND published_at >= '2026-07-14'
    AND published_at <  '2026-07-15'
  GROUP BY author_id, DATE(published_at)
) AS s
  ON s.member_id = d.member_id AND s.stat_date = d.stat_date
SET d.published_count = s.published_count,
    d.total_views = s.total_views,
    d.updated_at = CURRENT_TIMESTAMP(6);

SELECT ROW_COUNT() AS fixed_rows;

COMMIT;

다음 글 격리 수준과 잠금에서는 두 연결이 같은 게시글을 동시에 고칠 때, 격리 수준과 잠금이 어떤 결과를 만드는지 다룹니다.