저장 프로시저 기초: “포인트 사용”을 DB에서 하나의 호출로 처리하기

지금까지는 애플리케이션이 여러 SQL을 순서대로 실행한다는 관점으로 진행했습니다. 그런데 포인트 사용처럼 “항상 같은 흐름”을 따르는 작업은, DB에 로직을 저장해 하나의 호출로 실행하는 방식도 많이 사용합니다. 이때 등장하는 것이 저장 프로시저(Stored Procedure)입니다.

저장 프로시저는 “자주 쓰는 SQL 묶음”을 DB 안에 저장해두고, 필요할 때 CALL로 호출하는 형태입니다. 장점은 흐름이 한 곳에 고정된다는 점이고, 단점은 로직이 DB에 묶이면서 운영/배포/테스트 전략이 필요해진다는 점입니다.

이 단원의 목적: 핵심 작업(포인트 사용)을 “단일 진입점”으로 만들기

포인트 사용은 멱등성/동시성/원장 기록/상태 관리가 같이 얽히는 대표 작업입니다. 프로시저로 만들면 다음 같은 효과를 기대할 수 있습니다.

  1. 처리 흐름(선점 → 차감 → 기록 → 완료)이 한 곳에 모인다
  2. 누가 호출하든(웹/앱/배치) 같은 규칙으로 처리된다
  3. 실수로 순서를 바꾸거나 일부 단계를 누락할 위험이 줄어든다

이번 글에서는 “요청 ID 선점 테이블(point_requests)”과 “잔액 테이블(point_balance)”을 사용하는 흐름을 기반으로, 포인트 사용 프로시저를 구성하는 예시를 보여줍니다.

1) 준비: 필요한 테이블/제약조건 요약

프로시저 예시는 아래 전제를 둡니다.

  • point_requests: (uid, request_id) UNIQUE로 중복 요청 차단
  • point_balance: uid PK, balance 컬럼에 현재 잔액 저장
  • point_history: 원장 기록, hide=0 기본

그리고 포인트 사용은 “조건부 UPDATE(잔액 충분할 때만 차감)” 패턴을 사용합니다.

2) 프로시저 설계: 입력값과 출력값을 먼저 정한다

프로시저는 “인자”로 입력을 받고, 필요하면 결과를 출력합니다. 포인트 사용 프로시저라면 최소 입력은 다음이 필요합니다.

  • p_uid: 사용자
  • p_request_id: 멱등 키(중복 요청 차단)
  • p_amount: 사용 금액(양수로 받는다고 가정)
  • p_ref_id: 주문번호 등 추적용

출력은 보통 “성공/실패 코드” 하나면 충분합니다. 여기서는 p_result_code를 OUT 파라미터로 두겠습니다.

  • 0: 성공
  • 1: 잔액 부족
  • 2: 중복 요청
  • 9: 기타 오류

3) 프로시저 구현 예시(MySQL 계열 문법)

아래는 “요청 선점 → 잔액 조건부 차감 → 원장 기록 → 요청 DONE”을 한 번에 수행하는 예시입니다. 실제 운영에서는 오류 처리/로그/락 정책이 더 들어가지만, 학습 단계에서는 흐름이 보이게 구성하는 것이 먼저입니다. 

