MySQL ‐ Advanced Topics in MySQL

MySQL - Advanced Topics in MySQL

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

START TRANSACTION
       ↓
   [ Active ]           ← SQL 실행 중
       ↓
[ Partially Committed ] ← COMMIT 명령어 입력됨
       ↓
   디스크 반영 시도
    ↙        ↘
[Committed]  [Failed → Aborted]

❓Active 상태

START TRANSACTION;

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

• 변경 내용은 Buffer Pool(메모리)에만 기록된다.
• Undo Log에 변경 전 데이터를 기록한다.(롤백 대비)
• Redo Log Buffer에 변경 내역을 순차 기록한다.
• 디스크에 아직 아무것도 쓰이지 않는다.

[ 메모리 ]
  Buffer Pool     ← 변경된 데이터 페이지 (Dirty Page)
  Redo Log Buffer ← 변경 로그 임시 보관
  Undo Log        ← 롤백용 이전 데이터

❓Partially Committed

• 애플리케이션에서 COMMIT 명령어를 보내면 InnoDB는 아직 처리 중인 상태가 된다.

COMMIT 명령 수신
       ↓
Redo Log Buffer → Redo Log File(디스크) 로 flush
       ↓
fsync() 호출 → OS 버퍼까지 완전히 디스크에 내려씀
       ↓
성공하면 → Committed
실패하면 → Failed

• 이 때, innodb_flush_log_at_trx_commit 값별 동작은 다음과 같이 이루어진다.

값	동작	데이터 안정성	성능
0	1초마다 flush + fsync	최대 1초치 유실 가능	가장 빠르다
1	COMMIT마다 flush + fsync	완전 보장	가장 느리다
2	COMMIT마다 flush, fsync는 1초마다	OS 크래시 시 유실 가능	중간

• 기본값은 1(=ACID 완전 보장)이다.

❓Committed

• Redo Log에 성공적으로 기록되고 나면 트랜잭션은 완전히 종료된다.

Redo Log 디스크 기록 완료
          ↓
클라이언트에 "OK" 응답 반환
          ↓
백그라운드에서 Buffer Pool의 Dirty Page를
천천히 디스크에 반영 (Checkpoint)

• 이 때, Committed 상태가 되었다고 해서 데이터 파일에 즉시 반영되는 것이 아니다.
• Redo Log에 기록된 것이 보장의 근거이고 실제 데이터 파일 반영은 이후 CheckPoint 과정에서 이루어진다.

❓Failed → Aborted

오류 감지 또는 ROLLBACK 명령
          ↓
Undo Log를 역순으로 읽어서
변경 사항을 되돌림
          ↓
Undo Log 정리
          ↓
Aborted (트랜잭션 완전 취소)

• Redo Log가 "무엇을 했는가"에 대한 기록이라면 Undo Log는 "어떻게 되돌릴 것인가"에 대한 기록이다.

START TRANSACTION
      ↓
   Active
   ├── Buffer Pool에 Dirty Page 생성
   ├── Undo Log에 이전 데이터 기록
   └── Redo Log Buffer에 변경 로그 기록
      ↓
COMMIT 명령 수신
      ↓
Partially Committed
   └── Redo Log Buffer → 디스크 flush (fsync)
      ↓
   ┌──────────────────────┐
성공                    실패
   ↓                      ↓
Committed             Failed → Aborted
(Redo Log 보장)       (Undo Log로 롤백)

• 결국 트랜잭션의 내구성(Durability)은 Redo Log의 디스크 기록이 보장하고, 원자성(Atomicity)은 Undo Log의 롤백이 보장한다.

SHOW VARIABLES LIKE 'autocommit';  -- 기본값 ON (1)

• Autocommit은 "DML 하나하나를 자동으로 트랜잭션으로 감싸줄 것인가"에 대한 설정이다.

❓Autocommit = 1(기본값)

UPDATE users SET last_login = NOW() WHERE id = 1;

• 위의 쿼리는 내부적으로 이렇게 동작한다.

UPDATE 실행
    ↓
trx_start_if_not_started()   ← InnoDB가 암묵적으로 트랜잭션 시작
    ↓
변경 수행 (Buffer Pool, Undo Log, Redo Log)
    ↓
ha_commit_trans()            ← InnoDB가 자동으로 COMMIT

• 개발자가 아무런 액션을 취하지 않아도 SQL 하나가 곧 하나의 트랜잭션이 된다.

❓Autocommit = 0

SELECT * FROM users WHERE id = 1;

autocommit = 0 상태에서 SELECT 실행
              ↓
     Read View 생성
     (일관된 읽기 스냅샷 확보)
              ↓
     이후 DML 실행 시
     트랜잭션 ID 부여

• SELECT만 실행해도 Read View가 생성된다는 점이 핵심이다. 이후 다른 트랜잭션이 데이터를 변경해도 이 Read View를 기준으로 데이터를 읽게 된다.
• autocommit = 0인 상태에서 명시적 Commit 없이 커넥션을 끊으면 자동 RollBack이 된다. 커넥션 풀 환경에서 설정이 세션에 남아 있게 된다면 예상치 못한 롤백이 발생할 수 있다.

❓Implicit vs Explicit 트랜잭션

