제 18 장 (Oracle) 오라클에서 트랜잭션 지원

트랜잭션 개념 트랜잭션의 4가지 성격 원자성(atomicity) : 트랜잭션은 전부, 전무(all or nothing)의 실행만이 있다 일부 실행으로 트랜젝션의 기능을 가질 수는 없다. 일관성(consistency) : 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관된 데이터베이스 상태로 된다. 격리성(isolation) : 연산의 중간결과에 다른 트랜잭션이나 작업이 접근할 수 없다. 영속성(durability) : 트랜잭션의 일단 그 실행을 성공적으로 끝내면 그 결과를 어떠한 경우에라도 보장받는다.

1. 트랜잭션 트랜잭션(transaction): DBMS에서 데이터를 다루는 논리적인 작업의 단위 데이터베이스에서 트랜잭션을 정의하는 이유 데이터베이스에서 데이터를 다룰 때 장애가 일어날 때 데이터를 복구하는 작업의 단위가 됨. 데이터베이스에서 여러 작업이 동시에 같은 데이터를 다룰 때가 이 작업을 서로 분리하는 단위가 됨. 트랜잭션은 전체가 수행되거나 또는 전혀 수행되지 않아야 함(all or nothing) 예)은행 업무를 보는데 A 계좌(박지성)에서 B 계좌(김연아)로 10,000원을 이체할 경우 BEGIN ① A 계좌(박지성)에서 10,000원을 인출하는 UPDATE 문 ② B 계좌(김연아)에 10,000원을 입금하는 UPDATE 문 END

1. 트랜잭션

트랜잭션 제어 명령문 COMMIT ROLLBACK SAVEPOINT ROLLBACK TO <SAVEPOINT> 트랜잭션을 종료하고 트랜잭션의 변경 내용을 영구적으로 보존 ROLLBACK 트랜잭션 실행 내용을 원래대로 되돌리고 트랜잭션을 종료함 SAVEPOINT 트랜잭션 내에서 ‘표시된 지점’을 생성함 ROLLBACK TO <SAVEPOINT> SAVEPOINT 지점까지만 롤백함 SET TRANSACTION 고립 수준 등 트랜잭션의 다양한 속성을 설정 오라클은 BEGIN TRANSACTION 문장이 없음

트랜잭션 제어 명령문 2019-05-09 MS SQL Server 2000

COMMIT/ROLLBACK 개념 (1) CREATE TABLE trantest ( empID char(4), empName varchar(20) ); INSERT INTO trantest values(‘A001’, ‘김병직’); COMMIT; SELECT * FROM trantest; DELETE trantest WHERE empID = ‘A001’; ROLLBACK;

COMMIT/ROLLBACK 개념 (2) 예제-1: 주문 내용이 없는 고객을 고객테이블에서 삭제 DECLARE Count int; CustomerID varchar(5); BEGIN SELECT COUNT(*) INTO Count FROM Orders o WHERE o.CustomerID = CustomerID; IF Count = 0 THEN dbms_output.put_line(‘자료가 존재하므로 삭제할 수 없습니다.’); ELSE DELETE FROM Customers c WHERE c.CustomerID = CustomerID; END IF; END;

COMMIT/ROLLBACK 개념 (3) 예제-2: 주문 내용이 없는 고객을 고객테이블에서 삭제 DECLARE CustomerID varchar(10) := '홍길동'; BEGIN DELETE FROM Customers c WHERE c.CustomerID = CustomerID; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line( '다른 테이블에서 참조하므로 삭제할 수 없습니다.'); END;

SAVEPOINT ROLLBACK 할 위치를 지정한 후 부분 ROLLBACK 가능 (예) SAVEPOINT statement1; INSERT INTO t VALUES(1); IF error THEN ROLLBACK TO statement1; SAVEPOINT statement2; INSERT INTO t VALUES(-1); IF error THEN ROLLBACK TO statement2; BEGIN SAVEPOINT sp; P; Exception when others then rollback to sp; END;

트랜잭션 고립 수준 트랜잭션 고립 수준 명령어(transaction isolation level instruction) DBMS는 트랜잭션을 동시에 실행시키면서 락보다 좀 더 완화된 방법으로 문제를 해결하기 위해 제공하는 명령어 표 8-5 트랜잭션 고립 수준 명령어와 발생 현상 문제 고립 수준 오손 읽기 반복불가능 읽기 유령데이터 읽기 READ UNCOMMITTED 가능 READ COMMITTED 불가능 REPEATABLE READ SERIALIZABLE

READ UNCOMMITTED(Level = 0) 고립 수준이 가장 낮은 명령어로, 자신의 데이터에 아무런 공유락을 걸지 않음 (배타락은 갱신손실 문제 때문에 걸어야 한다). 또한 다른 트랜잭션에 공유락과 배타락이 걸린 데이터를 대기하지 않고 읽음. 심지어 다른 트랜잭션이 COMMIT하지 않은 데이터도 읽을 수 있음. 그 때문에 오손(dirty) 페이지의 데이터를 읽게 됨. 이 명령어는 SELECT 질의의 대상이 되는 테이블에 대해서 락을 설정하지 않은 것(NOLOCK)과 같음 표 8-6 READ UNCOMMITTED 모드 요약 모드 READ UNCOMMITTED LOCK SELECT 문 - 공유락 걸지 않음 UPDATE 문 - 배타락 설정 다른 트랜잭션의 공유락과 배타락이 걸린 데이터를 읽음 SQL 문 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 문제점 오손 읽기, 반복불가능 읽기, 유령데이터 읽기

