[SQLD] TCL(Transaction Control Language)

Shy·2024년 5월 20일
SQLDTCL

SQLD

목록 보기
12/23

트랜잭션

트랜잭션(Transaction)은 데이터베이스에서 논리적 연산 단위를 의미한다. 이는 밀접하게 관련된 여러 개의 데이터베이스 조작을 하나의 단위로 묶은 것이다. 트랜잭션은 최소 한 개 이상의 SQL 문장을 포함하며, 분리할 수 없는 최소의 단위로 취급된다. 즉, 트랜잭션은 전부 적용되거나 전부 취소되는 “All or Nothing”의 개념을 가진다.

계좌이체를 예시로 들어 설명하자면, 계좌이체에는 최소한 두가지 작업으로 구성된다.

  1. 송금자의 계좌에서 잔액을 확인하고 이체할 금액을 인출한다.
  2. 수취인의 계좌에 이체된 금액을 입금한다.

이 두 작업이 모두 성공적으로 완료되어야 계좌이체가 성공적으로 종료된다. 만약 하나의 작업이라도 실패하면 전체 트랜잭션을 취소(롤백)하여 원래 상태로 되돌려야 한다. 이렇게 하는 이유는 일부 작업만 성공했을 경우 데이터의 일관성이 깨져 문제가 발생할 수 있기 때문이다.

1️⃣ 트랜잭션의 명령어

트랜잭션을 제어하는 명령어로는 다음이 있다.

  • 커밋(COMMIT): 트랜잭션 내의 모든 작업을 데이터베이스에 반영하는 명령어이다.
  • 롤백(ROLLBACK): 트랜잭션 시작 이전의 상태로 되돌리는 명령어이다.
  • 저장점(SAVEPOINT): 트랜잭션 내의 특정 지점까지 작업을 저장해두는 명령어이다.

이 명령어들은 트랜잭션 제어 언어(TCL: Transaction Control Language)에 속한다.

2️⃣ 트랜잭션의 특성 (ACID 특성)

트랜잭션은 다음과 같은 ACID 특성을 만족해야 한다.

  1. 원자성(Atomicity): 트랜잭션의 모든 작업이 모두 반영되거나 모두 반영되지 않아야 한다.
  2. 일관성(Consistency): 트랜잭션이 완료되면 데이터베이스는 일관된 상태를 유지해야 한다.
  3. 격리성(Isolation): 동시에 수행되는 트랜잭션들이 서로의 작업에 영향을 미치지 않아야 한다.
  4. 지속성(Durability): 트랜잭션이 성공적으로 완료되면 그 결과는 영구적으로 반영되어야 한다.

3️⃣ 잠금(Locking)

잠금은 트랜잭션의 일관성과 격리성을 보장하기 위해 사용된다. 데이터베이스는 다양한 레벨의 잠금 기능을 제공하여 특정 데이터에 대해 다른 트랜잭션이 동시에 접근하지 못하도록 제한한다. 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만 독점적으로 접근할 수 있으며, 다른 트랜잭션은 해당 데이터에 접근할 수 없다. 잠금은 트랜잭션이 완료되거나 롤백될 때 해제된다.

잠금에는 여러 종류가 있다.

  • 공유 잠금(Shared Lock): 여러 트랜잭션이 동시에 읽을 수 있지만, 쓰기는 할 수 없다.
  • 전용 잠금(Exclusive Lock): 한 트랜잭션만 읽고 쓸 수 있다.

이러한 잠금 메커니즘을 통해 데이터베이스는 트랜잭션의 원자성과 일관성을 보장한다.

이와 같은 트랜잭션의 개념과 특성은 데이터베이스의 안정성과 신뢰성을 유지하는 데 중요한 역할을 한다.

COMMIT

COMMIT 명령어는 데이터베이스에서 트랜잭션의 변경 사항을 영구적으로 반영하는 데 사용된다. 이를 통해 입력, 수정, 삭제 등의 작업이 확정되어 다른 사용자들도 변경된 내용을 볼 수 있게 된다.

1️⃣ COMMIT 이전의 데이터 상태

COMMIT 명령어가 실행되기 전의 데이터 상태는 다음과 같다.

  • 메모리 BUFFER에만 반영: 변경 사항은 아직 메모리 버퍼에만 존재하며, 데이터베이스에 영구적으로 저장되지 않는다. 이 때문에 언제든지 롤백할 수 있다.
  • 현재 사용자만 확인 가능: 현재 사용자는 SELECT 문으로 변경된 내용을 확인할 수 있지만, 다른 사용자는 볼 수 없다.
  • 잠금 상태: 변경된 데이터는 잠금이 걸려 있어 다른 사용자가 변경할 수 없다.

2️⃣ COMMIT 예제

1. 데이터 입력 후 COMMIT

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

2. 데이터 수정 후 COMMIT

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

3. 데이터 삭제 후 COMMIT

DELETE FROM PLAYER;
-- 480개의 행이 삭제되었다.
COMMIT;
-- 커밋이 완료되었다.

