과목II SQL 기본 및 활용 1장 - 4절 TCL

4절. TCL (TRANSACTION CONTROL LANGUAGE)

트랜잭션 

- 데이터베이스의 논리적 연산단위

- 분할할 수 없는 최소의 단위

- 하나의 트랜잭션에는 하나 이상의 SQL문장이 포함된다. 

- ALL OR NOTHING의 개념 

 

트랜잭션의 특성

1. 원자성 - 모두 성공적으로 실행되던지 전혀 실행되지 않은 상태 

2. 일관성 - 트랜잭션 실행 전과 후에 일관성이 있어야 함 

3. 고립성(격리성) - 트랜잭션이 실행되는 도중 다른 트랜잭션의 영향을 받아 잘못된 결과 만들면 안됨 

4. 지속성 - 트랜잭션 수행시 갱신한 데이터베이스의 내용은 영구 저장 

 

1. 커밋(COMMIT) - 변경된 데이터를 데이터베이스에 영구적으로 반영 

COMMIT;

2. 롤백(ROLLBACK) - 변경 전 데이터로 복귀

ROLLBACK;

ROLLBACK TO SVPT1;

3. 저장점(SAVEPOINT) - 지정한 저장점까지 롤백

SAVEPOINT SVPT1; 

 

효과 

- 데이터 무결성 보장

- 영구변경 전 데이터의 변경 사항 확인 가능 

- 논리적으로 연관된 작업을 그룹핑하여 처리 가능 

 

※ DDL 문장을 실행시에는 자동으로 커밋된다.

   ORACLE의 경우 DML 문장 이후에 커밋이 없어도 DDL문장이 실행되면 DDL 수행 전에 자동으로 커밋된다. 

   데이터베이스 정상 종료시 자동 커밋

   데이터베이스 이상 종료시 자동 롤백 

    SQL SERVER의 경우에는 자동 커밋모드이므로 따로 처리 필요 X 

 

SQL SERVER - AUTO COMMIT

ORACLE - 암시적 트랜잭션(시작은 DBMS가 처리) 

+ 명시적 트랜잭션(시작(BEGIN)과 끝(COMMIT) 명시적으로 지정) 

 

격리성에서의 문제점 

1. 갱신 분실 (lost update)

- 하나의 트랜잭션이 수행한 데이터 변경 연산이 저장되기 전에 다른 트랜잭션이 데이터를 가지고 가서 데이터를 변경하고 다시 저장하는 경우 첫번째 트랜잭션이 수행한 연산은 무효가 된다

2. 오손판독 (dirty bit) 

- commit 되지 않은 값을 읽어왔을 때, 나중에 commit 없이 종료한다면 오손된 값을 가지고 있게 되어 꼬인다. 

3. 반복 불가능(uprepeatable read) 

 - 한 트랜잭션이 값을 읽어온 후 다른 트랜잭션이 그 값을 변경하거나 없애버리면 그 data를 다시 읽을 수 없음. 

4. 팬텀 문제 

- 한 트랜잭션(T1)이 어떠한 조건으로 값을 조회한 후 다른 트랜잭션(T2)이 해당 조건에서 data를 삽입,삭제하면 T1이 다시 값을 조회했을 때 변경된 data가 조회된다. 이 상태에서 T2가 rollback한다면? 

 

트랜잭션의 4가지 격리 레벨(isolation level)

미완료된 트랜잭션 읽기(read uncommitted) 

- 커밋하지 않은 데이터를 읽을수 있다.

- 오손판독, 반복불가능 읽기 , 팬텀문제 발생 

완료된 트랜잭션 읽기(read committed)

- 커밋 완료된 데이터만 읽을 수 있다.

- 반복 불가능 읽기, 팬텀문제가 발생 

반복가능 읽기(repeatable read)

- 트랜잭션 내에서 한번 조회한 데이터를 반복해서 조회해도 같은 데이터가 조회 된다.

- 팬텀문제가 발생

직렬 가능(serializable)  

- 위 3가지 문제점을 모두 예방 가능 

- 오손판독, 반복 불가능 읽기, 팬텀 문제 모두 예방