SQL・データベース
PR

SQLのトランザクション入門|BEGIN・COMMIT・ROLLBACKを実行結果で解説

SQLのトランザクション入門|BEGIN・COMMIT・ROLLBACKを実行結果で解説
hr
記事内に商品プロモーションを含む場合があります

「A さんの口座から 3,000 円引いて、B さんの口座に 3,000 円足す」。この 2 つの UPDATE のあいだでエラーが起きると、A さんのお金だけが減って、3,000 円がどこかへ消えてしまいます。これを防ぐのがトランザクション(BEGIN・COMMIT・ROLLBACK)です。

INSERT・UPDATE・DELETE の書き方は「SQLのINSERT・UPDATE・DELETEとトランザクション入門」で解説しています。この記事ではその続きとして、Python 標準の SQLite で実行した結果を使い、ACID、SAVEPOINT、2 つの接続での見え方と database is locked、Python の sqlite3 の autocommit と with 文までを解説します。

トランザクションとは

トランザクションは、複数の SQL をひとまとまりの処理として扱う仕組みです。確定するまでは仮の状態で、途中で失敗したら全部をなかったことにできます。

命令すること
BEGIN;トランザクションを始める
COMMIT;BEGIN 以降の変更を確定する(ほかの接続からも見えるようになる)
ROLLBACK;BEGIN 以降の変更をすべて取り消す

BEGIN を書かないときは、1 つの SQL ごとに自動でトランザクションが始まり、その SQL が終わると自動で確定します。これを自動コミット(autocommit)と呼びます(SQLite の公式ドキュメントで確認)。

練習用のスクリプトを用意する

口座(accounts)テーブルをメモリー上に作り、SQL を 1 行ずつ入力して試せるスクリプトです。tx_practice.py として保存します。終了するとデータは消え、起動し直すと最初の状態に戻ります。

import sqlite3

