안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 14-3. 트랜잭션과 잠금처리 - 트랜잭션과 체크포인트를 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 트랜잭션과 잠금처리 - 트랜잭션과 체크포인트입니다.

TL;DR

트랜잭션과 트랜잭션 로그의 관계, 데이터 변경 작업이 디스크에 기록되는 체크포인트 시점에 대한 내용을 다룹니다. 체크포인트 발생 시 트랜잭션과 데이터의 처리 상황을 설명하고, 재해 복구 과정에서 REDO, UNDO 작업의 중요성을 설명합니다.

트랜잭션과 트랜잭션 로그는 아주 밀접한 관계가 있습니다. 트랜잭션 로그는 장애(Failure)와 복구(Recovery)와도 밀접한 관련이 있지요.

트랜잭션과 체크포인트의 관계

강좌를 시작하기 전에 퀴즈입니다. 매번 데이터 변경 작업이 일어날 때마다, 예를 들면 INSERT나 DELETE, UPDATE 작업이 수행될 때마다 이 작업이 매번, 즉시, 디스크의 데이터 파일과 로그 파일에 기록될까요? 답은 “X - 아니다”입니다.

SQL Server는 빠른 처리 속도를 위해 이 데이터 변경을 캐시에 저장하고 있다가 체크포인트 프로세스(Check-point Process)라고 하는 SQL서버 내부의 프로세스가 활성화될 경우에 이 캐시의 데이터 변경을 디스크에 쓰게 됩니다. 왜 그런가요? - 빠른 처리 속도를 위해서입니다. 

그렇다면 또 질문입니다.

테이블에 데이터를 INSERT를 하자마자 SELECT 하면 항상 데이터가 삽입된 게 보입니다. 이건 왜 그런가요?

SQL Server는 SELECT를 수행하면,

1. 디스크의 테이블에서 데이터를 조회
2. 메모리상의 데이터 캐시 영역에서 조회
3. SELECT의 결과셋을 내부적으로 생성하고 사용자에게 출력

절차로 진행됩니다. 

조금 더 흥미 있는 논의를 진행해 볼까요? 진행 중인 트랜잭션 역시 디스크에 쓰게 됩니다. 언제 쓰나요? 체크포인트 발생시마다 쓰게 됩니다. 체크포인트는 퍼지 알고리즘에 의해 대략 1분 정도에 한 번씩 발생합니다. - 주기는 설정으로 변경 가능합니다.

이때 현재 활성 트랜잭션(계속 실행 중인 트랜잭션) 역시 디스크에 씁니다. 이 작업을 Write Ahead Log(WAL - 먼저 쓰기 로그)라고 하며 SQL서버는 이 방식으로 로그를 기록합니다. 

☑️ 챗GPT 활용: 데이터베이스 트랜잭션 - Write Ahead Log(먼저 쓰기 로그)에 대해서 알려줘

트랜잭션, 체크포인트와 장애

이미지 - 트랜잭션과 체크포인트

트랜젝션과 체크포인트를 공부하면 보게 되는 그림입니다.

체크포인트 및 장애 발생 상황

체크포인트 프로세스가 두 번 실행되었고, 시스템 장애가 발생했습니다. Tran #1, Tran #2, Tran #3 작업이 실행 중입니다. 

Tran #1 상태
Tran #1은 첫 번째 체크포인트에서 실행 중이었습니다. 두 번째 체크포인트 전에 종료되었습니다. 즉, 두 번째 체크포인트에서 디스크에 기록되었습니다.

Tran #2 상태
Tran2는 첫 번째 체크포인트에서 실행 중, 두 번째 체크포인트에서도 실행 중이고 트랜젝션이 끝났습니다. 하지만, 체크포인트 없이 장애가 발생합니다.

Tran #3 상태
두 번째 체크포인트에서 실행 중이었으며 실행 중에 시스템 장애가 발생했습니다.

장애 해결 - 재수행(REDO)과 철회(UNDO)

이런 장애 상황에서 어떻게 트랜잭션을 복구해야 정석일까요? 강좌 시작에서 말씀드린 REDO와 UNDO가 여기에서 필요합니다. SQL Server는 시작 시 항상 자동복구 프로세스가 동작합니다. 자동 복구 프로세스는 먼저 로그를 확인하고 REDO 할 트랜잭션과 UNDO 할 트랜잭션을 판단합니다. 

Tran #1 처리
체크포인트 전에 Commit이 있습니다. REDO(재수행) 시킵니다.

Tran #2 처리
체크포인트 전에 Commit 기록이 없으니 UNDO(철회) 시킵니다. 즉, 데이터를 트랜잭션이 시작하기 전의 상태로 바꿉니다.

Tran #3 처리
마찬가지로, 체크포인트 전에 Commit이 없으니 철회됩니다.

위의 작업 후 데이터베이스는 스테이블한 상태가 되면서 계속 작업을 수행합니다. 체크포인트가 중요한 프로세스이고, 장애 시 복구와 처리 여부가 결정되는 처리기입니다. 그럼 체크포인트는 언제 실행되나요?

• 자동적으로 약 1분 정도에 한 번씩 발생합니다. (실행 주기 변경 가능)
• SSMS에서 “CHECKPOINT”라고 수행할 경우
• SHUTDOWN 구문을 수행할 경우
• SQL서버 서비스를 중단할 경우

체크포인트 발생 주기 변경

아래 방식으로 주기를 변경할 수 있습니다. 단위는 분단위입니다.

USE AdventureWorks;
GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- 체크포인트 주기를 3분으로 변경
EXEC sp_configure 'recovery interval (min)', 3;
GO
RECONFIGURE;
GO

체크포인트와 자동 복구 프로세스는 SQL Server의 내부 구조로 트랜잭션 처리 과정과 디스크 IO로 인한 성능 저하 해결 등에 중요하니 차근차근 살펴보시길 바랍니다.

그럼 다음 강좌인 잠금 강좌로 넘어가도록 하겠습니다.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022