SQLD 2과목 관리구문 - TCL

<목차> DML TCL DDL DCL

※ 자료출처: 데이터 산업 진흥원(K-DATA)

 

SQL 문장의 종류

 

명령어의 종류	명령어	설명
데이터 조작어(DML: Data Manipulation Language)	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어 (RETRIEVE)
	INSERT UPDATE DELETE	- 데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어 - 데이터를 테이블에 새로운 행에 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어
데이터 정의어(DDL: Data Definition Language)	CREATE ALTER DROP RENAME	- 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어 - 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어
데이터 제어어(DCL: Data Control Language)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
트랜잭션 제어어(TCL: Transactuon Control Language)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어

---

2. TCL (Transaction Control Language)

A. 트랜잭션의 개요

 

• 트랜잭션: 데이터베이스의 연산단위. 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작
• 하나의 트랜잭션에는 하나 이상의 SQL 문장 포함.
• 트랜잭션은 분할할 수 없는 최소의 단위 (전부 적용하거나 전부 취소 - ALL OR NOTHING)
• 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합
• 데이터베이스 응용 프로그램은 트랜잭션의 집합

커밋(COMMIT)	올바르게 반영된 데이터를 데이터베이스에 반영시키는 것
롤백(ROLLBACK)	트랜잭션 시작 이전의 상태로 되돌리는 것
TCL	저장점(SAVEPOINT) 기능과 함께 트랜잭션을 컨트롤

 

• 트랜잭션의 대상이 되는 SQL문: UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML
• SELECT 문장은 직접적인 트랜잭션의 대상 아님.
• SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있음.

[트랜잭션의 특성]

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

 

---

B. COMMIT

 

• 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단 되었을 경우 COMMIT 영령어를 통해 트랜잭션을 완료

 

• COMMIT 이나 ROLLBACK 이전의 데이터 상태

단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능 현재 사용자는 SELECT 문장으로 결과를 확인 가능 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다

 

[예제]

PLAYER 테이블에 데이터를 입력하고 COMMIT 실행

<ORACLE> INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. COMMIT; 커밋이 완료되었다.

 

[예제]

PLAYER 데이터에 있는 데이터를 수정하고 COMMIT 실행

<Oracle> UPDATE PLAYER SET HEIGHT = 100; 480개의 행이 수정되었다. COMMIT; 커밋이 완료되었다.

 

[예제]

PLAYER 테이블에 있는 데이터를 삭제하고 COMMIT 실행

<Oracle> DELECT FROM PLAYER; 480개의 항이 삭제되었다. COMMIT; 커밋이 완료되었다.

---

•  COMMIT 이후의 데이터 상태

