소스: sql-src/work_06_transaction/01_commit_rollback.sql, 02_savepoint.sql. 계좌 표 account(id, owner, balance)는 김대표 1000, 이개발 500, 박영업 300 세 행이고, balance >= 0 CHECK 제약이 있습니다. transfer_log(id, from_id, to_id, amt)는 비어 있습니다.
H2 는 실행기(SqlRun)가 자동 커밋 상태로 연결합니다. 스크립트 안에서 SET AUTOCOMMIT OFF 로 끄고 COMMIT, ROLLBACK, SAVEPOINT 를 그대로 쓸 수 있습니다.
UPDATE account SET balance = balance - 100 WHERE id = 1;
ROLLBACK;
SELECT
id
, balance
FROM account
ORDER BY id;조회하면 1번 잔액은 900, 2·3번은 그대로입니다. ROLLBACK 을 했는데 900 이 남았습니다. UPDATE 가 끝나는 순간 이미 커밋됐기 때문입니다. 소스는 이어서 100 을 더해 1000 으로 되돌립니다.
자동 커밋을 끄고 김대표에서 이개발로 200 을 이체합니다. 출금, 입금, 로그 세 문장입니다.
SET AUTOCOMMIT OFF;
UPDATE account SET balance = balance - 200 WHERE id = 1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
INSERT INTO transfer_log VALUES (1, 1, 2, 200);
SELECT
id
, balance
FROM account
ORDER BY id;ID | BALANCE
---+--------
1 | 800
2 | 700
3 | 300
(3행)내 세션에서는 커밋 전 변경이 보입니다. 이 값은 아직 다른 세션에는 보이지 않습니다. 이제 ROLLBACK 하고 다시 조회합니다.
ROLLBACK;
SELECT
id
, balance
FROM account
ORDER BY id;ID | BALANCE
---+--------
1 | 1000
2 | 500
3 | 300
(3행)잔액이 원래대로 돌아왔고 로그 건수도 0 으로 돌아옵니다(소스에서 조회). 세 문장이 하나로 취소된 것입니다.
UPDATE account SET balance = balance - 200 WHERE id = 1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
INSERT INTO transfer_log VALUES (1, 1, 2, 200);
COMMIT;
ROLLBACK;
SELECT
id
, balance
FROM account
ORDER BY id;ID | BALANCE
---+--------
1 | 800
2 | 700
3 | 300
(3행)로그 1건도 남아 있습니다(소스에서 조회). COMMIT 뒤의 ROLLBACK 은 되돌릴 변경이 없어서 아무 일도 하지 않습니다. 확정된 변경은 ROLLBACK 으로 돌릴 수 없습니다.
이개발(잔액 700)이 박영업에게 900 을 보내는 이체입니다. 출금이 CHECK 제약 위반으로 실패합니다.
-- @error
UPDATE account SET balance = balance - 900 WHERE id = 2;예상 오류: Check constraint violation: "CK_ACCOUNT_BALANCE: "실패한 문장은 변경을 남기지 않습니다. 그런데 트랜잭션은 열려 있어서 뒤 문장이 그대로 실행됩니다. 애플리케이션이 오류를 무시하고 입금과 로그를 이어 가면 어떻게 되는지 봅니다.
UPDATE account SET balance = balance + 900 WHERE id = 3;
INSERT INTO transfer_log VALUES (2, 2, 3, 900);
SELECT
id
, balance
FROM account
ORDER BY id;ID | BALANCE
---+--------
1 | 800
2 | 700
3 | 1200
(3행)박영업만 900 이 늘어났습니다. 출금 없는 입금이라 돈이 생겼습니다. 오류를 받은 쪽에서 ROLLBACK 해야 이 상태가 사라집니다.
ROLLBACK;
SELECT
id
, balance
FROM account
ORDER BY id;ID | BALANCE
---+--------
1 | 800
2 | 700
3 | 300
(3행)ROLLBACK 은 예제 3 에서 커밋한 뒤부터의 변경만 취소했습니다. 로그는 예제 3 이체의 1건만 남습니다.
주의문장이 실패해도 트랜잭션은 자동으로 닫히지 않습니다. 오류를 잡아 ROLLBACK 하는 코드가 없으면 앞 문장의 변경이 남습니다. MSSQL 의 XACT_ABORT ON 은 대부분의 오류에서 전체를 롤백합니다.
소스 02_savepoint.sql 은 이체 세 건을 한 트랜잭션에서 처리합니다. 이체 A(1번에서 2번으로 100) 뒤에 sp1, 이체 B(2번에서 3번으로 400) 뒤에 sp2, 이체 C(3번에서 1번으로 50)를 실행합니다.
SAVEPOINT sp1;
-- 이체 B: 출금, 입금, 로그 2번
SAVEPOINT sp2;
-- 이체 C: 출금, 입금, 로그 3번
SELECT * FROM transfer_log ORDER BY id;ID | FROM_ID | TO_ID | AMT
---+---------+-------+----
1 | 1 | 2 | 100
2 | 2 | 3 | 400
3 | 3 | 1 | 50
(3행)sp2 로 되돌리면 표식 뒤의 이체 C 만 취소되고 로그는 1, 2번이 남습니다. 이어서 sp1 로 되돌리면 이체 B 도 취소되어 이체 A 만 남습니다.
ROLLBACK TO SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;
SELECT
id
, balance
FROM account
ORDER BY id;ID | BALANCE
---+--------
1 | 900
2 | 600
3 | 300
(3행)이체 A 의 100 만 반영되어 김대표 900, 이개발 600 입니다. 소스는 표식마다 조회해 단계별 결과를 보입니다. 표식으로 되돌려도 트랜잭션은 열려 있으므로 이체 D(2번에서 3번으로 30)를 하고 COMMIT 합니다.
COMMIT;
ROLLBACK;
SELECT * FROM transfer_log ORDER BY id;ID | FROM_ID | TO_ID | AMT
---+---------+-------+----
1 | 1 | 2 | 100
4 | 2 | 3 | 30
(2행)로그는 A 와 D 만 남고 B, C 는 없습니다. 잔액은 900, 570, 330 입니다.