3️⃣ COMMIT 이후의 데이터 상태

COMMIT 명령어가 실행된 후의 데이터 상태는 다음과 같다.

  • 데이터베이스에 반영: 변경 사항이 영구적으로 데이터베이스에 저장된다.
  • 이전 데이터 손실: 변경 이전의 데이터는 영구적으로 손실된다.
  • 다른 사용자와 공유: 모든 사용자가 변경된 데이터를 볼 수 있다.
  • 잠금 해제: 관련된 행에 대한 잠금이 풀리며, 다른 사용자들이 해당 데이터를 조작할 수 있게 된다.

4️⃣ SQL Server의 COMMIT

SQL Server에서는 기본적으로 AUTO COMMIT 모드가 활성화되어 있어 DML 문장이 성공적으로 수행되면 자동으로 COMMIT이 실행된다. 반면, 오류가 발생하면 자동으로 ROLLBACK이 수행된다.

SQL Server 예제는 다음과 같다.

1. 데이터 입력

INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
-- 1개의 행이 만들어졌다.

2. 데이터 수정

UPDATE PLAYER SET HEIGHT = 100;
-- 480개의 행이 수정되었다.

3. 데이터 삭제

DELETE FROM PLAYER;
-- 480개의 행이 삭제되었다.

5️⃣ SQL Server의 트랜잭션 방식

SQL Server는 기본적으로 3가지 트랜잭션 방식을 지원한다.

  1. AUTO COMMIT
    • 기본 설정으로, 각 DML, DDL 명령어가 자동으로 트랜잭션을 관리한다.
    • 명령어가 성공하면 자동으로 COMMIT, 오류가 발생하면 자동으로 ROLLBACK.
  2. 암시적 트랜잭션
    • Oracle과 유사하게, 트랜잭션 시작은 자동으로 처리되며 사용자가 명시적으로 COMMIT 또는 ROLLBACK을 실행한다.
    • 인스턴스 단위로 설정하려면 서버 속성 창에서 “암시적 트랜잭션”을 체크.
    • 세션 단위로 설정하려면 SET IMPLICIT TRANSACTION ON을 사용.
  3. 명시적 트랜잭션
    • 트랜잭션의 시작과 끝을 사용자가 명시적으로 지정.
    • BEGIN TRANSACTION으로 시작하고, COMMIT TRANSACTION 또는 ROLLBACK TRANSACTION으로 종료.
    • ROLLBACK 명령어는 최초의 BEGIN TRANSACTION 시점까지 모든 변경을 롤백.

COMMIT 명령어는 데이터베이스 트랜잭션의 중요한 요소로, 트랜잭션의 변경 사항을 확정하여 데이터베이스에 반영합니다. 이는 데이터 일관성과 무결성을 유지하는 데 중요한 역할을 합니다. Oracle과 SQL Server는 각각의 방식으로 COMMIT을 처리하며, 트랜잭션 제어 방법에도 차이가 있습니다.

ROLLBACK 명령어

ROLLBACK 명령어는 데이터베이스에서 트랜잭션의 변경 사항을 취소하고, 데이터를 이전 상태로 복구하는 데 사용다. COMMIT 이전에 입력, 수정, 삭제한 데이터를 되돌릴 수 있다.

2️⃣ ROLLBACK의 기능

  • 데이터 변경 취소: 트랜잭션 동안 이루어진 모든 데이터 변경 사항을 취소한다.
  • 잠금 해제: 관련된 행에 대한 잠금을 해제하여 다른 사용자가 데이터 변경을 할 수 있게 한다.

3️⃣ Oracle의 ROLLBACK

1. 데이터 입력 후 ROLLBACK

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

2. 데이터 수정 후 ROLLBACK

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

3. 데이터 삭제 후 ROLLBACK

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

4️⃣ SQL Server의 ROLLBACK

SQL Server에서는 기본적으로 AUTO COMMIT 모드를 사용하므로, 명시적으로 ROLLBACK을 수행하려면 트랜잭션을 선언해야 합니다.

1. 데이터 입력 후 ROLLBACK

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

2. 데이터 수정 후 ROLLBACK

BEGIN TRAN
UPDATE PLAYER SET HEIGHT = 100;
-- 480개의 행이 수정되었다.
ROLLBACK;
-- 롤백이 완료되었다.

3. 데이터 삭제 후 ROLLBACK

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

5️⃣ ROLLBACK 후의 데이터 상태

  • 변경 사항 취소: 트랜잭션 동안의 모든 데이터 변경 사항이 취소된다.
  • 데이터 복구: 이전 데이터 상태로 복구된다.
  • 잠금 해제: 관련된 행에 대한 잠금이 풀려 다른 사용자가 해당 데이터를 조작할 수 있다.

