SQL 8-11. 데이터베이스 트랜잭션

데이터베이스 트랜색션은 다음 중 하나로 구성된다.

․ 데이터를 일관성 있게 변경하는 하나 이상의 DML문

․ DDL문 하나

오라클의 트랜잭션

트랜잭션 시작 및 종료 시기

트랜잭션은 첫 번째 DML문이 실행될 때 시작되어 다음과 같은 이벤트가 발생하면 종료된다. ․ COMMIT 또는 ROLLBACK문이 실행되는 경우 ․ DDL문(예:CREATE)이 실행되는 경우 ․ iSQL*Plus를 종료하는 경우 ․ 시스템에 장애가 있거나 시스템이 고장난 경우

트랜잭션이 종료되면 실행 가능한 다음SQL문이 다음 트랜잭션을 자동으로 시작한다.

DDL문 또는 DCL문은 자동으로 커밋되므로 트랜잭션이 암시적(implicit)으로 종료된다.

MSSQL의 트랜잭션

트랜잭션은 복구와 작업의 단일 단위로 처리되는 하나 이상의 Transaction-SQL 문의 집합이다.

즉, ALL OR NOTING

트랜잭션 종류

1. 자동으로 트랜잭션을 처리해 주는 암시적 트랜잭션

SQL명령을 실행시키면 DDL문이든, DML문이든, DCL문이든 자동 종료가 된다.(즉 자동COMMIT 된다.)

2. 사용자가 트랜잭션의 시작과 끝을 명시해 주는 명시적 트랜잭션

BEGIN TRAN <쿼리문> COMMIT TRAN,

BEGIN TRAN <쿼리문> ROLLBACK TRAN

을 해주어야 한다.

트랜잭션의 진행단계

1. 응용프로그램은 암시적 명시적으로 트랜잭션이 시작되었음을 알린다.

이전 트랜잭션이 완료된 후 실행된 첫번째 Transaction-SQL 문은 자동으로 새 트랜잭션을 시작한다.

트랜잭션이 시작될 때 로그에는 레코드가 기록되지 않는다.

즉, 응용프로램이 데이터 수정 내용에 대해 첫째 로드 레코드를 생성할 때 첫째 레코드가 로그에 기록된다.

2. 응용프로그램이 데이터 수정을 시작한다.

수정하고자하는 데이터를 메모리로 로드한다.

내용이 수정되면 데이터베이스가 일시적으로 일관되지 않은 중간 상태를 유지할 수 있다.

3. 모든 수정 작업이 성공되면 로그는 메모리에서 디스크로 기록된다.

정상적인 상태로 수정작업이 완료되고 데이터가 일관된 상태가 되면 응용프로그램은 해당 트램잭션을 커밋한다.

4. CheckPoint나 Lazy Writer에 의해 모든 수정된 내용이 데이터베이스의 영구적인 부분인 데이터 파일에 기록한다.

5. 응용 프로그램에서 트랜잭션을 완료하지 못하게 하는 일부 오류가 발생하는 경우 응용프로그램은 모든 데이터

수정내용을 실행 취소하거나 롤백한다.

이 경우 트랜잭션이 시작되기 전에 일관된 상태로 데이터베이스가 반환된다.

6. Commit이 되었지만 디스크 즉, 데이터 파일에 데이터의 변경 내용이 반영되지 않았다면, Commit된 로그의 내용을

데이터에 반영시키는 롤포워드(Roll Forward) 작업을 한다.

․ DCL문 하나

SQL server는 트랙잭션을 기반으로 데이터 일관성을 보장한다.

트랜잭션은 데이터를 변경할 때 보다 많은 융통성과 제어 기능을 제공하여 사용자 프로세스가 중단되거나 시스템 장애가

발생한 경우에도 데이터 일관성을 보장한다.

트랜잭션은 데이터를 일관성 있게 변경하는 DML문으로 구성된다.

예를 들어, 두 계좌사이에 자금을 이체하는 경우 한 계좌의 출금 금액과 다른 계좌의 입금 금액이 동일해야 하며 반드시

