[한기교] SQL 활용 필기(8) - 트랜잭션, TCL

트랜잭션

 

 

< 트랜젝션 >

ㅁ 트랜젝션의 개념

: 논리적인 일의 단위

=> 일반적으로 하나의 SQL은 하나의 트랜젝션이지만,

여러 개의 SQL문들이 합쳐져서 하나의 트랜젝션이 될 수도 있다

 

 

ㅁ 동시성

실생활에서는 항공기 예약, 은행, 신용카드 처리, 대형마트 등

대규모 데이터베이스를 수많은 사용자들이 동시에/여러부분에 접근한다 ===> 동시성

 

 

ㅁ 트랜잭션 단위 동시성 제어

: 동시에 수행되는 트랜잭션들이 데이터베이스에 미치는 영향

= 이들을 순차적으로 수행하였을 때 데이터베이스에 미치는 영향과 같도록 보장

- 다수 사용자가 데이터베이스를 동시에 접근하도록 허용하면서 데이터베이스의 일관성 유지

- 여러 사용자나 여러 응용 프로그램들이 동시에 수행되어도 서로 간섭하지 못하도록 보장함

 

 

ㅁ 트랜잭션 단위 회복

: 데이터베이스를 갱신하는 도중에 시스템 고장 시에도 데이터베이스의 일관성 유지

 

 

ㅁ 트랜잭션이 없다면? 발생 가능한 문제들

ex1) 은행계좌 이자 증가

- 전체 계좌들에 대한 이자가 모두 계산되어야 함

- 일부 계좌 이자만 증가되고 컴퓨터가 리부팅 될 경우, 처음부터 다시 계산 시 이중으로 이자계산

 

ex2) 항공권, 영화관 등 다양한 예약 시스템

- 좌석을 선점하고 돈을 내기 전에 시스템이 다운되면, 해당 좌석은 이미 선점된 것으로 처리

 

ex3) 은행계좌 이체

- A계좌에서 100원을 빼서 B계좌로 이체해야 하는 경우, 도중에 오류가 생겼다면..

i) A계좌에서 먼저 돈을 빼서 B계좌로 이체 --> 도중에 오류로 B에 돈이 입금되지 않으므로 은행이 돈 횡령한 꼴

ii) B계좌에 먼저 입금 후 A계좌에 돈 출금 --> 은행에 막대한 손실이 발생

 

==> 이런 경우들을 방지하기 위해 두 개의 SQL문을 모아서 하나의 트랜젝션으로 관리

 

 

ㅁ 트랜잭션의 특성 ACID

- Atomicity 원자성

- Consistency 일관성

- Isolation 격리성

- Durability 영속성

 

1) 원자성 Atomicity

: 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않음을 의미(All or Nothing)

- DBMS의 회복 모듈은 시스템이 다운되는 경우에, 부분적으로 데이터베이스를 갱신한 트랜잭션의 영향을 취소함으로써 트랜잭션의 원자성을 보장

- 완료된 트랜잭션이 갱신한 사항은 트랜잭션의 영향을 재수행함으로써 트랜잭션의 원자성을 보장

 

 

2) 일관성 Consistency

: 어떤 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에 데이터베이스는 또 다른 일관된 상태를 가짐

- 트랜잭션이 수행되는 도중에는 데이터베이스가 일시적으로 일관되지 않은 상태를 가질 수 있음

 

3) 격리성 Isolation = 고립성

: 한 트랜잭션이 데이터를 갱신하는 동안 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 함

- 다수의 트랜잭션들이 동시에 수행되더라도 그 결과는 어떤 순서에 따라 트랜잭션들을 하나씩 차례대로 수행한 결과와 같아야 함

- DBMS의 동시성 제어 모듈이 트랜잭션의 고립성을 보장

- DBMS는 응용들의 요구사항에 따라 다양한 고립 수주냐(Isolation Level)을 제공

 

4) 지속성 Durability 

: 일단 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 그 후에 시스템에 고장이 발생하더라도 손실되지 않음

- 완료된 트랜잭션의 효과는 시스템이 고장난 경우에도 데이터베이스에 반영됨

- DBMS의 회복 모듈은 시스템이 다운되는 경우에도 트랜잭션의 지속성을 보장함

 

 

ㅁ 트랜잭션의 상태

- 부분완료 : 마지막 명령문을 실행한 상태

- 완료 : 모든 트랜잭션 결과를 DB 에 반영한 상태

- 실패 : 트랜잭션의 실패

- 철회 : 트랜잭션의 모든 결과를 원상태로 돌려 놓은 상태

 

 

ㅁ ACID와 DB 기능

-> ACID 특성과 DB의 기능은 모두 다 연관이 있음

 

- DB 회복 기능 <-- 원자성, 지속성

- DB 동시성 제어 <-- 일관성, 고립성

- 무결성 제약조건 <-- 일관성

 

 

 

ㅁ 트랜잭션의 상태 변화

 

 

 

< 트랜잭션 제어문 (TCL) >

ㅁ COMMIT

: 트랜잭션의 마지막 명령어가 수행되었음을 나타냄

- 트랜잭션에 의한 변경을 확정

- COMMIT된 트랜잭션은 철회가 불가능

- COMMIT 명령문 실행하기 전에 하나의 트랜잭션 변경한 결과를 다른 트랜잭션에서 접근할 수 없도록 방지하여 일관성 유지

 

 

ㅁ ROLLBACK

: 트랜잭션의 변경을 취소하고 트랜잭션 종료

 

 

ㅁ SAVEPOINT

: 현재 트랜잭션에서 ROLLBACK 시킬 위치 지정

- 대규모 트랜잭션(복수개의 명령어들로 이루어진 트랜잭션)에서 오류 발생이 나면

  전체 트랜잭션을 취소시키는 것이 큰 부담이 될 수 있음

- 실패한 일정부분만 취소시키도록 함

 

 

ㅁ MS-SQL은 3가지 트랜잭션 모드를 지원

1) 자동 커밋 트랜잭션

: 하나의 명령문이 하나의 트랜잭션이 됨

- MS-SQL에서 기본 모드임

 

2) 명시적 트랜잭션

: 명시적으로 사용자가 트랜잭션을 정의하는 형태

- BEGIN TRAN ~ COMMIT TRAN(또는 ROLLBACK TRAN)으로 이루어짐

 

3) 묵시적 트랜잭션

- 자동 커밋 트랜잭션의 반대되는 개념

- BEGIN TRAN이 필요없음

- 사용자가 COMMIT TRAN(또는 ROLLBACK TRAN)을 입력하기 전까지 복수개의 명령문을 하나의 트랜잭션으로 간주

- 트랜잭션 종료마다 사용자가 반드시 COMMIT/ROLLBACK 을 실행시켜야 하므로, 고급사용자에게 권장

SET IMPLICIT TRANSACTIONS {ON|OFF}

 

 

ㅁ 오류발생에 따른 트랜잭션 철회

 

- MS-SQL에서 명령문의 오류는 @@ERROR라는 변수에 저장됨

- T-SQL에서 IF~ELSE~ 및 GOTO같은 구문을 사용할 수 있음

 

 

 

ㅁ SAVEPOINT 이용한 트랜잭션 부분 철회

 

트랜잭션 내에 저장점명을 다르게 하면 여러 개의 SAVEPOINT를 지정할 수 있음

SAVE TRAN 저장점 이름

특정 저장점까지 취소

ROLLBACK TRAN 저장점 이름