SQL의 트랜잭션과 데드락

김규원·2025년 12월 9일

sql

DB

목록 보기

6/22

트랜잭션(Transaction)

데이터베이스에서 수행되는 하나의 논리적인 작업 단위

ACID 원칙

Atomicity(원자성)

트랜잭션은 모두 성공하거나 모두 실패해야 한다

Consistency(일관성)

트랜잭션이 실행되면 DB는 항상 정합성을 유지해야한다(즉, A+3 을 했을 때 후의 값은 A+3이 되어야 하고, 실패한다면 A 상태 그대로 남아있어야 한다는 의미)

Isolation(고립성)

동시에 실행되는 여러 트랜잭션이 서로 간섭하지 않고 독립적으로 처리되어야 함(즉, A+3과 A-3이 처리된다면 이 값은 순서대로 A+3-3 => A 가 나와야 함)

Durability(지속성)

트랜잭션이 완료되면 그 결과는 영구적으로 저장되어야 함

트랜잭션의 종류

Autocommit(자동 커밋)

SQL문이 실행될 때마다 DB가 자동으로 트랜잭션을 시작하고 종료(Commit)하는 방식
즉 사용자가 commit이나 rollback같은 명령어를 명시적으로 사용할 필요가 없음
기본설정

INSERT INTO users (id, name) VALUES (1, 'Alice'); -- 이 문장 실행 후 즉시 커밋됨
UPDATE products SET price = 100 WHERE id = 5;      -- 이 문장 실행 후 즉시 커밋됨

Explicit(명시적 트랜잭션)

사용자가 직접 트랜잭션의 시작-끝을 지정하는 방식
이를 통해 여러 SQL문을 하나의 원자(ATOMIC)작업 단위로 묶을 수 있음

START TRANSACTION;  -- 트랜잭션 시작

UPDATE accounts SET balance = balance - 1000 WHERE user_id = 'A'; -- 1. A 계좌에서 출금
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 'B'; -- 2. B 계좌로 입금

COMMIT;             -- 두 작업 모두 성공했으므로 영구 반영

-- 만약 2번 문에서 오류가 났다면, ROLLBACK; 명령어로 1번 작업까지 모두 취소됨

SavePoint

일반적인 트랜잭션 관리에서는 ROLLBACK 명령어를 사용하면 트랜잭션이 시작된 지점 전체로 모든 변경사항이 취소, 하지만 SAVEPOINT를 사용하면 중간에 오류가 발생했을 경우 오류가 발생한 단계 이전까지만 작업을 되돌릴 수 있음

-- 트랜잭션 시작
START TRANSACTION;
-- 1단계 작업 실행
INSERT INTO orders (id, item) VALUES (101, 'Laptop');
-- 1단계 작업 후 저장점 설정 (성공 가정)
SAVEPOINT step1_complete;
-- 2단계 작업 실행
UPDATE inventory SET stock = stock - 1 WHERE item = 'Laptop';
-- 2단계 작업 후 저장점 설정
SAVEPOINT step2_complete;
-- 3단계 작업 실행 (여기서 오류가 발생했다고 가정)
-- 예를 들어, 고객 잔액이 부족하여 결제 처리(UPDATE)가 실패함
-- 오류 발생 시, 2단계 작업까지만 유지하고 3단계의 실패한 작업만 취소하고자 함
ROLLBACK TO step2_complete; 
-- 2단계까지의 작업은 유효하므로, 롤백된 후 다시 시도하거나 다음 단계를 진행
-- ...
-- 최종적으로 트랜잭션 커밋
COMMIT;

Implicit(암묵적 트랜잭션)

특정 SQL문이 실행되면 DB가 자동으로 트랜잭션을 시작하지만, 종료(COMMIT, ROLLBACK)은 사용자가 명시적으로 해주어야 하는 방식
이는 Autocommit과 Explicit Transaction의 중간 형태

-- 트랜잭션 시작 명령어 없이 DML 문 실행
UPDATE products SET stock = stock - 10 WHERE product_id = 5; 

-- 이 시점에서 트랜잭션이 자동으로 시작된 상태임
-- 데이터는 아직 영구 저장되지 않음

COMMIT; -- 사용자가 명시적으로 커밋해야 트랜잭션이 완료됨

트랜잭션 실습

다음을 하나의 트랜잭션으로 실행

고객 상품 구매 시, 주문테이블, 주문상세테이블에 새로운 주문정보가 입력
상품 재고 컬럼에서 주문 수량 만큼 차감
결제 테이블에 결제 정보 입력

이때 결제 테이블에 입력 이상 발생시 앞서 발생한 주문테이블, 주문상세테이블, 상품 재고 컬럼 수량 변경 등이 트랜잭션 이전으로 rollback되는 지 확인

데드락(Deadlock)

두 개 이상의 트랜잭션이 서로 상대방이 점유한 자원을 기다리면서 무한 대기하는 상태
PostgreSQL은 트랜잭션 간 순환적 잠금 대기 상태(즉, 데드락)를 감지하면, 특정 트랜잭션을 강제로 종료하여 문제를 해결함.
기본적으로 데드락 감지는 일정 주기로 수행되며, 감지되면 즉시 해당 트랜잭션을 롤백함.
다만, MSSQL처럼 특정 시간(예: 30초) 후 자동 해제되는 것이 아니라, 데드락이 발생하는 순간 시스템이 감지하고 바로 처리하는 방식
PostgreSQL에서 데드락 해결 방식:
- 데드락 감지 시 자동으로 롤백됨 (ERROR: deadlock detected 오류 발생)
- pg_stat_activity, pg_locks 등을 사용해 잠금 상태 확인 가능

SELECT * FROM pg_stat_activity ORDER BY query_start ASC; 
-- wait_event_type: lock

SELECT * FROM pg_locks

-- query tool 1 (session 1)
create table prod
(
prod_id int   not null unique
, stock int default 0
);
insert into prod values (101,10); insert into prod values (102,10);

BEGIN; 
UPDATE prod SET stock = stock - 1 WHERE prod_id = 101;

UPDATE prod SET stock = stock + 5 WHERE prod_id = 102; -- 대기

select *
from prod

-- query tool 2(session2)
SELECT * FROM pg_stat_activity ORDER BY query_start ASC;

SELECT * FROM pg_locks

-- query tool 3(session3)
BEGIN; 
UPDATE prod SET stock = stock - 1 WHERE prod_id = 102;

UPDATE prod SET stock = stock + 5 WHERE prod_id = 101; -- 대기