두 작업이 함께 처리되거나 취소되어야 한다.

즉 출금이 처리되지 않은 상태에서 입금이 처리되어서는 안된다.

트랜잭션 유형

유형 설명

DML(데이터 조작어)

Oracle server에서 단일 항목 또는 작업의 논리 단위로 취급되는 여러 DML문으로 구성됩니

다.

DDL(데이터 정의어) DDL 문 하나만으로 구성됩니다.

DCL(데이터 제어어) DCL문 하나만으로 구성됩니다.

8-12-1. COMMIT 및 ROLLBACK문의 장점

․ 데이터 일관성을 보장한다.

․ 데이터 변경 내용을 영구히 저장하기 전에 미리 볼 수 있다.

․ 논리적을 관련된 작업을 묶다.

명령문 설명

COMMIT 보류 중인 모든 데이터 변경 내용을 영구히 저장하고 현재 트랜잭션을 종료합니다.

SAVEPOINT name 현재 트랜잭션 내에 저장점을 표시합니다.

ROLLBACK POLLBACK은 보류 중인 모든 데이터 변경 내용을 버리고 현재 트랜잭션을 종료합니다.

ROLLBACK TO

SAVEPOINT name

ROLLBACK TO SAVEPOINT는 현재 트랜잭션을 지정된 저장점으로 롤백하여 롤백하는 저장점 이후

에 생성된 모든 변경 내용 및 저장점을 버립니다.

TO SAVEPOINT절을 생략할 경우 ROLLBACK문은 전체 트랜잭션을 롤백합니다. 저장점은 논리적인

것이므로 작성한 저장점을 나열할 수는 없습니다.

8-12-2. 트랜잭션 제어

① 명시적(Explicit)트랜잭션 제어문

COMMIT,SAVEPOINT 및 ROLLBACK문을 사용하여 트랜잭션 논리를 제어할 수 있다.

참고 : SAVEPOINT는 ANSI표준 SQL이 아닙니다.

오라클 형식

COMMIT

트랜잭션 <------ ROLLBACK

DELETE

SAVEPOINT　A <------ SAVEPOINT A로 ROLLBACK

INSERT

UPDATE

SAVEPOINT　B <------ SAVEPOINT B로 ROLLBACK

INSERT

표시자까지 변경 내용 롤백 예시

UPDATE...

SAVEPOINT update_done;

Savepoint created.

INSERT...

ROLLBACK TO update_done;

Rollback complete.

MSSQL 형식

BEGIN TRAN

UPDATE ...

BEGING TRAN

UPDATE ...

INSERT ...

COMMIT TRAN | ROLLBACK TRAN

COMMIT TRAN | ROLLBACK TRAN

BEGIN TRAN

UPDATE ...

SAVE TRAN SAVEPOINT

UPDATE ...

INSERT ...

SAVE TRAN SAVEPOINT1

DELETE ...

ROLLBACK TRAN SAVEPOINT1 | COMMIT TRAN

INSERT ...

ROLLBACK TRAN SAVEPOINT | COMMIT TRAN

ROLLBACK TRAN | COMMIT TRAN

SAVEPOINT문을 사용하여 현재 트랜잭션에 표시자를 생성하면 트랜잭션을 더 작은 부분으로 나눌 수 있다.

ROLLBACK TO SAVEPOINT( MSSQL은 ROLLBACK TRAN SAVEPOINT )문을 사용하면 보류 중인 변경 내용 중에서 해당

표시자 이후에 변경된 내용은 버릴 수 있다.

두 번째 표시자를 이전 표시자와 동일한 이름으로 생성하면 이전 표시자가 삭제된다.

② 암시적(implicit) 트랜잭션 처리

․ 자동 롤백은 iSQL*Plus가 비정상적으로 종료되거나 시스템에 장애가 있을 때 발생한다.

상태 Oracle 상황 MSSQL 상황

자동 커밋

․ DDL문 또는 DCL문이 실행되는 경우

