IT 기술 정보 뉴스팩토리

포인트 시스템은 “정상 처리”만 잘 만들면 끝나는 것처럼 보이지만, 실제로는 시간이 지날수록 다양한 이유로 데이터가 어긋날 수 있습니다. 예를 들어 이런 상황이 생깁니다. 중간 장애로 트랜잭션이 일부만 반영된 것처럼 보이는 케이스(재처리/수동 개입 포함) 운영 정정(ADJUST)이나 소프트 삭제(hide) 처리 기준이 흔들린 케이스 배치 작업(만료/정산)이 예상과 다른 시간에 들어간 케이스 코드 변경 이후 특정 action_type이 누락/중복 집계된 케이스 그래서 “점검 쿼리”는 기능 쿼리만큼 중요합니다. 특히 balance 테이블(잔액 캐시)을 함께 쓰는 구조라면, 주기적으로 원장 합계와 balance가 같은지 확인해야 합니다. 이 단원의 목적: 불일치를 “발견”하고 “원인”을 좁힐 수 있는 쿼리 세트 만들기 이번 글에서는 아래 3가지를 목표로 합니다. 원장 합계(SUM)와 balance를 비교해 불일치 UID를 뽑는다 불일치가 발견되면 어디서부터 어긋났는지 범위를 좁힌다 자주 발생하는 유형(숨김 처리, 중복 요청, 배치 만료)을 점검 항목으로 만든다 1) 1차 점검: uid별 “원장 잔액”과 “balance 잔액” 비교 가장 기본은 uid별로 두 값을 나란히 놓고 비교하는 것입니다. 여기서는 hide=0만 원장 합계에 포함한다고 가정합니다. SELECT b.uid, b.balance AS balance_cache, COALESCE(l.ledger_balance, 0) AS balance_ledger, b.balance - COALESCE(l.ledger_balance, 0) AS diff FROM point_balance b LEFT JOIN ( SELECT uid, SUM(amount) AS ledger_balance FROM point_history WHERE hide = 0 GROUP BY uid ) l ON l.uid = b.uid WHERE b.balance <> COALESCE(l.ledger_...

지금까지는 애플리케이션이 여러 SQL을 순서대로 실행한다는 관점으로 진행했습니다. 그런데 포인트 사용처럼 “항상 같은 흐름”을 따르는 작업은, DB에 로직을 저장해 하나의 호출로 실행 하는 방식도 많이 사용합니다. 이때 등장하는 것이 저장 프로시저(Stored Procedure) 입니다. 저장 프로시저는 “자주 쓰는 SQL 묶음”을 DB 안에 저장해두고, 필요할 때 CALL 로 호출하는 형태입니다. 장점은 흐름이 한 곳에 고정된다는 점이고, 단점은 로직이 DB에 묶이면서 운영/배포/테스트 전략이 필요해진다는 점입니다. 이 단원의 목적: 핵심 작업(포인트 사용)을 “단일 진입점”으로 만들기 포인트 사용은 멱등성/동시성/원장 기록/상태 관리가 같이 얽히는 대표 작업입니다. 프로시저로 만들면 다음 같은 효과를 기대할 수 있습니다. 처리 흐름(선점 → 차감 → 기록 → 완료)이 한 곳에 모인다 누가 호출하든(웹/앱/배치) 같은 규칙으로 처리된다 실수로 순서를 바꾸거나 일부 단계를 누락할 위험이 줄어든다 이번 글에서는 “요청 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: 멱등 키(중복 ...

포인트 시스템을 만들다 보면 쿼리가 점점 길어집니다. 회원별 잔액, 최근 30일 적립/사용, 최근 활동 시각, 이상 징후용 집계까지 한 번에 뽑으려면 JOIN과 서브쿼리가 늘어나고, 같은 로직이 여러 곳에서 반복되기 시작합니다. 이때 도움이 되는 도구가 VIEW(뷰) 입니다. VIEW는 데이터를 저장하는 테이블이 아니라, 특정 SELECT 쿼리에 이름을 붙여 “가상 테이블처럼” 쓰는 기능 입니다. 즉, “자주 쓰는 쿼리 조합을 한 번 정의해두고, 이후에는 SELECT로 간단히 가져오는 방식”입니다. 이 단원의 목적: “반복되는 리포트 쿼리”를 한 곳에서 관리하기 VIEW를 도입하는 이유는 속도를 올리기 위해서가 아니라, 복잡도를 낮추기 위해서 인 경우가 많습니다. 이번 글에서는 아래 3가지를 목표로 합니다. VIEW가 무엇이고, 테이블과 무엇이 다른지 이해한다 포인트 시스템에서 자주 쓰는 “회원별 요약” VIEW를 만든다 VIEW를 쓸 때의 주의점(성능, 권한, 업데이트 가능 여부)을 정리한다 1) VIEW는 “저장된 SELECT”다 VIEW는 실행 결과를 저장하는 게 아니라, 정의된 SELECT를 매번 실행해서 결과를 보여줍니다(일반적인 VIEW 기준). 그래서 VIEW는 아래처럼 생각하면 이해가 쉽습니다. 테이블: 데이터가 실제로 저장됨 뷰: SQL 정의가 저장됨(결과는 요청할 때 계산) 즉, VIEW를 만든다고 해서 데이터가 복제되거나 늘어나지 않습니다. 대신 “이 SELECT를 앞으로는 view_name이라는 이름으로 부르겠다”는 약속을 만들어주는 것입니다. 2) 첫 번째 VIEW: 사용자별 현재 잔액(원장 기반) 가장 기본은 잔액 뷰입니다. point_history 원장을 uid별로 SUM해서 “현재 잔액”처럼 보이게 만들 수 있습니다. hide=1(숨김)은 제외한다고 가정합니다. CREATE OR REPLACE VIEW v_user_balance AS SELECT uid, SUM(amount) AS bala...