6️⃣ COMMIT과 ROLLBACK의 효과

  • 데이터 무결성 보장: 트랜잭션 중 오류 발생 시 변경 사항을 취소하여 데이터의 일관성과 무결성을 유지한다.
  • 변경 사항 확인: 영구적인 변경을 하기 전에 데이터의 변경 사항을 확인할 수 있다.
  • 작업 그룹화: 논리적으로 연관된 작업을 하나의 트랜잭션으로 묶어 일괄 처리할 수 있다.

COMMIT과 ROLLBACK 명령어는 데이터베이스 트랜잭션을 제어하는 중요한 도구로, 데이터 일관성과 무결성을 보장하고 오류를 방지하는 데 필수적이다.

SAVEPOINT

SAVEPOINT 명령어는 트랜잭션 내에서 특정 지점을 저장하여, 필요한 경우 해당 지점까지 롤백할 수 있도록 한다. 이는 복잡한 트랜잭션에서 부분적인 롤백을 가능하게 하여 에러가 발생한 부분만 수정할 수 있도록 도와준다.

1️⃣ SAVEPOINT의 기능

  • 부분 롤백: 트랜잭션의 일부만 롤백하여 전체 트랜잭션을 취소하지 않고도 일부 변경 사항을 취소할 수 있다.
  • 복수 저장점: 여러 개의 저장점을 정의할 수 있으며, 동일한 이름으로 저장점을 정의하면 나중에 정의한 저장점이 유효하다.

2️⃣ Oracle의 SAVEPOINT

  • 특정 저장점으로 롤백하면 해당 저장점 이후에 설정한 다른 저장점은 무효화된다. 예를 들어, 저장점 A로 롤백한 후 저장점 B는 더 이상 존재하지 않게 된다.
  • 저장점 없이 ROLLBACK을 실행하면 모든 변경 사항이 취소되고 트랜잭션 시작 위치로 돌아간다.

1. 데이터 입력 후 SAVEPOINT 지정 및 롤백

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

2. 데이터 수정 후 SAVEPOINT 지정 및 롤백

SAVEPOINT SVPT2;
-- 저장점이 생성되었다.
UPDATE PLAYER SET WEIGHT = 100;
-- 480개의 행이 수정되었다.
ROLLBACK TO SVPT2;
-- 롤백이 완료되었다.

3. 데이터 삭제 후 SAVEPOINT 지정 및 롤백

SAVEPOINT SVPT3;
-- 저장점이 생성되었다.
DELETE FROM PLAYER;
-- 480개의 행이 삭제되었다.
ROLLBACK TO SVPT3;
-- 롤백이 완료되었다.

3️⃣ SQL Server의 SAVEPOINT

SQL Server에서는 SAVE TRANSACTION 명령어를 사용하여 동일한 기능을 수행할 수 있다.

1. 데이터 입력 후 SAVEPOINT 지정 및 롤백

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

2. 데이터 수정 후 SAVEPOINT 지정 및 롤백

BEGIN TRAN
SAVE TRAN SVTR2;
-- 저장점이 생성되었다.
UPDATE PLAYER SET WEIGHT = 100;
-- 480개의 행이 수정되었다.
ROLLBACK TRAN SVTR2;
-- 롤백이 완료되었다.

3. 데이터 삭제 후 SAVEPOINT 지정 및 롤백

BEGIN TRAN
SAVE TRAN SVTR2;
-- 저장점이 생성되었다.
UPDATE PLAYER SET WEIGHT = 100;
-- 480개의 행이 수정되었다.
ROLLBACK TRAN SVTR2;
-- 롤백이 완료되었다.

트랜잭션 관리

1️⃣ COMMIT과 ROLLBACK의 역할

  • COMMIT: 데이터 변경 사항을 확정하여 영구적으로 반영한다.
  • ROLLBACK: 데이터 변경 사항을 취소하여 이전 상태로 되돌린다.
  • SAVEPOINT: 특정 지점을 저장하여 부분적인 롤백을 가능하게 한다.

2️⃣ 트랜잭션 종료 조건

Oracle과 SQL Server 모두 특정 조건에서 자동으로 트랜잭션을 종료한다.

  • Oracle:
    • DDL 문장(CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등)을 실행하면 자동으로 COMMIT.
    • 데이터베이스 정상 종료 시 자동 COMMIT.
    • 애플리케이션 이상 종료 시 자동 ROLLBACK.
  • SQL Server:
    • 기본적으로 AUTO COMMIT 모드로, DML 명령어 실행 후 자동 COMMIT 또는 ROLLBACK.
    • 애플리케이션 이상 종료 시 자동 ROLLBACK.

SAVEPOINT 명령어는 복잡한 트랜잭션 내에서 부분 롤백을 가능하게 하여 데이터 무결성과 일관성을 유지하는 데 중요한 역할을 한다. Oracle과 SQL Server 모두 이러한 기능을 제공하며, 트랜잭션 관리의 유연성을 높여준다.

profile
Shy
신입사원...
이전 포스트

[SQLD] DML(Data Manipulation Language)

다음 포스트

[SQLD] WHERE

0개의 댓글