[SQL] DML 실행시 데이터베이스 프로세스

DML 실행시 데이터베이스 프로세스

데이터베이스에서 DML(Data Manipulation Language) 명령(INSERT, UPDATE, DELETE 등)을 실행할 때, 내부적으로 어떤 일이 벌어지는지를 보는 것은 중요함.

여기서 다루는 주요 개념과 동작 원리는 전통적인 RDBMS(Oracle, PostgreSQL, MySQL InnoDB 엔진 등)를 기준으로 설명하며, 구체적인 구현 세부 사항은 DBMS 제품마다 다를 수 있음을 염두에 둬야 함.

 

SQL 명령 수신 및 파싱

1. SQL 명령 수신

클라이언트 애플리케이션에서 SQL 문을 DB 서버에 전송함.

DB 서버는 해당 SQL 문을 받아 내부 프로세스(파서, 옵티마이저 등)에 전달함.

 

2. 파서(Parser) 단계

SQL 문이 문법적으로 올바른지 검사함(렉싱, 파싱).

SQL을 파싱 트리(Parse Tree) 형태로 변환함.

 

3. SQL 재작성(Rewrite) 혹은 전처리 단계

뷰(View)가 포함된 경우, 뷰를 실제 테이블로 치환하는 등 내부적으로 SQL을 재작성함.

복잡한 서브쿼리를 내부적으로 다른 형태(Join, Inline View 등)로 변환할 수 있음.

 

옵티마이저를 통한 실행 계획 생성

1. 옵티마이저의 역할

파싱 트리를 입력받아, 통계 정보(테이블, 인덱스, 컬럼 분포 등)를 활용하여 가능한 실행 계획들 중 비용(Cost)이 최소화되는 방법을 찾음.

DML 문도 SELECT와 비슷하게, 내부적으로 어떤 인덱스를 사용할지, 어떤 순서로 데이터를 읽을지 등을 결정함.

 

2. 실행 계획(Execution Plan)

SQL 문을 효율적으로 실행하기 위한 단계별 연산(Access Method, Join Method, 인덱스 스캔 여부 등)이 정해짐.

결과물은 DB 엔진이 실제로 수행할 “플랜(Plan)”임.

 

실행과 접근 단계 : 접근 경로 및 버퍼 캐시

옵티마이저가 결정한 접근 방식(인덱스 접근, 풀 스캔 등)에 따라 실제 데이터를 읽거나 수정함.

DBMS는 디스크에 있는 데이터를 바로 수정하지 않고, 먼저 버퍼 캐시(Buffer Cache, SGA/Shared Buffer 등)에 데이터를 적재한 후 처리함.

InnoDB(MySQL)나 PostgreSQL, Oracle 등 대부분의 DB 엔진은 버퍼 캐시를 통해 디스크 접근 횟수를 최소화하고 성능을 높임.

 

실행과 접근 단계 : 로우 검색 및 잠금

1. UPDATE/DELETE 시에는 수정될(또는 삭제될) 로우를 찾아야 하고, INSERT 시에는 새로운 로우를 어느 위치(페이지, 블록)에 삽입할지 결정해야 함.

2. 동시성 제어(Concurrency Control)

전통적인 DBMS(Oracle 등)는 로우 단위 잠금(Row-level Lock) 또는 MVCC(Multi-Version Concurrency Control) 방식을 사용함.

MySQL InnoDB, PostgreSQL 등은 MVCC를 통해 “쓰기 스레드”와 “읽기 스레드”가 최대한 충돌 없이 동작하도록 함.

필요에 따라 인덱스 키 범위에 대한 잠금 또는 테이블 수준 잠금이 걸릴 수 있음.

단, 일반적인 RDBMS는 로우 수준에서 처리함.

 

실행과 접근 단계 : 트랜잭션 로그 기록

1. 트랜잭션 로그(REDO/Undo, Write-Ahead Logging, etc.)

DML로 인해 변경되는 내용을 복구 가능하도록, 먼저 로그(Redo/Write Ahead Log 등)에 기록함.

