컴알못의 SQL 공부 (트랜잭션)

트랜잭션

개요

관련된 SQL 문장들을 한데 묶어서 하나의 처리 단위로 다루는 것.

데이터베이스의 작업은 하나의 작업도 여러 SQL명령문으로 이루어진 경우가 많기 때문에 필요합니다.

 

트랜잭션이란 한묶음으로 처리되어야 하는 SQL 명령문들의 집합.

즉, 트랜잭션 안의 SQL 명령문들은 전부 정상처리 되어야 하나의 트랜잭션이 완료되는 것입니다.

 

트랜잭션이 정상 종료되었다면 커밋.

트랜잭션이 실패하면 롤백.

정확히 말하면 커밋은 SQL 명령문들을 전부 정상 수행해 실행 결과를 성공적으로 반영하는 것.

롤백은 SQL 명령문 수행 도중 오류가 발생하여 명령문 실행 전 상태로 되돌리는 것을 말합니다.

 

목적

데이터베이스 서버에 다수의 클라이언트의 동시 접근성을 제어하고, 장애 발생시에 안정적으로 데이터를 복구하여 데이터 부정합을 방지하기 위해서 사용합니다.

 

특징

원자성 (Atomicity)

트랜잭션 내부의 SQL문들은 모두 성공하여 반영되거나, 모두 실패하여 철회되거나, 두 가지 상태 뿐이라는 의미입니다.

중간만 성공했다거나 그런 애매한 케이스는 존재해선 안됩니다.

일관성 (Consistency)

트랜잭션의 시작 전과 시작 후에도 데이터의 일관성이 유지됨을 의미합니다.

고립성 (Isolation)

트랜잭션이 커밋될 때까지 트랜잭션이 실행 중인 임시 실행 결과가 외부에 노출되선 안된다는 의미입니다.

같은 데이터를 처리하는 다른 트랜잭션들간의 간섭을 방지하기 위해서입니다.

그렇기 때문에 트랜잭션은 완전히 커밋되기 전까지는 실행 결과를 반영하지 않습니다.

지속성 (Durability)

트랜잭션의 수행 결과는 지속되어서 보존되어야한다는 의미입니다.

즉, 저장 장치에 장애가 발생하더라도 로그 기록 같은 것을 참조하여 성공적으로 데이터를 복원할 수 있어야함을 의미합니다.

 

이 4가지 특징을 트랜잭션의 ACID 특성이라고 합니다.

데이터베이스는 트랜잭션의 이러한 특성을 만족시키기 위해 2가지 모듈을 제공합니다.

 

동시성 제어 모듈 (concurrency control)

동시에 실행되는 트랜잭션들 간의 간섭을 제어합니다.

트랜잭션의 고립성과 일관성을 보장합니다.

회복 모듈 (recovery)

트랜잭션 결과의 복구를 보장합니다.

트랜잭션의 원자성과 지속성을 보장합니다.

 

종류

트랜잭션은 설정에 따라 3가지 모드로 구분 가능합니다.

 

명시적 트랜잭션

트랜잭션의 시작과 끝을 사용자가 직접 명시합니다.

'사용자 트랜잭션' 또는 '수동 트랜잭션'이라고도 합니다.

 

정의

START TRANSACTION;
   SQL 명령문;
COMMIT | ROLLBACK;

START TRANSACTION 명령문으로 직접 트랜잭션의 시작을 명시합니다. 이 명시를 통해 트랜잭션이 시작되고, 기본모드인 자동 모드 상태에서 수동 모드로 전환됩니다.

COMMIT 명령문의 경우는 트랜잭션의 처리 결과를 성공적으로 완료합니다.

ROLLBACK 명령문의 경우는 트랜잭션 처리 내용을 취소하고 트랜잭션 시작 이전의 상태로 되돌립니다.

 

자동완료 트랜잭션

명시적과 달리 SQL문의 실행결과에 따라서 자동적으로 커밋하거나 롤백하는 트랜잭션입니다.

'시스템 트랜잭션'이라고도 불립니다.

 

