[SQLD] 15. TCL(Transaction Control Language)

제 4절 TCL(Transaction control language)

1. 트랜잭션 개요

- 트랜잭션은 데이터베이스의 논리적 연산단위

- 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작

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

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

- ALL OR NOTHING 개념

1.1 트랜잭션의 특성

1.2 잠금(LOCKING)

  트랜잭션의 특성을 충족하기 위해 데이터베이스는 다양한 레벨의 잠금 기능을 제공하고 있는데 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다. 

  잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고 다른 트랜잭션으로부터 간섭이나 방해를 받지 않는 것이 보장된다. 그리고 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만이 해제할 수 있다.

1.3 TCL(Transaction control language)

  

- COMMIT

- ROLLBACK

- SAVEPOINT

  데이터베이스 변경 작업 시 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 커밋(COMMIT), 트랜잭션 시작 이전의 상태로 되돌리는 것을 롤백(ROLLBACK)이라고 하며, 저장점(SAVEPOINT) 기능과 함께 3가지 명령어를 트랜잭션을 콘트롤하는 TCL(Transaction control language)로 분류한다.

  트랜잭션의 대상이 되는 SQL 문은 자동 COMMIT 기능을 제공하는 DDL과는 달리 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML가 대상이 된다. SELECT 문장은 직접적인 트랜잭션의 대상이 아니지만 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다. 

2. COMMIT(커밋)

  데이터베이스에서 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 수행 할 수 있다.

다음 내용은 ROLLBACK이나 COMMIT 이전의 데이터 상태를 설명한다.

- 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구가 가능

- 현재 사용자는 SELECT 문장으로 결과를 확인

- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없음

- 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없음

2.1 커밋 예시

INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);

1개의 행이 만들어졌다.

COMMIT;

커밋이 완료되었다.

------------------------------------------------------

UPDATE INTO PLAYER

SET HEIGHT = 100;

480개의 행이 수정되었다.

COMMIT;

커밋이 완료되었다.

------------------------------------------------------

DELETE FROM PLAYER;

480개의 행이 삭제되었다.

COMMIT;

커밋이 완료되었다.

------------------------------------------------------

COMMIT 명령어는 이처럼 INSERT 문장, UPDATE 문장, DELETE 문장을 사용한 후에 이런 변경 작업이 완료되었음을 데이터베이스에 알려주기 위해 사용한다. 

COMMIT 이후의 데이터 상태는 다음과 같다.

- 데이터에 대한 변경 사항이 데이터베이스에 반영

- 이전 데이터는 영원히 잃음

- 모든 사용자는 결과를 볼 수 있음

- 관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있음

2.2 SQL Server의 COMMIT

  앞서 다룬바와 같이 SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다. DML 구문이 성공이면 자동적으로 커밋하고, 만일 오류가 발생할 경우 자동으로 ROLLBACK 처리를 하게 된다.

SQL Server에서의 트랜잭션은 기본적으로 3가지 방식으로 수행될 수 있다.

- AUTO COMMIT

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

- 암시적 트랜잭션

  Oracle과 같은 방식으로 처리된다. 즉, 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT, ROLLBACK 할 수 있다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다. 

- 명시적 트랜잭션

  트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다. 

BEGIN TRANSACTION(= BEGIN TRAN)으로 트랜잭션을 시작

COMMIT TRANSACTION or ROLLBACK TRANSACTION(TRANSACTION 은 생략 가능) 으로 트랜잭션을 종료

3. ROLLBACK(롤백)

  테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백(ROLLBACK) 기능을 사용한다. 롤백은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

3.1 롤백 예제

UPDATE PLAYER SET HEIGHT = 100;

480개의 행이 수정되었다.

ROLLBACK;

롤백이 완료되었다.

=> 이 경우 480개의 행이(키 들이) 100으로 수정되었던 결과를 다시 되돌리기 위해 롤백하여 처음의 상태로 돌아오도록 만드는 것

3.2 SQL Server의 ROLLBACK

SQL Server에서 롤백 기능을 사용하려면 명시적으로 트랜잭션을 수행하여야한다.

BEGIN TRAN

UPDATE PLAYER SET HEIGHT =100;

