Transaction(트랜잭션)과 TCL(Transaction Control Language)

1. Transaction(트랜잭션)의 정의

• 트랜잭션은 데이터베이스에서 실행되는 작업의 논리적인 단위입니다.
• 트랜잭션은 여러 작업(쿼리)을 묶어 하나의 작업처럼 실행하며, 모두 성공하거나 모두 실패하도록 보장합니다.
• ACID 특성을 기반으로 동작합니다:
  1. Atomicity (원자성): 트랜잭션의 모든 작업은 성공하거나 실패하며, 일부만 실행되는 경우는 없음.
  2. Consistency (일관성): 트랜잭션 완료 후 데이터베이스는 항상 일관된 상태를 유지함.
  3. Isolation (격리성): 트랜잭션은 독립적으로 실행되어 다른 트랜잭션의 영향을 받지 않음.
  4. Durability (지속성): 트랜잭션 완료 후 변경된 데이터는 영구적으로 저장됨.

---

2. TCL(Transaction Control Language)의 정의

• TCL은 트랜잭션을 관리하기 위한 SQL 명령어 집합입니다.
• 트랜잭션의 완료 또는 취소를 결정하고, 데이터베이스의 상태를 제어합니다.

---

3. TCL 명령어

(1) COMMIT

• 트랜잭션의 작업을 데이터베이스에 영구적으로 저장.
• 트랜잭션 성공 시 호출.
• 명령어 예시:

  START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  

(2) ROLLBACK

• 트랜잭션 내의 모든 작업을 취소하고, 데이터베이스를 이전 상태로 되돌림.
• 트랜잭션 실패 시 호출.
• 명령어 예시:

  START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- 실수로 에러가 발생한 경우
  ROLLBACK;
  

(3) SAVEPOINT

• 트랜잭션 내에서 중간 저장 지점을 생성.
• 필요 시 저장 지점으로 되돌릴 수 있음.
• 명령어 예시:

  START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT sp1;
  UPDATE accounts SET balance = balance - 50 WHERE id = 2;
  ROLLBACK TO sp1; -- sp1으로 롤백
  

(4) SET TRANSACTION

• 트랜잭션의 격리 수준을 설정.
• 트랜잭션 실행 중 다른 트랜잭션과의 격리 정도를 제어.
• 명령어 예시:

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

---

4. Transaction의 실행 흐름

(1) 기본 흐름

1. START TRANSACTION: 트랜잭션 시작.
2. 쿼리 실행: 여러 데이터 변경 작업 수행.
3. COMMIT 또는 ROLLBACK: 성공 시 COMMIT, 실패 시 ROLLBACK.

(2) SAVEPOINT 활용 흐름

1. START TRANSACTION
2. 중간 저장 지점 생성: SAVEPOINT
3. 필요 시 저장 지점으로 되돌아가기: ROLLBACK TO SAVEPOINT
4. 최종적으로 COMMIT 또는 ROLLBACK

---

5. 트랜잭션 격리 수준 (Isolation Levels)

트랜잭션 간 간섭을 방지하기 위한 격리 수준을 설정합니다.

격리 수준	설명	읽기 현상
READ UNCOMMITTED	커밋되지 않은 데이터를 읽을 수 있음. (Dirty Read 가능)	Dirty Read, Non-repeatable Read, Phantom Read
READ COMMITTED	커밋된 데이터만 읽을 수 있음. (Non-repeatable Read 가능)	Non-repeatable Read, Phantom Read
REPEATABLE READ	동일 트랜잭션 내에서는 동일 데이터만 읽음. (Phantom Read 가능)	Phantom Read
SERIALIZABLE	트랜잭션을 순차적으로 실행하여 완벽한 격리 보장. (가장 느리지만 완벽한 일관성 제공)	없음

---

6. 실습 예제

(1) 기본 트랜잭션 작업

START TRANSACTION;

-- 데이터 변경
UPDATE products SET stock = stock - 1 WHERE product_id = 101;
INSERT INTO orders (order_id, product_id, quantity) VALUES (1, 101, 1);

-- 트랜잭션 저장
COMMIT;

(2) SAVEPOINT 활용

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp1;

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

-- sp1으로 롤백
ROLLBACK TO sp1;

COMMIT;

---

주의사항

1. 서로 다른 세션에서의 데이터 접근

(1) 트랜잭션의 격리 수준 문제

