[데이터베이스] 9장. 트랜잭션

경희대학교 이영구 교수님의 데이터베이스 강의를 기반으로 정리한 글입니다.

동시성 제어와 회복

동시성 제어(concurrency control)

• 동시에 수행되는 트랜잭션들이 데이터베이스에 미치는 영향은 이들을 순차적으로 수행하였을 때 데이터베이스에 미치는 영향과 같도록 보장
• 다수 사용자가 데이터베이스를 동시에 접근하도록 허용하면서 데잍어베이스의 일관성을 유지

회복(recovery)

• 데이터베이스 갱신 도중 시스템이 고장나도 데이터베이스의 일관성을 유지

트랜잭션(transaction)

• 데이터베이스 응용에서 하나의 논리적인 단위를 수행하는 데이터베이스 연산들의 모임
• 데이터 객체(투플, 릴레이션)들을 접근하고, 갱신도 하는 프로그램 수행의 단위

9.1 트랜잭션 개요

• 이때 500명 전원의 급여가 수정되거나 한 명의 급여도 갱신되지 않도록 DBMS가 보장해야 함
• 320번째 사원까지 수정한 상태에서 컴퓨터 시스템 다운 → 로그(log) 유지를 통해 어디까지 수정했는가

• 두 UPDATE문은 둘 다 완전하게 수행되거나 한 UPDATE문도 수행되어서는 안됨 → 하나의 트랜잭션처럼 DBMS가 보장해야 함
• 기본적으로 각각의 SQL문이 하나의 트랜잭션으로 취급
• 두 개 이상의 SQL문들을 하나의 트랜잭션으로 취급하려면 사용자가 명시적으로 표시

• 세 개의 SQL문이 모두 완전하게 수행되거나 하나도 수행되어서는 안됨 → 하나의 트랜잭션처럼 DBMS가 취급해야 함
• DBMS는 각 SQL문의 의미를 알 수 없으므로 하나의 트랜잭션으로 취급해야 하는 SQL문들의 범위를 사용자가 명시적으로 표시

트랜잭션의 특성(ACID 특성)

원자성(Atomicity)

• 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않음(all or nothing)을 의미
• DBMS의 회복 모듈은 시스템이 다운되는 경우 → 부분적으로 데이터베이스를 갱신한 트랜잭션의 영향을 취소 → 트랜잭션의 원자성 보장
• 완료된 트랜잭션이 갱신한 사항 → 트랜잭션의 영향을 재수행 → 트랜잭션의 원자성 보장

일관성(Consistency)

• 어떤 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에 데이터베이스는 또 다른 일관된 상태를 가짐
• 트랜잭션이 수행되는 도중에는 데이터베이스가 일시적으로 일관된 상태를 갖지 않을 수 있음

고립성(Isolation)

• 한 트랜잭션이 데이터를 갱신하는 동안 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 함
• 다수의 트랜잭션들의 동시에 수행되더라도 그 결과는 어떤 순서에 따라 트랜잭션들을 하나씩 차례대로 수행한 결과와 같아야 함
• DBMS의 동시성 제어 모듈이 트랜잭션의 고립성을 보장함
• DBMS는 응용들의 요구사항에 따라 다양한 고립 수준(isolation level)을 제공함

지속성(Durability)

• 일단 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 그 후에 시스템에 고장이 발생하더라도 손실되지 않음
• 완료된 트랜잭션의 효과는 시스템이 고장난 경우 → 데이터베이스에 반영됨
• DBMS의 회복 모듈은 시스템이 다운되는 경우 → 트랜잭션의 지속성 보장

트랜잭션의 완료(commit)

• 트랜잭션에서 변경하려는 내용이 데이터베이스에 완전하게 반영됨
• SQL 구문상 COMMIT WORK

트랜잭션의 철회(abort)

• 트랜잭션에서 변경하려는 내용이 데이터베이스에 일부만 반영된 경우에는 원자성을 보장하기 위해서, 트랜잭션이 갱신한 사항을 트랜잭션이 수행되기 전의 상태로 되돌림
• SQL 구문상 ROLLBACK WORK

