유효 기간 모델

공지 게시글의 노출 우선순위가 7월에는 60, 8월부터 90이라면 값 하나를 UPDATE해서는 안 됩니다. 7월의 게시판 화면을 나중에 다시 계산할 때 당시의 정책이 필요하기 때문입니다. 여기서 묻는 시간은 행이 데이터베이스에 들어간 시각이 아니라, 정책이 현실에서 효력을 갖는 기간입니다.

기간 모델의 버그는 대개 구간의 끝에서 생깁니다. 종료일을 포함한다고 생각한 쓰기 주체와 포함하지 않는다고 보는 쿼리가 섞이면, 하루가 비거나 두 정책이 동시에 선택됩니다. 그래서 이 글은 [valid_from, valid_to)의 반개구간 하나로 뜻을 고정하고, 길이가 제각각인 기간의 겹침을 INSERT 전에 검사합니다.

회원 게시판은 조회하는 날짜에 유효한 게시글의 노출 정책을 고릅니다. 과거의 게시판 화면은 그날 유효했던 정책을 써야 하고, 미래의 예약 정책도 미리 등록할 수 있어야 합니다. 정책이 없는 날짜는 시스템의 기본값을 쓰지만, 두 정책이 같은 날짜를 덮는 상태는 허용하지 않습니다.

MySQL의 CHECK는 한 행의 시작과 끝의 순서는 검증할 수 있지만, 다른 행과 겹치는지는 볼 수 없습니다. 그래서 게시글 부모 행을 잠금의 대상으로 삼아, 같은 게시글의 기간 등록을 한 번에 하나씩 처리합니다.

겹치는 유효 기간

UNIQUE (post_id, valid_from)은 시작일이 같은 중복만 막습니다. 7월 1일에 시작한 기간과 7월 15일에 시작한 기간은 키가 다르므로 둘 다 들어가고, 조회가 ORDER BY valid_from DESC LIMIT 1로 우연히 하나를 고르면 손상은 감춰집니다.

DROP TABLE IF EXISTS post_visibility_period_bad;
CREATE TABLE post_visibility_period_bad (
  policy_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  display_priority SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (policy_id),
  UNIQUE KEY uq_visibility_bad_start (post_id, valid_from),
  CHECK (valid_to > valid_from)
) ENGINE=InnoDB;

INSERT INTO post_visibility_period_bad
  (post_id, valid_from, valid_to, display_priority)
VALUES
  (10, '2026-07-01', '2026-08-01', 60),
  (10, '2026-07-15', '2026-09-01', 90);

SELECT policy_id, display_priority
FROM post_visibility_period_bad
WHERE post_id = 10
  AND '2026-07-20' >= valid_from
  AND '2026-07-20' < valid_to
ORDER BY policy_id;

SELECT a.policy_id AS left_policy, b.policy_id AS right_policy
FROM post_visibility_period_bad AS a
JOIN post_visibility_period_bad AS b
  ON b.post_id = a.post_id AND b.policy_id > a.policy_id
WHERE a.valid_from < b.valid_to AND b.valid_from < a.valid_to;

policy_id | display_priority
1         | 60
2         | 90

left_policy | right_policy
1           | 2

UNIQUE와 CHECK는 아무것도 거부하지 않았지만, 7월 20일의 조회가 두 정책을 돌려주고 겹침 검사가 정책 1과 2의 쌍을 찾아냅니다.

두 기간 A와 B가 겹치는 정확한 조건은 A.start < B.end AND B.start < A.end입니다. "새 시작점이 기존 기간 안에 있는가"만 검사하면, 새 기간이 기존 기간 전체를 감싸는 경우를 놓치므로 두 방향을 대칭으로 비교합니다.

검사 쿼리를 애플리케이션에서 실행하는 것만으로는 부족합니다. 작업자 A와 B가 동시에 빈 결과를 보고 서로 겹치는 정책을 넣을 수 있기 때문입니다. 아직 정책 행이 하나도 없다면 잠글 대상도 없으므로, 항상 있는 posts의 부모 행을 FOR UPDATE로 잡습니다.