Write-Ahead Logging(WAL) 기법: 데이터 페이지에 변경을 반영하기 전에, 로그 파일에 해당 변경을 기록하여 장애 발생 시에도 DB 복구가 가능하도록 함.

Undo(또는 Rollback) 세그먼트/테이블스페이스(Oracle, InnoDB 등에서 사용)는 트랜잭션 롤백 시점에 원본 데이터를 복구하기 위해 필요한 정보를 저장함.

Redo 로그는 커밋 후 시스템 크래시가 발생해도, 해당 로그를 재적용하여 데이터 일관성을 보장함.

 

내부 메모리 및 디스크 구조 변화 : 버퍼 캐시에서의 변경

DML 문이 실행되어 테이블에 데이터가 삽입/수정/삭제될 때, 해당 데이터가 들어 있는 버퍼 페이지(블록) 또한 메모리 내에서 수정됨.

수정된 버퍼 페이지는 Dirty Page 상태가 되며, 일정 시점(체크포인트, 버퍼 풀 정책 등)에 디스크로 기록됨.

 

내부 메모리 및 디스크 구조 변화 : 디스크 쓰기

1. DBMS는 Checkpoint를 통해 로그 및 데이터 페이지를 디스크에 반영함.

 

2. 체크포인트 시

변경 로그(REDO, WAL) 파일을 디스크에 영구적으로 기록하고,

특정 시점(LSN: Log Sequence Number 등)까지 데이터 파일로 동기화함.

 

3. 이렇게 하면 장애가 발생하더라도 체크포인트 이후 구간만 로그를 재적용하여 빠르게 복구할 수 있음.

 

트랜잭션 제어 : 커밋

사용자가 COMMIT 명령을 내리면, 해당 트랜잭션에서 발생한 변경을 영구화(Durable)함.

DBMS는 적어도 REDO/WAL 로그를 디스크에 안전하게 기록해야 커밋을 성공으로 보고, 애플리케이션에 “커밋 완료”를 알림.

커밋 시점에 버퍼 캐시에 있는 실제 데이터 페이지를 디스크에 바로 기록할 수도 있지만, 반드시 즉시 쓰지는 않을 수 있음.

다음 체크포인트까지 지연될 수도 있음.

핵심은 로그의 영구 기록임.

 

트랜잭션 제어 : 롤백

트랜잭션 중간에 오류가 발생하거나 사용자가 ROLLBACK을 명령하면, Undo/Undo 로그를 참조하여 변경 내용을 원상태로 복구함.

MVCC를 사용하는 DBMS(예: PostgreSQL, MySQL InnoDB 등)에서는 이전 버전 정보를 저장하여 읽기 일관성(Read Consistency)을 보장하고 롤백 시 활용함.

 

동시성 제어와 격리 수준

1. 동시성 제어 메커니즘

락(Lock) 기반: 오라클, MySQL(InnoDB), MSSQL 등은 Row-level Lock 또는 Index Lock을 사용.

MVCC 기반: PostgreSQL, MySQL(InnoDB), Oracle(실질적으로 MVCC) 등은 스냅샷(Snapshot)을 만들어 읽기 트랜잭션이 쓰기 트랜잭션에 의존하지 않도록 함.

 

2. 격리 수준(Isolation Level)

Read Uncommitted, Read Committed, Repeatable Read, Serializable

각 격리 수준에 따라 Dirty Read, Non-repeatable Read, Phantom Read 등과 같은 현상의 허용 범위가 달라짐.

DBMS마다 기본 격리 수준이 다를 수 있으며(예: Oracle은 Read Committed, PostgreSQL은 Read Committed, MySQL은 대부분 Repeatable Read), 내부 구현(락 or MVCC) 방식과 연계되어 최종 사용자에게 일관성을 보장함.

 

옵티마이저 통계 갱신 및 메타데이터 반영

DML 수행으로 인해 데이터가 변경되면, 컬럼 분포나 테이블의 레코드 수, 인덱스 분포 등이 달라질 수 있음.