DBMS가 각 SQL문장 앞에 START TRANSACTION과 COMMIT이나 ROLLBACK을 자동으로 붙여 실행합니다.

 

자동완료 설정을 확인하는 법

SELECT @@AUTOCOMMIT;

자동완료 설정을 하여 자동완료 트랜잭션을 시작하는 법

SET AUTOCOMMIT=1;

AUTOCOMMIT이 1이어야 자동완료 설정이 되어있다는 뜻입니다.

 

자동완료 트랜잭션이 우리가 흔히 사용하는 SQL 환경입니다.

우리가 입력한 각각의 SQL문이 독립적인 트랜잭션으로 취급되어지기 때문에 ROLLBACK 명령문을 기입한다고 해도 이미 전의 SQL문들은 전부 COMMIT되거나 ROLLBACK 되었기 때문에 롤백할 대상이 없어 아무런 일도 일어나지 않습니다.

 

수동완료 트랜잭션

수동완료 트랜잭션은 트랜잭션의 끝만 사용자가 직접 명시합니다.

'암시적 트랜잭션'이라고도 합니다.

 

START TRANSACTION만 생략하는 모드입니다.

 

수동완료 트랜잭션을 설정하는 법

SET AUTOCOMMIT=0;

 

우리가 수동완료 모드로 시작을 할 때 START TRANSACTION을 생략하는 이유는, 보통 데이터를 조작하는 DML문을 실행하면 START TRANSACTION은 알아서 실행되기 때문입니다.

 

예시

DELETE FROM 과목 WHERE 이름='과학';
SELECT * FROM 과목;
INSERT INTO 과목 VALUES ('C000', '멘틀의 은밀한 비밀', '필수과목', '이영희');
SELECT * FROM 과목;
ROLLBACK;
SELECT * FROM 과목;

 

트랜잭션과 로그

트랜잭션 처리 과정

먼저 사용자로부터 SQL문이 서버로 들어오면,

쿼리 처리기가 이 명령문들을 해석합니다.

 

다음으로 필요한 데이터들을 디스크에서 주기억장치로 가져와야겠죠.

데이터베이스 버퍼 캐시에 필요한 데이터가 있는지 확인후 디스크로부터 가져옵니다.

 

해당 데이터가 성공적으로 반영한다고 해도 이를 바로 디스크에 저장하지 않습니다.

지연 쓰기 전략은 주기억장치가 해당 데이터를 좀 더 오래 가지고 있어 디스크의 방문 횟수를 최소화합니다.

 

로그 먼저 쓰기 규약

하지만 주기억장치는 휘발성이기 때문에 디스크에 덮어쓰지도 않은 변경사항들이 사라질 위험이 있습니다.

그렇기 때문에 DBMS는 디스크에 데이터를 덮어쓰지 않더라도, 트랜잭션 수행 직전에 그 기록을 디스크 안의 로그 데이터베이스 파일에 기록합니다.

이를 로그 먼저 쓰기 규약(write-ahead log protocol)이라고 합니다.

 

로그 데이터베이스는 트랜잭션의 모든 데이터변경 사항을 디스크에 쓰기 전에 미리 기록하는 특별한 데이터베이스입니다.

로그란, 데이터베이스의 장애에 대비하여 후추 복구를 위해 저장하는 트랜잭션 처리 정보입니다.

 

백업 데이터베이스는 로그 데이터베이스와는 다른 개념으로, 데이터베이스의 물리적 손실을 걱정하여 별도로 복제한 데이터베이스입니다. 즉, 말그대로 백업입니다.

백업의 종류는 여러가지가 있습니다.

전체 데이터베이스의 복사본을 저장하는 전체 백업(Full backup).

전체 데이터베이스의 복사본과 차이가 있는 부분만 추가로 백업하는 차등 백업(Deferential backup).

전체 데이터베이스 복사본과 이후 로그 데이터베이스 복사본을 저장하는 증분 백업(Incremental backup)

 

백업 데이터베이스를 이용해 원래 상태로 되돌리는 작업을 복원이라고 합니다.

 

트랜잭션 로그

