[SQL] 트랜잭션

 

개념

 

개념

트랜잭션은 여러 SQL 작업을 하나의 작업 단위로 묶어서 처리하는 기능 

(일부만 반영되는 상황을 막는다) 

 

EX] 사용 예시

• 계좌이체 - 출금과 입금
• 주문 처리 - 주문 생성, 재고 감소, 결제 기록
• 회원 탈퇴 - 회원 상태 변경, 관련 데이터 처리
• 예약 처리 - 예약 생성, 좌석 수 감소
• 포인트 사용 - 포인트 차감, 사용 기록 생성

 

문법

START TRANSACTION; -- 트랜잭션 시작

SQL문1;
SQL문2;
SQL문3;

COMMIT | ROLLBACK ; -- 작업 내용 최종 반영 | 작업 내용 취소하고 이전 상태로 복구

명령어	역할
COMMIT	트랜잭션에서 실행한 변경 내용을 최종 반영
ROLLBACK	트랜잭션에서 실행한 변경 내용을 모두 취소

 

---

 

트랜잭션의 특성 : ACID

 

구분	영문	의미
원자성	Atomicity	작업을 전부 처리하거나 전부 취소
일관성	Consistency	처리 전후에도 데이터 규칙 유지 ex) 계좌잔액은 음수가 될 수 없음 => 트랜잭션 완료 후에도 이 규칙을 지키는지 검사해야 함
격리성	Isolation	동시에 실행되는 트랜잭션끼리 영향을 최소화
지속성	Durability	COMMIT된 결과를 계속 보존

 

EX]

주문처리에 다음 작업이 포함됨

주문생성, 재고 감소, 결제 내역 저장, 포인트 차감

원자성	모든 작업 성공 → 전체 반영 결제 내역 저장 실패 → 주문 생성과 재고 감소도 모두 취소
일관성	재고는 0보다 작을 수 없음 포인트는 보유 포인트보다 많이 사용할 수 없음 주문은 존재하는 회원과 상품을 참조해야 함
격리성	여러 사용자가 같은 재고를 동시에 주문해도 재고보다 많은 주문이 처리되지 않도록 제어
지속성	주문이 COMMIT된 뒤에는 서버가 재시작되어도 주문 결과가 유지

 

 

---

 

SAVEPOINT

 

개념

트랜잭션을 진행하는 중간에 되돌아갈 수 있는 지점을 지정하는 기능 

 

문법

START TRANSACTION;

SQL문1;

SAVEPOINT 저장지점명;

SQL문2;
SQL문3;

ROLLBACK TO SAVEPOINT 저장지점명;

COMMIT; -- SAVEPOINT로 되돌렸다고 트랜잭션이 완료된 것은 아니고, 변경 사항 반영하려면 COMMIT 해야 함

 

 

EX] 주문 처리 중 선택 작업만 취소

주문 처리 과정)

주문 생성 → 재고 감소 → 쿠폰 사용 → 포인트 사용

 

쿠폰 사용은 성공했지만 

-- EX] 주문 처리 중 선택 작업만 취소
-- 주문 처리 과정 : 주문 생성 → 재고 감소 → 쿠폰 사용 → 포인트 사용

-- 쿠폰 사용은 성공했지만 포인트 사용 과정에서 문제가 발생함. 주문과 재고 감소는 유지하고, 쿠폰과 포인트 작업만 취소
START TRANSACTION;

INSERT INTO orders (
    user_id,
    product_id,
    quantity
)
VALUES (
    1,
    10,
    2
);

UPDATE products
SET stock = stock - 2
WHERE id = 10;

SAVEPOINT before_discount;

UPDATE coupons
SET used_at = CURRENT_TIMESTAMP
WHERE id = 5;

UPDATE users
SET point = point - 3000
WHERE id = 1;

ROLLBACK TO SAVEPOINT before_discount;

COMMIT;

/** 처리 결과
| 작업     | 결과 |
| ------ | -- |
| 주문 생성  | 유지 |
| 재고 감소  | 유지 |
| 쿠폰 사용  | 취소 |
| 포인트 차감 | 취소 |
*/

 

 