트랜잭션이 성공하지 못하는 원인

• 시스템(사이트) 고장: 중앙 처리 장치, 주기억 장치, 전원 공급 장치 등이 고장남
• 트랜잭션 고장: 트랜잭션 고장은 트랜잭션이 수행되는 도중에 철회됨
• 매체 고장: 디스크 헤드, 디스크 콘트롤러 등이 고장 나서 보조 기억 장치의 전부 또는 일부 내용이 지워짐
• 통신 고장
• 자연적 재해
• 부주의 또는 고의적인 고장

9.2 동시성 제어

• 여러 사용자들이 동시에 동일한 테이블에 접근 → DBMS의 성능을 높이기 위해 여러 사용자의 질의나 프로그램들을 동시에 수행하는 것이 필수적
• 동시성 제어 기법: 여러 사용자들이 다수의 트랜잭션들을 동시에 수행하는 환경에서 트랜잭션들 간의 간섭이 생기지 않도록 함

직렬 스케줄(serial schedule)

• 여러 트랜잭션들의 집합을 한 번에 한 트랜잭션씩 차례대로 수행
• 트랜잭션 내부 연산 순서는 섞이지 않음 → 트랜잭션 자체의 실행 순서만 고려
• 가능한 개수: N!

비직렬 스케줄(non-serial schedule)

• 여러 트랜잭션들을 동시에 수행
• 섞이지만 같은 트랜잭션 내 순서는 유지

직렬가능(serializable)

• 비직렬 스케줄의 결과가 어떤 직렬 스케줄의 수행 결과와 동등

데이터베이스 연산

Input(X)

• 데이터베이스 항목 X를 포함하고 있는 블록을 주기억 장치의 버퍼로 읽어들임

Output(X)

• 데이터베이스 항목 X를 포함하고 있는 블록을 디스크에 기록

read_item(X)

• 주기억 장치 버퍼에서 데이터베이스 항목 X의 값을 프로그램 변수 X로 복사

write_item(X)

• 프로그램 변수 X의 값을 주기억 장치 내의 데이터베이스 항목 X에 기록

동시성 제어를 하지 않고 다수의 트랜잭션을 동시에 수행할 때 생길 수 있는 문제

갱신 손실(lost update)

• 수행 중인 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어씀으로써 갱신이 무효되는 것

오손 데이터 읽기(dirty read)

• 완료되지 않은 트랜잭션이 갱신한 데이터를 읽는 것

반복할 수 없는 읽기(unrepeatable read)

• 한 트랜잭션이 동일한 데이터를 두 번 읽을 때 서로 다른 값을 읽는 것

항공기 예약 트랜잭션

• 여러 여행사에서 동시에 동일한 날짜에 출발하는 항공기의 빈 좌석 유무 검사

로킹(locking)

• 동시에 수행되는 트랜잭션들의 동시성을 제어하기 위해 가장 널리 사용되는 기법
• 로크(lock): 데이터베이스 내의 각 데이터 항목과 연관된 하나의 변수
• 각 트랜잭션이 수행 시작 → 데이터 항목 접근 → 요청 로크에 관한 정보 → 로크 테이블(lock table)에 유지
• 트랜잭션에서 데이터 항목을 접근할 때 로크 요청, 접근 끝낸 후 로크 해제(unlock)
  • 갱신 목적 접근: 독점 로크(X-lock, eXclusive lock) 요청
  • 판독(읽기) 목적 접근: 공유 로크(S-lock, Shared lock) 요청

2단계 로킹 프로토콜(2-phase locking protocol)

• 로크를 요청하는 것과 로크를 해제하는 것이 2단계로 이루어짐
• 로크 확장 단계가 지난 후에 로크 수축 단계에 진입
• 로크를 한 개라도 해제하면 로크 수축 단계 진입

로크 확장 단계(1단계)

• 트랜잭션이 데이터 항목에 대하여 새로운 로크 요청 가능 → 보유하고 있던 로크를 하나라도 해제할 수 없음

로크 수축 단계(2단계)