데이터에 대한 변경 사항이 데이터베이스에 반영 이전 데이터는 영원히 잃어버리게 된다 모든 사용자는 결과를 볼 수 있다 관련된 행에 대한 잠금 (LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다

---

 

■ SQL Server의 COMMIT

• Oracle: DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며 DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해주어야 트랜잭션 종료
• SQL Server: 기본적으로 AUTO COMMIT 모드. DML 수행 후 사용자가 COMMIT이나 ROLLBACK 처리할 필요 없음. (DML 구문이 성공이면 자동으로 COMMIT, 오류가 발생할 경우 자동으로 ROLLBACK 처리)

[SQL Server 예제] PLAYER 테이블에 데이터를 입력 INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다.

[SQL Server 예제] PLAYER 테이블에 있는 데이터 수정 UPDATE PLAYER SET HEIGHT = 100; 480개의 행이 수정되었다.

[SQL Server 예제] PLAYER 테이블에 있는 데이터 삭제 DELECT FROM PLAYER; 480개의 항이 삭제되었다.

 

■ SQL Server 에서의 트랜잭션 방식

• AUTO COMMIT

SQL 기본 방식이며, DML, DDL 을 수행할 때마다 DBMS가 트랜잭션을 컨트롤 하는 방식. 명령어가 성공적으로 수행되면 자동으로 COMMIT 을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행

 

• 암시적 트랜잭션

Oracle과 같은 방식으로 처리. 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리. 인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본연결 옵션 중 암시적 트랜잭션에 체크. 세션 단위로 설정하기 위해서는 세션 옵션 중 SET IMPLICT TRANSACTION ON 사용

 

• 명시적 트랜잭션

트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식 BEGIN TRANSACTION (BEGIN TRAN) 으로 트랜잭션을 시작 COMMIT TRANSACTION (TRANACTION 생략 가능) 또는 ROLLBACK TRANSACTION (TRANSACTION 생략 가능)으로 트랜잭션 종료 ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 수행 됨.

 

---

 

C. ROLLBACK

• 테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 ROLLBACK 기능 사용.
• ROLLBACK 은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
• SQL Server 는 AUTO COMMIT 이 기본 방식이므로 임의적으로 ROLLBACK을 수행하려면 명시적으로 트랜잭션을 선언해야 한다.

 

• COMMIT과 ROLLBACK 을 사용함으로써 다음과 같은 효과를 볼 수 있다

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

• 어플리캐이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백

[에제]

PLAYER 테이블에 데이터를 입력하고 ROLLBACK 실행

<Oracle> INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. ROLLBACK; 롤백이 완료되었다.

 

[예제]

PLAYER 테이블에 있는 데이터 수정하고 ROLLBACK 실행

<Oracle> UPDATE PLAYER SET HEIGHT = 100; 480개의 행이 수정되었다. ROLLBACK; 롤백이 완료되었다.

 

[예제]

PLAYER 테이블에 있는 데이터를 삭제하고 ROLLBACK 실행

DELETE FROM PLAYER; 480개의 행이 삭제되었다. ROLLBACK; 롤백이 완료되었다.

---

■ SQL Server의 ROLLBACK

• SQL Server는 AUTO COMMIT 이 기본 방식이므로 ROLLBACK을 수행하려면 명시적으로 트랜잭션을 선언해야 함.

[SQL Server 예제] PLAYER 테이블에 데이터를 입력하고 ROLLBACK 실행 BEGIN TRAN INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. ROLLBACK; 롤백이 완료되었다.

[SQL Server 예제] PLAYER 테이블에 있는 데이터 수정하고 ROLLBACK 실행 BEGIN TRAN UPDATE PLAYER SET HEIGHT = 100; 480개의 행이 수정되었다. ROLLBACK; 롤백이 완료되었다.

[SQL Server 예제] PLAYER 테이블에 있는 데이터 삭제하고 ROLLBACK 실행 DELECT FROM PLAYER; 480개의 항이 삭제되었다. ROLLBACK; 롤백이 완료되었다.

 

---

 

D. SAVEPOINT

 

• SAVEPOINT(저장점)를 정의하면 ROLLBACK 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT 까지 트랜잭션의 일부만 롤백할 수 있다.
• 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT 까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다.
• 복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효.

 

ROLLBACK의 원리(Oracle 기준 / 출처: 데이터 산업 진흥원)

위의 그림에서 보듯이 저장점 A로 되돌리고 나서 다시 B와 같이 미래 방향으로 되돌릴 수 없음.
일단 특정 저장점가지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 됨.
'ROLLBACK TO A'를 실행한 시점에서 저장점 A 이후에 정의한 저장점 B는 존재하지 않음.

 

 

 

 

• SAVEPOINT SVPT1; ▶ 저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 명시.
• ROLLBACK TO SVPT1; ▶  ROLLBACK에 SAVEPOINT 명을 부여하여 실행하면 저장점 설정 이후에 있었던 데이터 변경에 대해서만 원래 데이터 상태로 되돌아감.
• SQL Server는 SAVE TRANSACTION 을 사용하여 동일한 기능 수행.

SAVE TRANSACTION SVTR1; ▶ 저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 명시. ROLLBACK TRANSACTION SVTR1;

 

 

[예제]

SAVEPOINT를 지정하고, PLAYER 테이블에 데이터를 입력한 다음 롤백 이전에 설정한 저장점까지 실행

<Oracle> SAVEPOINT SVPT1; 저장점이 생성되었다. INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. ROLLBACK TO SVPT1; 롤백이 완료되었다.

<SQL Server> SAVE TRAN SVTR1; 저장점이 생성되었다. INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1); 1개의 행이 만들어졌다. ROLLBACK TRANS SVTR1; 롤백이 완료되었다.

 

---

정리

 

• 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행 시 그 변경되는 데이터의 무결성을 보장하는 것이 커밋과 롤백의 목적
• 커밋: 변경된 데이터를 테이블이 영구적으로 반영
• 롤백: 변경된 데이터를 반영하지 말고 일정 시점(SAVEPOINT)로 복귀
• 저장점 (SAVEPOINT / SAVETRANSACTION): 데이터 변경을 사전에 지정한 저장점까지만 롤백

 

• Oracle의 트랜잭션: 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK을 실행한 시점에서 종료 

▶ COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료되는 경우

CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋됨. DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋

 

• SQL Server의 트랜잭션: DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식.

▶ COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료되는 경우

애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백

 

 

이전글

DML

 

다음글

DDL