[Section10~12] 데이터 무결성, 트랜잭션, 저장 프로시저/함수/트리거

[gusdn7142]

아래 형식으로 공유할 내용을 남겨주세요~!

새롭게 알게 된 점
어려웠거나 궁금했던 점
업무와 관련된 내용
기타 (자유 내용)

[gonasooc]

새롭게 알게 된 점

• 트랜잭션 섹션을 가장 재밌게 본 것 같아요. 그동안 귀동냥으로 어렴풋이 알고 있던 내용을 좀 더 명확하게 알 수 있어서 그랬던 거 같아요.

어려웠거나 궁금했던 점

• 내용이 어렵다기보다는 실무에서 이 내용을 나라면 어떻게 적용할까 상상해보는 게 까다롭게 느껴졌습니다. 그래서 오늘 아침에 저희 서버 개발자에게 물음표 살인마 시전했습니다. ORM 기반으로 뭔가 하나 해봐야 좀 더 와닿을 것 같아요.

업무와 관련된 내용

• 동시성 이슈가 생길 때 주로 어떻게 처리하시는지 궁금합니다. 저희 서버 개발자분한테 여쭤 보니 대체로 전체 롤백을 취한다고 하더라고요.
• 표준 격리 수준을 조정하신 경험이 있으신지, 혹시 있으시다면 어떤 상황에서 어떤 수준으로 조정하시게 됐는지 궁금해요.

[gusdn7142]

새롭게 알게 된 점 & 궁금했던 점

1. 관계형 데이터베이스의 근간이 되는 모델을 제시한 “에드거 프랭크 커드 (Edgar F. Codd)”

   published a paper describing a system that could store and access information without providing a formal organizational structure or even recording exact locations for data
   > 데이터의 물리적 저장 위치나 공식적인 구조를 알지 못해도 정보를 저장하고 접근할 수 있는 시스템을 제안

   • “A Relational Model of Data for Large Shared Data Banks.” (대규모 공유 데이터베이스를 위한 관계형 데이터 모델)을 설명하는 논문 발표
     • 핵심 목표 : 데이터 종속성 → 데이터 독립성으로의 변화 (데이터 무결성 강화)
       • 트리 or 포인터 구조에서 관계형 모델로의 전환
       • 앱 코드에 물리적인 경로(포인터)를 하드코딩하던 방식을 버리고, 앱은 논리적인 요청(SQL)만 보내도록 변경됨
       • 이에따라 물리적 구조(저장 방식)와 논리적 구조(앱 코드)가 분리됨

2. NOT NULL 컬럼에 값을 넣지 않았을 때 어떻게 되는가?

   "For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:”
   > INSERT / UPDATE 문에서 NOT NULL 컬럼에 대해 값을 포함하지 않거나, NULL로 설정시 Strict SQL mode에 따라 동작이 달라짐
   > Strict SQL Mode 활성화되어 있으면, 트랜잭션 테이블에 대해 에러가 발생하고 롤백됨
   > Strict SQL Mode가 비활성화된 경우, MySQL은 해당 컬럼을 컬럼 데이터 타입에 대한 암묵적 기본값을 설정
   > 암묵적 기본값 : INT → 0, VARCHAR → '', DATE → '0000-00-00'

3. Strict SQL Mode가 활성화 되었다는 것은?

   STRICT_TRANS_TABLES와 STRICT_ALL_TABLES 둘중 하나가 활성화 되었다는 것을 의미
   > STRICT_TRANS_TABLES : MySQL 8.0의 기본값에 포함된 모드로, 트랜잭션을 지원하는 스토리지 엔진(예: InnoDB)에 대해 엄격 모드를 활성화
   > STRICT_ALL_TABLES : 트랜잭션 지원 여부와 관계없이 모든 스토리지 엔진(예: MyISAM 포함)에 대해 엄격 모드를 활성화

   Strict SQL Mode가 활성화 되면, 1️⃣ NO_ZERO_IN_DATE, 2️⃣ NO_ZERO_DATE, 3️⃣ ERROR_FOR_DIVISION_BY_ZERO 같은 모드들에도 영향이 생김.