자동 커밋

개념

각 SQL문이 정상적으로 실행될 때마다 데이터베이스가 자동으로 COMMIT하는 설정

자동 커밋이 활성화되어 있으면 각각의 SQL문이 별도의 트랜잭션처럼 처리 됨.

 

확인과 설정

-- 현재 설정 확인
SELECT @@autocommit;
-- 1 -> 자동 커밋 활성화
-- 0 -> 자동 커밋 비활성화

-- 자동 커밋 셋팅
SET autocommit = 1|0;

 

 

---

 

트랜잭션 격리 수준

 

개념

여러 트랜잭션이 동시에 실행될 때, 다른 트랜잭션의 변경 내용을 어느 정도까지 볼 수 있게 할지 정하는 기준

 

격리 수준이 높을수록 다른 트랜잭션의 영향을 적게 받아 데이터의 일관성이 높아짐. 

하지만 동시에 처리할 수 있는 작업이 줄거나 대기 시간이 늘어날 수 있음.

 

격리 수준에서 발생할 수 있는 문제 

Dirty Read

다른 트랜잭션에서 아직 COMMIT하지 않은 변경 값을 읽는 문제 

 

EX]

-- 회원 잔액 = 50000

-- 트랜잭션 A가 잔액을 변경했지만 아직 확정하지 않았다. 
START TRANSACTION;

UPDATE accounts
SET balance = 1000
WHERE user_id = 1;

-- 이때 트랜잭션 B가 변경된 1000을 조회  : balance = 10000 조회 

-- 그런데 여기서 트랜잭션 A가 작업을 취소
ROLLBACK;
-- 실제 잔액은 다시 50000가 됨 VS 트랜잭션은 10000을 읽음

 

Non-Repeatable Read

하나의 트랜잭션 안에서 같은 행을 두 번 조회했는데 값이 달라지는 문제 

 

EX]

-- 트랜잭션 A가 회원 잔액을 조회
START TRANSACTION;

SELECT balance
FROM accounts
WHERE user_id = 1;

-- 첫 번째 결과 : balance = 50000

-- 그 사이 트랜잭션 B가 잔액을 변경하고 COMMIT한다
UPDATE accounts
SET balance = 3000
WHERE user_id = 1;

COMMIT;

-- 트랜잭션 A가 같은 행을 다시 조회한다
SELECT balance
FROM accounts
WHERE user_id = 1;

-- 두 번째 결과 : balance = 30000

 

Phantom Read

하나의 트랜잭션 안에서 같은 조건으로 두 번 조회했는데, 결과 행의 개수가 달라지는 문제 

 

EX]

-- 트랜잭션 A가 10000원 이상인 주문을 조회한다
START TRANSACTION;

SELECT *
FROM orders 
WHERE total_price >= 10000;
-- 이 결과를 두 행으로 가정

-- 그 사이 트랜잭션 B가 조건을 만족하는 주문을 추가하고 COMMIT
INSERT INTO orders (
    user_id,
    total_price
)
VALUES (
    3,
    150000
);

COMMIT;

-- 트랜잭션 A가 같은 조건으로 다시 조회 
SELECT *
FROM orders
WHERE total_price >= 100000;
-- 결과가 3행

 

 

격리 수준의 종류

격리 수준	정도	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	낮음 높음	발생 가능	발생 가능	발생 가능
READ COMMITTED		방지	발생 가능	발생 가능
REPEATABLE READ		방지	방지	표준상 발생 가능
SERIALIZABLE		방지	방지	방지

 

구분	낮은 격리 수준	높은 격리 수준
동시 처리	많은 작업을 동시에 처리하기 유리	다른 트랜잭션을 기다리는 상황이 늘어날 수 있음
데이터 일관성	조회 결과가 달라질 가능성이 큼	일관된 데이터 보장이 강화됨
대기·충돌	비교적 적음	증가할 수 있음
선택 기준	약간의 시점 차이가 허용되는 조회	정확성과 충돌 방지가 중요한 작업

                                              

READ UNCOMMITTED