․ COMMIT 또는 ROLLBACK명령이 명시적

(explicit)으로 실행 되지 않은 채 iSQL*Plus가

정상적으로 종료되는 경우

․ iSQL*Plus에서 Exit버튼을 누르면 세션이 정상적

으로 종료된다.

․ SQL*Plus에서는 프롬프트에 EXIT명령을 입력하

면 정상적으로 종료된다.

․ 창을 닫는 것은 비정상적으로 종료로 해석된다.

쿼리문이 실행되면 무조건 컴밋이 된다.

자동 롤백

iSQL*Plus가 비정상적으로 종료되거나 시스템 장

애가 발생한 경우

실행중 client 프로그램이 다운된 경우

참고 : 오라클의 iSQL*Plus에서는 또 다른 명령을 사용할 수도 있다.

AUTOCOMMIT명령은 ON또는 OFF로 토글할 수 있는데 ON으로 설정하면 각 개별 DML문이 실행되지 마자 커밋되

어 변경내용을 롤백할 수 없으며 OFF로 설정하면 COMMIT을 명시적으로 실행할 수 있다.

COMMIT문은 DDL문이 실행될 때 또는 iSQL*Plus이 종료될 때 실행된다.

참고 : Oracle server의 시스템 장애

시스템 장애로 인해 트랜잭션이 중단되면 트랜잭션 전체가 자동으로 롤백되어 잘못된 데이터 변경으로 인한 오류발생을

방지하며 테이블을 마지막 커밋된 상태로 되돌린다.

Oracle server는 이러한 방식으로 테이블의 무결성을 보존한다.

8-12-3. COMMIT 또는 ROLLBACK 실행이 이전의 데이터 상태 : 변경 내용 컴밋

트랜잭션에서 변경한 데이터는 트랜잭션을 커밋할 때 까지는 임시 데이터이다. ․ 데이터 조작 작업은 주로 데이터베이스 버퍼에 영향을 주기 때문에 데이터를 이전 상태로 복구할 수 있다. ․ 현재 사용자는 테이블을 질의하여 데이터 조작 결과를 검토할 수 있다. ․ 현재 사용자가 사용 중인 DML문의 결과를 다른 사용자는 볼 수 없다. ․ 관련 행이 잠기므로 다른 사용자는 관련 행의 데이터를 변경할 수 없다.

Oracle server는 읽기 일관성을 제공하여 각 사용자가 마지막 커밋된 상태의 데이터를 볼 수 있도록 한다. ․ 관련 행이 잠기므로 다른 사용자는 관련 행의 데이터를 변경할 수 없다.

8-12-4. COMMIT실행 이후의 데이터 상태

COMMIT문을 사용하여 보류 중인 변경 내용을 모두 영구히 저장한다 ․ 데이터 변경 내용이 데이터베이스에 영구히 저장된다. ․ 데이터의 이전 상태는 완전히 없어진다. ․ 모든 사용자가 결과를 볼 수 있다. ․ 관련 행 잠금이 해제되어 다른 사용자가 행을 조작할 수 있다. ․ 모든 저장점이 지워진다.

예제) 데이터 커밋 ․ 변경을 수행합니다.

오라클 디비 예제 : DELETE FROM employees

WHERE employee_id = 99999;

1 row deleted.

INSERT INTO departments VALUES (290, 'Corporate TAx', NULL, 1700);

1 row inserted. ․ 변경 내용을 커밋한다.

오라클 디비 예제 : COMMIT;

Commit complete.

☞설명) 변경 내용 커밋

예제는 EMPLOYEES테이블에서 행을 삭제하고 DEPARTMENTS테이블에 새로운 행을 삽입한다.

그런 다음 COMMIT문을 실행하여 변경 내용을 영구히 저장한다.

예제)

DEPARTMENTS테이블에서 부서290과300을 제거하고 COPY_EMP테이블에서 행을 갱신한다.

데이터 변경을 내용을 영구히 저장한다.