노출 정책은 날짜에 적용되는 정책이고, 이 예제는 기준 시간대를 UTC의 업무일로 정했으므로 DATE가 알맞습니다. 지역별 정책이라면 zone_id를 식별 키에 넣고, 현지 날짜로의 변환은 API의 입력 단계에서 한 번만 합니다.

반개구간과 직렬화 지점

valid_to는 마지막으로 유효한 날짜가 아니라, 처음으로 유효하지 않은 날짜입니다. 그래서 8월 정책이 2026-08-01에 시작하면 7월 정책의 valid_to도 같은 날짜가 되고, 두 행 사이에 겹침도 빈틈도 생기지 않습니다.

시작 날짜만 검사하면 놓치는 겹침이 있습니다. 기존 A가 [07-01, 08-01)이고 새 B가 [06-01, 10-01)이라면, B의 시작은 A 안에 없지만 07-01 < 10-01과 06-01 < 08-01이 모두 참이므로 두 기간은 겹칩니다. 반대로 B가 [08-01, 09-01)이면 07-01 < 09-01은 참이지만 08-01 < 08-01은 거짓이므로 겹치지 않으며, 엄격한 부등호 덕분에 끝 날짜를 공유하는 두 반개구간이 허용됩니다.

MySQL에는 PostgreSQL의 배제 제약 같은 기간 겹침 제약이 없으므로, 잠금과 겹침 검사, INSERT를 프로시저로 묶습니다. 운영에서는 직접 INSERT의 권한을 회수해 기간 등록을 이 프로시저로만 하게 하며, 아래 실습에는 GRANT와 REVOKE의 설정은 넣지 않았습니다. 마이그레이션이나 복구 중에만 데이터베이스 소유자가 테이블에 직접 접근하고, 작업 뒤에는 전체 겹침 검사를 실행합니다.

겹침의 금지와 연속성은 다른 규칙입니다. 회원 게시판은 정책이 없는 날에 기본 노출 정책을 쓰므로 빈 구간을 허용하지만, 항상 한 행이 있어야 하는 정책이라면 새 기간을 등록할 때 앞뒤 이웃과 끝 날짜가 맞물리는지도 검사합니다. 트랜잭션 전체를 SERIALIZABLE로 올리는 대신 게시글 한 행을 명시적으로 잠그면, 충돌의 범위가 게시글 단위로 보여 운영자가 잠금 대기의 원인을 해석하기 쉽습니다.

더 깊이: 빈 구간 대신 게시글 행을 잠그는 이유

겹치는 기간이 있는지를 기간 테이블에 대한 잠금 읽기로 확인하면, 맞는 행이 없을 때 InnoDB는 읽은 범위의 빈 구간에 갭 잠금(gap lock)을 겁니다. 그런데 MySQL 8.4 매뉴얼에 따르면 갭 잠금의 목적은 다른 트랜잭션이 그 구간에 행을 넣지 못하게 하는 것뿐이어서, 같은 구간의 갭 잠금은 서로 충돌하지 않고 여러 트랜잭션이 함께 가질 수 있습니다. 공유 갭 잠금과 배타 갭 잠금의 구별도 없습니다.

그래서 두 작업자가 같은 게시글에 겹치는 기간을 동시에 등록하면, 둘 다 빈 결과와 갭 잠금을 얻은 채 검사를 통과할 수 있습니다. 이어서 각자 INSERT를 하려 하면 상대가 가진 갭 잠금 때문에 서로 기다리게 되고, 결국 교착 상태로 한쪽이 롤백될 수 있습니다. 겹침은 막히더라도 어느 범위가 잠기는지가 인덱스와 데이터의 분포에 따라 달라, 잠금 대기의 원인을 해석하기 어렵습니다.

