안녕하세요. 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(먼저 쓰기 로그)에 대해서 알려줘

 

 

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

153-1-체크포인트.png

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


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

 

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

체크포인트 프로세스가 두 번 실행되었고, 시스템 장애가 발생했습니다. 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 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 21874
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12245
2314 SQL강좌: 14-7. 트랜잭션과 잠금처리 - 교착상태(데드락-DeadLock) 관리 [1] 코난(김대우) 2023.08.18 152
2313 SQL강좌: 14-6. 트랜잭션과 잠금처리 - 잠금 관리 file 코난(김대우) 2023.08.18 37
2312 SQL강좌: 14-5. 트랜잭션과 잠금처리 - 잠금과 트랜잭션 격리 수준 코난(김대우) 2023.08.18 24
2311 SQL강좌: 14-4. 트랜잭션과 잠금처리 - 잠금(Lock)과 블로킹 코난(김대우) 2023.08.18 46
» SQL강좌: 14-3. 트랜잭션과 잠금처리 - 트랜잭션과 체크포인트 [1] file 코난(김대우) 2023.08.18 91
2309 SQL강좌: 14-2. 트랜잭션과 잠금처리 - 트랜잭션 종류 코난(김대우) 2023.08.18 52
2308 SQL강좌: 14-1. 트랜잭션과 잠금처리 - 트랜잭션 이해 코난(김대우) 2023.08.18 58
2307 SQL강좌: 13-5. 백업과 복원 - 로그 전달, Always On 고가용성과 재해 복구 구현 file 코난(김대우) 2023.08.18 28
2306 SQL강좌: 13-4. 백업과 복원 - 유지 관리 계획 수립 file 코난(김대우) 2023.08.18 41
2305 SQL강좌: 13-3. 백업과 복원 - 백업과 복원 전략 실행 file 코난(김대우) 2023.08.18 31
2304 SQL강좌: 13-2. 백업과 복원 - 백업과 복원 전략 file 코난(김대우) 2023.08.18 33
2303 SQL강좌: 13-1. 백업과 복원 - 백업과 복원 이해 file 코난(김대우) 2023.08.18 45
2302 SQL강좌: 12-9. 인덱스 생성과 관리 - DTA(데이터베이스 엔진 튜닝 관리자) file 코난(김대우) 2023.08.18 41
2301 SQL강좌: 12-8. 인덱스 생성과 관리 - 인덱스 재구성/재구축 코난(김대우) 2023.08.18 36
2300 SQL강좌: 12-7. 인덱스 생성과 관리 - 인덱스 옵션 코난(김대우) 2023.08.18 43
2299 SQL강좌: 12-6. 인덱스 생성과 관리 - 클러스터형 vs 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 35
2298 SQL강좌: 12-5. 인덱스 생성과 관리 - 비클러스터형 인덱스 file 코난(김대우) 2023.08.18 30
2297 SQL강좌: 12-4. 인덱스 생성과 관리 - 클러스터형 인덱스 file 코난(김대우) 2023.08.18 34
2296 SQL강좌: 12-3. 인덱스 생성과 관리 - 인덱스 생성 file 코난(김대우) 2023.08.18 29
2295 SQL강좌: 12-2. 인덱스 생성과 관리 - 인덱스 종류 코난(김대우) 2023.08.18 62





XE Login