동시성(잠금)만 잘 잡아도 잔액이 마이너스로 내려가는 문제는 많이 줄어듭니다. 그런데 운영에서 더 자주 만나는 현실은 “동시에 두 번”이 아니라 같은 요청이 재전송되어 여러 번 들어오는 것 입니다. 네트워크 타임아웃, 버튼 중복 클릭, 서버 재시도 로직은 생각보다 흔합니다. 그래서 포인트 사용 처리에서 정말 중요한 것은 아래 두 가지를 동시에 만족시키는 것입니다. 동시에 여러 요청이 들어와도 잔액이 깨지지 않는다(동시성) 같은 요청이 여러 번 들어와도 결과는 한 번만 반영된다(멱등성) 이번 글에서는 이 둘을 같이 해결하는 대표 패턴인 “요청 ID 선점(Reservation)” 전략을 다룹니다. 핵심은 간단합니다. “먼저 이 요청을 내가 처리하겠다고 기록하고(중복 차단), 그 다음 실제 차감을 진행한다.” 이 단원의 목적: “중복 요청”을 DB가 구조적으로 거절하게 만들기 멱등성은 코드로도 구현할 수 있지만, 서비스가 커질수록 여러 서버/여러 경로에서 동일 요청이 들어오며 코드만으로 일관성을 유지하기가 어려워집니다. 그래서 데이터베이스에 “중복을 거절하는 규칙(UNIQUE)”을 두고, 그 규칙을 활용해 처리 흐름을 설계하는 것이 강력합니다. 이번 글의 목표는 아래 3가지입니다. 요청 ID를 어디에 저장하고 어떤 UNIQUE를 걸어야 하는지 정한다 요청을 먼저 선점한 뒤 잔액 차감을 진행하는 트랜잭션 흐름을 만든다 실패/재시도 상황에서 “어떤 상태가 남는지”를 명확히 이해한다 1) 요청 ID 선점용 테이블 만들기 point_history는 원장이고, 요청 선점은 “처리 상태”를 관리하는 성격이 강합니다. 그래서 보통 별도 테이블을 둡니다. 이 테이블은 “요청이 들어왔는지”, “처리되었는지”, “실패했는지”를 추적할 수 있어야 합니다. CREATE TABLE IF NOT EXISTS point_requests ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uid CHAR(36) NOT...

