트랜잭션 개요
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 트랜잭션(Transaction)[편집]
1.1 트랜잭션이란?[편집]
- 동시에 실행되는 작업의 단위를 의미
- 사용자의 의도에 따라 여러 개의 문장으로 구성된 트랜잭션은 반드시 동시에 실행(COMMIT)되거나 취소(ROLLBACK), ALL OR NOTHING
- RDBMS는 트랜잭션을 통해 작업의 단위를 결정함으로써 작업 결과의 신뢰성을 확보
- 금융 거래 계좌 이체 - A계좌에서 100만원을 B계좌로 이체한다면 최소 2가지 작업이 수행
1) A계좌에서 100만원 출금2) B계좌에 100만원이 입금- A계좌에서 100만원 출금은 성공했지만 B계좌에 100만원이 입금되기 전에 시스템이 다운된다면 B계좌에는 100만원이 입금되지 않고, A의 계좌에서만 100만원이 없어지게 된다.
- 이를 방지하기 위해 이 2개의 과정을 하나의 트랜잭션으로 묶어서 2개의 과정이 모두 처리되지 않으면 ROLLBACK 하여 거래가 취소되도록 함, 거래의 신뢰성을 확보
4. 트랜잭션의 시작과 종료
1) 시작
이전 트랜잭션이 종료된 이후
DML(INSERT, UPDATE, DELETE) 문장이나 DDL(CREATE, ALTER, DROP, TRUNCATE), DCL(GRANT, REVOKE)문장이 실행되었을 때 시작
2) 종료
2-1)COMMIT 이나 ROLLBACK 명령이 실행시 종료
2-2)DDL이나 DCL문장이 실행이 완료되면 자동으로 종료
2-3)사용자의 정상 종료 시 종료
2-4)데드락(DeadLock)이 걸리면 트랜잭션의 일부만 종료
트랜잭션은 하나의 세션에서 하나만 시작할 수 있다.
DML 인 경우 반드시 COMMIT 이나 ROLLBACK 으로 종료한다.
DDL, DCL 은 오라클 자동 제어
(세션이란? 오라클서버를 접속한 단위, 하나의 연결)
5. 트랜잭션과 언두 세그먼트(Undo segment)
DML 작업을 ROLLBACK 하기 위해서는 작업 이전 데이터를 어딘가에 저장해 두어야 한다.
오라클은 작업이 발생하면 즉시 테이블의 내용을 변경 -> DELETE나 UPDATE문을 실행하면 테이블의 정보또한 즉시 변경
ROLLBACK 실행시 DML작업에 의해서 변경된 정보를 이전 정보로 환원필요,
-> 작업 이전 정보를 저장하고 있지 않다면 ROLLBACK 실행 불가
==> 작업 이전 정보를 저장하고 관리하는 것이 트랜잭션의 기본 기능
이를 위해서 오라클은 Undo Tablespace 와 Undo Segment 라는 물리적 구조를 이용한다.
구조는 자동으로 관리되며 사용자는 구조정보를 직접 볼 수 없으며 트랜잭션에서만 사용된다.
6. 트랜잭션 과정
- DML 문장은 과정이 모두 동일
- 예시
테이블의 데이터 5를 7에 update 하는 과정
1) UPDATE 명령
2) 문장 수행을 위해 오라클은 Undo Segment 를 결정
3) 테이블에 저장되어있던 원래 값(5)이 Undo Segment에 저장
4) UPDATE 된 값(7)이 테이블에 저장
5) 값이 변경된 행(레코드)는 독점 잠금(Lock)이 발생
6) 테이블에는 공유 잠금(Lock)이 발생
7) 트랜잭션이 COMMIT되면 잠금은 해제, 변경된 값(7)은 영구히 저장
8) 트랜잭션이 ROLLBACK 되면 Undo Segment 에 저장했던 원래 값(5)을 테이블로 환원하고 잠금이 해제
7. 독점 잠금(Exclusive Lock)과 공유 잠금(Share Lock)
1) 독점 잠금 : 현재 세션 이외에는 접근을 불허하는 잠금
- 트랜잭션으로 행에 잠금이 발생하면 다른 세션에서는 해당 행을 검색할 수 없고 Undo Segment의 정보만 확인 가능
- 이때 테이블에는 공유 잠금이 발생
2) 공유 잠금 : DML 작업으로 행이 잠겨있는 테이블에 대해서 DDL (DROP, ALTER)작업을 방지
<실습>
1. 트랜잭션 잠금의 이해
1) 실습에는 2개의 일반 사용자 세션이 사용된다.
2) 동일한 계정으로 접속한 SQL Developer를 2개 실행한다.
3) 2개의 창은 [세션1]과 [세션2]로 구분한다.
--[세션1]
--1) --SELECT * FROM student WHERE sname='마초'; UPDATE student SET avr = 0.1 WHERE sname='마초';
--2) COMMIT 을 하지 않았으므로 마초의 행에는 -- 독점 잠금이 발생하고, student 테이블에는 -- 공유 잠금이 발생하였다. -- student 테이블의 avr 값은 새로 변경되었고 -- Undo Segment 에는 마초의 이전 avr 값이 저장되었다. SELECT sno, sname, avr FROM student WHERE sname='마초';
--4) 트랜잭션을 완료하자
COMMIT;
--[세션 2]
--3) 다른 세션에서 동일한 행을 검색해본다 -- 독점 잠금이 세션1에 의해 걸려있으므로 -- Undo Segment 에서 값을 읽어온다. SELECT sno, sname, avr FROM student WHERE sname='마초';
--5) 다시 검색하자
SELECT sno, sname, avr
FROM student
WHERE sname='마초';
2. 트랜잭션에 의한 대기
--[세션 1]
--1) 데이터를 변경하자(UPDATE) - 트랜잭션 시작 -- 마초의 행에는 독점 잠금이 발생된다. -- student 테이블에는 공유 잠금이 발생된다. -- student 테이블은 새로운 학과인 '사회'로 변경된다. -- Undo Segment 에는 마초의 변경 이전 학과인 -- '화학' 정보가 저장된다. SELECT sno, sname, major FROM student WHERE sname='마초';
UPDATE student SET major='사회' WHERE sname='마초';
SELECT sno, sname, major FROM student WHERE sname='마초';
--4) 상대방이 독점잠금이 걸린 행을
-- 다시 변경하려고 시도하였으나
-- 세션1의 트랜잭션이 끝나지 않아
-- 무한 대기상태에 걸려있다
--
-- 트랜잭션을 완료한다
COMMIT;
--커밋을 완료하면 상대 세션인 세션2는 대기상태에서 빠져나와 --새로 변경된 데이터를 적용한다
--5) 세션1의 트랜잭션은 마무리되었고
-- 세션2의 트랜잭션은 진행중인 상태
-- 현재 검색을 하면 내가 적용한 상태로 검색된다
SELECT sno, sname, major
FROM student
WHERE sname='마초';
--8) 세션2의 트랜잭션이 완료되고 검색을 한다 SELECT sno, sname, major FROM student WHERE sname='마초';
--[세션 2]
--2) 다른 세션에서 변경하고 트랜잭션이 -- 완료되지 않은 행을 검색한다 -- Undo Segment 의 데이터를 읽어온다 SELECT sno, sname, major FROM student WHERE sname='마초';
--3) 독점 잠금이 걸린 행을 UPDATE 시도한다 UPDATE student SET major='경제' WHERE sname='마초';
--6) 무한 대기가 풀리고 명령이 실행된 후
-- 검색을 한다
SELECT sno, sname, major
FROM student
WHERE sname='마초';
--7) 트랜잭션 완료
COMMIT;