4. Strict SQL Mode로 인해 영향을 받는 SQL Mode

   • 1️⃣ NO_ZERO_IN_DATE (날짜의 월/일 부분에 0 금지)

     • 규칙: '2025-10-00'이나 '2025-00-10'처럼, 연도는 정상이지만 월(Month)이나 일(Day) 부분에 '0'이 포함된 날짜를 유효하지 않다고 봄
     • Mode OFF: '0000-00-00'으로 변환하여 삽입하고 경고만 띄움
     • Mode ON: 쿼리 실행을 중단하고 오류를 발생시킴

   • 2️⃣ NO_ZERO_DATE ('0000-00-00' 날짜 금지)

     • 규칙: '0000-00-00'이라는 값 자체를 유효하지 않다고 봄
     • Mode OFF: '0000-00-00' 삽입을 허용하고 경고만 띄움
     • Mode ON: 쿼리 실행을 중단하고 오류를 발생시캄

   • 3️⃣ ERROR_FOR_DIVISION_BY_ZERO (0으로 나누기 오류)

     • 규칙: INSERT나 UPDATE 같은 데이터 변경 작업 중 1/0처럼 0으로 나누는 연산이 발생하는 것을 유효하지 않다고 봄
     • Mode OFF: NULL 값을 삽입하고 경고만 띄움
     • Mode ON: 쿼리 실행을 중단하고 오류를 발생시킴

5. MySQL 5.0 부터 지원하기 시작한 Stored Procedure

   "Stored routines (procedures and functions) are supported in MySQL 5.0."
   > MySQL 5.0 릴리스에서 이 기능이 공식적으로 도입되었음

6. MySQL에서 Stored Procedure의 도입 배경

   “When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.”
   > 비즈니스 로직의 중앙화 : 서로 다른 플랫폼이지만, 동일한 데이터베이스에서 작업을 하기 위함

   "In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.”
   > 보안 강화 : 애플리케이션과 사용자는 데이터베이스 테이블에 직접 접근(SELECT, INSERT 등)할 수 없으며, 오직 특정 저장 루틴만 실행(EXECUTE)가능

   "Stored routines can provide improved performance because less information needs to be sent between the server and the client."
   > 네트워크 트래픽 감소 : 서버와 클라이언트 간에 전송되는 정보를 줄여줌

7. Modern 개발에서 프로시저를 기피하게 되는 이유
   • 1️⃣ 유지보수의 어려움 : 애플리케이션과 데이터베이스 로직의 분산
   • 2️⃣ 성능 및 확장성 문제 : 모든 로직이 DB에 집중되면 과부하 (+Scale In만 가능)
   • 3️⃣ 벤더 종속성 : 각 DB마다 다른 문법

[mixxeo]

오.. 1번에 공유해주신 논문 재미있을 것 같아요 클로드의 도움을 받아서 읽어봐야겠네용 ㅋㅋㅋ

[chordpli]

좋은 내용들 공유 너무 감사합니다!!

[witch-factory]

https://johngrib.github.io/wiki/clipping/e-f-codd/a-relational-model-of-data-for-large-shared-data-banks/

[chordpli]

업무와 관련된 내용

• 외부 서비스와 협력할 때 해당 서비스의 DB를 보게 되는 일이 있었는데, 프로시저가 400개가 넘고 관리가 안 되는 것을 실제로 본 적이 있습니다. 이슈가 발생할 때마다, 프로시저를 확인 하셨었는데 프로시저를 찾고 수정하는 일이 보통이 아니더라구요.
• Supabase에서 제공하는 Auth 서비스를 사용하게 되면 Trigger를 사용하도록 문서에 작성되어 있습니다. (Auth.User 테이블과 <서비스 스키마>.User 테이블의 싱크를 맞춰야 하기 때문입니다.)

기타

• 면접에서 나온 질문 공유합니다.
  • 하나의 비즈니스 로직에 이벤트 발행과, 데이터 저장 로직이 함께 존재할 때 트랜잭션 범위를 어떻게 묶을 것인가?
  • 각각 이벤트 드리븐을 할 때, MSA 간 발생하는 트랜잭션을 어떻게 구성하고 이슈 발생시 롤백할 것인가?
    • 데이터베이스는 아니지만, 꼬리질문으로 이벤트간의 정합성은 어떻게 맞출 것인가?