• 보유하고 있던 로크를 해제 가능 → 새로운 로크 요청 불가능
• 로크를 조금씩 해제할 수도 있고, 트랜잭션 완료 시점에 한 번에 모든 로크를 해제할 수도 있음
• 일반적으로 한 번에 해제하는 방식 사용

로크 포인트(lock point)

• 한 트랜잭션에서 필요로 하는 모든 로크를 걸어놓은 시점

데드록(deadlock)

• 2단계 로킹 프로토콜에서는 데드록이 발생할 수 있음
• 두 개 이상의 트랜잭션들이 서로 상대방이 보유하고 있는 로크를 요청하면서 기다리고 있는 상태
• 데드록을 방지하는 기법이나 데드록을 탐지하고 희생자를 선정하여 푸는 기법을 사용해 해결

1. T1이 X에 대해 독점 로크 요청 → 허가
2. T2이 Y에 대해 독점 로크 요청 → 허가
3. T1이 Y에 대해 공유 로크나 독점 로크 요청 → 로크 해제까지 기다림
4. T2이 X에 대해 공유 로크나 독점 로크 요청 → 로크 해제까지 기다림

다중 로크 단위(multiple granularity)

• 소수의 투플들을 접근하는 데이터베이스 응용: 투플 단위 로크 → 로크 테이블을 다루는 시간이 오래 걸리지 않음
• 트랜잭션들이 많은 투플을 접근하는 데이터베이스 응용: 투플 단위 로크 → 로크 테이블에서 로크 충돌 검사 → 로크 정보 기록 → 시간 오래 걸림
• 트랜잭션이 접근하는 투플 수에 따라 로크 데이터 항목의 단위 구분이 필요
• 다중 로크 단위: 한 트랜잭션에서 로크할 수 있는 데이터 항목이 두 가지 이상
• 데이터베이스에서 로크할 수 있는 단위: 데이터베이스, 릴레이션, 디스크 블록, 투플 등
• 일반적으로 DBMS는 각 트랜잭션에서 접근하는 투플 수에 따라 자동적으로 로크 단위 조정
• 로크 단위가 작을수록 → 로킹에 따른 오버헤드 증가, 동시성 정도 증가

팬텀 문제(phantom problem)

• T1: 1번 부서에 근무하는 사원들의 이름을 검색하는 동일한 SELECT문 두개
• T2: 1번 부서에 근무하는 사원 투플을 한 개 삽입하는 INSERT문
• 시간 1: T1의 SELECT문 수행 → 1번 부서에 근무하는 사원들의 이름 박영권, 김상원 검색
• 시간 2: T2의 INSERT문 수행 → EMPLOYEE 릴레이션에 1번 부서에 근무하는 정희연 투플 삽입
• 시간 3: T1의 두 번째 SELECT문 수행 → 박영권, 김상원, 정희연 검색
• → T1에 속한 첫 번째 SELECT문과 두 번째 SELECT문의 수행 결과가 다름 → 팬텀 문제

9.3 회복

회복의 필요성

• 어떤 트랜잭션 T를 수행하는 도중 시스템 다운 → T의 수행 효과가 디스크의 데이터베이스에 일부 반영 가능 → 어떻게 수행을 취소하여 원자성 보장?
• 트랜잭션 T가 완료된 직후 시스템 다운 → T의 모든 갱신 효과가 주기억 장치로부터 디스크에 기록되지 않을 수 있음 → 어떻게 완전하게 반영되도록 하여 지속성 보장?
• 디스크 헤드 등의 고장 → 디스크의 데이터베이스 접근 불가능 → 어떻게 할 것인가?

회복의 개요

• 여러 응용이 주기억 장치 버퍼 내의 동일한 데이터베이스 항목 갱신 후 디스크에 기록하여 성능 향상 중요
• 버퍼 내용을 디스크에 기록하는 것을 최대한 줄이는 것이 일반적
• 트랜잭션이 버퍼에는 갱신 사항 반영 → 버퍼의 내용이 디스크에 기록되기 전 고장 발생 가능
• 고장이 발생하기 전에 트랜잭션이 완료 명령 수행 → 회복 모듈은 갱신 사항을 재수행(REDO) → 지속성 보장
• 고장이 발생하기 전에 트랜잭션이 완료 명령 수행X → 데이터베이스에 반영했을 가능성 있는 갱신 사항 취소(UNDO) → 원자성 보장

