MySQL ‐ Advanced Topics in MySQL

MySQL - Advanced Topics in MySQL

MySQL Transaction Deep Dive(LifeCycle, Autocommit, Statement vs Row Based)

데이터베이스 트랜잭션 상태 변화 흐름

1. 트랜잭션 시작 (START TRANSACTION)

• 데이터베이스 작업 단위인 트랜잭션이 처음 시작되는 단계이다.

2. 활성 상태 (Active)

• 실제로 SQL 쿼리문(INSERT, UPDATE, DELETE 등)이 실행 중인 상태이다.
• 데이터가 아직 메모리 상에서만 처리되고 있다.

3. 부분 완료 상태 (Partially Committed)

• 모든 SQL 작업이 끝나고 COMMIT 명령어가 입력된 상태이다.
• 논리적인 연산은 모두 성공적으로 끝났지만, 그 결과가 아직 물리적인 디스크(데이터베이스)에 영구적으로 기록되지는 않은 상태이다.

4. 디스크 반영 시도 및 결과

• 부분 완료 상태에서 실제 디스크에 변경 사항을 저장(반영)하려는 시도를 하며, 그 결과에 따라 다음 두 가지 상태 중 하나로 전환한다.
• 성공 시 ➔ 완료 (Committed)
  • 디스크에 데이터를 기록하는 데 성공한 상태이다.
  • 트랜잭션이 성공적으로 종료되며, 변경 사항이 영구적으로 적용된다.
• 실패 시 ➔ 실패 및 철회 (Failed ➔ Aborted)
  • Failed : 시스템 오류 등으로 인해 디스크 반영에 실패하거나 트랜잭션을 정상적으로 진행할 수 없는 상태이다.
  • Aborted : 실패(Failed) 상태가 확인되어, 트랜잭션에서 수행했던 모든 작업을 취소(ROLLBACK)하고 트랜잭션 시작 이전 상태로 되돌려진(철회된) 상태이다.

트랜잭션 활성(Active) 상태 분석

• 활성 상태는 START TRANSACTION 이후 실제 SQL 쿼리들이 실행되고 있는 단계로, 물리적인 디스크가 아닌 메모리 상에서만 데이터 변경이 이루어지는 것이 핵심이다.

START TRANSACTION;

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

• 이 단계에서는 트랜잭션의 안전성과 시스템 복구 능력을 보장하기 위해 데이터베이스의 주요 메모리 및 로그 영역이 다음과 같이 작동한다.
• Buffer Pool (버퍼 풀 / 메모리)
  • 실제 데이터 변경 작업이 일어나는 메인 작업 공간이다.
  • 변경된 데이터는 현재 메모리(Buffer Pool) 상에만 반영되어 있다.
• Undo Log (언두 로그)
  • 데이터가 변경되기 전의 원본 상태를 기록한다.
  • 작업 중 에러가 발생하거나 ROLLBACK 명령이 내려졌을 때, 데이터를 수정 이전 상태로 안전하게 되돌리기(취소하기) 위해 사용한다.
• Redo Log Buffer (리두 로그 버퍼)
  • 어떤 데이터가 어떻게 변경되었는지 그 변경 내역을 순차적으로 기록한다.
  • 아직 디스크에 저장되지 않은 상태에서 시스템 장애(정전 등)가 발생했을 때, 잃어버린 작업을 재실행(Redo)하여 데이터를 복구하기 위해 사용한다.
• Disk (물리적 하드 디스크)
  • 이 단계에서는 실제 디스크의 데이터 파일에 아무런 변경 사항도 쓰이지 않는다.

트랜잭션 부분 완료(Partially Committed) 상태 분석

1. 디스크 반영(Commit) 처리 흐름

• COMMIT 명령이 떨어지면 InnoDB 엔진 내부에서는 다음과 같은 작업이 순차적으로 일어난다.
  • COMMIT 명령 수신 : 트랜잭션을 끝내고 데이터를 저장하라는 신호를 받는다.
  • 플러시(Flush) : 메모리(Redo Log Buffer)에 모아둔 변경 내역을 디스크의 파일(Redo Log File)로 보낸다.
  • fsync() 호출 : 버퍼에 들고 있지 말고 지금 당장 물리적인 하드 디스크에 확실히 저장하라는 강제 동기화 명령을 보낸다.
  • 상태 결정 : 위 과정이 성공적으로 끝나면 ➔ 완료(Committed) 상태가 된다. 단, 디스크 용량 부족이나 하드웨어 장애로 실패하면 ➔ 실패(Failed) 상태가 된다.