READ UNCOMMITTED(Level = 0) 테이블 생성 및 투플 삽입 create table member ( id int, name char(20), password char(20), email char(40) ); insert into member values (1,'jhkim','1234','jhkim@kanagwon.ac.kr'); insert into member values (2,'johyeon','1234','johyeon@kanagwon.ac.kr'); insert into member values (3,'seok','1234','seok@kanagwon.ac.kr'); Commit;

READ UNCOMMITTED(Level = 0) READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT name FROM member where id = 2; COMMIT;

READ COMMITTED(Level=1) 오손(dirty) 페이지의 참조를 피하기 위해 자신의 데이터를 읽는 동안 공유락을 걸지만 트랜잭션이 끝나기 전에라도 해지 가능 다른 트랜잭션 데이터는 락 호환성 규칙에 따라 진행. 이 옵션은 오라클의 기본 설정으로 아무런 설정을 하지 않으면 READ COMMITTED 방식으로 수행됨 표 8-7 READ COMMITTED 모드 요약 모드 READ COMMITTED LOCK SELECT 문 - 공유락을 걸고 끝나면 바로 해지 UPDATE 문 - 배타락 설정 다른 트랜잭션이 설정한 공유락은 읽지만 배타락은 읽지 못함 SQL 문 SET TRANSACTION ISOLATION LEVEL READ COMMITTED 문제점 반복불가능 읽기, 유령데이터 읽기

READ COMMITTED(Level=1) SET TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; SELECT name FROM member where id = 2; Wait COMMIT;

REPEATABLE READ(Level=2) 자신의 데이터에 설정된 공유락과 배타락을 트랜잭션이 종료할 때까지 유지하여 다른 트랜잭션이 자신의 데이터를 갱신(UPDATE)할 수 없도록 함 다른 트랜잭션 데이터는 락 호환성 규칙에 따라 진행한다. 다른 고립화 수준에 비해 데이터의 동시성(concurrency)이 낮아 특별하지 않은 상황이라면 사용하지 않는 것이 좋음 표 8-8 REPEATABLE READ 모드 요약 모드 REPEATABLE READ LOCK SELECT 문 - 공유락을 걸고 트랜잭션을 끝까지 유지 UPDATE 문 - 배타락 설정 다른 트랜잭션이 설정한 공유락은 읽지만 배타락은 읽지 못함 SQL 문 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 문제점 유령데이터 읽기

REPEATABLE READ(Level=2) 반복읽기불가능 문제 발생 테스트 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT name FROM member where id = 2; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; COMMIT; SELECT name FROM member where id = 2 /* 앞의 결과와 다름 */

REPEATABLE READ(Level=2) READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT name FROM member where id = 2 FOR UPDATE; UPDATE member SET name = ‘kangjohyeon’ WHERE id = 2; Wait SELECT name FROM member where id = 2 /* 앞의 결과와 다름 */ COMMIT;

SERIALIZABLE(Level=3) 고립 수준이 가장 높은 명령어로, 실행 중인 트랜잭션은 다른 트랜잭션으로부터 완벽하게 분리됨 데이터 집합에 범위를 지어 잠금을 설정할 수 있기 때문에 다른 사용자가 데이터를 변경하려고 할 때 트랜잭션을 완벽하게 분리할 수 있음. 이 명령어는 네 가지 고립화 수준 중 제한이 가장 심하고 데이터의 동시성도 낮음. SELECT 질의의 대상이 되는 테이블에 미리 배타락을 설정한 것과 같은 효과를 냄 표 8-9 SERIALIZABLE 모드 요약 모드 SERIALIZABLE LOCK SELECT 문 - 공유락을 걸고 트랜잭션을 끝까지 유지 UPDATE 문 - 배타락 설정 다른 트랜잭션이 설정한 공유락은 읽지만 배타락은 읽지 못함 인덱스에 공유락을 설정하여 다른 트랜잭션의 INSERT 문이 금지됨 SQL 문 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 문제점 없음

SERIALIZABLE(Level=3) 유령 데이터 읽기 문제 테스트 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; DELETE FROM member WHERE id =2; COMMIT; SELECT Name FROM member WHERE id BETWEEN 1 AND 3; insert into member values (2,'johyeon','1234', 'johyeon@kanagwon.ac.kr');

SERIALIZABLE(Level=3) 유령 데이터 읽기 문제 테스트 트랜잭션 T1 READ COMMITTED 모드 (기본 모드) 트랜잭션 T2 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; DELETE FROM member WHERE id =2; COMMIT; SELECT Name FROM member WHERE id BETWEEN 1 AND 3; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into member values (2,'johyeon','1234', 'johyeon@kanagwon.ac.kr'); Wait;