DELIMITER $$ CREATE PROCEDURE sp_use_points( IN p_uid CHAR(36), IN p_request_id CHAR(36), IN p_amount INT, IN p_ref_id VARCHAR(64), OUT p_result_code INT ) proc: BEGIN DECLARE v_rows INT DEFAULT 0; -- 기본값: 기타 오류 SET p_result_code = 9; START TRANSACTION; -- 1) 요청 선점(중복이면 UNIQUE 위반) -- 중복 요청을 코드로 구분하기 위해 INSERT 성공 여부를 활용 -- (실무에서는 핸들러로 오류 코드를 더 세밀하게 처리하기도 함) INSERT INTO point_requests (uid, request_id, request_type, amount, ref_id, status, created_at, updated_at) VALUES (p_uid, p_request_id, 'USE', p_amount, p_ref_id, 'RESERVED', NOW(), NOW()); -- 2) 잔액 조건부 차감 UPDATE point_balance SET balance = balance - p_amount, updated_at = NOW() WHERE uid = p_uid AND balance >= p_amount; SET v_rows = ROW_COUNT(); -- 잔액 부족(차감 실패) IF v_rows = 0 THEN -- 요청 상태를 FAILED로 남길지 여부는 정책이지만, 여기서는 남기는 예시 UPDATE point_requests SET status = 'FAILED', updated_at = NOW() WHERE uid = p_uid AND request_id = p_request_id; COMMIT; SET p_result_code = 1; LEAVE proc; END IF; -- 3) 원장 기록 INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, memo, created_at) VALUES (p_uid, 'FREE', 'USE', -p_amount, p_ref_id, CONCAT('요청ID:', p_request_id), NOW()); -- 4) 요청 완료 UPDATE point_requests SET status = 'DONE', updated_at = NOW() WHERE uid = p_uid AND request_id = p_request_id; COMMIT; SET p_result_code = 0; END$$ DELIMITER ;

이 프로시저는 “성공 여부”를 OUT 파라미터로 돌려줍니다. 호출 측에서는 결과 코드에 따라 화면/응답을 결정할 수 있습니다.

4) 호출 예시: CALL 한 번으로 처리

SET @result := -1;

CALL sp_use_points(
'11111111-1111-1111-1111-111111111111',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
150,
'ORDER_9001',
@result
);

SELECT @result AS result_code;

result_code가 0이면 성공, 1이면 잔액 부족입니다. 그리고 중복 요청은 이 예시 코드만으로는 “오류”로 떨어질 수 있어, 다음 섹션에서 중복을 어떻게 코드로 분리하는지 다룹니다.

5) 중복 요청(UNIQUE 위반)을 결과 코드로 분리하려면

(uid, request_id)에 UNIQUE가 걸려 있으므로, 같은 요청이 다시 들어오면 point_requests INSERT에서 실패합니다.

이 경우를 result_code=2로 돌리고 싶다면, “에러 핸들러(DECLARE ... HANDLER)”를 이용해 예외를 잡는 방식이 흔합니다. 문법이 낯설 수 있어, 핵심 아이디어만 잡아봅니다.

  • UNIQUE 위반이 발생하면 트랜잭션을 롤백하거나, 이미 처리된 상태를 조회한다
  • point_requests에서 status가 DONE이면 2(중복)로 응답할 수 있다

학습 단계에서는 “중복은 DB에서 막힌다”는 사실과, “그 막힘을 결과 코드로 다듬을 수 있다” 정도만 잡아도 충분합니다.

6) 프로시저 방식의 장단점(현실적인 관점)

장점

  • 규칙이 한 곳에 고정: 호출자가 달라도 같은 흐름
  • 실수 방지: 차감만 하고 원장 기록을 빼먹는 등 누락 위험 감소
  • 트랜잭션이 자연스럽다: BEGIN~COMMIT 흐름을 DB 내부에서 강제

단점

  • 테스트/배포 전략이 필요: 애플리케이션 코드처럼 쉽게 CI로 돌리기 어렵다
  • DB 종속성: DBMS 변경 시 이식 비용이 커질 수 있다
  • 로직이 커지면 유지보수가 어려워질 수 있음: 역할 분리가 중요

그래서 저장 프로시저는 “전부 DB로”가 아니라, 정말 핵심이면서 반복되는 작업을 작은 단위로 고정하는 용도로 쓰는 경우가 많습니다.

7) 간단 검증: 프로시저 실행 후 확인 쿼리

SET @uid := '11111111-1111-1111-1111-111111111111'; SET @request_id := 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';

-- 요청 상태
SELECT status, amount, ref_id, created_at, updated_at
FROM point_requests
WHERE uid = @uid AND request_id = @request_id;

-- 잔액
SELECT balance, updated_at
FROM point_balance
WHERE uid = @uid;

