TCL(Transaction Control Language)

TCL 이란?

DCL(Data Control Language)에서 트랜잭션을 제어하는 명령인 COMMIT과 ROLLBACK만을 따로 분리해서 TCL이라고 한다.

 

트랜잭션

데이터베이스의 논리적 연산 단위

• 의미적으로 분할할 수 없는 최소의 단위
• 일반적으로 하나의 트랜잭션은 여러 SQL 문장을 포함한다.
• 성공 시 모든 연산을 반영, 취소 시 모든 연산을 취소한다 => All or Nothing

 

트랜잭션 상황 예시

도서 주문

• 재고 수량 감소,
• 주문 내역 생성
• 결제
• 포인트 적립

 

교통카드 충전

• 잔액 증가
• 결제

 

계좌 이체

• 원 계좌의 잔액 감소
• 주문 내역 생성
• 결제
• 포인트 적립

이체라는 하나의 행위를 실행하기 위해서는 한 쪽에서 잔액을 감소하고, 다른 한쪽에서 잔액을 증가시키는 두 연산이 필요하다.

 

트랜잭션의 특성 (ACID 특성)

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

 

지속성

• 트랜잭션을 수행하기 위해서는 데이터베이스에 있는 테이블들을 메모리로 가지고 와야 한다.
• 메모리에서 연산을 모두 수행하고 나서, 문제없이 종료되면 DB에 길고하라는 commit 명령어 실행한다.
• 반영되고 난 데이터베이스의 내용은 다른 트랜잭션이 수행되지 않은 한 영구적으로 안바뀐다는 의미이다. 

 

트랜잭션의 ACID 특성을 보장하기 위해 DBMS는 동시성 제어(Concurrency Control) 수행

• DBMS에서는 트랜잭션 관리자 라는 모듈을 따로 둔다. 이 트랜잭션 관리자가 동시성 제어를 수행한다.
  • 트랜잭션 따로 따로 수행하게 되면 비효율적이기 때문에 섞여서 수행되는데
    이 때, 주의해야 할 점은 섞여서 수행되지만 혼자 따로 따로 고립되어서 수행된 것처럼 보장해줘야 한다.
  • 이 때 사용하는 알고리즘이 동시성 제어 알고리즘이다.
    • LOCK 기반, TIMESTAMPE 기반

 

트랜잭션을 제어하기 위한 명령어

• COMMIT: 변경된 내용을 DB에 영구적으로 반영한다.
• ROLLBACK:
  • 기본 - 변경된 내용을 버리고 변경 전 상태(마지막 COMMIT)로 복귀한다.
  • SAVEPOINT를 지정한 경우, 지정한 저장점까지만 복귀한다.
  • SAVEPOINT: 부분 복귀를 위해 지정한 저장점
• 트랜잭션은 SQL 문 실행 시 자동 시작, COMMIT/ROLLBACK 실행 시 종료한다.
• 자동 커밋 / 자동 롤백
  • DDL 문장 수행 시 DDL 수행 전에 자동으로 커밋한다.(auto commit)
  • DB를 정상적으로 접속 종료하면 자동 커밋한다.

 

COMMIT

UPDATE player SET height = height + 10;

• COMMIT 실행 전 상태에서는
  • 변경된 내용은 메모리에 임시로 저장된다.
  • 현재 사용자는 증가한 HEGHT 값을 읽을 수 있다. (메모리에 저장되어 있어서)
  • 하지만, 다른 사용자는 증가 전 HEIGHT 값만 읽을 수 있다. (아직 DB에는 반영 X)
  • HEIGHT에는 잠금(Locking)이 설정되어 다른 사용자는 값을 변경할 수 없다.

 

COMMIT;

• COMMIT 실행 후
  • 변경된 내용이 DB에 저장된다.
  • 변경 내용은 모두 다른 사용자가 볼 수 있다.
  • 이전 데이터는 모두 사라진다. (별도 로그 보관 시 복구 가능)
  • 관련된 행에 대한 잠금이 해제되어 모든 사용자가 변경할 수 있다.

 

ROLLBACK

• https://www.postgresql.org/docs/current/sql-rollback-to.html

위 사진에서 현재 위치에서 ROLLBACK이 일어나면 갈 수 있는 곳은 3가지이다.

1. 트랜잭션이 시작하는 부분(그냥 ROLLBACK하면 시작 위치가 이동한다.)
2. SAVEPOINT A
3. SAVEPOINT B

COMMIT;

UPDATE player SET height = height + 10;
DELETE FROM player;

ROLLBACK;

변경한 내용이 모두 취소되어 이전 데이터가 다시 재저장된다. 그리고 관련된 행에 대한 잠금이 해제되어 모든 사용자가 변경할 수 있다.

 

SAVEPOINT

• https://www.postgresql.org/docs/current/sql-savepoint.html

미리 지정한 SAVEPOITN까지만 ROLLBACK한다. 특정 저장점까지 롤백하면 그 이후의 명령과 저장점은 모두 무효가 된다.

...
SAVEPOINT PT1;

...
SAVEPOINT PT2;

...
SAVEPOINT PT3;

ROLLBACK TO PT1; -- SAVEPOINT PT1으로 이동 -> 이후 명령은 모두 무효가 됨

 

 

커밋하지 않은 데이터를 다른 곳에서  조회할 수 있으면 어떤 문제가 발생할까?

사용자의 요청마다 커넥션과 세션이 생성되고, 세션이 SQL을 실행한다. 이 때 사용자1, 사용자2에 따른 세션1과 세션2가 있다고 가정하자.

사용자1이 트랜잭션을 시작하여 신규 회원1, 2를 DB에 추가하고, 아직 커밋하지 않은 상태이다. 새로운 데이터는 임시 상태로 저장된다.

이때 커밋하지 않은 데이터가 보인다면, 세션2는 데이터를 조회했을 때 신규 회원1, 2가 보일텐데 사용자2는 신규 회원 1, 2가 있다고 가정하고 어떤 로직을 수행할 것이다. 그런데 세션1이 롤백을 수행하면 신규 회원 1, 2의 데이터가 사라지고 데이터 정합성에 큰 문제가 발생한다.

즉, 세션2에서 세션1이 아직 커밋하지 않은 변경 데이터가 보인다면, 세션1이 롤백했을 때 심각한 문제가 발생할 것이다.

따라서 커밋 전의 데이터는 다른 세션에서 보이지 않는 것이 좋다.

 

AUTOCOMMIT 적용한 경우

세션 1

세션 1에서 자동 커밋 모드를 설정하여 다음과 같이 데이터를 삽입하면 다른 세션에서는 어떻게 레코드를 반환하는지 확인해보자.

 

세션2

autocommit 설정을 통해서 해 명령어마다 commit이 자동으로 실행되어 다른 세션에서도 반영된 것을 확인할 수 있다.

 

AUTOCOMMIT을 적용하지 않은 경우

세션1

세션1에 autocommit을 false로 설정하고 다른 세션에서는 어떻게 레코드를 반환하는지 확인해보자.

 

세션2

세션2에서는 세션1에서 commit하기 전까지는 DB에 반영되지 않아 레코드 두 개가 반환되지 않은 것을 확인할 수 있다.