저장 장치의 유형

• 휘발성 저장 장치에 들어 있는 내용: 시스템이 다운된 후 모두 사라짐(ex. 주기억 장치)
• 비휘발성 저장 장치에 들어 있는 내용: 디스크 헤드 등이 손상 입지 않는 한 시스템 다운 후 유지(ex. 디스크)
• 안전 저장 장치(stable storage): 모든 유형의 고장을 견딜 수 있는 저장 장치
• 두 개 이상의 비휘발성 저장 장치가 동시에 고장날 가능성 → 매우 낮음 → 사본 중복 저장 → 안전 저장 장치 구현

재해적 고장과 비재해적 고장

재해적 고장

• 디스크가 손상을 입어서 데이터베이스를 읽을 수 없는 고장
• 재해적 고장으로부터의 회복: 데이터베이스를 백업해 놓은 자기 테이프를 기반

비재해적 고장

• 그 이외의 고장
• 대부분 회복 알고리즘들은 비재해적 고장에 적용
• 로그를 기반으로 한 즉시/지연 갱신, 그림자 페이징(shadow paging) 등 여러 알고리즘
• 대부분의 상용 DBMS에서 로그를 기반으로 한 즉시 갱신 방식 사용

로그를 사용한 즉시 갱신

• 트랜잭션이 데이터베이스를 갱신한 사항이 주기억 장치의 버퍼에 유지되다가 트랜잭션이 완료되기 전이라도 디스크의 데이터베이스에 기록 가능
• 데이터베이스에는 완료된 트랜잭션의 수행 결과뿐만 아니라 철회된 트랜잭션의 수행 결과도 반영 가능
• 트랜잭션의 원자성과 지속성을 보장하기 위해 DBMS는 로그(log)라고 부르는 특별한 파일 유지
• 데이터베이스의 항목에 영향을 미치는 모든 트랜잭션의 연산들에 대해 로그 레코드 기록
• 각 로그 레코드는 로그 순서 번호(LSN: Log Sequence Number)로 식별

• 주기억 장치 내의 로그 버퍼에 로그 레코드들을 기록 → 로그 버퍼가 꽉 찰 때 디스크에 기록
• 로그는 데이터베이스 회복에 필수적 → 일반적으로 안전 저장 장치에 저장
• 이중 로그(dual logging): 로그를 두 개의 디스크에 중복해서 저장하는 것
• 각 로그 레코드가 어떤 트랜잭션에 속한 것인가 식별하기 위해 → 각 로그 레코드마다 트랜잭션 ID를 포함
• 동일한 트랜잭션에 속하는 로그 레코드들은 연결 리스트로 유지

로그 레코드의 유형

[Trans-ID, start]

• 한 트랜잭션이 생성될 때 기록되는 로그 레코드

[Trans-ID, X, old_value, new_value]

• 주어진 Trans-ID를 갖는 트랜잭션이 데이터 항목 X를 이전값(old_value)에서 새값(new_value)로 수정했음을 나타내는 로그 레코드

[Trans-ID, commit]

• 주어진 Trans-ID를 갖는 트랜잭션이 데이터베이스에 대한 갱신을 모두 성공적으로 완료하였음을 나타내는 로그 레코드

[Trans-ID, abort]

• 주어진 Trans-ID를 갖는 트랜잭션이 철회되었음을 나타내는 로그 레코드

트랜잭션의 완료점(commit point)

• 한 트랜잭션의 데이터베이스 갱신 연산이 모두 끝나고 데이터베이스 갱신 사항이 로그에 기록되었을 때
• DBMS의 회복 모듈은 로그를 검사하여 로그에 [Trans-ID, start] 로그 레코드와 [Trans-ID, commit] 로그 레코드가 모두 존재하는 트랜잭션들은 재수행
• [Trans-ID, start] 로그 레코드는 로그에 존재하지만 [Trans-ID, commit] 로그 레코드가 존재하지 않는 트랜잭션들은 취소

