[개미의 걸음 SQLD 2과목] SQL종류④ TCL(Transaction Control Language)

TCL[Transaction Control Language]

트랜잭션을 처리하는 SQL문

* COMMIT   : DML로 변경된 데이터를 DB에 적용   * ROLLBACK : DML로 변경된 데이터를 변경 이전 상태로 되돌림   * SAVEPOINT : 오류 복구 처리에 효과적인 방법                         → 전체 트랜잭션을 ROLLBACK하지 않고도 오류 복귀 가능

 

트랜잭션[Transaction]

데이터베이스의 작업을 처리하는 논리적 연산 단위[분리될 수 없는 최소 단위]

• 분리될 수 없는 한 개 이상의 데이터베이스 조작
      → 전부 적용하거나 전부 취소[All or Nothing]
• 하나의 트랜잭션에는 하나 이상의 SQL문 포함
      → 트랜잭션의 대상이 되는 SQL문은 DML문[SELECT, INSERT, UPDATE, DELETE 등] 사용
      → SELECT문은 직접적인 트랜잭션 대상이 아님
           (but, SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT문장은 트랜잭션 대상)

트랜잭션의 특성

원자성 [Atomicity]	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아야함     → all or nothing
일관성 [Consistency]	트랜잭션 실행 전의 데이터베이스 내용이 잘못되지 않으면 트랜잭션 실행 후에도 데이터베이스 내용이 잘못되면 안됨
고립성 [Isolation]	트랜잭션 실행 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안됨
영속성, 지속성 [Durability]	트랜잭션 실행이 성공적일 때, 그 트랜잭션이 갱신한 데이터베이스 내용은 영구적으로 저장됨

트랜잭션에서 격리성이 낮은 경우 발생할 수 있는 문제점

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

 

1. COMMIT

COMMIT;

• DML로 변경된 데이터를 데이터베이스에 적용할 때 사용
      → INSERT, UPDATE, DELETE문 등을 사용한 후 변경 작업을 데이터베이스에 반영하기 위해 사용
• COMMIT문 사용시 이전 데이터는 영원히 지워짐
• COMMIT문 사용시 모든 사용자가 변경된 데이터 확인 가능
• COMMIT문 사용시 관련 행의 잠금(LOCKING)이 풀려 다른 사용자들이 행 조작 가능
• Oracle 데이터베이스에서는 DML문 이후 사용자가 COMMIT이나 ROLLBACK을 수행해야 트랜잭션 종료
      → SQL Server는 기본적으로 AUTO COMMIT모드이므로 자동으로 COMMIT, ROLLBACK 수행
           (DML문이 성공하면 자동으로 COMMIT, 오류가 발생하면 자동으로 ROLLBACK 처리)
• COMMIT 실행시 하나의 트랜잭션 과정을 종료
      → COMMIT이 실행되기 전에는 ROLLBACK으로 직전 COMMIT한 지점까지 데이터 복구 가능
      → COMMIT이 실행되기 전에는 다른 사용자가 완료되지 않은 데이터를 보거나 변경할 수 없음

 

2. ROLLBACK

ROLLBACK;

• DML로 변경된 데이터를 변경 이전 상태로 되돌릴 때 사용
      → 데이터에 대한 변경 사항 취소
• ROLLBACK문 사용시 이전 데이터 다시 재저장
      → COMMIT되지 않은 상위 Transaction을 모두 ROLLBACCK시킴
      → "SAVE TRANSACTION 특정시점"입력 후 "ROLLBACK TRANSATION 특정시점"입력 시 특정시점까지만 ROLLBACK
• ROLLBACK문 사용시 관련 행의 잠금(LOCKING)이 풀려 다른 사용자들이 데이터 변경 가능
• SQL Server는 기본적으로 AUTO COMMIT모드이므로 자동으로 COMMIT, ROLLBACK 수행
    (DML문이 성공하면 자동으로 COMMIT, 오류가 발생하면 자동으로 ROLLBACK 처리

 

3. COMMIT&ROLLBACK 사용 효과

1. 데이터 무결성 보장 2. 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능 3. 논리적으로 연관된 작업을 그룹핑하여 처리 가능

 

4. SAVEPOINT

SAVEPOINT SVPT1;
ROLLBACK TO SVPT1;

• 효과적으로 오류 복구 처리를 위해 저장점을 저장할 때 사용
     → 전체 트랜잭션을 ROLLBACK하지 않고 현시점에서 SAVEPOINT까지 일부 트랜잭션만 오류 복귀 가능
• 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 주로 사용
• 복수의 저장점 정의 가능
• 저장점까지 롤백할 때는 "ROLLBACK TO 저장점;" 사용