実行できるSQLiteドキュメント: トランザクション

Q: SQLiteでトランザクションを始めるにはどうすればいいですか？

BEGIN;（または BEGIN TRANSACTION;）を実行して処理を行い、確定するなら COMMIT;、捨てるなら ROLLBACK; です。BEGIN を明示しない場合は、各ステートメントが自動的に独立したトランザクションとしてコミットされます（autocommitモード）。

Q: BEGIN・BEGIN IMMEDIATE・BEGIN EXCLUSIVEの違いは？

BEGIN（BEGIN DEFERRED と同じ）は、実際に書き込みが発生するまで書き込みロックを取りません。そのため、後から他のプロセスに先を越されると SQLITE_BUSY で失敗することがあります。BEGIN IMMEDIATE は最初から書き込みロックを確保。BEGIN EXCLUSIVE はさらに踏み込んで他の読み取りもブロックします（WALモード以外でしか実質的な意味はありません）。

Q: SQLiteでネストしたトランザクションは使えますか？

BEGIN の中でさらに BEGIN を呼ぶ、というやり方はできません。ネストしたい場合は SAVEPOINT と RELEASE / ROLLBACK TO を使い、1つのトランザクション内で部分的なロールバックを行います。詳しくは次のページで解説します。

トランザクションは「全部成功か、全部なかったことに」

SQLite のトランザクションは、複数の SQL 文をひとまとめにして、すべて反映するか、まったく反映しないかのどちらかにする仕組みです。途中で何か問題が起きたらロールバックすれば、データベースは処理開始前の状態に戻ります。

定番の例といえば、口座間の送金処理です。

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

2 つの UPDATE はワンセットで扱うべきものです。もし両者の間でデータベースがクラッシュしたら、Ada だけが 2000 セント減って、Boris の残高は増えないままになってしまいます。BEGIN ... COMMIT で囲んでペアをアトミックにすれば、両方とも実行されるか、まったく実行されないかのどちらかになります。

autocommit:すでに使っているデフォルト動作

ここまで実行してきた SQL 文は、実はすべてトランザクションとして動いていました。SQLite はデフォルトで autocommit モード になっており、各文が暗黙の BEGIN と COMMIT でくるまれて実行されているのです。

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('1番目');
INSERT INTO notes (body) VALUES ('2番目');
INSERT INTO notes (body) VALUES ('3番目');

SELECT * FROM notes;

INSERT を 3 回流すと、トランザクションも 3 つ、ディスクへの fsync も 3 回走ります。単発の書き込みなら問題ありませんが、バルクロードでは遅くなりますし、複数の文をひとまとまりとして取り消すこともできません。BEGIN を実行すると、次の COMMIT または ROLLBACK までオートコミットがオフになります。

ROLLBACK：なかったことにする

ROLLBACK は、対応する BEGIN 以降の操作をすべて破棄します。データベースはトランザクション開始前の状態へ戻ります。

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

UPDATE も DELETE もきれいに消えて、テーブルは BEGIN 前の状態に戻ります。複数文にまたがる処理の途中でエラーが起きたとき、アプリ側が安全に処理を中断できる「セーフティネット」になっているわけです。

ちなみに、トランザクション中に制約違反が起きても、トランザクション全体が自動でロールバックされるわけではありません。違反した文だけが取り消され、トランザクション自体は開いたまま、どうするかの判断をこちらに委ねてきます。「全部成功か、全部なかったことに」を実現したいなら、エラーを検知した時点でアプリから明示的に ROLLBACK を発行する必要があります。

バルクインサートを高速化する

autocommit モードでは1文ごとに fsync が走るため、大量の処理を1つのトランザクションでまとめるだけで100倍近く速くなることもよくあります。

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

ディスク同期は1行ごとではなく COMMIT のタイミングで1回だけ走ります。数千行をインポートしていて「なんでこんなに遅いんだ?」となったときは、だいたいこれが原因です。

DEFERRED、IMMEDIATE、EXCLUSIVE の違い

BEGIN には、SQLite がいつロックを取得するかを指定するモードがあります。

BEGIN DEFERRED(デフォルト) — 読み書きするまでロックは取りません。書き込みロックは遅延取得され、最初の書き込み文が実行された時点で確保されます。
BEGIN IMMEDIATE — 書き込みロックをその場で取得します。他の接続から読み取りはできますが、書き込みを開始することはできません。
BEGIN EXCLUSIVE — IMMEDIATE と同じ挙動に加えて、他の接続からの読み取りもブロックします。ただし WAL モードでは IMMEDIATE と同じ動作になり、この違いが意味を持つのは古いロールバックジャーナルモードのときだけです。

BEGIN DEFERRED;     -- 通常のBEGINと同じ
BEGIN IMMEDIATE;    -- 今すぐ書き込みロックを予約
BEGIN EXCLUSIVE;    -- すべてを予約(ロールバックジャーナルモード)

この選択は同時実行性に大きく関わります。素の BEGIN だと、2つのコネクションが両方ともトランザクションを開始でき、それぞれ気持ちよく読み込みを進めたあげく、いざ書き込もうとした瞬間に競合します。書き込みロックを後から要求した側は SQLITE_BUSY を食らいますし、しかも厄介なことに、そこまでに行った読み込みを丸ごと捨てるはめになります。