최근에 진행한 레거시 쿼리 개선 작업 간단 소개

• 발생한 문제
  • A 쿼리, B 쿼리에서 가져온 값을 C쿼리의 조건문의 IN절에 대입하는 상황 발생.
  • 수천~수만건의 쿼리가 IN 절에 들어갈 수 있고, 이에 Hang 상태 발생. (내부에서 Parsing하는 과정이 이루어지는데, 텍스트 연산 작업이므로 CPU와 메모리를 소모)
• 해결
  • A & B 를 union all 하여 '전체 대상' 임시 테이블로 정의 (CTE 사용, Common Table Expressions 로 With절을 의미)
  • IN 절로 처리하던 C 쿼리를 '제외 대상' 임시테이블로 정의
  • 이 둘(전체 대상을 기준으로 제외 대상)을 Left Join... Where key IS Null을 사용하여 Anti-Join(차집합) 시도.
• 결과
  • Hang으로 인하여 3분 이상 소요되던 쿼리가 3초 미만으로 정리
  • A, B, C를 각각 질의 하면서 발생하는 DB <-> App의 네트워크 통신 비용 절감

[gusdn7142]

유용한 정보 공유 감사합니다~

[ZetBe]

새로 배운 점

• 무결성 통해 데이터 체크
• 트랜잭션으로 중요한 프로세스를 묶어서 처리
  • 판매 - 재고 감소같이 동시애 해야하는 로직을 묶어서 처리
  • 트랜잭션 상황에서 어디까지 접근 가능한지 계층적으로 표시

• 저장 프로시저, 함수, 트리거로 거의 자동화
  • sql이 이렇게 보니 파이썬 같은 언어랑 비슷한 기능이 있구나 싶었습니다.
  • 이전까지 공부하면서 저장 프로시저 같이 여러 줄을 한줄로 바꾸는 기능을 생각했는데 마지막에 있네요
  • 다만 프론트엔드나 백엔드 처럼 아키텍처를 구성하고 버전관리하는 기능들이 없다는 것도 새로 배웠습니다. 근데 아키텍처가 있었다면 수업 커리큘럼에 있었겠네요

어려웠거나 궁금했던 점

강의에서는 애플리케이션에서 데이터 무결성을 체크하고 데이터베이스 쪽에서는 진짜 중요한 컬럼이 아니면 무결성을 체크를 지양한다는데, 이럴 경우 모종의 이유로 들어오면 안되는 데이터가 들어오는 경우를 무시하는지 궁금합니다.

그리고 자동으로 데이터를 저장하는 기능이 따로 백엔드에 있는지 궁금합니다.
일일히 벡엔드에서 값을 가져와 일일히 INCERT하진 않을 것 같아서 그렇습니다.
ai에게 물어보니 자바 스프링 orm에는 이 기능이 있고, 트리거를 언급하는걸 보니 트리거를 실제로 사용하시나요?

기타

사실 sql배우러 mysql강의를 들은 거라.. 이후 강의는 들을 의향이 없지만, 스터디 아니였으면 못들었을텐데 스터디 분들에게 감사합니다!

[chordpli]

개인적인 의견으로 답변을 남겨보겠습니다. 아닐수도 있어요!

애플리케이션에서 데이터 무결성을 체크하고 데이터베이스 쪽에서는 진짜 중요한 컬럼이 아니면 무결성을 체크를 지양한다는데, 이럴 경우 모종의 이유로 들어오면 안되는 데이터가 들어오는 경우를 무시하는지

모종의 이유로 데이터는 저장되어서 안된다고 생각합니다. 그런 상황이 발생하지 않도록 예외를 처리해야 한다고 생각해요.

자동으로 데이터를 저장하는 기능이 따로 백엔드에 있는지 궁금합니다

어떤 것을 어떤 상황에서 자동으로 데이터를 저장하는 것을 말씀하시는 걸까요?

트리거를 언급하는걸 보니 트리거를 실제로 사용하시나요?

저도 실무에서는 거의 사용한 적은 없으나, 딱 한 번 Supabase Auth 서비스를 이용할 때 사용했습니다.
이벤트처리처럼, 어떤 동작을 했을 때 함께 변경되어야 하는 로직이 필요하다면 사용할 수 있을 것 같아요.
하지만 강의처럼 그런 변동사항이 있으면 어플리케이션 단에서 처리할 것 같습니다.