오라클 디비 예제 : DELETE FROM departments

MSSQL 디비 예제 :

MSSQL은 명시적 트랜잭션은 BEGIN TRAN을 사용할 때만 가능하다.

USE test

GO

BEGIN TRAN

DELETE FROM publishers WHERE department_id IN (0877,9901);

UPDATE copy_emp

SET pub_id = 1389

WHERE emp_id = 'A-C71970F';

COMMIT TRAN

MSSQL 디비 예제 :

MSSQL은 명시적 트랜잭션은 BEGIN TRAN을 사용할 때만 가능하다.

USE test

GO

BEGIN TRAN

DELETE FROM copy_emp

Rollback TRAN

오라클예제 MSSQL 예제

DELETE FROM test;

25,000 rows deleted.

ROLLBACK;

Rollback complete.

BEGIN TRAN

DELETE FROM test;

25,000 rows deleted.

ROLLBACK TRAN;

Rollback complete.

BEGIN TRAN

WHERE department_id IN (290, 300);

2 rows deleted.

UPDATE copy_emp

SET department_id = 80

WHERE employee_id = 206;

1 row updated.

COMMIT;

Commit Complete.

8-12-5. ROLLBACK실행 이후의 데이터 상태

ROLLBACK문을 사용하여 보류 중인 변경 내용을 모두 버린다.

․ 데이터 변경이 취소된다

․ 데이터가 이전 상태로 복구된다.

․ 관련 행에 대한 잠금이 해제된다.

오라클 디비 예제 : DELETE FROM copy_emp;

22 rows deleted.

ROLLBACK;

Rollback complete.

예제)

TEST테이블에서 레코드를 제거하다가 실수로 테이블의 내용이 삭제되는 경우가 발생할 수 있는데, 이러한 경우 오류를

수정하고 적합한 명령문을 재실행하여 데이터 변경 내용을 영구히 저장할 수 있다.

Oracle의 읽기 일관성 구현

1. 트랜잭션이 발생하면 데이터 블럭의 해당 데이터는 언두 세그먼트에 저장된다.

2. 변경을 요청한 사용자의 데이터는 데이터 블럭에 저장이 된다.

3. 변경을 요청한 사용자 이외의 사용자는 언두 세그먼트의 데이터를 보게된다.

변경 내용이 커밋되기 전까지는 데이터를 수정하는 사용자만 변경된 데이터베이스를 볼 수 있다.

( 데이터를 읽는 사용자는 현재 변경 중인 데이터가 아닌 일관성 있는 데이터를 볼 수 있다.)

4. commit이 발생하면 SELECT문을 실행하는 모든 사용자가 데이터베이스의 변경내용을 볼 수 있다.

언두 세그먼트의 데이터는 다음 트랜잭션을 위 공간을 비워 두게된다.

4-1. rollback이 되면 언두세그먼트의 데이터가 데이터블럭의 다시 저장이 된다.

5. 변경을 요청한 사용자 이외의 사용자도 데이터 블럭의 데이터를 보게 된다.

모든 사용자는 트랜잭션 시작 이전의 데이터베이스를 보게됩니다.

DELETE FROM test

WHERE id = 100;

1 row deleted.

SELECT *

FROM test

WHERE id = 100;

NO rows selected.

COMMIT;

Commit complete.

DELETE FROM test

WHERE id = 100;

1 row deleted.

SELECT *

FROM test

WHERE id = 100;

NO rows selected.

COMMIT TRAN;

Commit complete.

명령문 레벨 롤백

․ 트랜잭션 실행 중 단일 DML문 실행에 실패하면 해당 명령문만 롤백된다.

․ 해당 트랜잭션의 이전DMS문에 의해 변경된 내용은 그대로 유지된다.

․ 이들 변경 내용은 사용자가 명시적으로 커밋 또는 롤백할 수 있다.

․ 명령문 실행 오류가 감지된 경우 암시적 롤백에 의해 트랜잭션 일부가 버려질 수 있다.