START TRANSACTION;  -- 또는 BEGIN
INSERT INTO orders (user_id, product_id, amount) VALUES (1, 100, 50);
-- 이 시점에서 트랜잭션 ID 부여
INSERT INTO order_items (order_id, item_id) VALUES (LAST_INSERT_ID(), 200);
COMMIT;

START TRANSACTION
       ↓
트랜잭션 ID 부여 (첫 DML 시점)
       ↓
모든 변경이 하나의 원자 단위로 묶임
       ↓
COMMIT or ROLLBACK으로 명시적 종료

• 여러 테이블을 묶어서 원자성을 보장해야 할 때 사용한다.

-- autocommit = 1 상태
UPDATE users SET last_login = NOW() WHERE id = 1;
-- SQL 끝나는 순간 자동 커밋

• 각 SQL이 독립된 트랜잭션으로 처리된다. 빠르고 단순하지만 여러 테이블을 묶을 순 없다.

❓DDL의 암묵적 커밋

START TRANSACTION;
INSERT INTO temp_data VALUES (1, 'test');   -- ← 아직 커밋 안 됨
CREATE TABLE new_table (id INT);            -- ← DDL 실행 순간
-- INSERT가 ROLLBACK될 것으로 기대했지만
-- DDL이 암묵적 COMMIT을 발생시켜 INSERT도 함께 커밋됨
ROLLBACK;  -- 이미 늦음. INSERT는 이미 커밋된 상태

START TRANSACTION
       ↓
INSERT (아직 롤백 가능)
       ↓
CREATE TABLE 실행
       ↓
암묵적 COMMIT 발생 ← INSERT까지 함께 커밋
       ↓
ROLLBACK 불가

• DDL이 암묵적 커밋을 유발하는 이유는 DDL 자체가 트랜잭션으로 묶일 수 없는 작업이기 떄문이다.
• MySQL은 DDL 실행 이전에 현재 진행 중인 트랜잭션을 강제로 커밋한다.
• 그래서 트랜잭션 안에서 DDL을 절대 섞지 않아야 한다.

❓MySQL Binlog

포맷	기록 방식	장점	단점
SBR(Statement-Based)	SQL 문장 그대로	로그 용량 작음	NOW(), RAND()등 비결정적 함수로 복제 불일치 위험 존재
RBR(Row-Based)	변경된 행의 Before/After 이미지	정확한 복제, 안전	대량 UPDATE시 로그 용량 폭증
MIXED	상황에 따라 SBR/RBR 자동 선택	두 방식 절충	예측이 어려워 디버깅 복잡

• biglog는 MySQL이 데이터 변경 사항을 순서대로 기록하는 로그 파일이다.
  • 복제(Replication) : Source DB → binlog 기록 → Replica DB가 읽어서 동일하게 재실행
  • 특정 시점 복구(Point-in-time Recovery) : 백업 시점 + binlog를 통해 원하는 시점으로 복구가 가능
• SBR이 적합한 경우 : 단순 DDL 위주, 로그 용량이 중요한 환경
• RBR이 적합한 경우 : 데이터 정합성이 중요한 금융/커머스 도메인, READ COMMITTED 격리 수준 사용 시

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

❓S-lock / X-lock (행 레벨 핵심)

-- S-lock (Shared Lock)
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- X-lock (Exclusive Lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;```

• S-lock(Shared Lock, 공유 락)은 읽기 목적으로 사용되며 다른 트랜잭션도 같은 행에 S-lock 사용이 가능하나 아무도 수정할 수 없다.
• X-lock(Exclusive Lock, 배타 락)은 쓰기 목적으로 사용되며 다른 트랜잭션에서 S-lock, X-lock 모두 사용 불가하다. INSERT, UPDATE, DELETE시 자동으로 걸리게 된다.

❓IS / IX Lock — 의사 표시

-- FOR UPDATE 실행 시
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 내부적으로 일어나는 일
-- 1단계: 테이블에 IX-lock 자동 설정
-- 2단계: id=1 행에 X-lock 설정

❓Next-Key Lock — 범위 조회 시 자동

-- id: 10, 20, 30 존재
SELECT * FROM t WHERE id > 10 FOR UPDATE;

• Phantom Read 방지 목적으로 내부적으로 넓게 잡는다.
• 다만 이게 항상 좋은 것은 아니고 다음과 같은 문제도 발생할 가능성이 있다.

-- 의도: id=20 하나만 잠그고 싶었는데
SELECT * FROM t WHERE id > 10 FOR UPDATE;
-- 실제: 10 이상 전체 범위가 잠김
-- → 다른 트랜잭션 INSERT 전부 대기

❓Insert Intention Lock — INSERT 시 자동

INSERT INTO t VALUES (15, 'a');
-- (10, 20) Gap에 Insert Intention Lock 자동 설정

-- id: 10, 30 존재
-- T1: INSERT (15) ─┐ 둘 다 (10,30) Gap이지만
-- T2: INSERT (20) ─┘ 위치가 달라서 서로 허용 ✅

-- 만약 Gap Lock이 있으면
SELECT * FROM t WHERE id > 10 AND id < 30 FOR UPDATE;  -- Gap Lock
INSERT INTO t VALUES (15, 'a');  -- 대기 ❌

MySQL InnoDB Internals [ Crash Recovery, Redo Log & MVCC ]

Transaction Isolation Levels [ Read Phenomena, MVCC & Snapshot ]

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 ]