-- 원장(요청ID로 추적)
SELECT id, action_type, amount, ref_id, memo, created_at
FROM point_history
WHERE uid = @uid
AND memo LIKE CONCAT('%', @request_id, '%')
ORDER BY created_at DESC, id DESC
LIMIT 10;

이 세 가지를 함께 보면 “요청이 어떤 상태로 끝났는지”를 빠르게 확인할 수 있습니다.

다음 글 예고: 에러 처리/검증 쿼리 — “원장 합계 vs balance” 불일치를 찾는 점검 SQL 만들기

프로시저까지 도입하면 처리 흐름은 단단해지지만, 운영에서 완벽한 시스템은 없기 때문에 “점검”이 필요합니다. 다음 글에서는 원장 합계(SUM)와 balance 값이 다르면 바로 찾아낼 수 있는 점검 쿼리, 그리고 어떤 유형의 불일치가 발생할 수 있는지(중간 실패, 수동 정정, 잘못된 숨김 처리)를 정리합니다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

JOIN 기초: users와 point_history를 합쳐 ‘회원별 요약(잔액/최근 활동)’ 만들기

포인트 원장(point_history)만으로도 잔액과 통계를 만들 수 있지만, 실제 화면이나 운영 도구에서는 거의 항상 “사용자 정보”가 함께 필요합니다. 예를 들어 이런 질문들은 point_history만으로는 답이 부족합니다. 이 uid가 누구인지(가입일, 상태 등) 함께 보고 싶다 회원 목록을 보면서 각 회원의 잔액/최근 활동을 한 줄로 요약하고 싶다 최근 7일 동안 사용이 많은 회원을 “회원 정보와 함께” 추려 보고 싶다 이때 필요한 것이 JOIN입니다. JOIN은 테이블을 “합치는” 기능이지만, 정확히는 두 테이블의 행을 연결 해 새로운 결과를 만드는 방식입니다. 이번 글에서는 포인트 시스템에서 가장 흔한 JOIN 패턴(회원 + 원장 요약)을 중심으로, INNER JOIN/LEFT JOIN의 차이와 실전에서 헷갈리는 포인트를 정리합니다. 이 단원의 목적: “회원 목록 + 포인트 요약”을 안정적으로 뽑아내기 JOIN은 문법을 외우는 것보다 “어떤 결과를 만들고 싶은지”를 먼저 그리는 것이 중요합니다. 이번 글의 목표는 아래 3가지입니다. INNER JOIN과 LEFT JOIN의 차이를 이해하고 상황에 맞게 선택한다 집계(GROUP BY)와 JOIN을 결합해 ‘회원별 잔액/통계’를 만든다 JOIN에서 자주 발생하는 오류(행 폭증, 중복 집계)를 피하는 패턴을 익힌다 준비: users에 최소한의 컬럼을 더해 “JOIN 결과가 읽히게” 만들기 이전 글에서는 users가 uid와 created_at만 있었지만, JOIN 결과를 보기 편하게 하려면 display_name 같은 컬럼이 하나 있으면 좋습니다. 실제 서비스라면 이메일/닉네임/상태값이 있을 텐데, 여기서는 학습 목적상 간단히 display_name을 추가합니다. -- 이미 있다면 생략 ALTER TABLE users ADD COLUMN display_name VARCHAR(50) NULL; -- 예시 값 입력(학습용) UPDATE users SET display_n...
자세한 내용 보기

집계 기초: SUM/COUNT로 잔액·기간별 적립/사용 리포트 만들기

