SQLのSAVEPOINTの動きを確認する

テスト用のデータベースの作成

CREATE DATABASE testing1;

テーブル定義

CREATE TABLE dept (
  deptno INT,
  dname VARCHAR(14),
  loc VARCHAR(13));

部署テーブル

CREATE TABLE emp (
  empno INT,
  ename VARCHAR(10),
  job VARCHAR(9),
  mgr INT,
  hiredate DATE,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2),
  deptno INT);

従業員テーブル

SAVEPOINTの動きを確認する

BEGIN;
INSERT INTO dept (deptno, dname, loc)  VALUES (1, 'one', '1');
COMMIT;

1が追加される。

BEGIN;
INSERT INTO dept (deptno, dname, loc)  VALUES (2, 'two', '2');
ROLLBACK;

2は追加されない。

BEGIN;
SAVEPOINT foo1;
INSERT INTO dept (deptno, dname, loc)  VALUES (3, 'three', '3');
RELEASE SAVEPOINT foo1;
COMMIT;

3は追加される。

BEGIN;
SAVEPOINT foo4;
INSERT INTO dept (deptno, dname, loc)  VALUES (4, 'four', '4');
RELEASE SAVEPOINT foo4;
ROLLBACK;

4は追加さない。

BEGIN;
SAVEPOINT foo5;
INSERT INTO dept (deptno, dname, loc)  VALUES (5, 'five', '5');
ROLLBACK;
RELEASE SAVEPOINT foo5;

ROLLBACK により BEGIN でのトランザクション開始までデータが巻き戻され、また SAVEPOINT は全て解消される。そのため5は追加されず、更に RELEASE SAVEPOINT でエラーする。

BEGIN;
SAVEPOINT foo6;
INSERT INTO dept (deptno, dname, loc)  VALUES (6, 'six', '6');
SELECT * FROM dept;  -- 6が追加されている

SAVEPOINT foo7;
INSERT INTO dept (deptno, dname, loc)  VALUES (7, 'seven', '7');
SELECT * FROM dept;  -- 7が追加されている

ROLLBACK TO SAVEPOINT foo7;
SELECT * FROM dept;  -- 7が破棄されている

RELEASE SAVEPOINT foo6;

COMMIT;
SELECT * FROM dept;  -- 6のみが追加されている

ROLLBACK TO SAVEPOINT foo7 によって7の追加の前までデータが巻き戻され、その後 COMMIT されるため、6のみが追加される。