로그를 통한 회복의 기본단위 역시 트랜잭션입니다.

트랜잭션 로그의 기본구조는 다음과 같습니다.

 

[ 트랜잭션식별자, 로그유형, 데이터항목, 변경전값, 변경후값 ]

 

예시

[T1, START]
[T1, UPDATE, 학생(A), 성적, 88, 89]
[T2, START]
[T2, UPDATE, 학생(B), 성적, 91, 80]
[CHECKPOINT]
[T2, UPDATE, 학생(C), 성적, 76, 90]
[T1, UPDATE, 학생(D), 성적, 55, 60]
[T1, COMMIT]
[T2, COMMIT]

CHECKPOINT란 데이터 버퍼 캐시 안의 모든 변경 내용을 디스크에 저장하는 시점을 말합니다.

이렇게 디스크에 저장해주면 장애 발생 시 복구 작업량을 줄일 수 있습니다.

이러한 저장 시점 역시 체크포인트로 기록을 하여, 이 이전의 로그 기록은 볼 필요가 없음을 명시해줍니다.

 

로그를 이용한 회복 기법

트랜잭션 로그를 이용한 회복 기법의 적용 과정은 다음과 같습니다.

 

회복은 장애가 발생하여 중단된 트랜잭션의 작업 내용을 롤백 시키기 위해서 로그 기록의 역순으로 이전 상태로 되돌립니다.

=> 마지막 체크포인트 이후의 커밋되지 않은 트랜잭션의 로그 내용들은 모두 역순으로 취소(UNDO)됩니다.

=> 반대로, 장애 발생 시점까지 정상적으로 종료된 트랜잭션들은 실행 결과를 보장하기 위해서 로그 기록 순서대로 재실행(REDO)합니다.

=> 마지막 체크포인트 이후의 커밋된 트랜잭션 로그 내용들은 모두 롤포워드(rollforward) 즉, 재실행됩니다.

 

예시

[CHECKPOINT]
[T2, UPDATE, 학생(A). 성적, 60, 70]
[T1, UPDATE, 학생(B), 성적, 70, 80]
[T1, COMMIT]
장애발생

위와 같은 경우에는, T1에 관한 트랜잭션은 COMMIT을 했기 때문에 재실행(REDO)되고 T2에 관한 트랜잭션은 COMMIT되지 않았기 때문에 취소(UNDO)됩니다.

 

트랜잭션과 락

데이터베이스는 데이터에 여러 사용자가 접근가능하지만, 여기에 적절한 제어가 없다면 여러 문제들이 발생합니다.

락 잠금이라는 것은 다중 사용자들이 데이터에 접근할 수 있도록 올바르게 제어하기 위한 기법입니다.

 

바람직한 동시성(concurrency)이란 여러 트랜잭션을 동시에 실행시키는 비직렬 스케줄의 결과와 트랜잭션을 하나씩 순차적으로 실행하는 직렬 스케줄의 결과가 같도록 보장하는 것입니다.

이를 보장하는 트랜잭션 스케줄을 직렬 가능(serializable)이라고 합니다.

락은 직렬 가능성을 보장하기 위한 방법 중 하나입니다.

 

락을 사용하는 동시성 제어 기법에서는 트랜잭션 내에서 데이터에 SQL 명령문을 실행하기 위해서는 반드시 해당 데이터에 락을 설정하여 잠궈야합니다. 그리고 트랜잭션이 커밋 혹은 롤백된 후에야 트랜잭션이 실행했던 락들을 일제히 해제할 수 있어요.

 

락의 종류

공유 락

데이터를 검색하는 SELECT문을 실행하기 위한 읽기 전용 락입니다.

읽기만 가능하고 수정은 불가능합니다.

여러 트랜잭션 간에 공유 락을 같은 데이터에 설정할 수 있습니다.

 

독점 락

데이터 변경을 위한 독점적인 배타적 락입니다.

오직 하나의 트랜잭션만 해당 데이터에 독점 락을 설정할 수 있습니다.

이 경우 다른 트랜잭션들은 이 데이터에 공유 락도, 독점 락도 설정할 수 없습니다.