• 서로 다른 세션이 동시에 데이터에 접근할 경우, 격리 수준 (Isolation Level) 에 따라 트랜잭션 간 데이터 충돌이 발생하거나 읽기 오류가 나타날 수 있습니다.
• 격리 수준에 따라 나타날 수 있는 문제:
  • Dirty Read: 커밋되지 않은 데이터를 다른 세션에서 읽음.
  • Non-repeatable Read: 동일 트랜잭션 내에서 같은 데이터를 읽었을 때 값이 달라짐.
  • Phantom Read: 다른 세션에서 삽입된 데이터가 갑자기 조회 결과에 나타남.

(2) 해결 방법

1. 적절한 격리 수준 설정:
   • 예: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2. 트랜잭션 충돌을 방지하기 위해 락(Lock) 사용:
   • 공유 락: 다른 세션에서 읽기는 가능하지만 쓰기는 제한.
   • 배타 락: 다른 세션에서 읽기 및 쓰기를 모두 제한.
   • 예시:

     SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 배타 락
     

---

2. 트랜잭션 도중에 DDL 실행

(1) DDL 명령어의 특징

• DDL 명령어 (CREATE, DROP, ALTER 등)는 자동으로 COMMIT을 발생시킵니다.
• 트랜잭션 중에 DDL 명령어를 실행하면 트랜잭션이 강제로 종료됩니다.

(2) 예시

START TRANSACTION;

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

-- DDL 실행 (자동 COMMIT 발생)
CREATE TABLE new_table (id INT);

-- 이후 트랜잭션은 유효하지 않음
ROLLBACK; -- 아무 효과 없음

(3) 해결 방법

1. 트랜잭션 내에서는 DDL 명령어를 사용하지 않도록 설계.
2. DDL이 필요한 경우, 트랜잭션 외부에서 실행:
   • 트랜잭션을 COMMIT하거나 ROLLBACK한 후에 DDL 실행.
3. DDL 작업을 분리하여 실행하고 필요한 경우 애플리케이션에서 트랜잭션을 재구성.

---

3. 자동 커밋 (Autocommit) 주의

• 데이터베이스의 autocommit 설정에 따라 트랜잭션 동작이 달라질 수 있습니다.
• 자동 커밋 활성화 상태에서 트랜잭션 사용 시 문제:
  • START TRANSACTION을 명시적으로 호출하지 않으면 각 쿼리가 자동으로 커밋됩니다.

(1) 확인 및 설정

-- 자동 커밋 상태 확인
SELECT @@autocommit;

-- 자동 커밋 비활성화
SET autocommit = 0;

---

4. 트랜잭션 내에서 잠금 경합 (Lock Contention)

(1) 문제 상황

• 여러 트랜잭션이 동일한 데이터에 접근하려고 하면 잠금 경합이 발생.
• 경합이 심할 경우 Deadlock(교착 상태) 이 발생할 수 있음.

(2) 해결 방법

1. 트랜잭션이 짧고 간결하게 유지되도록 설계.

2. 잠금 대상을 최소화:

   • 예: 특정 행만 잠그도록 쿼리를 설계.

   SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 특정 행 잠금
   

3. Deadlock 감지 및 회피:

   • 데이터베이스는 Deadlock을 감지하고 한쪽 트랜잭션을 강제로 중단시킴.
   • 애플리케이션에서 이를 감지하고 적절히 재시도하도록 구현.

---

5. 트랜잭션과 연결 해제 문제

(1) 문제 상황

• 트랜잭션 중 클라이언트가 데이터베이스 연결을 끊으면, 데이터베이스는 자동으로 해당 트랜잭션을 ROLLBACK 처리.
• 장시간 트랜잭션은 연결 끊김 가능성을 높임.

(2) 해결 방법

1. 트랜잭션 실행 시간을 최소화.
2. 장시간 트랜잭션이 필요한 경우 Connection Pooling을 사용하여 안정성을 확보.

---

6. 트랜잭션과 로그 관리

• 로그 파일 크기와 트랜잭션 처리량이 관련.
• 트랜잭션 로그 (redo log)가 가득 차면 성능 저하 발생.

(1) 로그 설정 확인

SHOW VARIABLES LIKE 'innodb_log_file_size';

(2) 로그 크기 조정

SET GLOBAL innodb_log_file_size = 256M;