로그 먼저 쓰기(WAL: Write-Ahead Logging)

• 트랜잭션이 데이터베이스를 갱신 → 주기억 장치의 데이터베이스 버퍼에 갱신 사항 기록 → 로그 버퍼에는 이에 대응되는 로그 레코드 기록
• 데이터베이스 버퍼가 로그 버퍼보다 먼저 디스크에 기록 → 로그 버퍼가 디스크에 기록되기 전 시스템 다운 및 재기동 → 데이터베이스 버퍼와 로그 버퍼의 내용 X → 로그 레코드가 없어서 이전 값 알 수 없음 → 트랜잭션 취소 불가능
• 데이터베이스 버퍼보다 로그 버퍼를 먼저 디스크에 기록

체크포인트(checkpoint)

체크포인트 필요성

• 시스템이 다운된 시점으로부터 오래 전에 완료된 트랜잭션들이 데이터베이스를 갱신한 사항은 이미 디스크에 반영되었을 가능성이 큼
• DBMS가 로그를 사용하더라도 어떤 트랜잭션의 갱신 사항이 주기억 장치 버퍼로부터 디스크에 기록되었는가 구분할 수 없음
• DBMS는 회복 시 재수행할 트랜잭션의 수를 줄이기 위해 주기적으로 체크포인트를 수행

체크포인트 전략

• 체크포인트 시점에는 주기억 장치의 버퍼 내용이 디스크에 강제 기록 → 체크포인트를 수행하면 디스크 상에서 로그와 데이터베이스의 내용 일치
• 체크포인트 작업이 끝나면 로그에 [checkpoint] 로그 레코드가 기록
• 일반적으로 체크포인트를 10~20분마다 한 번씩 수행

체크포인트를 할 때 수행되는 작업

• 수행 중인 트랜잭션들을 일시적으로 중지 → 회복 알고리즘에 따라서는 이 작업이 필요하지 않을 수 있음
• 주기억 장치의 로그 버퍼를 디스크에 강제 출력
• 주기억 장치의 데이터베이스 버퍼를 디스크에 강제 출력
• [checkpoint] 로그 레코드를 로그 버퍼에 기록한 후 디스크에 강제 출력
• 체크포인트 시점에 수행 중이던 트랜잭션들의 ID도 [checkpoint] 로그 레코드와 함께 기록
• 일시적으로 중지된 트랜잭션의 수행 재개

→ Fuzzy checkpoint: 애매하다

데이터베이스 백업과 재해적 고장으로부터의 회복

• 데이터베이스가 저장되어 있는 디스크의 헤드 등이 고장 → 데이터베이스를 읽을 수 없는 경우 발생
• 주기적으로 자기 테이프에 전체 데이터베이스와 로그를 백업 → 자기 테이프를 별도의 공간에 보관
• 사용자들에게 데이터베이스 사용을 계속 허용 → 지난 번 백업 이후 갱신된 내용만 백업하는 점진적인 백업(incremental backup)이 바람직

9.4 PL/SQL의 트랜잭션

트랜잭션의 시작과 끝

• 오라클에서 한 트랜잭션은 암시적으로 끝나거나 명시적으로 끝날 수 있음
• 한 트랜잭션은 실행 가능한 첫 번째 SQL문이 실행될 때 시작 → 데이터 정의어/제어어를 만나거나, COMMIT/ROLLBACK 없이 Oracle SQL Developer를 정상적으로 종료했을 때 → 수행 중이던 트랜잭션이 암시적으로 완료(commit)
• COMMIT, ROLLBACK, SAVEPOINT문을 사용하여 트랜잭션의 논리를 명시적으로 제어 가능

COMMIT

• 현재 트랜잭션에서 수행한 한 개 이상의 데이터 조작어의 결과를 데이터베이스에 모두 반영하고 현재 트랜잭션을 완료

ROLLBACK

• 현재의 트랜잭션에서 수행한 한 개 이상의 데이터 조작어의 결과를 데이터베이스에서 모두 되돌리고 현재의 트랜잭션을 철회

SAVEPOINT

