MSSQL - 트랜잭션의 개념 이해와 종류, 사용 방법

해당 포스팅은 MSSQL을 기준으로 작성하였습니다. 오라클과는 다를 수 있습니다.

트랜잭션이란? (Transaction)

데이터베이스 관리 시스템에서 상호작용의 단위
하나의 논리적 작업 단위로 수행되는 일련의 작업
성공과 실패가 분명하고 상호 독립적이며, 일관되고 믿을 수 있는 시스템을 의미
이론적으로 원자성(Atomicity), 일관성(Consistency), 독립성(Isolation), 영구성(Durability)을 보장 (ACID)
- 원자성 : 더 이상 분류 할 수 없는 작업이여야 함
- 일관성 : 모든 데이터는 일관되어야 함
- 독립성 : 다른 트랜잭션에 의한 수정과 독립되어야 함
  - 트랜잭션이 종료 전까지는 다른 트랜잭션에서 해당 트랜잭션의 데이터를 볼 수 없음
- 영구성 : 트랜잭션이 완료되면 영구적으로 시스템에 적용되어야 함
- 실제로는 성능향상을 위해 이런 특성들이 종종 완화

트랜잭션의 개념 이해를 위한 예시

1. 급여 통장에서 100만원을 생활비 통장으로 송금

2. 정전으로 인하여 급여 통장에서는 100만원이 빠졌는데 생활비 통장은 미입금

3. 그럼 100만원은 어디로???

위와 같은 예시처럼 문제가 생기면 전부 되거나 아예 안되도록하는 일의 최소 단위를 트랜잭션이라고 정의

트랜잭션의 이해

실행 과정

정상(커밋) 처리
- 시작 > 처리 > 커밋 > 데이터베이스에 처리 내용 적용 > 종료
롤백 처리
- 시작 > 처리 > 롤백 > 데이터베이스 이전 상태로 복구 > 종료

트랜잭션 구문

BEGIN
- 트랜잭션 시작
- 커밋이나 롤백을 명령하기 전까지 이후의 모든 실행을 해당 트랜잭션으로 구분
COMMIT
- 트랜잭션 종료
- 트랜잭션의 처리가 정상적으로 완료되면 데이터베이스에 처리 내용 적용
ROLLBACK
- 트랜잭션 취소
- 트랜잭션이 처리한 모든 실행을 이전의 상태로 복구

-- !!! success(commit) !!!
-- Transaction Start
BEGIN TRAN
    -- input SQL Query
-- Transaction End
COMMIT TRAN


-- !!! failed(rollback) !!!
-- Transaction Start
BEGIN TRAN
    -- input SQL Query
    -- ERROR
-- Transaction Rollback (State=End)
ROLLBACK TRAN

트랜잭션의 종류

시스템 트랜잭션 (암시적 트랜잭션, Implicit Transaction)

시작과 끝을 시스템이 지정하고 처리
- SQL 서버 자체가 트랜잭션 처리
모든 데이터를 수정하는 SQL문에는 자동적으로 시스템 트랜잭션이 처리
- update문마다 'BEGIN TRAN', 'COMMIT TRAN'을 앞 뒤로 붙여 처리
즉, 우리가 직접 명시하지 않아도 시스템에서 알아서 해당 구문을 붙여 처리해줌
예시) update문 실행에 따른 시스템 트랜잭션의 처리 구문

-- 테이블의 값을 수정하는 update 문장
-- !!! update query !!!
update table set field1 = 'a' where field2 = '1'

-- update 문장 실행시 시스템은 아래의 문장으로 처리
-- !!! system transaction (=implicit transaction) !!!
BEGIN TRAN
	update table set field1 = 'a' where field2 = '1'
COMMIT TRAN

시스템 트랜잭션이 수행되는 키워드

ALTER TABLE
CREATE
DELETE
DROP
FETCH
GRANT
INSERT
OPEN
REVOKE
SELECT
TRUNCATE TABLE
UPDATE
일괄 처리 범위의 트랜잭션
분산 트랜잭션
암시적 트랜잭션 내에 프로시저

사용자 트랜잭션 (명시적 트랜잭션, Explicit Transaction)

트랜잭션의 시작과 끝을 사용자가 명시적으로 지정
예시) update문 반복 실행에 따른 시스템 트랜잭션과 사용자 트랜잭션의 처리 구문 (차이 확인)
- 업데이트 반복 실행시 트랜잭션의 과정
  - 시스템 트랜잭션 : 각 업데이트문마다 트랜잭션 수행
  - 사용자 트랜잭션 : 모든 업데이트문에 대한 트랜잭션 수행