BEGIN IMMEDIATE はこれを解決してくれます。書き込むことが分かっているなら、最初から書き込みロックを取りに行くというわけです。後から来たコネクションは、捨てる羽目になる作業を始める前の段階で、即座にブロック(あるいは即失敗)します。

目安として、書き込みを伴うトランザクションなら BEGIN IMMEDIATE を使う。これが鉄則です。

トランザクション内の読み取りはスナップショットを見る

トランザクションを開いている間、読み取りはデータベースの一貫したスナップショットを参照します。WAL モードならトランザクション開始時点、ロールバックジャーナルモードなら最初の読み取り時点の状態です。他のコネクションが裏でコミットしても、その変更が突然クエリ結果に現れるようなことはありません。

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99、自分自身の書き込みは見える
COMMIT;

SELECT n FROM counter;

自分が書き込んだ未コミットの変更は自分には見えますが、他の接続からは見えません。COMMIT した瞬間に、その新しい値がすべての接続から見えるようになります。SQLite が シリアライザブル と言われるのはこのためで、READ COMMITTED のような分離レベルを切り替えるオプションは用意されていません。デフォルトがすでに最も強い分離レベルだからです。

アプリケーションコードでのトランザクションの書き方

実際のプログラムでは、処理本体を try/except（言語によっては try/catch）で囲み、エラー時には ROLLBACK するパターンが定番です。

-- 任意のクライアントライブラリ向けの擬似コード
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

ほとんどのクライアントライブラリ（Python の sqlite3、better-sqlite3 など）は、with ブロックや transaction() ヘルパーでこのあたりをラップしてくれます。とはいえ、デフォルトの挙動は意外とクセがあるので、自分が使うライブラリのドキュメントには一度目を通しておくのがおすすめです。特に Python の sqlite3 は、autocommit 周りの挙動が長らく独特でした。最近のバージョンでようやく、ちゃんとした autocommit パラメータが追加されて改善されています。

ハマりやすいポイント

DDL もトランザクションの中で動く。 CREATE TABLE、ALTER TABLE、さらには DROP TABLE までロールバックできます。これは SQLite ならではで、多くの DB では DDL は自動的にコミットされてしまいます。
VACUUM はトランザクション内では実行できない。 他にもいくつかメンテナンス系コマンドは同様です。これらは autocommit モードで実行してください。
COMMIT の失敗もれっきとした失敗。 COMMIT が SQLITE_BUSY を返した場合（まれですが起こり得ます）、トランザクションはコミットされていません。アプリ側でハンドリング（通常はリトライ）が必要です。
長いトランザクションは他の書き込みをブロックする。 数分間開きっぱなしのトランザクションは、その数分間ずっと他のライターを止めてしまいます。開くのは遅く、閉じるのは早く、が鉄則です。

次回: セーブポイント

BEGIN と COMMIT はオール・オア・ナッシングです。でも、トランザクションの「一部だけ」をロールバックしたい場面もあります。たとえば、リスクのあるステップだけは取り消したいけれど、それ以外の処理は活かしたい、というケース。そんなときに使うのがセーブポイント（savepoint）で、次回はこの仕組みを見ていきます。

よくある質問

SQLiteでトランザクションを始めるにはどうすればいいですか？

BEGIN;（または BEGIN TRANSACTION;）を実行して処理を行い、確定するなら COMMIT;、捨てるなら ROLLBACK; です。BEGIN を明示しない場合は、各ステートメントが自動的に独立したトランザクションとしてコミットされます（autocommitモード）。

BEGIN・BEGIN IMMEDIATE・BEGIN EXCLUSIVEの違いは？

BEGIN（BEGIN DEFERRED と同じ）は、実際に書き込みが発生するまで書き込みロックを取りません。そのため、後から他のプロセスに先を越されると SQLITE_BUSY で失敗することがあります。BEGIN IMMEDIATE は最初から書き込みロックを確保。BEGIN EXCLUSIVE はさらに踏み込んで他の読み取りもブロックします（WALモード以外でしか実質的な意味はありません）。

SQLiteにトランザクション分離レベルはありますか？

SQL標準の意味での分離レベルはありません。SQLiteは実質的に SERIALIZABLE で、各トランザクションは一貫したスナップショットを見る一方、書き込みは直列化されます。READ COMMITTED や REPEATABLE READ のような切り替えはなく、選べるのは DEFERRED / IMMEDIATE / EXCLUSIVE のみ。これは「何が見えるか」ではなく「いつロックを取るか」を決めるオプションです。

SQLiteでネストしたトランザクションは使えますか？

BEGIN の中でさらに BEGIN を呼ぶ、というやり方はできません。ネストしたい場合は SAVEPOINT と RELEASE / ROLLBACK TO を使い、1つのトランザクション内で部分的なロールバックを行います。詳しくは次のページで解説します。

SQLiteのトランザクション入門：BEGIN/COMMIT/ROLLBACK