반면 아래 프로시저처럼 posts의 한 행을 기본 키로 잠그면, 유일한 인덱스로 한 행을 찾는 잠금이므로 갭 잠금 없이 그 레코드만 잠깁니다. 두 번째 작업자는 같은 게시글의 행에서 첫 번째 작업자가 커밋할 때까지 기다린 뒤, 첫 번째가 넣은 기간을 보고 겹침을 판단합니다. REPEATABLE READ의 스냅샷은 트랜잭션의 첫 일관 읽기에서 정해지는데, 이 프로시저에서는 잠금을 얻은 뒤에 실행하는 겹침 검사가 그 첫 일관 읽기이기 때문입니다. 다만 여러 열로 된 유일한 인덱스의 일부 열만으로 찾으면 갭 잠금이 생기므로, 직렬화 지점으로 쓰는 행은 키의 모든 열로 지정합니다.

유효 기간의 등록

프로시저는 종료값을 NULL로 받지 않습니다. 끝이 정해지지 않은 정책도 9999-12-31 같은 센티널 날짜로 저장해 비교식을 단순하게 유지하며, 끝없는 기간을 NULL로 나타내려면 모든 쿼리에서 COALESCE의 규칙을 일관되게 적용해야 합니다. @post_id는 12장 4편의 시드 게시글 1입니다.

DROP PROCEDURE IF EXISTS add_post_visibility_period;
DROP TABLE IF EXISTS post_visibility_period;

CREATE TABLE post_visibility_period (
  policy_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  display_priority SMALLINT UNSIGNED NOT NULL,
  policy_reason VARCHAR(240) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (policy_id),
  CONSTRAINT uq_post_visibility_start UNIQUE (post_id, valid_from),
  CONSTRAINT fk_post_visibility_post FOREIGN KEY (post_id)
    REFERENCES posts (id) ON DELETE RESTRICT,
  CONSTRAINT chk_post_visibility_period CHECK (valid_to > valid_from),
  CONSTRAINT chk_post_visibility_priority CHECK (display_priority BETWEEN 1 AND 100),
  INDEX ix_post_visibility_asof (post_id, valid_from, valid_to)
) ENGINE=InnoDB;

SET @post_id = (SELECT id FROM posts ORDER BY id LIMIT 1);
SET @author_id = (SELECT author_id FROM posts WHERE id = @post_id);

DELIMITER //
CREATE PROCEDURE add_post_visibility_period (
  IN p_post_id BIGINT UNSIGNED,
  IN p_valid_from DATE,
  IN p_valid_to DATE,
  IN p_display_priority SMALLINT UNSIGNED,
  IN p_reason VARCHAR(240)
)
BEGIN
  DECLARE v_post BIGINT UNSIGNED;
  DECLARE v_overlap BIGINT UNSIGNED;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  SELECT id INTO v_post
  FROM posts
  WHERE id = p_post_id
  FOR UPDATE;

  IF v_post IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'post not found';
  END IF;
  IF p_valid_to <= p_valid_from THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'period must be non-empty';
  END IF;

  SELECT policy_id INTO v_overlap
  FROM post_visibility_period
  WHERE post_id = p_post_id
    AND valid_from < p_valid_to
    AND p_valid_from < valid_to
  ORDER BY valid_from
  LIMIT 1;

  IF v_overlap IS NOT NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'visibility period overlaps existing period';
  END IF;

  INSERT INTO post_visibility_period
    (post_id, valid_from, valid_to, display_priority, policy_reason)
  VALUES
    (p_post_id, p_valid_from, p_valid_to, p_display_priority, p_reason);
  COMMIT;
END//
DELIMITER ;

CALL add_post_visibility_period(
  @post_id, '2026-07-01', '2026-08-01', 60,
  '7월 공지 게시글 기본 노출 우선순위'
);
CALL add_post_visibility_period(
  @post_id, '2026-08-01', '2026-09-01', 90,
  '8월 캠페인에 따른 노출 우선순위 상향'
);

두 정책은 8월 1일이라는 끝 날짜를 공유하지만, 어느 날짜에도 함께 선택되지 않습니다. 새 테이블이므로 정책 id는 1과 2입니다.

