[SQLD] 2과목: SQL 기본 및 활용 - Chapter1. SQL 기본 - (4) TCL (Transaction Control Language)

• TCL
  • 논리적인 작업 단위를 묶어 DML에 의해 조작된 결과를 작업 단위별로 제어
  • Commit, Rollback 등이 해당
  • 일부에서는 DCL로 분류하기도 한다

 

• 트랜잭션
  • 데이터를 조작하기 위한 하나의 논리적인 작업 단위
  • 밀접히 관련되어 분리될 수 없는 한개 이상의 DB 조작을 가리킴
  • 하나 이상의 SQL 문장이 포함됨
  • 분할할 수 없는 최소단위이므로 전부 적용하거나 전부 취소해야 함

 

• 트랜잭션의 특징

특징	설명
원자성 (Atomicity)	트랜잭션의 연산은 모두 성공하거나 모두 실패해야 한다
일관성 (Consistency)	트랜잭션 실행 전 DB에 이상이 없다면 실행 후에도 일관되게 이상이 없어야 한다
고립성 (Isolation)	트랜잭션 실행 중 다른 트랜잭션의 영향을 받아서는 안 된다
지속성 (Durability)	트랜잭션이 성공적으로 수행되면 영구적으로 반영되어 저장되어야 한다

 

 

• DB 트랜잭션에 대한 격리성이 낮을 경우 발생하는 문제점

문제점	설명
Dirty Read	다른 트랜잭션에 의해 수정되었지만 아직 Commit되지 않은 Dirty한 데이터를 읽는 것
Non-Repeatable Read	한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데 그 사이 다른 트랜잭션이 값을 수정/삭제해서 두 쿼리 결과가 다르게 나타나는 현상
Phantom Read	한 트랜재션 내에서 같은 쿼리를 두 번 수행했는데,  첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

 

 

• 트랜잭션을 컨트롤하는 TCL
  • COMMIT: 문제없이 처리된 트랜잭션을 전부 DB에 반영
  • ROLLBACK: 트랜잭션 수행 이전 상태로 되돌림
  • 트랜잭션 대상 SQL
    • DML: INSERT, UPDATE, DELETE
    • SELECT: SELECT FOR UPDAE 등 배타적 LOCK을 요구하는 것들

 

1. COMMIT

• 삽입, 수정, 삭제한 이력이 문제가 없을 경우 COMMIT명령어로 변경 사항 적용

	상태
COMMIT 이전	- Memory Buffer 에만 영향을 주고 이전 상태로 복구 가능 - 현재 사용자는 Select문으로 변경 결과 확인 가능 - 다른 사용자는 현재 사용자가 수행한 결과를 확인 불가능 - 변경된 행은 아직 잠금(Locking) 설정되어 다른 사용자가 변경 불가능
COMMIT 이후	- 데이터에 대한 변경사항을 DB에 영구 반영 - 이전 데이터는 영원히 잃어버림 - 모든 사용자가 결과 조회 가능 - 변경된 행은 잠금이 해제되어 다른 사용자가 변경할 수 있고 확인도 가능

 

• Auto Commit
  • Oracle: DDL 문장 수행 후 자동으로 COMMIT 수행 -> ROLLBACK해도 저장이 되어 버린다.
  • SQL Server: DDL 문장 수행 후 자동으로 COMMIT 수행하지 않음

 

2. ROLLBACK

• 테이블에 삽입, 수정, 삭제한 데이터에 대해 변경된 내용을 취소하는 명령어
• Rollback 후...
  • 데이터에 대한 변경사항 취소됨
  • 이전 데이터가 다시 재저장됨
  • 관련 행에 대한 잠금이 풀리고 다른 사용자들이 데이터 변경 가능

 

 

3. BEGIN TRANSACTION

• 트랜잭션을 시작하는 구문 
• COMMIT이나 ROLLBACK으로 종료
• ROLLBACK을 만나면 최초의 BEGIN 시점까지 모두 ROLLBACK 수행
• 예)

품목ID	단가
001	1000
002	2000
003	1000
004	2000

BEGIN TRANSACTION
INSERT INTO 품목 (품목ID, 단가) VALUES ('005', 2000);
COMMIT                                                     --> 실행됨. 종료
BEGIN TRANSACTION                                          --> 다시 시작
DELETE 품목 WHERE 품목ID = '002';                           --> 취소
BEGIN TRANSACTION                                          --> 아직 종료 안 됨
UPDATE 품목 SET 단가=2000 WHERE 단가=1000;                  --> 취소
ROLLBACK                                                   --> COMMIT 되지 않은 트랜잭션 다 취소
SELECT COUNT(품목ID) FROM 품목 WHERE 단가=2000;

결과: 3 (품목ID 기준 002, 004, 005 의 3개 데이터 해당)

 

• COMMIT 과 ROLLBACK을 사용함으로써 얻을 수 있는 효과
  • 데이터 무결성 보장
  • 영구적인 변경을 하기 전에 데이터의 변경 사항 확인
  • 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

 

4. SAVEPOINT 

• 저장점
• SAVEPOINT를 지정하면 롤백할 경우 전체 롤백이 아닌 포인트까지의 일부만 롤백 가능
• 여러 개 지정할 수 있음
• 동일 이름으로 지정 시, 가장 나중에 정의한 POINT가 유효 (덮어쓰기)

--Oracle--
SAVEPOINT 포인트이름;
ROLLBACK TO 포인트이름;


--SQL Server--
SAVE TRANSACTION 포인트이름;
ROLLBACK TRANSACTION 포인트이름;

 

• SAVEPOINT 주의점
  • Point1로 되돌리고 나서 그보다 미래인 Point2로 되돌릴 수 없다
  • 특정 Point까지 롤백하면 그 이후에 설정한 Point는 전부 무효가 된다
  • Point 업이 롤백하면 모든 변경사항을 취소한다