포인트 시스템의 “진짜 쓸모”는 원장(point_history)에 기록된 사실을 사람이 이해할 수 있는 형태로 요약하는 데서 나옵니다. 화면에서는 잔액 한 줄로 보이지만, 그 뒤에는 적립/사용/만료/취소가 뒤섞인 수많은 기록이 존재합니다. 그래서 포인트 시스템에서 SQL의 핵심은 단순 조회(SELECT)보다 집계(aggregation) 입니다. 집계는 원장 데이터를 합치고(SUM), 개수를 세고(COUNT), 기준별로 묶어서(GROUP BY) “의미 있는 숫자”로 바꾸는 과정입니다. 이 단원의 목적: 원장 데이터로 ‘요약 결과’를 만드는 습관 만들기 이번 글에서는 아래 3가지를 목표로 합니다. SUM으로 잔액을 만들고, 기간별 합계를 안정적으로 계산한다 COUNT로 이벤트 건수를 집계해 “양”을 파악한다 집계를 할 때 자주 발생하는 실수(NULL, 숨김 데이터, 중복 기준)를 피한다 준비: 집계를 위한 최소 데이터 점검 집계는 데이터가 적으면 티가 안 나지만, 데이터가 쌓이면 작은 실수가 큰 오차로 확대됩니다. 그래서 집계 전에 “필수 조건”을 먼저 확인해 두면 좋습니다. amount가 NULL이 아닌가 숨김 데이터(hide=1)가 섞여 있다면 집계에서 제외할 것인가 부호 정책(적립 + / 차감 -)이 일관적인가 아래 예시는 hide 컬럼이 있다고 가정하고, 집계에서는 hide=0만 포함하는 패턴으로 진행합니다. SET @uid := '11111111-1111-1111-1111-111111111111'; 1) 잔액 계산의 기본: SUM(amount) 가장 기본적인 잔액 조회는 “해당 사용자의 amount를 전부 더하는 것”입니다. 이 방식의 장점은 단순함과 신뢰성입니다. 원장이 올바르면, 잔액은 항상 재현 가능합니다. SELECT uid, SUM(amount) AS balance FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY uid; 여기서...
자세한 내용 보기

키 설계 기초: PRIMARY KEY/UNIQUE로 ‘중복 적립’과 ‘중복 차감’을 구조적으로 막는 방법

포인트 시스템에서 가장 골치 아픈 문제는 “한 번만 처리되어야 하는 요청이 두 번 처리되는 것”입니다. 네트워크 재시도, 클라이언트 중복 클릭, 서버 타임아웃 후 재요청은 운영에서 흔한 일이고, 이때 코드만으로 완벽하게 막는 건 생각보다 어렵습니다. 그래서 많은 서비스는 마지막 방어선으로 키(Primary Key/Unique Key) 를 활용합니다. 키 설계가 잘 되면, 애플리케이션이 실수하더라도 DB가 중복을 거절 합니다. 이번 글에서는 “키는 그냥 ID 하나”라는 수준을 넘어서, 포인트 원장(히스토리)에서 실제로 필요한 멱등성(idempotency) 을 어떻게 만들지에 초점을 맞춥니다. 이 단원의 목적: “중복이 생길 수밖에 없는 현실”을 전제로 설계하기 포인트 기능은 보통 결제, 이벤트, 쿠폰, 고객센터 보정 등 다양한 경로에서 호출됩니다. 호출 경로가 늘어날수록 중복 처리 가능성도 늘어납니다. 이번 글의 목표는 아래 3가지입니다. PK/UNIQUE의 역할을 정확히 이해하고 “어디에 걸어야 하는지” 결정한다 포인트 원장에서 중복을 막는 대표 전략(요청 ID/ref_id)을 설계한다 실제로 중복 요청이 들어오는 상황을 가정해, DB 레벨에서 안전하게 막는다 1) PRIMARY KEY는 “행을 식별하는 유일한 주소”다 PRIMARY KEY(PK)는 테이블에서 한 행을 유일하게 식별합니다. 흔히 AUTO_INCREMENT id를 PK로 두는 이유는 단순합니다. 쉽고, 빠르고, 안정적 이기 때문입니다. 하지만 포인트 시스템에서는 “id가 유일하다”만으로는 부족합니다. id는 단지 “기록의 번호”일 뿐, “같은 요청이 두 번 들어왔다”는 문제를 막아주지 않습니다. 즉, PK는 필요조건이지만 중복 방지의 충분조건은 아닙니다. -- 기록 자체의 식별자(기본) PRIMARY KEY (id) 2) UNIQUE는 “같은 의미의 기록이 두 번 들어오는 것”을 막는다 UNIQUE는 “비즈니스적으로 같은 의미”의 중복을 차단하는 도구입니다. ...
자세한 내용 보기