[과목2.SQL 기본 및 활용 - 3-4. TCL (Transaction Control Language)]
1. 트랜잭션이란?
(1) 트랜잭션은 데이터베이스의 논리적 연산단위이다.
(2) 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.
(3) 트랜잭션은 분할할 수 없는 최소의 단위다.
(4) 전부 적용하거나 전부 취소.
(5) ALL 또는 NOTHING의 개념
2. 트랜잭션의 특성
(1) 원자성 : 모두 성공적으로 끝나거나 모두 실패해야한다.
(2) 일관성 : 실행되기 전의 데이터베이스의 내용이 잘못되어 있지 않다면 실행된 이후에도 데이터 베이스의 내용에 잘못이 있으면 안된다.
(3) 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
(4) 지속성 : 트랜잭션이 성공적으로 수행되면 그 데이터 베이스의 내용은 영구적으로 저장된다.
3. 계좌 이체 예시
- 현금이 인출되기 전에 다른계좌에 입금되는 것은 문제발생 시킬수 있다. 이체가 결정 되기 전까지는 다른사람이 그 계좌의 정보를 변경할 수 없다. 이를 잠금(LOCKING)이라고 표현한다.
4. COMMIT
- 입력, 수정, 삭제했던 데이터에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다.
5. COMMIT이나 ROLLBACK이전의 데이터 상태
- 메모리 BUFFER에만 쌓여있기 때문에 데이터 변경 이전상태로 복구 가능
- 현재 사용자는 SELECT 문장으로 결과를 확인 가능
- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
- 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
6. COMMIT 이후의 상태
- 변경사항이 데이터베이스에 반영 된다.
- 이전 데이터는 영원히 잃어버리게 된다.
- 모든 사용자는 결과를 볼 수 있다.
- 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자가 변경이 가능하다.
7. SQL Server의 COMMIT
- Oracle 서버는 DML 문장 수행 후 사용자가 임의로 TCL(commit, rollback)을 수행해주어야하나,
SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에, DML 수행 후 사용자가 TCL을 처리할 필요가 없다. DML 구문이 성공이면 자동으로 commit이 되고, 오류시에는 rollback이 된다.
8. ROLLBACK
(1) 테이블 내 입력/수정/삭제 데이터에 대해 commit 이전에는 변경 사항을 취소할 수 있다. 이때 ROLLBACK 기능을 사용한다.
(2) 데이터의 변경 사항이 취소돼서 데이터의 이전 상태로 복구가 된다.
(3) 관련 행의 잠금(LOCKING)이 풀리고 다른 사용자가 변경이 가능하다.
9. SAVEPOINT
(1) 저장점(SAVEPOINT)를 정의하면 롤백시에 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 현 시점에서 지정한 SAVEPOINT 까지 트랜잭션의 일부만 롤백 할 수 있다.
(2) 복잡한 대규모 트랜젝션에서 에러발생 시 사용 용이
예시 )
<ORACLE>
SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;
<SQL Server>
SAVE TRANSACTION SVTR1;
ROLLBACT TRANSACTION SVTR1;
10. 트랜잭션 요점
(1) 데이터의 변경을 발생시키는 INSERT, UPDATE, DELETE 수행 시 그 데이터의 무결성을 보장하는 것이 커밋(COMMIT)과 롤백(ROLLBACK)의 목적
(2) 커밋은 ‘변경된 데이터를 테이블이 영구적으로 반영해라’는 의미를 갖는다.
(3) 롤백은 ‘변경 사항을 취소하고 변경 이전의 데이터로 복구하라’는 의미를 가진다.
(4) 저장점은 ‘데이터 변경을 사전에 지정한 저장점까지만 롤백하라’는 의미를 지닌다.
(5) Oracle의 트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, 커밋또는 롤백을 실행한 시점에서 종료가 된다.
11. 커밋/롤백 실행하지 않아도 자동으로 트랜잭션이 종료되는 경우
(1) CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장 실행한 경우 전후시점에 자동으로 커밋된다.
(2) DML 문장 이후에 커밋없이 DDL 문장이 실행되면 자동으로 커밋된다.
(3) 데이터베이스를 정상적으로 접속 종료하면 자동으로 트랜잭션이 커밋 된다.
(4) 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절 되었을때는 트랜잭션이 자동으로 롤백 된다.