[mixxeo]

애플리케이션에서 데이터 무결성을 체크하고 데이터베이스 쪽에서는 진짜 중요한 컬럼이 아니면 무결성을 체크를 지양한다는데, 이럴 경우 모종의 이유로 들어오면 안되는 데이터가 들어오는 경우를 무시하는지

준호님께서 말씀해주신 것처럼 로직상 유효성 검사를 합니다! 정말 단순하게는 상품 금액이 0이상인지 체크하는 로직 같은게 예가 될 수 있을 것 같아요

트리거를 언급하는걸 보니 트리거를 실제로 사용하시나요?

저는 DynamoDB를 메인으로 사용했던 적이 있는데, RDB와는 좀 다르지만.. 로그 생성이나 이벤트를 쏘거나 하는 작업을 DynamoDB Stream Listener로 걸어두는 식으로 트리거와 비슷한 기능을 활발하게 사용했었습니다

[ZetBe]

모종의 이유로 데이터는 저장되어서 안된다고 생각합니다. 그런 상황이 발생하지 않도록 예외를 처리해야 한다고 생각해요.

데이터베이스에도 거의 모든 컬럼에 무결성 체크한다고 보면 될까요?
백엔드에서 데이터베이스 서버로 네트워크 통신을 통해 이동을 한다지만, TCP 기능이 좋아도 매우 적은 확률로 데이터 모두 제대로 이동이 안될 수 도 있다고 생각해서 그렇습니다

어떤 것을 어떤 상황에서 자동으로 데이터를 저장하는 것을 말씀하시는 걸까요?

예를 들어 이커머스 같은 서비스에서 재고관리를 할 경우, 구매자가 구매 버튼을 누를 경우, 백엔드에서 이 이벤트를 인식하고 구매 내역을 데이터베이스에 incert로 입력인가요?

[mixxeo]

새롭게 알게된 점

• 트랜잭션 격리 수준과 동시성 문제

  • 동시성 문제: dirty read, 반복 불가능 읽기, 유령 읽기
  • 표준 격리 수준: read ucommitted, read committed, repeatable read, serializable

• 이와 관련한 문제를 경험하신 적이 있으신지 궁금합니다!

[chordpli]

저도 표준으로 지정된 설정만 사용해봐서 공유할만한 문제는 없는 것 같아요.
다만, 개인적으로 j-meter 같은 도구를 통해 동시성 테스트를 했을 때 serializable를 설정하면 경합상태로 인해서 요청한 수만큼 쿼리가 실행되지 않는 상태가 발생하는 경우는 있었습니다.

[SANGWOO-JOO]

다른 N개 이상의 API 서버가 동일한 데이터 베이스에 접근하도록 구성된 경우 격리성 단계를 상향하여 생각해야 했습니다.

[SANGWOO-JOO]

1. 제약 조건

제약 조건	방지 대상	범위	특징
NOT NULL	NULL 값	단일 컬럼	필수 데이터 누락 방지
UNIQUE	중복 값	단일 컬럼	고유성 보장
PRIMARY KEY	NULL + 중복	단일 컬럼	NOT NULL + UNIQUE, 테이블당 1개, 자동 인덱스
FOREIGN KEY	참조 무결성 위반	테이블 간	관계 일관성 보장, 유령 데이터 방지
CHECK	비즈니스 규칙 위반	단일 컬럼	값의 범위/조건 강제
DEFAULT	값 누락	단일 컬럼	기본값 자동 설정 (강제 아님)

2. 트랜잭션 격리 수준 동시성 제어

• 트랜잭션 격리 수준 : 여러 트랜잭션이 동시에 실행될 때 한 트랜잭션이 다른 트랜잭션의 변경 데이터를 얼마나 허용할지(즉, 얼마나 격리시킬지)를 나타내는 4단계의 레벨
• 데이터 불일치 현상 : 격리 수준이 완벽하지 않을 때(낮을 때) 여러 트랜잭션이 동시에 실행되면서 발생하는 이상현상