마찬가지로 해당 데이터의 모든 공유 락과 독점 락이 해제된 후에만 독점 락을 설정할 수 있습니다.

 

락 설정과 해제

트랜잭션은 반드시 락을 먼저 설정한 이후에만 데이터에 접근 가능합니다.

데이터베이스 안의 데이터에 접근하기 위해서는 먼저 락 테이블 안에 해당 데이터에 대한 락 정보를 기록합니다.

이때 락 잠금 대상의 크기를 락 단위라고 하는데, 가장 작은 단위가 테이블의 행이며 테이블 혹은 데이터베이스가 될 수도 있습니다.

 

락 단위가 커지면 관리가 용이하지만 충돌이 자주 발생하며, 락 단위가 작아지면 동시성이 향상하지만 관리가 어려워집니다.

 

2단계 락킹 규약(2phased locking protocol)은 각 트랜잭션별로 락을 설정하는 과정과 해제하는 과정을 2단계로 진행함으로써 필요한 순간까지 획득한 락을 유지하도록 하는 규약입니다.

락의 설정과 해제가 적절한 시점에 이루어지지 않는다면 고립성이 손상되기 때문에 필요한 규칙입니다.

1단계 - 접근하고자 하는 데이터에 대한 모든 락을 획득할 때까지 새로운 락을 지속적으로 요청하는 단계. 이때는 락의 해제가 불가능.

2단계 - 필요한 모든 락을 보유한 락 포인트(lock point)가 되면 보유하고 있던 락을 점차적으로 해제. 하나씩 해제해도 되지만 보통은 커밋 혹은 롤백 시점에 한꺼번에 해제. 이때는 새로운 락을 요청할 수 없습니다.

 

교착 상태란 둘 이상의 트랜잭션의 락이 서로 얽혀서 영원히 풀리지 않는 상태를 말합니다.

서로 상대방이 설정한 락이 풀리기만을 기다리고 있는 상태입니다.

이럴 경우에는 두 개 중 하나를 강제 롤백 시킬 필요도 있습니다.

 

트랜잭션의 고립 수준

고립 수준이란 트랜잭션이 다른 트랜잭션과 고립되는 정도를 의미.

수준	고립 수준 키워드	발생 문제 유형	락 설정
수준 0	READ UNCOMMITED	오손데이터 읽기 반복 불가능 읽기 유령 데이터 읽기	SELECT문 - 락 관계없이 자유롭게 읽음 UPDATE, DELETE문 - 독점락 설정 및 유지
수준 1	READ COMMITED	반복 불가능 읽기 유령 데이터 읽기	SELECT문 - 공유 락 설정, 실행 후 즉시 해제 UPDATE, DELETE문 - 독점 락 설정 및 유지
수준 2	REPEATABLE READ	유령 데이터 읽기	SELECT문 - 공유락 설정 및 유지 UPDATE, DELETE문 - 독점 락 설정 및 유지
수준 3	SERIALIZABLE	없음	SELECT문 - 공유 락 설정 및 유지, 인덱스 공유 락 설정 및 유지 UPDATE, DELETE문 - 독점 락 설정 및 유지

 

오손 데이터 읽기 : 커밋되지 않은 트랜잭션의 중간 결과를 다른 트랜잭션이 읽을 경우 발생하는 문제.

반복 불가능 읽기 : 트랜잭션이 수정 중인 데이터를 다른 트랜잭션이 변경한다면 발생하는 문제.

유령 데이터 읽기 : 트랜잭션이 작업 중인 데이터에 다른 트랜잭션이 데이터를 추가한다면 발생하는 문제.

 

고립 수준이 높아질 수록 이런 문제들에 대해서 자유롭지만 동시수행성을 제한하기 때문에 성능 저하를 야기할 수도 있습니다.

 

고립 수준을 설정하는 방법

SET TRASACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRASACTION ISOLATION LEVEL READ COMMITTED;
SET TRASACTION ISOLATION LEVEL REPEATABLE READ;
SET TRASACTION ISOLATION LEVEL SERIALIZABLE;