트랜잭션을 적용했다고 해서 모든 문제가 끝나지는 않습니다. 트랜잭션은 “중간 실패”를 막아주지만, 동시에 들어오는 요청(동시성) 까지 자동으로 해결해주지는 않습니다. 포인트 시스템에서 동시성이 터지는 대표 장면은 이렇습니다. 한 사용자가 거의 동시에 두 번 결제를 눌렀거나, 모바일/웹이 동시에 요청을 보내거나, 네트워크 재시도 때문에 같은 차감 요청이 겹쳐 들어옵니다. 이때 방어가 없으면 아래 같은 일이 생길 수 있습니다. 잔액이 충분하다고 판단한 두 요청이 동시에 차감을 진행해 잔액이 마이너스 가 된다 중복 차감이 발생한다(멱등성 문제와 결합) 원장과 balance 테이블이 순간적으로 어긋난다 이번 글에서는 “잠금(락)”을 어렵게 설명하기보다, 포인트 시스템에서 가장 현실적으로 쓰이는 동시성 방어 패턴을 중심으로 정리합니다. 이 단원의 목적: “읽고 판단 → 쓰기” 사이의 틈을 없애기 동시성 문제는 대부분 이 틈에서 발생합니다. 현재 잔액을 읽는다(충분하네) 차감한다 두 요청이 동시에 1번을 수행하면 둘 다 “충분하다”고 판단할 수 있습니다. 그래서 중요한 것은 1번과 2번 사이를 안전하게 만드는 것입니다. 이번 글의 목표는 아래 3가지입니다. 포인트 시스템에서 동시성 문제가 발생하는 구조를 이해한다 대표 잠금 패턴 2가지(행 잠금, 조건부 UPDATE)를 익힌다 원장(point_history)과 잔액(point_balance)을 함께 쓰는 경우의 안전한 흐름을 만든다 전제: 잔액을 어디에 두는가(원장만 vs balance 테이블) 원장만 쓰는 구조(매번 SUM으로 잔액 계산)는 개념적으로 단순하지만, “차감 가능 여부 확인”이 매번 무겁고, 동시성 제어도 구현 방식이 복잡해질 수 있습니다. 그래서 실무에서는 잔액을 별도 테이블(point_balance)로 캐시하고, 원장은 그대로 기록하는 “원장 + 잔액 캐시” 조합을 많이 씁니다. 이 글은 그 구조를 기준으로 설명합니다. -- 예시: 잔액 테이블 C...

지금까지는 “SQL이 정상적으로 실행된다”는 전제에서 원장과 조회를 만들었습니다. 하지만 실제 시스템은 언제든 실패할 수 있습니다. 네트워크가 끊길 수도 있고, 디스크가 꽉 찰 수도 있고, 잠깐의 락 경합으로 타임아웃이 날 수도 있습니다. 포인트처럼 숫자에 민감한 도메인에서 가장 위험한 상황은 여러 작업 중 일부만 성공하는 것 입니다. 예를 들어 다음 같은 경우는 운영에서 큰 문제로 이어집니다. 포인트 사용(차감) 원장은 기록됐는데, 주문 상태 업데이트가 실패했다 결제는 성공했는데, 포인트 적립 원장 기록이 실패했다 balance 테이블을 함께 쓰는데, 원장만 기록되고 balance 갱신이 실패했다 이런 “반쯤 성공”을 막기 위한 도구가 트랜잭션(Transaction) 입니다. 트랜잭션은 여러 SQL을 하나의 묶음으로 실행해서, 전부 성공하면 확정(COMMIT), 하나라도 실패하면 전부 되돌림(ROLLBACK)으로 처리합니다. 이 단원의 목적: 포인트 처리의 “원자성(Atomicity)” 확보 트랜잭션을 이해할 때 핵심은 “문법”이 아니라 “보장되는 성질”입니다. 이번 글의 목표는 아래 3가지입니다. 트랜잭션이 어떤 문제를 해결하는지(중간 실패, 일관성 깨짐)를 이해한다 포인트 적립/사용에서 트랜잭션을 어떻게 적용하는지 기본 패턴을 익힌다 balance 테이블을 함께 쓸 때 반드시 지켜야 할 트랜잭션 규칙을 정리한다 1) 트랜잭션의 핵심 개념: “전부 또는 전무(All or Nothing)” 트랜잭션은 여러 SQL을 하나의 작업처럼 다룹니다. 아래처럼 생각하면 이해가 빠릅니다. COMMIT: 지금까지의 변경을 “확정”한다 ROLLBACK: 지금까지의 변경을 “되돌린다” BEGIN/START TRANSACTION: 변경 묶음을 시작한다 가장 단순한 형태의 흐름은 이렇습니다. START TRANSACTION; -- 여러 작업 수행 -- 1) 포인트 원장 기록 -- 2) (필요 시) balance 갱신 -- 3) ...