SQLD 자격증 # SQL 기본(과목2)_TCL

1. 트랜잭션 개요

트랜잭션이란?

- 데이터베이스의 논리적 연산단위

- 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 의미한다.

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

- 트랜잭션은 분할할 수 없는 최소의 단위이다.

- 따라서, 전부 적용하거나 전부 취소. 즉,  ALL OR NOTHING

 

​

트랜잭션을 컨트롤하는 TCL

TRANSACTION CONTROL LANGUAGE

• 커밋(COMMIT) : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것
• 롤백(ROLLBACK) : 트랜잭션 시작 이전의 상태로 되돌리는 것
• 저장점(SAVEPOINT) 

 

트랜잭션 대상이 되는 SQL

UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문.

SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문.
(참고 : http://blog.naver.com/tyboss?Redirect=Log&logNo=70101836923)

​

​

트랜잭션 특성

• 원자성 : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야 한다.(all or nothing)
• 일관성 : 트랜잭션이 실행되기 전 데이터베이스에 이상이 없다면 실행 후 에도 이상이 없어야 한다.
• 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아선 안된다.
• 지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 저장된다.

 

2. COMMIT

입력, 수정, 삭제한 자료에 대해서 문제가 없을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료한다.

 

 

COMMIT이나 ROLLBACK 이전의 데이터 상태

• 단지 메모리 BRFFER에만 영향주고 데이터 변경 이전 상태로 복구 가능하다.
• 현재 사용자는 SELECT 문으로 변경 결과를 확인할 수 있다.
• 다른 사용자는 현재 사용자가 수행한 결과를 볼 수 없다.
• 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없다.

​

커밋 예

 

Oracle

(INSERT 문 / UPDATE 문 / DELETE 문 수행 후)

COMMIT;

 

커밋이 완료되었다.

 

SQL 

(INSERT 문 / UPDATE 문 / DELETE 문 수행 후)

COMMIT;

 

커밋이 완료되었다.

 

# COMMIT 명령어는 데이터 변경 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용한다.

​

COMMIT 이후의 데이터 상태

• 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
• 이전 데이터는 영원히 잃어버린다.
• 모든 사용자가 결과를 볼 수 있다.
• 관련된 행에 자금(LOCKING)이 풀리고, 다른 사용자가 행을 조작할 수 있다.

​

SQL Server의 COMMIT

• 기본적으로 AUTO COMMIT 모드
  DML 구문이 성공이면 자동으로 COMMIT 된다.
• 오류가 발생할 경우 자동으로 ROLLBACK 된다.

 

SQL Server에서의 트랜잭션 3가지 방식

1. AUTO COMMIT
2. 암시적 트랜잭션
3. 명시적 트랜잭션

 

 

​3. ROLLBACK(롤백)

• 테이블 내 입력, 수정, 삭제한 데이터에 대해서 COMMIT 이전에 변경 사항을 취소할 수 있는 기능
• 데이터 변경 사항 취소, 관련된 행에 대한 잠금이 풀리고 다른 사람들이 데이터를 변경할 수 있다.

​

롤백 예

 

Oracle

(INSERT 문 / UPDATE 문 / DELETE 문 수행 후)

 

ROLLBACK;

 

롤백이 완료되었다.

​

SQL Server

SQL Server는 AUTO COMMIT이 기본이므로 ROLLBACK을 수행하려면 트랜잭션을 선언해야 한다.

BEGIN TRAN

INSERT 문 / UPDATE 문 / DELETE 문 수행 후

 

ROLLBACK;

 

롤백이 완료되었다.

​

​롤백 후 데이터의 상태

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

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

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

​

COMMIT과 ROLLBACK을 사용함으로써 얻을 수 있는 효과

- 데이터 무결성 보장

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

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

 

 

4. SAVEPOINT(저장점)

SAVEPINT(저장점)이란?

저장점을 정의하면 롤백할때 전체 롤백이 아닌 일부만 롤백할 수 있다.

- 복수의 저장점을 정의할 수 있다.

- 동일 이름으로 저장점을 저장시 나중에 정의한 저장점이 유효하다.

​- 저장점 정의하고 저장점으로 롤백하기

 

Oracle

SAVEPOINT SVPT1;

ROLLBACK TO SVPT1;

 

SQL Server

SAVE TRANSACTION SVTR1;

ROLLBACK TRANSACTION SVTR1;

 

​

SAVEPOINT 설정 후 롤백(ROLLBACK)이 이뤄지는 범위

 

- 저장점 A로 되돌리고 나서 다시 B와 같은 미래로 되돌릴 수 없다.

- 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점은 무료가 된다.

- 저장점 없이 롤백하면 모든 변경 사항을 취소한다.

 

---

✅ REFERENCE

제 4절 TCL