다른 트랜잭션이 아직 COMMIT하지 않은 변경 내용도 읽을 수 있는 가장 낮은 격리 수준

 

READ COMMITTED

다른 트랜잭션에서 COMMIT이 완료된 데이터만 읽을 수 있는 격리 수준 

 

REPEATABLE READ

하나의 트랜잭션 안에서 같은 행을 여러 번 조회해도 동일한 값을 읽을 수 있도록 하는 격리 수준

 

SERIALIZABLE

동시에 실행되는 트랜잭션을 순서대로 하나씩 실행한 것처럼 처리 

 

장점

• 동시성 문제를 가장 강하게 방지

단점

• 대기 시간 증가
• 처리량 감소 가능
• 데드락 가능성 증가

 

문법

-- 현재 트랜잭션의 격리 수준 설정
SET TRANSACTION ISOLATION LEVEL 격리_수준;

-- 사용 예시
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SQL문;

COMMIT;

 

 

---

 

동시성 문제와 데드락

 

동시성 문제

여러 트랜잭션이 같은 데이터를 동시에 조회하거나 변경하면서 예상하지 못한 결과가 발생하는 문제 

트랜잭션 A ─┐
            ├→ 같은 데이터에 동시 접근
트랜잭션 B ─┘
            ↓
조회 결과 불일치 또는 변경 충돌

 

종류

문제	의미
Dirty Read	다른 트랜잭션의 미확정 데이터 조회
Non-Repeatable Read	같은 행을 다시 조회했을 때 값이 달라짐
Phantom Read	같은 조건을 다시 조회했을 때 행이 추가되거나 사라짐
Lost Update	두 트랜잭션이 같은 값을 조회한 뒤 각각 수정하면서 먼저 반영된 변경 내용이 사라지는 문제

 

Lost Update

EX] 

현재 재고가 10개

stock = 10

 

트랜잭션 A와 B가 동시에 재고를 조회 

    트랜잭션 A → stock 10 조회

    트랜잭션 B → stock 10 조회

 

각각 재고를 한 개 줄여 9를 저장

    트랜잭션 A → stock 10 조회

    트랜잭션 B → stock 10 조회

 

실제로는 두 개가 판매되었으므로 재고가 8이어야 하지만 최종 결과는 9가 됨.

 

잠금을 이용한 처리

조회한 값을 기준으로 여러 작업을 이어서 해야 한다면 해당 행을 잠글 수 있다. 

 

FOR UPDATE => 조회한 행을 다른 트랜잭션이 동시에 수정하지 못하도록 잠그는 데 사용 

START TRANSACTION;

SELECT
    컬럼1,
    컬럼2
FROM 테이블명
WHERE 조건
FOR UPDATE;

UPDATE 테이블명
SET 컬럼명 = 값
WHERE 조건;

COMMIT;

 

EX] 재고 확인 후 감소

START TRANSACTION;

SELECT
    stock
FROM products
WHERE id = 10
FOR UPDATE;

UPDATE products
SET stock = stock - 2
WHERE id = 10
  AND stock >= 2;

COMMIT;

 

데드락

개념

두 개 이상의 트랜잭션이 서로 상대방이 가진 잠금이 풀리기를 기다리면서 아무 작업도 진행하지 못하는 상태 

트랜잭션 A

→ 데이터 1 잠금

→ 데이터 2 잠금 대기

 

트랜잭션 B

→ 데이터 2 잠금

→ 데이터 1 잠금 대기

 

A는 B를 기다림

B는 A를 기다림

→ 서로 무한히 대기 

 

EX] 

-- EX] 서로 반대 순서로 계좌를 수정하는 경우
/*
accounts 테이블에 다음 두 계좌가 있음

| id | balance |
| -: | ------: |
|  1 |   50000 |
|  2 |   30000 |
*/

/*
트랜잭션 A : 계좌1 -> 계좌2로 10000원 이체
*/
START TRANSACTION;

UPDATE accounts
SET balance = balance - 10000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 10000
WHERE id = 2;

COMMIT;