innodb_flush_log_at_trx_commit

• innodb_flush_log_at_trx_commit 옵션의 설정값에 따라 데이터의 안정성(ACID 보장)과 처리 성능이 크게 달라진다.

설정값	동작 방식	데이터 안정성(장애 발생시)	성능
1(기본값)	매 Commit마다 Flush + fsync() 실행	완벽 보장(어떤 장애가 발생해도 데이터가 유실되지 않음(ACID 원칙 준수)	가장 느림(디스크 I/O 성능 병목 발생)
0	1초마다 주기적으로 Flush + fsync() 실행	최대 1초치 데이터 유실 위험(DB 프로세스가 비정상적으로 종료되면 유실)	가장 빠름
2	매 Commit마다 Flush 실행 + fsync()`는 1초마다 실행	OS 크래시 시 유실 위험(DB가 죽어도 데이터는 안전하지만 서버 전원이나 OS 자체가 죽으면 유실)	중간

• 대부분의 중요한 서비스(결제, 회원 정보 등등)에서는 성능을 조금 희생하더라도 데이터의 절대적인 안전을 위해 기본값인 1을 사용한다.
• 반면 로그 수집이나 단순 통계처럼 약간의 데이터 유실이 치명적이지 않으면서 쓰기 성능이 극도로 중요할 때는 0이나 2를 고려할 수 있다.

완료(Committed) 상태 분석

1. 트랜잭션 최종 완료 흐름

• Redo Log 디스크 기록 완료 : 변경 내역이 디스크의 안전한 영역인 Redo Log File에 쓰인다.
• 클라이언트에 "OK" 응답 : 데이터베이스가 애플리케이션(사용자)에게 작업이 성공적으로 끝났음을 알린다.
• 백그라운드 동기화 (Checkpoint) : 사용자는 이미 다음 작업을 하고 있는 동안, 데이터베이스는 내부적으로 바뀐 데이터를 실제 데이터 파일에 반영한다.

2. 핵심 개념: 성능을 위한 지연 쓰기(Lazy Writing)

• 매번 트랜잭션이 끝날 때마다 거대한 실제 데이터 파일을 찾아가서 수정하는 것은 매우 느리고 무거운 작업이다.
• InnoDB는 다음과 같은 전략을 사용한다.
  • 보장의 근거는 Redo Log : 시스템이 재부팅될 때 이 로그를 읽어와서 데이터를 완벽히 복구(Redo)한다.
  • 더티 페이지(Dirty Page) : 메모리(Buffer Pool) 상에서는 최신 데이터로 변경되었지만, 아직 디스크의 실제 데이터 파일에는 반영되지 않은 상태의 메모리 공간
  • 체크포인트(Checkpoint) : 데이터베이스가 비교적 한가할 때(백그라운드 프로세스), 이 더티 페이지들을 모아서 한 번에 실제 디스크의 데이터 파일에 반영하는 작업이다. 이를 통해 디스크 I/O를 최소화하여 성능을 끌어올린다.

실패 및 철회(Failed ➔ Aborted) 상태 분석

1. 롤백(Rollback) 처리 흐름

• 오류 감지 / ROLLBACK 명령 : 작업 중 문제가 생기거나 취소 명령이 떨어진다. 이 시점을 Failed 상태로 본다.
• Undo Log 역순 탐색 : 데이터베이스는 안전하게 백업해둔 Undo Log(언두 로그)를 가장 최근 기록부터 거꾸로 읽어 들인다.
• 변경 사항 되돌리기 : 메모리(Buffer Pool)에 수정해두었던 데이터들을 Undo Log에 적힌 원본 데이터로 덮어씌워 복원한다.
• 완전 취소(Aborted) : 데이터가 트랜잭션 시작 이전으로 완벽히 돌아가면, 사용했던 Undo Log를 정리하고 트랜잭션을 완전히 폐기한다.

2. 핵심 개념: 트랜잭션의 두 가지 타임머신

• Redo Log(리두 로그) : 해당 로그는 수행했던 작업을 다시 실행(Redo)하여 복구하는데 쓰인다.
• Undo Log(언두 로그) : 작업 중 문제가 생겼을 때, 과거 상태로 돌아가는데 쓰인다.

자동 커밋(Autocommit = 1) 동작

• 단일 SQL 쿼리를 실행할 때마다 데이터베이스가 시작과 끝을 관리해 귀찮은 수작업 없이도 데이터를 즉시 안전하게 저장해준다.

수동 커밋(Autocommit = 0) 동작 분석

1. 실무에서 겪는 치명적인 장애 포인트(주의사항)

• 무한 대기 및 스냅샷 유지
  • 명시적인 COMMIT이나 ROLLBACK을 하지 않으면, 트랜잭션은 영원히 끝나지 않는다.
  • 데이터베이스는 그 오래된 'Read View'를 유지하기 위해 과거 데이터(Undo Log)를 지우지 못하고 계속 쌓아두어야 하며, 이는 DB 성능 저하와 용량 부족으로 직결된다.
• 커넥션 끊김 = 자동 롤백 (Auto Rollback)
  • COMMIT을 깜빡하고 데이터베이스와의 연결(Connection)을 끊어버리면, 데이터베이스는 비정상 종료로 간주하고 지금까지 했던 모든 작업을 자동으로 ROLLBACK(취소) 처리한다.

명시적 트랜잭션 vs 암묵적 트랜잭션 핵심 비교

1. 명시적 트랜잭션 (Explicit Transaction)

• 개발자가 직접 트랜잭션의 시작과 끝을 선언(명시)하여 여러 작업을 하나의 묶음으로 통제하는 방식이다.
• 동작 흐름
  • START TRANSACTION (또는 BEGIN) 명령어로 시작을 알린다.
  • 첫 번째 DML(INSERT, UPDATE 등)이 실행되는 순간, 데이터베이스 내부적으로 고유한 '트랜잭션 ID'가 부여된다.
  • 이후 실행되는 모든 쿼리들은 이 트랜잭션 ID 아래 하나의 원자 단위(Atomic Unit)로 강력하게 묶인다.
  • 모든 작업이 정상적으로 끝나면 COMMIT으로 저장하고, 하나라도 실패하면 ROLLBACK으로 전체 취소하여 명시적으로 종료된다.

2. 암묵적 트랜잭션 (Implicit Transaction)

• 개발자가 따로 선언하지 않아도, 데이터베이스(기본적으로 autocommit = 1 상태)가 알아서 개별 SQL마다 트랜잭션을 열고 닫아주는 방식이다.
• 동작 흐름
  • UPDATE users ... 와 같이 단일 SQL 쿼리를 실행한다.
  • 쿼리가 시작될 때 DB가 암묵적으로 트랜잭션을 연다.
  • 쿼리 하나가 에러 없이 끝나는 즉시, DB가 자동으로 COMMIT을 실행한다.

DDL의 암묵적 커밋 (Implicit Commit) 분석

• 절대 섞어 쓰지 말 것 : 트랜잭션(START TRANSACTION ~ COMMIT/ROLLBACK) 내부에는 오직 데이터를 조작하는 DML(SELECT, INSERT, UPDATE, DELETE)만 넣어야 한다.
• DDL은 트랜잭션 밖에서 : 테이블을 생성하거나 구조를 변경하는 DDL 작업은 반드시 트랜잭션과 철저히 분리하여 독립적으로 실행해야 예상치 못한 데이터 강제 저장을 막을 수 있다.

MySQL Binlog(바이너리 로그) 분석

• Binlog는 데이터베이스에 발생한 모든 변경 사항(INSERT, UPDATE, DELETE, DDL 등)을 시간 순서대로 기록한다.
• 복제(Replication) : 메인 DB(Source/Master)가 자신이 한 일을 Binlog에 적어두면, 보조 DB(Replica/Slave)들이 이 로그를 가져가서 자기 자신에게 똑같이 따라 실행한다. 이를 통해 여러 대의 DB가 완벽히 똑같은 데이터를 유지하게 된다.
• 특정 시점 복구(PITR: Point-in-Time Recovery) : 개발자가 실수로 WHERE 절 없이 DELETE를 날렸을 때, 어제 새벽에 받아둔 '풀 백업본'을 복원한 뒤, 백업 이후부터 사고 발생 직전까지의 Binlog 기록을 쭉 재생(Replay)하여 데이터를 완벽히 복원할 수 있다.

기록 방식	동작 원리	장점	치명적 단점 및 주의사항
SBR	작성한 SQL 문장 자체를 그대로 복붙	로그에 텍스트 한 줄만 적히므로 파일 용량이 매우 작고 가볍다.	데이터 불일치 대참사 위험 - 비결정적 함수가 실행하는 시점에 따라 결과값이 달라져 데이터가 틀어진다.
RBR	변경된 실제 데이터의 Before/After 값을 기록	SQL이 아닌 결과값 자체를 복사하기에 가장 정확하고 안전한 데이터 복제가 보장된다.	로그 용량 폭발 - N건을 한 번에 UPDATE하면 로그 파일에도 N건 줄의 변경 내역이 기록되어 디스크와 네트워크에 큰 부담을 준다.
MIXED	평소엔 SBR로 가볍게 적다가 위험한 비결정적 함수를 만나면 알아서 RBR로 변경해 기록한다.	데이터 안정성과 용량 절약이라는 타협한다.	언제 어떤 방식으로 기록되었는지 예측하기 어려워 시스템 장애 시 원인 파악하기가 매우 어렵다.

MySQL Lock Deep Dive(Intention Lock, Gap Lock & Granularity Levels)

1. S-lock과 X-lock(가장 기본이 되는 '행(Row)' 잠금)

• S-lock (Shared Lock / 공유 락) : 데이터를 안전하게 읽기 위해 사용한다.
  • SELECT ... LOCK IN SHARE MODE
  • S-lock을 걸고 읽는 동안, S-lock을 걸고 같이 읽을 수 있다.
  • 하지만 누군가 이 데이터를 수정(X-lock)하려 한다면 읽기를 마칠 때까지 대기해야 한다.
• X-lock (Exclusive Lock / 배타 락) : 데이터를 안전하게 쓰기(수정/삭제)위해 사용한다.
  • SELECT ... FOR UPDATE
  • X-lock을 쥐고 있으면 다른 사람은 S-lock, X-lock 둘 다 걸 수 없다.

##2. IS / IX Lock(테이블에 거는 '의사 표시' 잠금)**

• IS / IX Lock (테이블에 거는 '의사 표시' 잠금)
  • 특정 행에 X-lock을 걸려면 엔진은 몰래 테이블 전체에 먼저 IX-lock을 걸고 그 다음에 해당 행애 X-lock을 건다.
  • 존재 이유(성능 최적화)

3. Next-Key Lock(범위 조회 시 걸리는 '광역' 잠금)

• 조건문으로 여러 행을 조회할 때 발생하는 방어막이다.
• WHERE id > 10 FOR UPDATE처럼 범위를 검색하면, 존재하는 데이터(10, 20, 30)뿐만 아니라 그 사이의 빈 공간(Gap)까지 모조리 잠가버린다.
• 장점(Phantom Read 방지) : 10~30 사이의 데이터를 작업하고 있는데, 다른 측에서 id=15를 새로 끼워 넣는(INSERT) 작업을 완벽히 차단한다.
• 오버 락킹 : 예를 들어, 데이터 하나만 잠그려고 했는데 범위로 지정하게 되면 10 이상의 모든 공간이 잠겨버려서, 다른 트랜잭션의 정상적인 INSERT들이 전부 멈춰(대기 상태)버리는 치명적인 성능 저하가 발생할 수 있다. 그렇기 때문에 락을 항상 필요한 행만 정확하게 짚어서 걸어야 한다.

4. Insert Intention Lock(INSERT 전용 틈새 잠금)

• 새로운 데이터를 끼워 넣기 전에 그 빈 공간에 거는 락을 말한다.
• 공간을 완전히 잠그는 일반적인 Gap Lock과 달리, 이 락은 위치만 다르다면 서로 방해하지 않는다.
• 즉, A가 15번을 넣고, 동시에 B가 20번을 넣어도 서로 목표 위치가 다르기 때문에 대기 없이 쾌적하게 동시에 데이터가 입력(INSERT)된다.

MySQL InnoDB Internals(Crash Recovery, Redo Log & MVCC)

1. Undo Log (언두 로그): 롤백과 동시성 제어의 핵심

• 데이터가 변경되기 전의 원본 상태(과거 데이터)를 저장한다.
• ROLLBACK(작업 취소) : 트랜잭션 실행 중 오류가 발생하거나 명시적으로 롤백할 때, 언두 로그에 기록된 이전 값을 가져와 덮어씌움으로써 원래 상태로 복구한다.
• MVCC(다중 버전 동시성 제어) : 다른 트랜잭션이 아직 커밋되지 않은 데이터를 수정하고 있을 때, 또 다른 트랜잭션이 조회를 요청하면 언두 로그에 있는 수정 전 원본 데이터를 보여준다. 이를 통해 락(Lock)을 걸지 않고도 대기 없이 안전한 읽기가 가능하다.
• SHOW ENGINE INNODB STATUS를 통해 확인할 수 있는 HLL(History List Length)는 현재 유지되고 있는 언두 로그의 크기를 말한다. 이 수치가 계속 높아지면 성능 문제가 발생할 수 있으며, 불필요해진 로그는 백그라운드 Purge 쓰레드가 알아서 정리한다.

2. Redo Log & Crash Recovery: 데이터 영구 보존의 마법

• 성능을 위해 메모리(버퍼 풀)에서만 수정된 데이터가 갑작스러운 정전 등으로 날아가는 것을 막기 위해, 변경 내역을 디스크에 빠르게 순차 기록하는 WAL(Write-Ahead Logging) 공간이다.
• Roll-forward(전진) : 트랜잭션이 성공적으로 COMMIT 되었으나 실제 데이터 파일에는 미처 반영되지 못하고 죽은 경우, 리두 로그를 보고 그대로 다시 실행하여 복원한다.
• Roll-backward(후진) : COMMIT 되지 않은 상태로 죽은 트랜잭션의 찌꺼기는 언두 로그를 활용해 깔끔하게 취소(롤백)한다.

3. MVCC와 Read View: 과거 데이터를 찾아가는 원리

• 하나의 데이터 행(Row)에 대해 여러 버전을 관리하여, 읽기 작업과 쓰기 작업이 서로 간섭하지 않게 만드는 원리이다.
• 숨겨진 메타 데이터 : InnoDB의 모든 테이블 행에는 사용자 눈에 보이지 않는 중요한 식별이 붙어 있다.
  • DB_TRX_ID : 이 행을 마지막으로 수정한 트랜잭션의 고유 번호
  • DB_ROLL_PTR : 수정되기 전 과거 데이터가 저장된 언두 로그 주소
• Read View : 특정 트랜잭션이 조회를 시작할 때, 현재 실행 중인 트랜잭션들의 ID 목록을 바탕으로 기준점을 만든다.

Transaction Isolation Levels(Read Phenomena, MVCC & Snapshot)

격리 수준(Isolation Level) 4단계

Lv 1. READ UNCOMMITTED(커밋되지 않은 읽기)

• MVCC(다중 버전 제어)를 무시하고, 메모리(Buffer Pool)에 있는 최신 데이터를 무작정 읽는다.
• 성능은 가장 빠르지만, Dirty Read가 발생하는 치명적인 문제가 있다. 남이 취소(Rollback)할지도 모르는 가짜 데이터를 기반으로 비즈니스 로직이 돌아가면 시스템이 망가질 수 있어 실무에서는 절대 쓰지 않는다.

Lv 2. READ COMMITTED(커밋된 읽기)

• 쿼리(SELECT)를 실행할 때마다 새로운 Read View(스냅샷)를 생성한다.
• 남이 커밋하기 전에는 Undo Log의 과거 데이터를 읽는다.
• Dirty Read는 막아준다. 하지만 트랜잭션 도중에 남이 커밋을 해버리면 다음 SELECT 때 그 변경된 값을 읽어버리므로 Non-Repeatable Read와 Phantom Read가 발생한다.

Lv 3. REPEATABLE READ(반복 가능한 읽기)

• 트랜잭션이 시작되는 순간(첫 SELECT)에 Read View를 딱 한 번만 생성하고, 트랜잭션이 끝날 때까지 그 기준만 잡고 있다.
• 남이 중간에 커밋을 하든 말든 나는 내 Undo Log를 통해 과거 버전만 일관되게 읽는다.
• 자신이 두 번 읽는 동안 값이 변하지 않기 때문에 Non-Repeatable Read가 방지된다.

Lv 4. SERIALIZABLE(직렬화)

• 단순한 조회(SELECT)조차도 내부적으로 읽기 잠금(LOCK IN SHARE MODE, S-lock)을 걸어버린다.
• 데이터를 읽고 있으면(S-lock 사용) 절대 수정(X-lock)을 할 수 없다. 모든 이상 현상을 완벽하게 방지하지만, 동시 처리 능력이 바닥으로 떨어져 실무에서 거의 사용하지 않는다.

MySQL Query Optimizer [ Query Rewriting, Cost Model & Histogram ]

MySQL Performance [ Filesort, Temporary Tables & Partitioning ]

MySQL Storage Architecture [ InnoDB Page Structure & Layout ]

MySQL B-Tree Index [ Clustered vs Secondary, Page Format ]

MySQL Buffer Pool [ Page Management, LRU & Eviction Strategy ]

MySQL Durability [ Doublewrite Buffer, WAL & FSync Performance ]