․ Oracle server는 암시적(implicit)저장점을 구현한다.

․ 오라클은 DDL(데이터 정의어)문 전후에 암시적으로 커밋을 실행하므로 DDL문이 성공적으로 실행되지 않았을 지라도

서버가 실행한 커밋 때문에 이전 명령문을 롤백할 수 없다

․ 다른 모든 변경 내용은 유지됩니다.

․ 사용자는 COMMIT 또는 ROLLBACK문을 실행하여 트랜잭션을 명시적(explicit)으로 종료해야 한다.

8-12-6. 읽기 일관성

데이터베이스 사용자는 다음 두 방법으로 데이터베이스에 액세스합니다.

․ 읽기(SELECT문)

․ 쓰기(INSERT,UPDATE,DELETE문)

읽기 일간성이 필요한 이유는 다음과 같습니다.

․ 데이터베이스를 읽거나 쓰는 사용자에게 일관성 있는 데이터 뷰를 제공하기 위해

․ 데이터를 읽는 사용자가 변경 중인 데이터를 보지 못하도록 하기 위해

한 사용자가 변경한 내용이 다른 사용자가 변경한 내용과 충돌하지 않습니다.

․ 데이터를 쓰는 사용자가 일관성 있는 방식으로 데이터베이스 데이터를 변경하도록 하기 위해

․ 한 사용자가 변경한 데이터와 다른 사용자가 변경한 데이터 간의 충돌을 방지하기 위해 읽기 일관성은 각 사용자가

DML작업을 시작하기 전에 마지막 커밋된 상태의 데이터를 볼 수 있도록 한다.

․ 읽기 일관성은 동일한 데이터에 대해 다음을 보장합니다.

- 데이터를 읽는 중에는 쓸 수 없습니다.

- 데이터를 쓰는 중에는 읽을 수 없습니다.

오라클 데이터베이스에서 데이터를 잠그는 방법

오라클 잠금은 자동으로 수행되며 사용자 작업이 필요 없다.

암시적 잠금은 요청된 작업에 따라 필요한 경우 SQL문에서 발생한다. 암시적 잠금은 SELECT를 제외한 모든 SQL문에

서 발생한다.

사용자가 수동으로 데이터를 잠글 수도 있으며 이를 명시적 잠금이라고 한다.

① 암시적(Implicit) 잠금

․ 두가지 잠금모드

- 배타적(exclusive) : 다른 사용자가 액세스하지 못하도록 자금

- 공유(share) : 다른 사용자의 액세스를 허용

․ 높은 레벨의 데이터 동시성

- DML : 테이블은 공유 잠금, 행은 배타적 잠금

- 질의 : 잠금 필요 없음

- DDL : 객체 정의 보호

․ 잠금은 커밋 또는 롤백이 수행될 때까지 유효한다.

MSSQL의 읽기 일관성 구현

SQL Server는 데이터베이스의 일관성을 유지하고 복구할 때 도움을 주기 위해 모든 트랜잭션을 트랜잭션 로그에 기록

한다

로그는 데이터베이스에 대한 변경 내용을 자동으로 추적하는 저장 영역이다.

SQL Server는 수정이 실행될 때 수정 사항을 데이터베이스에 저장하기 전에 디스크의 로그에 기록한다.

8-12-7. 잠금

데이터베이스에서 잠금은 다음과 같은 기능을 가진다.

․ 동시에 수행되는 트랜잭션 간에 파괴적인 상호 작용을 방지한다.

․ 사용자 작업이 필요 없다.

․ 자동으로 최대한 낮은 레벌의 제한을 사용한다.

․ 트랜잭션 실행 기간 동안 유지된다.

․ 명시적(explicit)자금과 암시적(implictit)잠금이라는 두 가지 유형이 있다.

잠금이란?

잠금은 사용자 객체(테이블 또는 행)또는 사용자가 볼 수 없는 시스템 객체(공유 데이터 구조 및 데이터 딕셔너리 행)와