1. Read Committed는 커밋이 완료된 데이터만 읽을 수 있는 격리 수준으로, Dirty Read를 방지하면서도 적절한 성능을 제공

   graph TB
       subgraph "🟠 Read Committed - 커밋된 데이터만 읽기"
           direction TB
   
           subgraph "📖 정의"
               D1[커밋된 데이터만<br/>읽을 수 있는 격리 수준]
               D2[가장 일반적으로<br/>사용되는 수준]
               D3[Oracle, PostgreSQL<br/>기본 설정]
           end
   
           subgraph "🔧 동작 원리"
               W1[트랜잭션 A가<br/>데이터 수정]
               W2[커밋하지 않은 상태]
               W3[트랜잭션 B는<br/>수정 전 데이터 읽음]
               W4[A가 커밋하면<br/>B는 새 데이터 읽음]
   
               W1 --> W2 --> W3 --> W4
           end
   
           subgraph "🔐 Lock 메커니즘"
               L1[읽기: Shared Lock<br/>짧은 시간만 유지]
               L2[쓰기: Exclusive Lock<br/>트랜잭션 종료까지]
               L3[읽은 후 즉시<br/>Lock 해제]
           end
   
           subgraph "✅ 장점"
               P1[✔️ Dirty Read 방지]
               P2[⚡ 적절한 성능]
               P3[🎯 실용적인 균형]
           end
   
           subgraph "❌ 단점"
               C1[💥 Non-Repeatable Read 발생]
               C2[💥 Phantom Read 발생]
           end
   
           subgraph "🎯 사용 사례"
               U1[일반 웹 애플리케이션]
               U2[SNS 피드]
               U3[게시판 시스템]
               U4[대부분의 CRUD 작업]
           end
       end
   
       style D1 fill:#ffa94d
       style W4 fill:#ffc078
       style P1 fill:#d0ebff
       style U1 fill:#e7f5ff
   
   

   Loading

2. Dirty Read 시나리오

   sequenceDiagram
       participant A as 트랜잭션 A<br/>(계좌이체)
       participant DB as 데이터베이스<br/>(잔액: 10,000원)
       participant B as 트랜잭션 B<br/>(잔액 확인)
       participant System as 시스템<br/>(구매 결정)
   
       Note over DB: 초기 상태: 10,000원
   
       A->>DB: BEGIN
       A->>DB: UPDATE 잔액 - 1000
       Note over DB: 임시 상태: 9,000원<br/>⚠️ 미커밋
   
       B->>DB: BEGIN (Read Uncommitted)
       B->>DB: SELECT 잔액
       DB-->>B: 9,000원 반환
   
       Note over B: "9,000원이 있네!"
   
       B->>System: 9,000원 이하 상품 검색
       System-->>B: 구매 가능 상품 목록
       B->>DB: COMMIT
   
       Note over B: ✅ 구매 결정 완료
   
       A->>DB: ROLLBACK ❌
       Note over DB: 복구: 10,000원
   
       rect rgb(255, 107, 107)
           Note over A,System: 🚨 Dirty Read 발생!<br/>B는 존재하지 않는<br/>9,000원을 기준으로 결정
       end
   
   

   Loading

[witch-factory]

아직 못 들은 부분들이 꽤 많은데..
예전에 제가 주워들었던 내용으로 제약 조건 같은 걸 실무에서는 DB단에서 거의 쓰지 않는다는 말이 있었는데, 왜 그런지 이제서야 알게 되어서 좋았습니다.
트랜잭션은 아직 듣고 있는 중인데 자주 듣던 ACID를 이제야 뭔지 좀 알게 되는 거 같습니다.

[chordpli]

추가로 제약조건이 걸려있으면, 마이그레이션을 진행할 때에도 큰 장애 요소가 될 수 있어서 지양하고 있습니다..!

[sylee6529]

알게된점

• CASCADE 옵션은 의도치 않은 대량 데이터 삭제를 유발할 수 있어, 실무에서는 잘 사용하지 않고 애플리케이션 계층에서 로직을 처리하는 것을 선호한다는거
• 트랜잭션 격리수준 동시성 제어

spring jpa 사용하면서 써봤던 이 애너테이션이 db insert, update 전에 실행되는거는 알고있었는데, 내부적으로 트리거를 통해 동작하는건지 찾아보고 알았습니다