[SQLD] #015 SQL 기본 - TCL

목차

1. 트랜잭션 개요

2. COMMIT

3. ROLLBACK

4. SAVEPOINT

5. 정리

 

 

---

 

 

1. 트랜잭션 개요

트랜잭션 : 데이터베이스의 논리적 연산 단위. 밀접하게 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 말한다. 

하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다. 

 

올바르게 반영된 데이터를 DB에 반영시키는 것을 커밋(COMMIT), 트랜잭션 시작 이전의 상태로 되돌리는 것을 롤백(ROLLBACK)이라고 하며 저장점(SAVEPOINT) 기능과 함께 3가지 명령어를 TCL(Transaction Control Language)로 분류한다. 

트랜잭션의 대상이 되는 SQL문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문이다. 

 

 

트랜잭션이 수행하는 동안 특정 데이터에 대해 다른 트랜잭션이 동시에 접근하지 못하도록 하는 기법을 잠금(LOCKING) 이라고 한다. 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 접근할 수 있고, 다른 트랜잭션부터 간섭을 받지 않는다.

 

 

 

 

 

 

 

 

2. COMMIT

입력한 자료나 수정한 자료, 또는 삭제한 자료에 대해 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다. 

 

COMMIT 이나 ROLLBACK 이전의 데이터 상태는 다음과 같다. 

- 단지 메모리 버퍼에만 영향을 받았기 때문에 변경 이전 상태로 복구 가능하다.

- 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다. 

- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다. 

- 변경된 행은 잠금이 설정되어서 다른 사용자가 변경할 수 없다. 

 

COMMIT 이후의 데이터 상태는 다음과 같다. 

- 데이터에 대한 변경 사항이 데이터베이스에 반영된다. 

- 이전 데이터는 영원히 잃어버리게 된다.

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있게 된다. 

 

 

 

SQL Server 의 COMMIT

SQL Server에서의 트랜잭션은 기본적으로 3가지 방식으로 이루어진다. 

 

1. AUTO COMMIT

SQL Server의 기본 방식이며, DML, DDL을 수행할 때마다 DBMS가 트랜잭션을 컨트롤하는 방식이다. 명령어가 성공적으로 수행되면 자동으로 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다. 

 

2. 암시적 트랜잭션

Oracle과 같은 방식으로 처리된다. 즉, 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다. 

 

3. 명시적 트랜잭션

트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방법이다. 

BEGIN TRANSACTION으로 트랜잭션을 시작하고 COMMIT TRANSACTION 또는 ROLLBACK TRANSACTION으로 트랜잭션을 종료한다. ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 롤백이 수행된다. 

 

 

 

 

 

 

 

 

 

 

 

 

3. ROLLBACK

테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백 기능을 사용한다. 

롤백은 데이터 변경 사항이 취소되어 이전상태로 복구되며, 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게된다. 

 

 

SQL Server 의 ROLLBACK

SQL Server는 AUTO COMMIT이 기본 방식이므로 임의적으로 ROLLBACK을 수행하려면 명시적으로 트랜잭션을 선언해야한다. 

 

롤백 후의 데이터 상태는 다음과 같다. 

- 데이터에 대한 변경 사항은 취소된다. 

- 이전 데이터는 다시 재저장된다. 

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있게 된다. 

 

COMMIT과 ROLLBACK을 사용함으로써 다음과 같은 효과를 볼 수 있다. 

- 데이터 무결성 보장

- 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

- 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

 

 

 

 

 

 

 

 

 

 

4. SAVEPOINT

SAVEPOINT를 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. 

##1. 저장점 정의 
-- ORACLE
SAVEPOINT 저장점명;

-- SQL Server
SAVE TRAN 저장점명;

##2. 저장점까지 되돌리기 
-- ORACLE
ROLLBACK TO 저장점명;

-- SQL Server
ROLLBACK TRAN 저장점명;

 

 

위 그림처럼 저장점 A로 되돌리고 나서 다시 B와 같이 미래 방향으로 되돌릴 수는 없다. 일단 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 되기 때문이다. 

저장점 지정 없이 롤백을 실행했을 경우 반영되지 않은 모든 변경사항을 취소하고 트랜잭션 시작 위치로 돌아간다. 

 

 

 

 

 

 

 

 

 

 

5. 정리

1. 커밋과 롤백의 목적은 입력/수정/삭제 수행 시 변경되는 데이터의 무결성을 보장하는 것이다. 

2. 커밋은 "변경된 데이터를 테이블이 영구적으로 반영해라" 라는 의미를 갖는 것이고, 롤백은 "변경된 데이터가 문제가 있으니 변경 전 데이터로 복귀해라" 라는 의미이다.

3. 저장점은 "데이터 변경을 사전에 지정한 저장점까지만 롤백해라" 라는 의미이다.

4. Oracle의 트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, 커밋 또는 롤백을 실행한 시점에서 종료된다. 하지만 아래의 경우에는 커밋과 롤백을 실행하지 않아도 자동으로 트랜잭션이 종료된다. 

 

- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다. 

- DML 문장 이후에 커밋없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다. 

- 데이터베이스를 정상적으로 접속 종료하면 자동으로 트랜잭션이 커밋된다. /

- 애플리케이션의 이상 종료로 DB와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다. 

 

5. SQL Server의 트랜잭션은 AUTO COMMIT이 기본이므로 아래의 경우일 때 자동으로 트랜잭션이 종료된다.

 

- 애플리케이션의 이상 종료로 DB와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다. 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.