같은 동일한 자원을 액세스하는 트랜잭션 간에 발생할 수 있는 파괴적인 상호작용을 방지하는 방식이다.

② DML 잠금

DML(데이터 조작어)작업을 수행할 때, Oracle server는 DML잠금을 통해 데이터 동시성을 제공한다.

DML잠금에는 두가지 레벨이 있다.

․ 공유잠금(share lock)은 DML작업 도중 테이블 레벨에서 자동을 수행된다.

공유잠금모드에서는 여러 트랜잭션이 동일한 자원에 대해 공유잠금을 획득할 수 있다.

․ 배타적잠금(exclusive lock)은 DML문에서 수정하는 각 행에 자동으로 수행된다.

배타적잠금은 트랜잭션이 커밋되거나 롤백될 때까지 다른 트랜잭션에서 해당 행을 변경하지 못하도록 한다.

이 잠금은 다른 사용자가 동시에 동일한 행을 수정하지 못하도록 하여 아직 커밋하지 않은 변경 내용을 다른 사용

자가 덮어 쓰지 못하도록 한다.

․ DDL잠금은 테이블과 같은 데이터베이스 객체를 수정할 경우 발생한다.

MSSQL 디비 예제 :

select price from titles

where title_id='bu1032'; -- 1

begin tran --2

update titles

set price = 100

where title_id='bu1032'; --3

select price from titles

where title_id='bu1032'; --4

rollback tran --5

select price from titles

where title_id='bu1032'; --6

--중첩 트랜잭션 예제 1..

select price from titles

where title_id='bu1032'; -- 1

begin tran --2

update titles

set price = 100

where title_id='bu1032'; --3

select price from titles

where title_id='bu1032'; --4

begin tran --5

update titles

set price = 300

where title_id='bu1032'; --6

MSSQL 데이터베이스에서 데이터를 잠그는 방법

배타적 잠금이란? (비관적 동시성 제어)

잠금 시스템은 사용자에게 영향을 주는 방식

한 사용자가 잠금이 적용되게 하는 작업을 하면 다른 사용자는 소유자가 잠금을 해제할 때까지 이 잠금과 충돌하는

작업은 수행할 수 없다.

경합이 많은 경우

공유 잠금이란? ( 최적 동시성 제어)

다른 사용자에게 영향을 미치지 않는 방식으로 데이터를 읽을 때 잠그지 않는다.

업데이트가 발생하게 되면 시스템은 다른 사용자가 데이터를 읽은 후 데이터가 변경했는지 확인 한다.

다른 사용자가 데이터를 업데이트를 한 경우 오류가 발생

경합이 적은 경우

select price from titles

where title_id='bu1032'; --7

rollback tran --8

select price from titles

where title_id='bu1032'; --9

중접 트랜잭이 이라고 하더래도 rollback한번이면 모두 취소

--중첩 트랜잭션 예제 2 ..

select price from titles

where title_id='bu1032'; -- 1

begin tran --2

update titles

set price = 100

where title_id='bu1032'; --3

select price from titles

where title_id='bu1032'; --4

begin tran --5

update titles

set price = 300

where title_id='bu1032'; --6

select price from titles

where title_id='bu1032'; --7

commit tran --8

select price from titles

where title_id='bu1032'; --9

rollback tran 또는 commit tran --10

select price from titles

where title_id='bu1032';

① commit 할 경우 begin tran한 만큼 commit을 해야 한다..

② rollback을 했을 경우 처음 begin tran한 것이 남아 있으면

③ rollback은 처음 begin trand으로 가게 된다.

--save transaction 예제 1...

select price from titles

where title_id='bu1032'; -- 1

begin tran --2

update titles

set price = 100

where title_id='bu1032'; --3

select @@trancount

save tran savepoint --4

select price from titles

where title_id='bu1032'; --5

begin tran --6

update titles

set price = 300

where title_id='bu1032'; --7

select @@trancount

save tran savepoint1 --8

select price from titles

where title_id='bu1032'; --9

commit tran savepoint1 -- 10

select @@trancount