/*
트랜잭션 B :계좌2 -> 계좌1로 5000원 이체
*/
START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE id = 2;

UPDATE accounts
SET balance = balance + 5000
WHERE id = 1;

COMMIT;

/*
실행과정

| 순서 | 트랜잭션 A           | 트랜잭션 B           | 결과               |
| -: | ---------------- | ---------------- | ---------------- |
|  1 | 계좌 1 `UPDATE`    |                  | 계좌 1 자동 잠금       |
|  2 |                  | 계좌 2 `UPDATE`    | 계좌 2 자동 잠금       |
|  3 | 계좌 2 `UPDATE` 시도 |                  | B가 잠그고 있어 A 대기   |
|  4 |                  | 계좌 1 `UPDATE` 시도 | A가 잠그고 있어 B 대기   |
|  5 | 계좌 2 잠금 대기       | 계좌 1 잠금 대기       | 서로 기다리는 순환 구조 발생 |

*/

 

데이터베이스의 처리

대부분의 DBMS는 데드락을 감지하면 트랜잭션 중 하나를 선택해 강제로 ROLLBACK함. 

 

데드락을 줄이는 방법

잠금 획득 순서를 통일

여러 행이나 테이블을 수정해야 한다면 모든 트랜잭션이 항상 같은 순서로 잠금을 획득하도록 한다. 

모든 트랜잭션

→ 번호가 작은 데이터

→ 번호가 큰 데이터

ORDER BY로 잠금 순서를 통일하면 한 트랜잭션이 먼저 잠금을 얻고, 다른 트랜잭션은 첫 번째 잠금에서 기다리게 된다

: 대기는 발생할 수 있음 → 순환 대기는 줄어듦

 

트랜잭션을 짧게 유지

트랜잭션 시작
→ 필요한 데이터 변경
→ 즉시 COMMIT 또는 ROLLBACK

 

다음 작업을 가급적 피한다

• 사용자 입력 대기
• 외부 API 호출
• 파일 처리
• 오래 걸리는 계산
• 불필요한 반복 조회

 

잠금 범위를 최소화

조건을 넓게 작성하면 필요 이상의 행이 잠길 수 있음.

넓은 조건
→ 많은 행에 접근
→ 잠금 충돌 가능성 증가

정확한 조건
→ 필요한 행만 접근
→ 잠금 충돌 가능성 감소

 

 

조건에 맞는 인덱스를 사용

WHERE조건에 적절한 인덱스가 없으면 DBMS가 많은 데이터를 탐색할 수 있음.

검색컬럼에 적절한 인덱스가 있으면 변경 대상을 더 빠르게 찾을 수 있다. 

 

한 번에 너무 많은 데이터를 변경하지 않기

가능한 경우 작업을 일정한 크기로 나누어 처리하기 

(다만, 반드시 전체 작업이 함께 성공하거나 실패해야 한다면 임의로 트랜잭션을 나누면 안 된다)

 

데드락 발생 시 재시도 

재시도 할 때는 다음을 고려

• 재시도 횟수 - 무한 반복하지 않고 제한
• 대기 시간 - 즉시 반복하지 않고 짧게 대기
• 중복 처리 방지 - 같은 주문/결제 등이 두 번 처리되지 않도록 설계
• 오류 기록 - 반복되는 데드락의 원인 확인 

 

---

 

트랜잭션 사용 시 주의사항

 

트랜잭션은 데이터의 일관성을 지켜주지만, 범위를 너무 넓게 잡거나 종료 처리를 빠뜨리면 잠금 대기, 데드락, 성능저하가 발생할 수 있음

 

트랜잭션을 짧게 유지

트랜잭션 안에서 데이터를 수정하면 관련 잠금이 일반적으로 COMMIT 또는 ROLLBACK까지 유지된다. 

트랜잭션 안에는 데이터베이스 변경에 필요한 작업만 포함한다. 

 

다음처럼 시간이 오래 걸리거나 결과를 기다려야 하는 작업은 트랜잭션 밖에서 처리하는 것이 좋음. 

ex) 사용자 입력 대기, 외부 API 호출, 파일 업로드/변환, 이메일/문자 발송, 오래 걸리를 계산

 

