[SQLD 학습 자료 요약] SQL 기본 및 활용 1.4. TCL

본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.

SQL 전문가 가이드 국내도서 저자 : 한국데이터산업진흥원 출판 : 한국데이터산업진흥원 2020.05.29 상세보기

 

4. TCL

1. 트랜잭션 개요

트랜잭션은 데이터베이스의 논리적 연산단위이다. 트랜잭션(TRANSACTION)이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

 

트랜잭션은 분할할 수 없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소한다. 즉, TRANSACTION 은 ALL OR NOTHING 의 개념인 것이다.

 

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

 

트랜잭션의 특성

• 원자성: 연산들은 모두 성공적으로 실행되든지 전혀 실행되지 않은 상태로 남아야 한다.
• 일관성: 실행되기 전의 DB 내용이 잘못되어 있지 않다면, 트랜잭션 실행 이후에도 잘못이 있으면 안된다.
• 고립성: 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과가 만들어지면 안된다.
• 지속성: 트랜잭션이 성공적으로 수행되면 그 내용은 영구적으로 저장된다.

 

트랜잭션의 특성(특히 원자성)을 충족하기 위해 데이터베이스는 다양한 레벨의 잠금 기능을 제공하고 있는데, 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다. 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고 다른 트랜잭션으로부터 간섭이나 방해를 받지 않는 것이 보장된다. 그리고 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만이 해제할 수 있다.

 

---

2. COMMIT

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

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

 

UPDATE PLAYER SET HEIGHT = 100;
COMMIT;

DELETE FROM PLAYER;
COMMIT;

 

COMMIT 이후의 데이터 상태

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

 

 

SQL Server의 COMMIT

• 기본적으로 AUTO COMMIT 모드이기 때문에 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다.
• 인스턴스 단위 또는 세션 단위로 COMMIT 모드를 설정할 수 있다. (암시적 트랜잭션)
• 명시적 트랜잭션은 시작과 끝을 모두 사용자가 지정하는 방식이다. BEGIN TRANSCATION (BEGIN TRAN)으로 시작하고 COMMIT (TRANSACTION)으로 종료한다. ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

 

---

3. ROLLBACK

DELETE FROM PLAYER;
ROLLBACK;

 

 

SQL Server의 ROLLBACK

• AUTO COMMIT이 기본이므로 임의적으로 ROLLBACK을 수행하려면 명시적으로 트랜잭션을 선언해야 한다.

BEGIN TRAN
DELETE FROM PLAYER;
ROLLBACK;

 

 

ROLLBACK 후의 데이터 상태

• 데이터에 대한 변경 사항은 취소된다.
• 이전 데이터는 다시 재저장된다.
• 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

COMMIT과 ROLLBACK의 효과

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

 

---

4. SAVEPOINT

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

 

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

-- Oracle
SAVEPOINT SVPT1;

ROLLBACK TO SVPT1;

-- SQL Server
SAVE TRANSACTION SVTR1;

ROLLBACK TRANSACTION SVTR1;

 

 

지정된 SAVEPOINT로 ROLLBACK (출처: SQL 전문가 가이드)

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

 

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

• DDL 문장 실행 시 자동으로 커밋
• DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 자동으로 커밋
• 정상적으로 DB 접속을 종료하면 커밋
• 이상 종료로 DB 접속이 단절되었을 경우에는 자동으로 롤백

 

---

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

1.1. 데이터 모델링의 이해

1.2. 엔터티

1.3. 속성

1.4. 관계

1.5. 식별자

 

Part 2. 데이터 모델과 성능

2.1. 성능 데이터 모델링 개요

2.2. 정규화와 성능

2.3. 반정규화와 성능

2.4. 대량 데이터에 따른 성능

2.5. 데이터베이스 구조와 성능

2.6. 분산 데이터베이스와 성능

 

2장. SQL 기본 및 활용

Part 1. SQL 기본

1.1. 관계형 데이터베이스 개요

1.2. DDL

1.3. DML

1.4. TCL

1.5. WHERE 절

1.6. 함수 (FUNCTION)

1.7. GROUP BY, HAVING 절

1.8. ORDER BY 절

1.9. 조인 (JOIN)

 

Part 2. SQL 활용

2.1. 표준 조인

2.2. 집합 연산자

2.3. 계층형 질의와 셀프 조인

2.4. 서브쿼리

2.5. 그룹 함수

2.6. 윈도우 함수

2.7. DCL

2.8. 절차형 SQL

 

Part 3. SQL 최적화 기본원리

3.1. 옵티마이저와 실행계획

3.2. 인덱스 기본

3.3. 조인 수행 원리

 

따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.