-- 테이블의 값을 수정하는 update 문장
-- !!! update query !!!
update table set field1 = 'a' where field2 = '1'
update table set field1 = 'b' where field2 = '2'

-- update 문장 실행시 시스템은 아래의 문장으로 처리
-- 시스템 트랜잭션은 update문을 각각의 BEGIN과 COMMIT으로 처리
-- !!! system transaction (=implicit transaction) !!!
BEGIN TRAN
	update table set field1 = 'a' where field2 = '1'
COMMIT TRAN
BEGIN TRAN
	update table set field1 = 'b' where field2 = '2'
COMMIT TRAN

-- BEGIN과 COMMIT을 명시하여 사용자 트랜잭션으로 처리
-- 사용자 트랜잭션을 이용하여 update의 반복 실행을 일관화
-- !!! user transaction (=explicit transaction) !!!
BEGIN TRAN
	update table set field1 = 'a' where field2 = '1'
	update table set field1 = 'b' where field2 = '2'
COMMIT TRAN

사용자 트랜잭션이 수행되는 키워드

CREATE DATABASE
ALTER DATABASE
DROP DATABASE
CREATE FULLTEXT CATALOG
ALTER FULLTEXT CATALOG
DROP FULLTEXT CATALOG
DROP FULLTEXT INDEX
ALTER FULLTEXT INDEX
CREATE FULLTEXT INDEX
BACKUP
RESTORE
RECONFIGURE
전체 텍스트 시스템 저장 프로시저

사용자 트랜잭션(명시적) 사용 예시

Commit

테이블의 값을 입력하고 커밋 전 후의 값 확인

-- transaction commit
BEGIN TRAN
	insert into MyPet (name, age) values ('hello', 999)
	select * from MyPet where name = 'hello';
COMMIT TRAN

select * from MyPet where name = 'hello';

쿼리 실행 결과
- 첫번째 결과 : 트랜잭션 내에서 해당 컬럼 조회
- 두번째 결과 : 트랜잭션 커밋 후 해당 컬럼 조회

Rollback

테이블의 값을 입력하고 롤백 전 후의 값 확인

-- transaction rollback
BEGIN TRAN
	insert into MyPet (name, age) values ('hello', 999)
	select * from MyPet where name = 'hello';
ROLLBACK TRAN

select * from MyPet where name = 'hello';

쿼리 실행 결과
- 첫번째 결과 : 트랜잭션 내에서 해당 컬럼 조회
- 두번째 결과 : 트랜잭션 롤백 후 해당 컬럼 조회
  - 트랜잭션 이전의 상태로 복구하였기 때문에 값이 존재하지 않음

Rollback Error Message 알고가기

트리거를 사용하여 롤백 할 경우 트랜잭션 종료 에러 메시지가 뜨지만

그냥 트랜잭션에서 롤백하였을 경우 메시지가 뜨지 않는다.

물론 메시지만 뜨지 않을 뿐, 롤백은 적용이 잘 된다.

이는 DB에서 에러가 일어나서 뜨지 않는 메시지가 아니고

원래 ROLLBACK TRANSACTION은 사용자에게 메시지를 반환하지 않는다.

참고

Microsoft - SQL 문서 - 트랜잭션 잠금 및 행 버전 관리 지침

https://learn.microsoft.com/ko-kr/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16

트랜잭션 잠금 및 행 버전 관리 지침 - SQL Server

트랜잭션 잠금 및 행 버전 관리 지침

learn.microsoft.com

위키백과 - 데이터베이스 트랜잭션

https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4_%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98

데이터베이스 트랜잭션 - 위키백과, 우리 모두의 백과사전

위키백과, 우리 모두의 백과사전. 데이터베이스 트랜잭션(Database Transaction)은 데이터베이스 관리 시스템 또는 유사한 시스템에서 상호작용의 단위이다. 여기서 유사한 시스템이란 트랜잭션이 성

ko.wikipedia.org

Microsoft - SQL 문서 - ROLLBACK TRANSACTION

https://learn.microsoft.com/ko-kr/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-ver16

ROLLBACK TRANSACTION(Transact-SQL) - SQL Server

ROLLBACK TRANSACTION(Transact-SQL)