습관처럼
SQL 기본 및 활용 - 제 4절 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)
트랜잭션 특성 (표 II-1-14 트랜잭션의 특성)
원자성 : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야 한다.(all or nothing)
일관성 : 트랜잭션이 실행되기 전 데이터베이스에 이상이 없다면 실행 후 에도 이상이 없어야 한다.
고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아선 안된다.
지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 저장된다.
2. COMMIT(커밋)
입력, 수정, 삭제한 자료에 대해서 문제가 없을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료한다.
COMMIT이나 ROLLBACK 이전의 데이터 상태
단지 메모리 BRFFER에만 영향주고 데이터 변경 이전 상태로 복구 가능하다.
현재 사용자는 SELECT 문으로 변경 결과를 확인할 수 있다.
다른 사용자는 현재 사용자가 수행한 결과를 볼 수 없다.
변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없다.
커밋 예
<Oracle 예>
INSERT 문 / UPDATE 문 / DELETE 문 수행 후
COMMIT;
커밋이 완료되었다.
<SQL Server 예>
INSERT 문 / UPDATE 문 / DELETE 문 수행 후
COMMIT;
커밋이 완료되었다.
# COMMIT 명령어는 데이터 변경 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용한다.
COMMIT 이후의 데이터 상태
데이터에 대한 변경 사항이 데이터베이스에 반영된다.
이전 데이터는 영원히 잃어버린다.
모든 사용자가 결과를 볼 수 있다.
관련된 행에 자금(LOCKING)이 풀리고, 다른 사용자가 행을 조작할 수 있다.
SQL Server의 COMMIT
기본적으로 AUTO COMMIT 모드
DML 구문이 성공이면 자동으로 COMMIT 된다.
오류가 발생할 경우 자동으로 ROLLBACK 된다.
SQL Server의 COMMIT
# SQL Server
- 기본적으로 AUTO COMMIT 모드
- DML 구문이 성공이면 자동으로 COMMIT 된다.
- 오류가 발생할 경우 자동으로 ROLLBACK 된다.
# SQL Server에서의 트랜잭션은 기본적으로 3가지 방식으로 이뤄진다.
- AUTO COMMIT : SQL Server의 기본 방식.
- 암시적 트랜잭션
- 명시적 트랜잭션
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와 같은 미래로 되돌릴 수 없다.
- 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점은 무료가 된다.
- 저장점 없이 롤백하면 모든 변경 사항을 취소한다.
출처 : http://bysql.net/
'Certification > Sqld' 카테고리의 다른 글
SQL 기본 및 활용 - 제 6절 함수 (0) | 2020.02.08 |
---|---|
SQL 기본 및 활용 - 제 5절 WHERE 절 (0) | 2020.02.08 |
SQL 기본 및 활용 - 제 3절 DML (0) | 2020.02.08 |
SQL 기본 및 활용 - 제 2절 DDL (1) | 2020.02.08 |
SQL 기본 및 활용 - 제 1절 관계형 데이터베이스의 개요 (0) | 2020.02.08 |