프로시저 안의 SELECT ... INTO가 0행을 읽으면 "데이터 없음" 조건이 생기지만, 이 조건은 SQLEXCEPTION이 아니므로 처리기가 잡지 않고, 변수는 선언할 때의 NULL로 남습니다. 그래서 게시글이 없을 때와 겹치는 기간이 없을 때를 IS NULL로 판정할 수 있습니다. 겹침이 이미 여러 개 있는 오염된 데이터에서도 LIMIT 1만으로 등록을 거부하므로, 기존의 손상은 마이그레이션 전에 별도의 교차 쿼리로 모두 정리합니다.

부모의 잠금은 같은 게시글의 정책 등록을 차례로 처리하지만, 다른 게시글끼리는 동시에 진행됩니다. 잠금 대기가 길다면 한 트랜잭션 안에 외부 API의 호출이나 사용자 입력의 대기를 넣지 않았는지 먼저 확인합니다.

화면에 "7월 31일까지"라고 보여 주려면 DATE_SUB(valid_to, INTERVAL 1 DAY)로 계산하되, 저장하는 뜻을 시작과 끝을 모두 포함하는 구간으로 바꾸지는 않습니다. API의 필드 이름도 validToExclusive처럼 끝 날짜의 규칙을 드러내면 오해를 줄일 수 있습니다.

기간 겹침의 검증

7월 31일과 8월 1일의 조회는 하루 차이로 생기는 오류를 찾고, 기존 기간을 모두 감싸는 6월 1일부터 10월 1일까지의 요청은 대칭 조건을 확인합니다. 마지막의 자기 조인은 운영에서 매일 실행할 수 있는 일치 확인입니다.

SELECT policy_id, display_priority
FROM post_visibility_period
WHERE post_id = @post_id
  AND '2026-07-31' >= valid_from
  AND '2026-07-31' < valid_to;

SELECT policy_id, display_priority
FROM post_visibility_period
WHERE post_id = @post_id
  AND '2026-08-01' >= valid_from
  AND '2026-08-01' < valid_to;

CALL add_post_visibility_period(
  @post_id, '2026-06-01', '2026-10-01', 30,
  '기존 두 기간을 감싸는 거부 예제'
);

SELECT a.post_id, a.policy_id, b.policy_id
FROM post_visibility_period AS a
JOIN post_visibility_period AS b
  ON b.post_id = a.post_id
 AND b.policy_id > a.policy_id
WHERE a.valid_from < b.valid_to
  AND b.valid_from < a.valid_to;

policy_id | display_priority
1         | 60

policy_id | display_priority
2         | 90

ERROR 1644 (45000): visibility period overlaps existing period

7월 31일과 8월 1일은 서로 다른 정책을 정확히 한 행씩 돌려줍니다. 감싸는 기간의 요청은 두 기간과 모두 겹쳐 거부되고, 처리기가 롤백하므로 행은 늘지 않습니다. 마지막의 자기 조인은 빈 결과입니다.

ix_post_visibility_asof는 post_id와 valid_from으로 범위를 좁힐 수 있게 둔 인덱스이며, 실제로 어떤 경로를 고르는지는 같은 조회 앞에 EXPLAIN ANALYZE를 붙여 데이터가 충분할 때 확인합니다. valid_to까지 한 번에 범위를 좁히지 못하더라도 게시글 하나의 정책 수가 작다면 충분하고, 정책이 수십만 건으로 늘어난다면 파티션보다 먼저 업무의 집계 단위가 잘못되지 않았는지 검토합니다.

정책의 변경과 취소

7월 20일부터 노출 우선순위를 75로 바꾸려면, 7월 행을 UPDATE하지 않고 기존 행을 20일에 닫은 뒤 [07-20, 08-01)의 새 행을 넣습니다. 그래야 19일까지의 뜻이 그대로 남습니다. 두 문장은 하나의 트랜잭션이어야 하며(연습 문제 1), 분할은 대상 정책 행을 잠그므로 부모를 잠그는 신규 등록과 잠금의 대상이 다릅니다. 다른 변경 경로까지 포함한 잠금의 순서는 따로 일관되게 정합니다.