480개의 행이 수정되었다.

ROLLBACK;

롤백이 완료되었다.

BEGIN TRAN

DELETE FROM PLAYER;

480개의 행이 삭제되었다.

ROLLBACK;

롤백이 완료되었다.

ROLLBACK 후의 데이터 상태는 다음과 같다.

- 데이터에 대한 변경 사항은 취소

- 이전 데이터는 다시 재저장

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있게 됨

3.3 COMMIT과 ROLLBACK의 효과

- 데이터 무결성 보장

- 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

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

4. SAVEPOINT(저장점)

  저장점을 정의하면 롤백을 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있다. 따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 저장점까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다. 

  복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다. 

SAVEPOINT SVPT1;

ROLLBACK TO SVPT1;

SQL Server에서는 다음과 같이 사용한다.

SAVE TRANSACTION SVTR;

ROLLBACK TRANSACTION SVTR;

4.1 SAVEPOINT에 따른 ROLLBACK의 원리 

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

  저장점 지정 없이 "ROLLBACK"을 실행하였을 경우 반영안된 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 되돌아간다.

4.2 SAVEPOINT 예시 (Oracle 기준)

SELECT COUNT(*) 

FROM PLAYER; 

COUNT(*) 

------- 

480 

1개의 행이 선택되었다. 

SELECT COUNT(*) 

FROM PLAYER 

WHERE WEIGHT = 100; 

COUNT(*) 

------- 

0 

1개의 행이 선택되었다.

------------------------------------------------------------------------

INSERT INTO PLAYER 

(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO) 

VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 

1개의 행이 만들어졌다. 

SAVEPOINT SVPT_A; 

저장점이 생성되었다. 

UPDATE PLAYER 

SET WEIGHT = 100; 

481개의 행이 수정되었다. 

SAVEPOINT SVPT_B; 

저장점이 생성되었다. 

DELETE FROM PLAYER; 

481개의 행이 삭제되었다. 

------------------------------------------------------------------------

SELECT COUNT(*) 

FROM PLAYER; 

COUNT(*) 

-------- 

0 

1개의 행이 선택되었다. 

ROLLBACK TO SVPT_B; 

롤백이 완료되었다. 

SELECT COUNT(*) 

FROM PLAYER; 

COUNT(*) 

------- 

481 

1개의 행이 선택되었다.

------------------------------------------------------------------------

SELECT COUNT(*) 

FROM PLAYER 

WHERE WEIGHT = 100; 

COUNT(*) 

------- 

481 

1개의 행이 선택되었다. 

ROLLBACK TO SVPT_A; 

롤백이 완료되었다. 

SELECT COUNT(*) 

FROM PLAYER 

WHERE WEIGHT = 100; 

COUNT(*) 

------- 

0

1개의 행이 선택되었다.

------------------------------------------------------------------------

SELECT COUNT(*) 

FROM PLAYER; 

COUNT(*) 

------- 

481 

1개의 행이 선택되었다. 

ROLLBACK; 

롤백이 완료되었다. 

SELECT COUNT(*) 

FROM PLAYER; 

COUNT(*) 

------- 

480 

1개의 행이 선택되었다.

5. 정리

  해당 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행 시 그 변경 되는 데이터의 무결성을 보장하는 것이 커밋(COMMIT)과 롤백(ROLLBACK)의 목적이다. 

  커밋은 변경된 데이터를 테이블에 영구적으로 반영해라 라는 의미를 갖고있고, 롤백은 변경된 데이터에 문제가 생겼으니 변경 전 데이터로 돌아가라 라는 의미이다. 마지막으로 저장점(SAVEPOINT, SAVE TRANSACTION)은 데이터 변경을 사전에 지정한 저장점까지만 롤백하라 라는 의미이다.

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

단 다음의 경우에는 커밋과 롤백을 실행하지 않아도 자동으로 트랜잭션이 종료된다.

- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋

- DML 문장 이후에 커밋없이 DDL 문장을 실행하면 자동으로 커밋

- 데이터베이스 정상 접속 종료시 자동으로 트랜잭션이 커밋

- 어플리케이션의 이상으로 데이터베이스와 접속이 단절됬을 때 트랜잭션이 자동으로 롤백