一覧DB-08-1 · トランザクションと同時実行

トランザクションのコミットとロールバック

PythonからSQLiteの同じファイルを開き、送金のCOMMIT、失敗後のROLLBACK、一部だけ戻すSAVEPOINTを順に使います。接続を閉じて開き直し、確定した変更と未確定の変更がどう残るかも確認します。

説明

二つの口座の間で200円を移すには、一方を200円減らし、もう一方を200円増やします。途中の失敗で片方だけが残ると、合計残高が変わってしまいます。トランザクションは複数の変更をまとめて確定・取消しするための単位です。

1二口座の残高を用意する

口座1は1000円、口座2は300円で、残高は負数を許しません。

import sqlite3, tempfile
from pathlib import Path
tmp = tempfile.TemporaryDirectory()

path = Path(tmp.name)/'ledger.db'保存先のDBファイル


c = sqlite3.connect(path,

isolation_level=None明示的なBEGINで範囲を管理

)
c.executescript('CREATE TABLE accounts(id INTEGER PRIMARY KEY,balance INTEGER NOT NULL

CHECK(balance>=0)負の残高を拒否

); INSERT INTO accounts VALUES(1,1000),(2,300);')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())

合計は1300円です。isolation_level=Noneにより、BEGINを明示しない各文は自動で確定します。ここからBEGINとCOMMIT/ROLLBACKで、どこまでを一組の変更にするか指定します。

2送金の引落しだけを行う

BEGINの後に口座1から200円を引きます。

c.execute('BEGIN')一組の変更を開始


c.execute('UPDATE accounts SET

balance=balance-200引落しを実行

 WHERE id=1')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:',

c.in_transaction未確定の処理中か)

この接続では800円・300円と表示され、合計は一時的に1100円です。まだ送金の途中で、transactionはTrueです。この状態だけで確定せず、入金と合わせて扱います。

3入金も行って確定する

口座2へ200円を足してCOMMITします。