DBMS는 통계를 자동으로 갱신하거나, 일정 주기 혹은 특정 임계값을 넘을 때 분석(ANALYZE, DBMS_STATS 등)을 재수행하여 옵티마이저가 좀 더 정확한 비용 추정이 가능하도록 함.

 

장애 발생 시 복구

Write-Ahead Logging을 통해 트랜잭션이 커밋된 시점의 로그가 디스크에 반영되어 있다면, 장애 후 재시작 시 로그를 다시 적용(REDO)하여 마지막 상태로 복원할 수 있음.

롤백이 필요한(커밋되지 않은) 부분은 Undo 정보를 사용해 복구(UNDO)함.

이러한 ARIES(Algorithm for Recovery and Isolation Exploiting Semantics) 알고리즘이나 유사한 복구 메커니즘을 통해 데이터 무결성과 일관성을 지킴.

 

성능 최적화와 주의 사항

1. 인덱스 관리

UPDATE/DELETE/INSERT는 테이블뿐 아니라 인덱스 페이지도 변경해야 함.

인덱스가 많을수록 DML 부하가 증가함.

쓰기 시 인덱스마다 추가 작업.

 

2. 배치 처리(Batch)와 트랜잭션 크기

대규모 DML 작업(예: 대량 INSERT/UPDATE)은 적절한 Batch 크기로 쪼개 수행하면 Redo/Undo 로그 처리 부담과 락 경합을 줄일 수 있음.

지나치게 큰 트랜잭션은 롤백 시에도 부담이 크고, 자원 점유가 길어져 동시성에 문제를 일으킴.

 

3. 락 경합(Lock Contention) 모니터링

UPDATE/DELETE가 같은 로우(또는 인접 범위)로 몰릴 경우 락 경합이 심해짐.

오라클의 경우 TM Lock, TX Lock, MySQL InnoDB의 경우 Row Lock 등의 대기 상황을 모니터링해야 함.

 

4. 자동 증가 컬럼(Sequence, Auto Increment) 충돌

Sequence나 Auto Increment를 사용하면 다중 노드/병렬 환경에서 충돌이 발생할 수 있으므로, 내부적으로 락이나 전용 구조를 활용해 일관성을 유지함.

 

요약 : DML 실행 시 내부 흐름

1. SQL 수신 → 파싱 → 옵티마이저

SQL 문법 검증, 실행 계획 수립

 

2. 실행(Access) → 버퍼 캐시 → Lock/MVCC

실제 데이터 읽기/변경, 트랜잭션 수준 락 또는 MVCC를 통한 동시성 제어

 

3. 로그 기록

WAL(Write-Ahead Log), Redo 로그 및 Undo 정보 기록

커밋 시점에 로그를 디스크에 안전하게 기록

 

4. 커밋/롤백

커밋: 트랜잭션의 영속화(로그 기록 보장)

롤백: Undo를 통한 원상복구

 

5. 체크포인트(Checkpoint)

변경된 버퍼(Dirty Page)를 디스크에 쓰고, 해당 시점까지의 로그와 동기화

 

6. 장애 발생 시 복구

Redo 로그를 재적용 & Undo 로그를 활용하여 일관성 유지

 

결론

DML(INSERT, UPDATE, DELETE)이 실행되면, 단순히 “테이블에 데이터가 바뀐다” 수준을 넘음.

 

1. SQL 파싱 및 최적화를 거쳐,

2. 버퍼 캐시와 로그 기록(WAL, Redo, Undo)

3. 동시성 제어(락 또는 MVCC)

4. 트랜잭션 제어(커밋/롤백)

5. 체크포인트/복구

등을 통해 ACID(원자성, 일관성, 격리성, 지속성)를 만족시키도록 체계적으로 처리됨.

이를 통해, DBMS는 장애나 동시성 상황에서도 데이터의 무결성과 안정성을 유지하며, 사용자는 안전하고 일관성 있는 데이터베이스 환경을 이용할 수 있게 됨.