• 현재의 트랜잭션 내에 저장점을 표시하여 트랜잭션을 더 작은 부분으로 나눔

ROLLBACK TO SAVEPOINT

• 현재의 트랜잭션에서 지정된 저장점 이후 갱신된 내용만 되돌림

• SQL*Plus에서는 묵시적으로 한 트랜잭션은 데이터 정의어나 데이터 제어어 이전까지 입력한 여러 개의 데이터 조작어로 이루어짐
• set 명령을 사용하여 각 데이터 조작어를 한 트랜잭션으로 처리 가능
• set auto on; 또는 set autocommit on;

트랜잭션의 속성

• 트랜잭션이 데이터베이스를 읽기만 한다면 읽기 전용임을 명시하여 DBMS가 동시성의 정도를 높일 수 있음

• 어떤 트랜잭션이 읽기 전용이라고 명시 → 그 트랜잭션은 어떤 갱신 작업도 수행할 수 없음

• 트랜잭션에 대해 SET TRANSACTION READ WRITE를 명시하면 SELECT, INSERT, DELETE, UPDATE 모두 수행 가능

고립 수준

• SQL2에서 사용자가 동시성의 정도를 몇 가지로 구분하여 명시 가능
• 고립 수준: 한 트랜잭션이 다른 트랜잭션과 고립되어야 하는 정도를 나타냄
• 고립 수준이 낮으면 동시성은 높아지지만 데이터의 정확성은 떨어짐
• 고립 수준이 높으면 데이터가 정확해지지만 동시성이 저하됨
• 응용의 성격에 따라 허용 가능한 고립 수준(데이터베이스의 정확성)을 선택함으로써 성능 향상 가능
• 응용에서 명시한 고립 수준에 따라 DBMS가 사용하는 로킹 동작이 달라짐
• 한 트랜잭션에 대해 명시한 고립 수준에 따라 그 트랜잭션이 읽을 수 있는 데이터에만 차이가 있음 → 다른 트랜잭션의 고립 수준에 영향 X

오라클에서 제공하는 몇 가지 고립 수준

READ UNCOMMITTED

• 가장 낮은 고립 수준
• 트랜잭션 내의 질의들이 공유 로크를 걸지 않고 데이터를 읽음
• 오손 데이터 읽기 가능
• 갱신하려는 데이터에 대해 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유

READ COMMITTED

• 트랜잭션 내의 질의들이 읽으려는 데이터에 대해 공유 로크를 걸고, 읽기가 끝나자마자 로크 해제
• 동일한 데이터를 다시 읽기 위해 공유 로크를 다시 걸고 데이터를 읽으면, 이전 읽은 값과 다른 값 읽기 가능
• 갱신하려는 데이터에 대해 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유
• PL/SQL의 디폴트

REPEATABLE READ

• 질의에서 검색되는 데이터에 대해 공유 로크를 걸고, 트랜잭션이 끝날 때까지 보유
• 한 트랜잭션 내에서 동일한 질의를 두 번 이상 수행할 때, 이전에 읽은 값이 항상 동일하게 유지
• 갱신하려는 데이터에 대해 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유

SERIALIZABLE

• 가장 높은 고립 수준
• 질의에서 검색되는 투플들 뿐 아니라 인덱스에 대해서도 공유 로크를 걸고, 트랜잭션이 끝날 때까지 보유
• 한 트랜잭션 내에서 동일한 질의를 두 번 이상 수행할 때 매번 같은 값을 포함한 결과를 검색
• 갱신하려는 데이터에 대해 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유
• SQL2의 디폴트

REPEATABLE READ vs. SERIALIZABLE

REPEATABLE READ

• 이 범위에 해당하는 데이터가 2건이 있는 경우(col1 = 1과 5) → 다른 사용자가 col1이 1이나 5인 Row에 대한 UPDATE 불가능
• col1이 1과 5를 제외한 나머지 이 범위에 해당하는 Row를 INSERT하는 것은 가능

SERIALIZABLE

• 영역에 해당되는 데이터에 대한 수정 및 입력 불가능
• SQL의 결과가 항상 동일