c.execute('UPDATE accounts SET balance=balance+200送金先へ入金

 WHERE id=2')

c.execute('COMMIT')二つの更新を確定


print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print(c.execute('SELECT

SUM(balance)合計残高を確認 FROM accounts').fetchone())

800円・500円、合計1300円です。二つの更新が一組として確定しました。各行の非負制約に加え、送金前後で合計が保たれることも確認しています。

4接続を開き直して確認する

同じファイルへ再接続します。

c.close()接続を閉じる

c =

sqlite3.connect(path同じファイルを開く

, isolation_level=None)
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())

800円・500円が返ります。接続内の変数だけでなく、同じDBファイルから確定済みの値を取得できています。プロセスが開き直した場合と、電源断時の保存保証は同じ検査ではなく、後者には同期設定や記憶装置の条件も関係します。

5二文目で制約違反が起きる

口座1を100円減らした後、口座2を誤って1000円減らします。

c.execute('BEGIN')
c.execute('UPDATE accounts SET

balance=balance-100 WHERE id=1先の文は成功

')
try:
    c.execute('UPDATE accounts SET

balance=balance-1000 WHERE id=2後の文で負数になり失敗

')

except sqlite3.IntegrityError制約違反を受け取る

 as error:
    print(type(error).__name__+':', error)
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:', c.in_transaction)

CHECK違反で二文目は取り消されますが、一文目の引落しは未確定の変更として残り、700円・500円です。SQLiteの既定のABORTは、その文を戻す処理で、トランザクション全体を自動で戻す処理ではありません。

6失敗した送金を全体で戻す

ROLLBACKします。

c.execute('ROLLBACK')送金全体を取り消す


print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:', c.in_transaction)

800円・500円へ戻り、transactionはFalseです。今回のBEGIN以降をまとめて取り消したため、最初の引落しも残りません。エラー処理では、どの範囲を取り消してから再試行するかを決めます。

7送金後の追加処理に保存点を付ける

50円送金した後、手数料処理の前にSAVEPOINTを置きます。

c.execute('BEGIN')
c.execute('UPDATE accounts SET balance=balance-50 WHERE id=1')
c.execute('UPDATE accounts SET balance=balance+50 WHERE id=2')

c.execute('SAVEPOINT fee')部分的な取消し位置


c.execute('UPDATE accounts SET

balance=balance-10保存点より後の追加変更

 WHERE id=1')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())

手数料10円も引かれて740円・550円です。SAVEPOINT feeは送金完了後の位置を記録します。これにより、送金を保ったまま追加の手数料だけを戻せます。

8保存点まで戻す

手数料の引落しを取り消します。

c.execute('ROLLBACK TO fee')手数料前まで戻す


print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:',

c.in_transaction外側は継続している)

750円・550円となり、50円の送金は残ります。ROLLBACK TOは保存点より後を戻し、外側のトランザクションは続いています。保存点fee自体も残っているため、次にRELEASEで解放します。

9保存点を解放して送金を確定する

RELEASEの後で外側をCOMMITします。

c.execute('RELEASE fee')内側の保存点を解放


print('after release:', c.in_transaction)

c.execute('COMMIT')外側の変更を確定


print('after commit:', c.in_transaction)

RELEASE直後はTrue、COMMIT後はFalseです。内側の保存点を解放するだけでは、外側のBEGINを確定しません。送金が確定するのは外側のCOMMITです。

10未確定のまま接続を閉じる

99円加算を確定せずに閉じ、再接続します。

c.execute('BEGIN')
c.execute('UPDATE accounts SET

balance=balance+99確定しない変更

 WHERE id=1')

c.close()COMMITせずに閉じる


c = sqlite3.connect(path, isolation_level=None)
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print(c.execute('SELECT

SUM(balance)最終合計を確認 FROM accounts').fetchone())

750円・550円、合計1300円です。閉じた接続の未確定99円は保存されません。途中状態の取消し、制約による検査、確定済み値の保持を、それぞれどの操作が担うか対応させます。

まとめ

一連の変更をBEGINからCOMMITへまとめ、途中失敗時は必要な範囲をROLLBACKします。

SAVEPOINTは部分的な取消し位置で、内側のRELEASEと外側のCOMMITは役割が異なります。

同じファイルへの再接続で、確定済みと未確定の変更の違いを確認できます。

操作履歴と解説を振り返れます。

Python / SQLite

import sqlite3, tempfile
from pathlib import Path
tmp = tempfile.TemporaryDirectory()
path = Path(tmp.name)/'ledger.db'
c = sqlite3.connect(path, isolation_level=None)
c.executescript('CREATE TABLE accounts(id INTEGER PRIMARY KEY,balance INTEGER NOT NULL CHECK(balance>=0)); INSERT INTO accounts VALUES(1,1000),(2,300);')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())

[(1, 1000), (2, 300)]

c.execute('BEGIN')
c.execute('UPDATE accounts SET balance=balance-200 WHERE id=1')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:', c.in_transaction)

[(1, 800), (2, 300)]
transaction: True

c.execute('UPDATE accounts SET balance=balance+200 WHERE id=2')
c.execute('COMMIT')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print(c.execute('SELECT SUM(balance) FROM accounts').fetchone())

[(1, 800), (2, 500)]
(1300,)

c.close()
c = sqlite3.connect(path, isolation_level=None)
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())

[(1, 800), (2, 500)]

c.execute('BEGIN')
c.execute('UPDATE accounts SET balance=balance-100 WHERE id=1')
try:
    c.execute('UPDATE accounts SET balance=balance-1000 WHERE id=2')
except sqlite3.IntegrityError as error:
    print(type(error).__name__+':', error)
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:', c.in_transaction)

IntegrityError: CHECK constraint failed: balance>=0
[(1, 700), (2, 500)]
transaction: True

c.execute('ROLLBACK')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:', c.in_transaction)

[(1, 800), (2, 500)]
transaction: False

c.execute('BEGIN')
c.execute('UPDATE accounts SET balance=balance-50 WHERE id=1')
c.execute('UPDATE accounts SET balance=balance+50 WHERE id=2')
c.execute('SAVEPOINT fee')
c.execute('UPDATE accounts SET balance=balance-10 WHERE id=1')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())

[(1, 740), (2, 550)]

c.execute('ROLLBACK TO fee')
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print('transaction:', c.in_transaction)

[(1, 750), (2, 550)]
transaction: True

c.execute('RELEASE fee')
print('after release:', c.in_transaction)
c.execute('COMMIT')
print('after commit:', c.in_transaction)

after release: True
after commit: False

c.execute('BEGIN')
c.execute('UPDATE accounts SET balance=balance+99 WHERE id=1')
c.close()
c = sqlite3.connect(path, isolation_level=None)
print(c.execute('SELECT * FROM accounts ORDER BY id').fetchall())
print(c.execute('SELECT SUM(balance) FROM accounts').fetchone())

[(1, 750), (2, 550)]
(1300,)

演習完了