성공과 실패를 명확히 처리

트랜잭션을 시작했다면 반드시 COMMIT 또는 ROLLBACK으로 끝내야 함. 

트랜잭션을 종료하지 않고 남겨 두면 잠금이 계속 유지되어 다른 작업을 막을 수 있음. 

 

함께 성공해야 하는 작업만 묶기

트랜잭션 범위는 하나의 논리적인 작업 단위로 정한다. 

반대로 서로 직접적인 관련이 없는 작업까지 한 트랜잭션에 넣으면 범위만 불필요하게 커진다. 

 

변경 조건을 정확하게 작성

WHERE 조건을 사용해서 필요한 데이터만 변경하도록 정확한 조건을 작성

 

동시 변경을 고려

여러 트랜잭션이 같은 데이터를 동시에 조회하고 수정할 수 있다는 점을 고려하여 변경 방식을 정해야 함. 

다음처럼 현재 값을 조회한 뒤 계산 결과를 다시 저장하는 방식은 주의 

 

두 트랜잭션이 변경 전의 값을 동시에 조회하면, 나중에 실행된 UPDATE가 앞선 변경 결과를 덮어쓸 수 있음.

(Loat Update)

 

하나의 UPDATE에서 직접 변경 

단순한 증감은 값을 먼저 조회하지 않고, 현재 저장된 값을 기준으로 바로 변경한다. 

UPDATE 테이블명
SET 변경할_컬럼 = 변경할_컬럼 + 변화량
WHERE 조건;

-- 감소할 때는 필요한 조건도 함께 작성할 수 있음
UPDATE 테이블명
SET 변경할_컬럼 = 변경할_컬럼 - 감소량
WHERE 조건 AND 변경할 컬럼 >= 감소량;

 

적용 예시]

• 재고 증가/감소
• 잔액 증감
• 포인트 적립/차감
• 조회수 증가
• 사용 횟수 증가

조회 후 여러 작업이 이어지면 잠금 사용

조회한 값을 기준으로 여러 판단이나 변경을 해야 한다면, 조회하는 시점부터 데이터를 잠글 수 있음. 

START TRANSACTION;

SELECT 변경할_컬럼
FROM 테이블명
WHERE 조건
FOR UPDATE;

-- 조회 결과를 이용한 판단
-- 여러 데이터 변경

COMMIT;

 

적용 예시]

• 값을 확인한 뒤 여러 테이블을 함께 변경
• 조회 결과에 따라 서로 다른 처리를 실행
• 상태를 확인한 뒤 다음 단계로 변경
• 여러 조건을 검사한 뒤 작업을 확정 

 

여러 데이터를 수정할 때 순서 통일

하나의 트랜잭션에서 여러 행이나 테이블을 수정한다면, 모든 트랜잭션이 같은 기준과 순서로 데이터에 접근하도록 설계 

여러 데이터를 처리하기 전에 식별값을 정렬하고, 항상 정해진 순서로 접근할 수 있음

작은 ID → 큰 ID
오래된 날짜 → 최근 날짜
상위 자원 → 하위 자원
기준 테이블 → 관련 테이블

 

UPDATE를 실행하면 변경 대상에 잠금이 자동으로 설정됨. 따라서 여러 트랜잭션이 같은 데이터를 서로 다른 순서로 수정하면 데드락이 발생할 수 있다. 

 

적절한 격리 수준 사용

격리 수준이 낮음
→ 동시 처리에 유리
→ 조회 결과가 달라질 가능성 증가

격리 수준이 높음
→ 데이터 일관성 강화
→ 대기·충돌 가능성 증가

 

작업 특성	고려할 방향
최신 커밋 데이터 조회가 중요함	READ COMMITTED 검토
한 트랜잭션 안에서 같은 값을 유지해야 함	REPEATABLE READ 검토
다른 트랜잭션의 삽입·변경까지 강하게 제한해야 함	SERIALIZABLE 검토
일부 시점 차이가 허용되는 단순 조회	비교적 낮은 격리 수준 검토