아직 효력이 시작되지 않은 예약도 DELETE만 하면 누가 왜 취소했는지 사라집니다. policy_status와 cancelled_at을 두거나 15장 3편의 감사 이벤트를 남기고, 유효 기간과 관리 상태는 다른 축이므로 valid_to를 취소 시각으로 쓰지 않습니다.

기존의 단일 값 열에서 기간 테이블로 옮길 때는, 모든 게시글에 기준일부터 9999-12-31까지의 행을 만들고, 기준 시점의 결과와 기존 값을 비교한 뒤 읽기 주체를 전환하며, 마지막에 이전 열을 지웁니다.

단순한 설정값이고 과거를 다시 계산할 일이 없다면 현재 열 하나가 낫습니다. 효력의 예약과 과거의 설명이 필요하다면 기간 행이 알맞으며, 채택할 때는 시작 포함과 끝 제외의 표기, 빈 구간의 처리, 직접 INSERT의 권한을 설계 문서에 고정합니다.

정리

• 효력 날짜는 저장한 시각이 아니라, 업무에서 값이 적용되는 달력의 구간입니다.
• [start, end) 표기는 이웃한 기간이 끝 날짜를 공유해도 겹치지 않게 처리합니다.
• 길이가 제각각인 기간의 겹침은 CHECK나 시작일의 UNIQUE만으로 막을 수 없고, 대칭 조건으로 검사합니다.
• 항상 있는 부모 행을 잠그면, 기간이 하나도 없을 때의 첫 경쟁 삽입도 직렬화됩니다.

연습 문제

1. 기간의 분할

7월의 노출 우선순위 60을 7월 20일부터 75로 바꾸는 분할 프로시저를 작성해 보세요. 기존 행을 잠그고 종료일을 줄인 뒤 나머지 구간을 넣어야 하며, 7월 19일과 20일의 조회가 각각 60과 75를 돌려주어야 합니다.

DROP PROCEDURE IF EXISTS split_post_visibility_period;
DELIMITER //
CREATE PROCEDURE split_post_visibility_period (
  IN p_policy_id BIGINT UNSIGNED,
  IN p_split_on DATE,
  IN p_new_priority SMALLINT UNSIGNED,
  IN p_reason VARCHAR(240)
)
BEGIN
  DECLARE v_post BIGINT UNSIGNED;
  DECLARE v_from DATE;
  DECLARE v_to DATE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;
  SELECT post_id, valid_from, valid_to
    INTO v_post, v_from, v_to
  FROM post_visibility_period
  WHERE policy_id = p_policy_id
  FOR UPDATE;

  IF v_post IS NULL OR p_split_on <= v_from OR p_split_on >= v_to THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'split date outside policy';
  END IF;

  UPDATE post_visibility_period
  SET valid_to = p_split_on
  WHERE policy_id = p_policy_id;

  INSERT INTO post_visibility_period
    (post_id, valid_from, valid_to, display_priority, policy_reason)
  VALUES (v_post, p_split_on, v_to, p_new_priority, p_reason);
  COMMIT;
END//
DELIMITER ;

SET @july_policy = (
  SELECT policy_id FROM post_visibility_period
  WHERE post_id = @post_id AND valid_from = '2026-07-01'
);
CALL split_post_visibility_period(
  @july_policy, '2026-07-20', 75, '캠페인 일정 변경에 따른 노출 우선순위 조정'
);

SELECT policy_id, valid_from, valid_to, display_priority
FROM post_visibility_period
WHERE post_id = @post_id
ORDER BY valid_from;

policy_id | valid_from | valid_to   | display_priority
1         | 2026-07-01 | 2026-07-20 | 60
3         | 2026-07-20 | 2026-08-01 | 75
2         | 2026-08-01 | 2026-09-01 | 90

2. 어느 요청이 겹치는가

정책 1, 2만 있는 본문의 상태에서 다음 요청은 각각 등록될까요? [06-01, 07-01), [07-15, 07-20), [08-31, 10-01), [09-01, 10-01).

다음 글 불변 감사 원장에서는 변경 사실 자체를 고칠 수 없게 만드는 추가 전용 감사 원장을 설계합니다.