con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE accounts (
  name    TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES ('A', 10000), ('B', 5000), ('C', 0);
""")

print("SQL を入力して Enter(終了は exit)")
while True:
    mark = "(トランザクション中)" if con.in_transaction else ""
    sql = input(f"sql{mark}> ").strip()
    if sql.lower() in ("exit", "quit"):
        break
    if not sql:
        continue
    try:
        cur = con.execute(sql)
    except sqlite3.Error as e:
        print("エラー:", e)
        continue
    if cur.description is None:
        print(f"({cur.rowcount} 行を処理)" if cur.rowcount >= 0 else "OK")
        continue
    print(" | ".join(d[0] for d in cur.description))
    for row in cur.fetchall():
        print(" | ".join(str(v) for v in row))

保存したフォルダーで次のコマンドを実行し、「sql>」と表示されたら SQL を 1 行ずつ入力して Enter を押します(Windows 11・Python 3.12.6・SQLite 3.45.3 で確認しました)。

py tx_practice.py

最初の残高は A が 10,000 円、B が 5,000 円、C が 0 円です。balance(残高)には「0 以上」という CHECK 制約を付けています。isolation_level=None は、入力した BEGIN・COMMIT のとおりに動かす設定です(後の章で説明)。トランザクション中は入力欄が「sql(トランザクション中)>」に変わります。

トランザクションなしで送金すると、片方だけ反映される

A から B へ 3,000 円を送金します。2 つ目の UPDATE は、列名 balance を balanse と打ち間違えたつもりです。

UPDATE accounts SET balance = balance - 3000 WHERE name = 'A';
UPDATE accounts SET balance = balanse + 3000 WHERE name = 'B';
SELECT * FROM accounts;
sql> (1 行を処理)
sql> エラー: no such column: balanse
sql> name | balance
A | 7000
B | 5000
C | 0

A は 7,000 円に減ったのに、B は 5,000 円のままです。合計は 15,000 円から 12,000 円になり、3,000 円が消えました。1 つ目の UPDATE は自動コミットですでに確定しているので、ROLLBACK と入力しても「cannot rollback – no transaction is active」(トランザクションが始まっていない)になり、元に戻せません。

BEGIN〜COMMIT・ROLLBACK で「全部か、何もなしか」にする

スクリプトを exit で終了し、もう一度起動して残高を最初の状態に戻します。今度は BEGIN してから同じ 2 つの UPDATE を実行します。

BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE name = 'A';
UPDATE accounts SET balance = balanse + 3000 WHERE name = 'B';
SELECT * FROM accounts;
sql> OK
sql(トランザクション中)> (1 行を処理)
sql(トランザクション中)> エラー: no such column: balanse
sql(トランザクション中)> name | balance
A | 7000
B | 5000
C | 0

この時点では A が 7,000 円に見えますが、まだ確定していません。ここで ROLLBACK; を実行してから SELECT * FROM accounts; すると、A は 10,000 円、B は 5,000 円に戻っていました。

2 つ目の UPDATE を正しく直して、もう一度やり直します。

BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE name = 'A';
UPDATE accounts SET balance = balance + 3000 WHERE name = 'B';
COMMIT;

COMMIT のあとに SELECT すると A は 7,000 円、B は 8,000 円で、SELECT SUM(balance) FROM accounts; の合計は 15,000 円のままでした。成功したら COMMIT、失敗したら ROLLBACK。これがトランザクションの基本の流れです。

成功したときは BEGIN、UPDATE A、UPDATE B、COMMIT で両方を確定する。UPDATE B でエラーが起きたときは ROLLBACK で両方を取り消し、A 10000、B 5000 に戻る
図 1:COMMIT で両方を確定し、ROLLBACK で両方を取り消す

注意したいのは、エラーが起きてもトランザクションは自動では取り消されないことです。上の例でも、エラーのあと入力欄は「トランザクション中」のままでした。ここで ROLLBACK ではなく COMMIT を実行すると、A が 7,000 円・B が 5,000 円のまま確定してしまいました。エラーが起きたら、自分で ROLLBACK します。長い SQL は SQL整形ツールで整形しておくと、列名の打ち間違いや WHERE の書き忘れに気づきやすくなります。

なお、COMMIT の代わりに END; と書いても同じ意味です(SQLite の公式ドキュメントに「END TRANSACTION is an alias for COMMIT」とあり、実行して確かめました)。

ACID:トランザクションが守る 4 つの性質

データベースの本や試験でよく出てくるのが ACID(アシッド)です。トランザクションが守るべき 4 つの性質の頭文字です。

性質意味この記事の例
Atomicity(原子性)全部実行されるか、何も実行されないかのどちらか引く・足すの片方だけが残らない
Consistency(一貫性)制約などのルールに合った状態が保たれるCHECK 制約で残高がマイナスにならない
Isolation(独立性)同時に動くトランザクションどうしが影響し合わないCOMMIT 前の変更はほかの接続から見えない
Durability(永続性)COMMIT した変更は、その後に障害が起きても失われないCOMMIT 後はファイルに保存されている

「全部か何もなしか」「ルールを守る」「途中を見せない」「確定したら消えない」とイメージできれば十分です。

SAVEPOINT:トランザクションの一部だけを取り消す

まとめて処理している送金の 1 件だけが失敗したとき、ROLLBACK すると成功分も全部消えます。SAVEPOINT(セーブポイント)を使うと、トランザクションの途中に「ここまで戻れる印」を付けられます。

命令すること
SAVEPOINT 名前;印を付ける
ROLLBACK TO 名前;印を付けた直後の状態に戻す。トランザクションは続く
RELEASE 名前;印を消す(変更は取り消さない)

スクリプトを起動し直して、3 件の送金を 1 つのトランザクションで処理します。2 件目は、存在しない口座 D に送ろうとしています。

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE name = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE name = 'C';
SAVEPOINT t2;
UPDATE accounts SET balance = balance - 2000 WHERE name = 'B';
UPDATE accounts SET balance = balance + 2000 WHERE name = 'D';

最後の UPDATE はエラーにならず「(0 行を処理)」と表示されました。条件に合う行がない UPDATE は成功扱いです。このまま SELECT すると、B だけが 3,000 円に減っています。

name | balance
A | 9000
B | 3000
C | 1000

ここで ROLLBACK TO t2; を実行して SELECT すると、B は 5,000 円に戻り、1 件目(A 9,000 円・C 1,000 円)は残っていました。入力欄は「トランザクション中」のままで、RELEASE t2; と COMMIT; で 1 件目だけが確定しました。SQLite の公式ドキュメントによると、ROLLBACK TO はトランザクションを終わらせず、戻った印(t2)も残ります。消えるのはその印より後に付けた印だけです。また、すでに消えた印を RELEASE すると「no such savepoint: t2」というエラーになりました。

BEGIN せずに SAVEPOINT を実行すると、BEGIN と同じようにトランザクションが始まり、その印を RELEASE すると COMMIT と同じになります(公式ドキュメントで確認し、実行して確かめました)。プログラムでは、「送金 1 件ごとに SAVEPOINT → 失敗したら ROLLBACK TO、成功したら RELEASE」のように使います。

ほかの接続からは COMMIT 前の変更が見えない

Isolation(独立性)を確かめます。アプリでは 1 つのデータベースに複数のプログラムがつながり、そのつながり 1 本を接続(コネクション)と呼びます。同じファイルに 2 つの接続を開き、接続 1 で送金している途中に接続 2 から残高を読んでみます。two_conn.py として保存してください。

import sqlite3
import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as folder:
    db = Path(folder) / "bank.db"  # 一時フォルダーに作り、最後に消える
    con1 = sqlite3.connect(db, isolation_level=None)
    con2 = sqlite3.connect(db, isolation_level=None)
    con1.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL)")
    con1.execute("INSERT INTO accounts VALUES ('A', 10000), ('B', 5000)")

    def show(label, con):
        print(f"{label}:", con.execute("SELECT * FROM accounts").fetchall())

    con1.execute("BEGIN")
    con1.execute("UPDATE accounts SET balance = balance - 3000 WHERE name = 'A'")
    con1.execute("UPDATE accounts SET balance = balance + 3000 WHERE name = 'B'")
    show("COMMIT 前・接続1", con1)
    show("COMMIT 前・接続2", con2)
    con1.execute("COMMIT")
    show("COMMIT 後・接続2", con2)
    con1.close()
    con2.close()
py two_conn.py
COMMIT 前・接続1: [('A', 7000), ('B', 8000)]
COMMIT 前・接続2: [('A', 10000), ('B', 5000)]
COMMIT 後・接続2: [('A', 7000), ('B', 8000)]

接続 1 は自分の変更(A 7,000 円)が見えていますが、接続 2 からは COMMIT 前の変更が見えず、元の残高のままです。COMMIT したあとで、接続 2 にも新しい残高が見えました。

接続1が BEGIN して UPDATE した後、接続1からは A 7000、B 8000 が見えるが、接続2からは A 10000、B 5000 のまま。接続1の COMMIT 後は接続2からも A 7000、B 8000 が見える
図 2:COMMIT 前の変更は、ほかの接続からは見えない

SQLite の公式ドキュメント「Isolation In SQLite」によると、確定前の変更は同じプロセスの接続からも見えず、SQLite のトランザクションは SERIALIZABLE(後述の分離レベルでいちばん強いもの)です(既定では使われない共有キャッシュと read_uncommitted を組み合わせた場合だけが例外)。

database is locked:書き込みがぶつかったとき

SQLite では、データベースに書き込めるのは同時に 1 つの接続だけです。two_conn.py と同じように 2 つの接続を開き、接続 1 が BEGIN して UPDATE したまま COMMIT しない間に、接続 2(timeout=2 で接続)が読み書きしてみました。次はその中心の部分です。

con2 = sqlite3.connect(db, isolation_level=None, timeout=2)  # 最大 2 秒待つ
con1.execute("BEGIN")
con1.execute("UPDATE accounts SET balance = balance - 3000 WHERE name = 'A'")
print("接続2で読む:", con2.execute("SELECT * FROM accounts").fetchall())
con2.execute("UPDATE accounts SET balance = balance + 100 WHERE name = 'B'")  # ここでエラー
con1.execute("COMMIT")
con2.execute("UPDATE accounts SET balance = balance + 100 WHERE name = 'B'")  # 今度は成功
接続2で読む: [('A', 10000), ('B', 5000)]
接続2で書く: database is locked(2.2 秒)
接続1の COMMIT 後: [('A', 7000), ('B', 5100)]

接続 2 は読むことはできましたが、書き込みは約 2 秒待ったあとで「database is locked」(データベースがロックされている)というエラーになりました。timeout は、ほかの接続の書き込みが終わるのを待つ秒数で、Python の公式ドキュメントによると既定は 5 秒です。接続 1 が COMMIT したあとは、接続 2 の UPDATE も成功しました(3 回とも約 2.2 秒)。

timeout を延ばす前に、原因の「開いたままのトランザクション」をなくします。BEGIN したら早めに COMMIT か ROLLBACK し、トランザクションの中でユーザーの入力や時間のかかる通信を待たないようにします。

読んでから書くなら BEGIN IMMEDIATE

SQLite の BEGIN は、既定では DEFERRED(後回し)で、最初にデータベースを読み書きしたときに実際のトランザクションが始まります。2 つの接続が両方とも BEGIN して残高を SELECT し、そのあとで両方が UPDATE しようとすると、次のようになりました(timeout はどちらも 2 秒)。

接続1: BEGIN → OK
接続1: SELECT(A の残高) → OK [(10000,)]
接続2: BEGIN → OK
接続2: SELECT(A の残高) → OK [(10000,)]
接続1: UPDATE(A から 3000 引く) → OK
接続2: UPDATE(A から 3000 引く) → database is locked(0.0 秒)
接続1: COMMIT → database is locked(2.3 秒)
接続2: ROLLBACK → OK
接続1: COMMIT → OK

接続 2 の UPDATE は、timeout を待たずにすぐエラーになりました。接続 1 の COMMIT も、接続 2 が読み取りのトランザクションを開いたままなので待たされ、接続 2 が ROLLBACK してやっと確定しました。お互いが相手を待つ「デッドロック」になりかけた状態で、SQLite の公式ドキュメント(sqlite3_busy_handler)にも、そう判断したときは待たずにエラーを返すと書かれています。

「残高を読んで、その値をもとに書き込む」ときは、BEGIN IMMEDIATE; で始めます。IMMEDIATE は、書き込む前から書き込みのトランザクションを始めます(公式ドキュメント)。2 つの処理をスレッドで同時に動かし、処理 1 が残高 10,000 円から 3,000 円を引き出す(1 秒かかる)間に、処理 2 も BEGIN IMMEDIATE しました(timeout は 5 秒。コードは省略)。

処理1: 待ち 0.0 秒、読んだ残高 10000 → 7000 に更新
処理2: 待ち 0.9 秒、読んだ残高 7000 → 4000 に更新
最終残高: 4000

処理 2 は BEGIN IMMEDIATE の時点で処理 1 の COMMIT を待ち、確定後の残高(7,000 円)を読んでから更新しました。両方が 10,000 円を読んで 7,000 円を書き込む、という食い違いは起きません。

Python の sqlite3 の暗黙のトランザクション

Python の sqlite3 は、設定によって BEGIN を自動で(暗黙に)実行します。4 つの設定で接続し、CREATE TABLE・SELECT・INSERT のあとにトランザクション中かどうか(in_transaction)を調べました(Python 3.12.6)。

connect() の指定接続直後CREATE TABLE 後SELECT 後INSERT 後
指定なし(既定)FalseFalseFalseTrue
isolation_level=NoneFalseFalseFalseFalse
autocommit=TrueFalseFalseFalseFalse
autocommit=FalseTrueTrueTrueTrue
  • 指定なし:autocommit 属性は LEGACY_TRANSACTION_CONTROL(値は -1、3.12 より前と同じ動き)。INSERT・UPDATE・DELETE・REPLACE の前にだけ自動で BEGIN し、commit() まで確定しない
  • isolation_level=None:自動の BEGIN をしない。BEGIN・COMMIT は自分で書く。3.11 以前でも使える
  • autocommit=True(3.12 から):SQLite の自動コミット。commit() と rollback() は何もしない
  • autocommit=False(3.12 から):常にトランザクションが開いていて、commit() か rollback() のあと次が自動で始まる。公式ドキュメントの推奨で、将来は既定値になる予定

isolation_level(”DEFERRED”・”IMMEDIATE”・”EXCLUSIVE”・None)が効くのは autocommit が既定の LEGACY_TRANSACTION_CONTROL のときだけで、公式ドキュメントでは autocommit を使うよう勧めています。また既定の設定では、executescript() が実行の前に確定していない変更を COMMIT します(公式ドキュメント)。

おすすめの書き方:autocommit=False と with conn:

接続を with で使うと、ブロックが例外なく終われば COMMIT、例外が起きれば ROLLBACK されます。送金の処理を関数にして、口座がない(処理した行数が 1 でない)ときは例外を出して取り消すようにしました。transfer.py として保存して実行します。

import sqlite3

con = sqlite3.connect(":memory:", autocommit=False)
con.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY,"
            " balance INTEGER NOT NULL CHECK (balance >= 0))")
con.execute("INSERT INTO accounts VALUES ('A', 10000), ('B', 5000)")
con.commit()


def transfer(src, dst, amount):
    with con:  # 例外なく終われば COMMIT、例外が起きたら ROLLBACK
        cur = con.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?",
                          (amount, src))
        if cur.rowcount != 1:
            raise ValueError(f"口座 {src} がありません")
        cur = con.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?",
                          (amount, dst))
        if cur.rowcount != 1:
            raise ValueError(f"口座 {dst} がありません")


for src, dst, amount in [("A", "B", 3000), ("A", "Z", 1000), ("B", "A", 9000)]:
    try:
        transfer(src, dst, amount)
        print(f"{src}→{dst} {amount} 円:成功")
    except (ValueError, sqlite3.IntegrityError) as e:
        print(f"{src}→{dst} {amount} 円:取り消し({e})")
    print("  ", con.execute("SELECT * FROM accounts").fetchall())
con.close()
py transfer.py
A→B 3000 円:成功
   [('A', 7000), ('B', 8000)]
A→Z 1000 円:取り消し(口座 Z がありません)
   [('A', 7000), ('B', 8000)]
B→A 9000 円:取り消し(CHECK constraint failed: balance >= 0)
   [('A', 7000), ('B', 8000)]

2 件目は A から 1,000 円を引いたあとで例外になりましたが、ROLLBACK されて A は 7,000 円のままです。3 件目は B の残高がマイナスになる UPDATE が CHECK 制約違反(sqlite3.IntegrityError)になり、何も変わっていません。値は文字列に埋め込まず、?(プレースホルダ)で渡しています。

公式ドキュメントによると、with conn: は出るときに開いているトランザクションを COMMIT か ROLLBACK するだけで、新しいトランザクションを始めることも、接続を閉じることもしません(閉じるのは close() か contextlib.closing())。トランザクションが開いていないときや autocommit=True のときは何もしません。

with 文のしくみそのものは「Pythonのwith文とは?コンテキストマネージャーの仕組みと作り方」で解説しています。

MySQL・PostgreSQL のトランザクションと分離レベル

BEGIN・COMMIT・ROLLBACK は MySQL や PostgreSQL でも使えますが、細かい動きは違います。この章は MySQL 8.4 と PostgreSQL 18 の公式ドキュメントで確認した内容の紹介で、実機では実行していません。

同時に動くトランザクションがどこまでお互いの変更を見えるかを決める設定を、分離レベル(isolation level)と呼びます。標準 SQL では、弱い順に READ UNCOMMITTED・READ COMMITTED・REPEATABLE READ・SERIALIZABLE の 4 つが決められています。

項目SQLiteMySQL(InnoDB)PostgreSQL
既定の分離レベルSERIALIZABLEREPEATABLE READREAD COMMITTED
BEGIN の扱いトランザクションを始めるSTART TRANSACTION の別名START TRANSACTION と同じ
BEGIN 中にもう一度 BEGINエラー(実行して確認)開いているトランザクションを COMMIT してから始まる警告が出て、状態は変わらない
CREATE TABLE などの取り消しROLLBACK で取り消せる(実行して確認)CREATE・ALTER・DROP TABLE などは、実行前に暗黙に COMMIT される(この記事では未確認)

MySQL は SET TRANSACTION 文で分離レベルを変えられます。PostgreSQL は 4 つを指定できますが、READ UNCOMMITTED は READ COMMITTED と同じ動きです。また REPEATABLE READ と SERIALIZABLE では、直列化の失敗(SQLSTATE 40001)が起きたらトランザクションを最初からやり直す前提で作るよう書かれています。

特に MySQL では、BEGIN の重ねがけや CREATE TABLE で前の変更が確定し、ROLLBACK で戻らないことがあります。使うデータベースの公式ドキュメントを確認してください。

よくある間違い

Python で commit() を忘れて、変更が保存されない

既定の設定(指定なし)で送金の 2 つの UPDATE を実行し、commit() を呼ばずに close() して、開き直しました。次はその部分の抜粋です(bank.db には A 10,000 円・B 5,000 円を入れて commit 済み)。

con = sqlite3.connect("bank.db")
con.execute("UPDATE accounts SET balance = balance - 3000 WHERE name = 'A'")
con.execute("UPDATE accounts SET balance = balance + 3000 WHERE name = 'B'")
print("閉じる前:", con.execute("SELECT * FROM accounts").fetchall())
con.close()  # commit() を忘れた

con = sqlite3.connect("bank.db")
print("開き直す:", con.execute("SELECT * FROM accounts").fetchall())
con.close()
閉じる前: [('A', 7000), ('B', 8000)]
開き直す: [('A', 10000), ('B', 5000)]

UPDATE の前に自動で BEGIN されたまま閉じたので、変更は保存されず、エラーも出ません。同じ接続では変更が見えるので、閉じる前の SELECT では気づけません。with conn: を使えば書き忘れを防げます。

autocommit=True のまま with conn: に頼る

with conn: は autocommit=True では何もしません。送金の 2 つ目の UPDATE を打ち間違えた(balanse)コードを、autocommit=False と True の両方で with conn: の中で実行した結果です。

エラー: no such column: balanse
{'autocommit': False} [('A', 10000), ('B', 5000)]
エラー: no such column: balanse
{'autocommit': True} [('A', 7000), ('B', 5000)]

autocommit=True では、1 つ目の UPDATE がその場で確定していて、A だけが減りました。with の中で自分で con.execute("BEGIN") しても、autocommit=True ではエラーのあとトランザクションが開いたまま残りました(isolation_level=None では ROLLBACK されました)。with conn: に任せるなら autocommit=False にします。

よくある質問

CREATE TABLE や DROP TABLE も ROLLBACK で戻せますか?

SQLite では戻せました。BEGIN のあと CREATE TABLE logs と DROP TABLE accounts を実行してから ROLLBACK すると、logs はなく、accounts は 3 行とも残っていました。MySQL では CREATE TABLE・DROP TABLE などが暗黙に COMMIT されるため戻せません(公式ドキュメント)。テーブルの作り方は「SQLのCREATE TABLE入門」をご覧ください。

まとめ

  • まとめて成功させたい変更は BEGIN〜COMMIT で囲み、エラーが起きたら自分で ROLLBACK する
  • 一部だけ取り消したいときは SAVEPOINT と ROLLBACK TO を使う
  • COMMIT 前の変更はほかの接続から見えない。SQLite で書けるのは同時に 1 つの接続だけで、待ちきれないと database is locked になる
  • Python 3.12 以降は autocommit=False と with conn: の組み合わせがおすすめ。接続は close() で閉じる

関連する記事・ツール

ABOUT ME
hr
hr
エンジニアとして働いている hr です。仕事や資格勉強で「こんなツールがあれば」と感じたものを、無料Webツールと解説記事にして発信しています。
記事URLをコピーしました