[SQLD] SQL 기본 - TCL (Transaction Control Language)

트랜잭션

• DB의 논리적 연산단위
• 밀접히 관련되어 분리될 수 없는 한 개 이상의 DB조작
• 하나의 트랜잭션은 하나 이상의 SQL 문장으로 구성되어 있음
• 트랜잭션은 분할할 수 없는 최소의 단위이므로 전부 적용되거나 전부 취소된다

 

트랜잭션 특성

특성	설명
원자성(atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (ALL or NOTING)
일관성(consistency)	트랜잭션이 실행되기 전의 DB 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 DB의 내용에 잘못이 있으면 안된다.
고립성(isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성(durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.

 

00. 트랜젝션 제어어 - TCL(Transaction Control Language)

트랜잭션 제어 명령어

명령어	설명
COMMIT	작업 완료 처리
ROLLBACK	작업 취소 처리
SAVEPOINT	작업 처리 저장 지점

 

01. COMMIT

입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 문제가 없을 때 COMMIT 명령을 통해 트랜잭션을 완료할 수 있다.

 

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

• 단지 메모리 버퍼에만 영향을 받았기 때문에 데이터 변경 이전 상태로 복구 가능
• 현재 사용자는 SELECT 문장으로 결과 확인 가능
• 다른 사용자는 현재 사용자가 수행한 명령의 결과 확인 불가
• 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경 불가

 

COMMIT 이후의 데이터 상태

• 데이터에 대한 변경 사항이 DB에 반영됨
• 이전 데이터는 영원히 잃어버리게됨
• 모든 사용자는 결과를 볼 수 있음
• 관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있음

 

Oracle의 COMMIT vs SQL Server의 COMMIT

Oracle은 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며, DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료된다.

SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리 할 필요가 없다.

 

02. ROLLBACK

테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에 변경 사항을 취소하고 싶을 때 ROLLBACK 기능을 사용한다.

 

 

03. SAVEPOINT

저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT 까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다.

 

복수의 저장점을 사용할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.

[Oracle]
SAVEPOINT S1; -- S1 이라는 저장점을 정의

ROLLBACK TO S1; -- 저장점 S1까지 롤백


[SQL Server]
SAVE TRANSACTION S1;

ROLLBACK TRANSACTION S1;

⚠️ 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 된다.

 

 

e.g.

SAVEPOINT S1;
INSERT INTO USER (ID, NAME, AGE) VALUES (1, 'A', 10);
SAVEPOINT S2;
INSERT INTO USER (ID, NAME, AGE) VALUE (2, 'B', 20);
ROLLBACK TO S2;
COMMIT;
SELECT * FROM USER;

>> USER  테이블 조회 시, S2 지점에 있던 (1, 'A', 10) 데이터만 조회가 되게 됩니다.