트랜잭션 기초

4. 응용 변형 예제

예제 6: H2 에서 되는 DB별 형태

소스 03_dialects.sql 은 SET MODE 로 방언을 바꿔 H2 가 받는 형태만 실행합니다. 먼저 격리 수준 조회와 변경입니다. H2 의 기본값과 문법은 실제 DB 의 근거가 아닙니다.

SELECT ISOLATION_LEVEL FROM INFORMATION_SCHEMA.SESSIONS;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

첫 조회는 READ COMMITTED, 변경 뒤 조회는 SERIALIZABLE 이 나옵니다.

MySQL 모드에서는 autocommit 변수로 자동 커밋을 끕니다. 끈 채 UPDATE 후 ROLLBACK 하면 값이 돌아옵니다.

SET MODE MySQL;
SET autocommit = 0;
UPDATE account SET balance = balance - 100 WHERE id = 1;
ROLLBACK;

ROLLBACK 뒤 잔액은 1000, 500, 300 그대로입니다.

H2 는 BEGIN 도 받습니다. 자동 커밋이 켜진 채로 BEGIN 으로 시작하면 ROLLBACK 이 그 트랜잭션을 취소합니다. 다만 Oracle 에서 BEGIN 은 PL/SQL 블록의 시작이라 트랜잭션 시작 문장이 아닙니다.

SET MODE Regular;
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
ROLLBACK;

이 ROLLBACK 도 값을 되돌려 1000 이 남습니다.

H2 는 START TRANSACTION, BEGIN TRAN, SAVE TRAN, ROLLBACK TRAN 을 MySQL·MSSQL 모드에서도 받지 않습니다. 이 문법은 예제 7 에서 문법 검토만 합니다.

예제 7: DB별 이체 트랜잭션 문법 (H2 에서 재현 안 됨, 문법 검토만)

-- Oracle · Tibero: 첫 DML 에서 트랜잭션이 시작된다
UPDATE account SET balance = balance - 200 WHERE id = 1;
SAVEPOINT sp1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- MySQL
START TRANSACTION;
UPDATE account SET balance = balance - 200 WHERE id = 1;
SAVEPOINT sp1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- MSSQL
SET XACT_ABORT ON;
BEGIN TRAN;
UPDATE account SET balance = balance - 200 WHERE id = 1;
SAVE TRAN sp1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
ROLLBACK TRAN sp1;
COMMIT TRAN;

MySQL 은 BEGIN 도 START TRANSACTION 과 같습니다. MSSQL 의 ROLLBACK TRAN 뒤에 이름이 없으면 트랜잭션 전체를 취소하고, 이름이 있으면 그 SAVE TRAN 까지만 취소합니다.

격리 수준을 바꾸는 명령도 DB마다 다릅니다.

-- Oracle · Tibero: 트랜잭션의 첫 문장으로
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- MySQL: 다음 트랜잭션에만. 세션 전체는 SET SESSION TRANSACTION ...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- MSSQL: 연결이 끝나거나 바꿀 때까지 유지
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

MSSQL 의 SNAPSHOT 은 데이터베이스에서 스냅샷 격리를 허용해 둬야 씁니다. 격리 수준을 바꾸는 일은 드물고, 기본값이 무엇인지 아는 것이 먼저입니다.

예제 8: DDL 이 중간에 끼면 (H2 는 DB별 차이를 재현하지 못한다)

Oracle 과 MySQL 의 DDL 은 암묵적 커밋입니다. CREATE TABLE 이나 TRUNCATE 를 만나면 그 앞까지의 변경이 확정되어 이후 ROLLBACK 으로 돌아가지 않습니다. MSSQL 은 DDL 도 트랜잭션 안에서 롤백할 수 있습니다.

H2 에서 확인하면 어느 모드에서든 DDL 이 앞의 UPDATE 를 커밋합니다. 자동 커밋을 끄고 UPDATE, CREATE TABLE, ROLLBACK 순서로 실행합니다.

SET AUTOCOMMIT OFF;
UPDATE account SET balance = balance - 100 WHERE id = 1;
CREATE TABLE ddl_probe (x INT);
ROLLBACK;
SELECT
       id
     , balance
  FROM account
 ORDER BY id;

조회하면 1번 잔액은 900, 2·3번은 그대로입니다. ROLLBACK 을 했는데 900 이 남았습니다. Oracle 과 MySQL 은 같은 결과입니다. SET MODE MSSQLServer 로 바꿔 DROP TABLE 을 끼워도 소스 5구간은 마찬가지로 커밋되어 800 이 남습니다. 실제 MSSQL 은 롤백되어 원래 값으로 돌아가므로 H2 에서는 재현 안 됨, 문법 검토만입니다.

팁

정리 스크립트에서 UPDATE 와 DDL 을 섞지 않습니다. Oracle 과 MySQL 에서 DDL 이 앞의 DML 을 확정해 버리고, MSSQL 만 되돌릴 수 있어서 DB를 옮기면 동작이 달라집니다.

예제 9: 두 세션의 시간 순서 (도식, 설명용)

두 세션이 동시에 움직이는 경우는 H2 스크립트 하나로 재현할 수 없습니다. 아래는 예제 데이터로 그린 시간 순서 도식이고 실행 결과가 아닙니다. 세션 A 는 자동 커밋을 끈 트랜잭션 안에 있습니다.

시간 순서 도식(설명용). READ COMMITTED 와 REPEATABLE READ 의 차이입니다.

4번의 결과는 격리 수준에 따라 다릅니다.

A 가 트랜잭션 없이 자동 커밋이었다면 1번과 4번은 각각 다른 트랜잭션이라 격리 수준과 상관없이 900 이 나옵니다. 같은 값을 두 번 읽고 비교하는 코드는 하나의 트랜잭션 안에 있어야 의미가 있습니다.

시간 순서 도식(설명용). 커밋하지 않은 쓰기가 다른 세션에 어떻게 보이는지입니다.

2번은 대부분 A 의 커밋 전 값 1000 을 그냥 읽습니다. Oracle 은 언두로 이전 값을 재구성하고 MSSQL 은 READ_COMMITTED_SNAPSHOT 을 켠 경우가 같습니다. MSSQL 기본(잠금 기반)에서는 A 가 커밋할 때까지 B 의 조회가 기다립니다. 3번은 A 가 같은 행을 잡고 있어서 어느 DB 에서든 A 의 COMMIT 이나 ROLLBACK 까지 기다립니다. 이 대기와 데드락은 대용량·배치 카테고리에서 다룹니다.