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

トランザクションの分離とスナップショット

WALモードのSQLiteを接続A・Bから開き、確定前後と読み取り中のスナップショットを調べます。古い読み取りからの更新が拒否される例も確認します。最後に共有キャッシュとread_uncommittedを組み合わせた別のメモリDBで、未確定値が見える条件を試します。

説明

同じDBを二つの接続から使うとき、一方の変更が他方へいつ見えるかは分離の規則で決まります。確定前の値を読むdirty read、読み直したときの値の変化、条件に合う行の増減を、操作の順序と合わせて確認します。

1WALモードのDBに二つの接続を開く

id 1の数量を10として保存します。

import sqlite3, tempfile
from pathlib import Path
tmp = tempfile.TemporaryDirectory()
path = Path(tmp.name)/'visibility.db'
a = sqlite3.connect(path, isolation_level=None,

timeout=0競合時の待ち時間を0にする

)
b = sqlite3.connect(path, isolation_level=None, timeout=0)
print(a.execute('

PRAGMA journal_mode=WALWALモードを指定

').fetchone())
a.executescript('CREATE TABLE stock(id INTEGER PRIMARY KEY,qty INTEGER); INSERT INTO stock VALUES(1,10);')

print('B:'接続Bからの読取り, b.execute('SELECT * FROM stock').fetchall())

journal_modeはwal、Bからは(1,10)が見えます。WALでは変更をログ側へ追加し、読み取り側は読み取り開始時点の状態を参照できます。timeout=0は競合時に待ち続けず、その場で結果を確認するための指定です。

2Aの未確定変更をBから読む

Aが数量20へ更新し、まだCOMMITしません。

a.execute変更する接続A

('BEGIN')
a.execute('UPDATE stock SET qty=20 WHERE id=1')

print('A:'自分の未確定値

, a.execute('SELECT qty FROM stock WHERE id=1').fetchone())

print('B:'他の接続の確定済み値, b.execute('SELECT qty FROM stock WHERE id=1').fetchone())

Aは自分の変更20を読み、Bは確定済みの10を読みます。通常の独立した接続では、他の接続の未確定変更は見えません。この段階でBが20を読むdirty readは起きていません。

3Aが確定するとBの次の読取りへ反映する

AをCOMMITし、Bから再取得します。

a.execute('COMMIT')Aの変更を公開


print('B:',

b.execute次の文として再取得('SELECT qty FROM stock WHERE id=1').fetchone())

Bは20を読みます。Bは長い読み取りトランザクションを開始していないので、次の文で新しい確定状態を取得できます。前の結果10を保持し続けるわけではありません。

4Bの読み取りを継続する

BでBEGINしてから表を読みます。

b.execute('BEGIN')読み取りを一つの処理範囲にする


print('B snapshot:', b.execute('

SELECT * FROM stockここで読み取りを開始 ORDER BY id').fetchall())

Bの読み取りスナップショットは一行(1,20)です。BEGINだけでなく、実際に読み始めた時点の状態が、この読み取りトランザクションの基準になります。

5Aが値の更新と行の追加を確定する

Aでid 1を30にし、id 2を40として追加します。

a.execute('BEGIN')
a.execute('UPDATE stock SET

qty=30既存行を変更

 WHERE id=1')
a.execute('INSERT INTO stock

VALUES(2,40)新しい行を追加

')

a.execute('COMMIT')両方を確定


print('A:', a.execute('SELECT * FROM stock ORDER BY id').fetchall())

Aでは(1,30)と(2,40)が見えます。WALでは既存の読み取りを保ったまま書込み側が進めるため、Bの読取りを終了させなくてもAは確定できます。

6Bのトランザクション内では値も行数も変わらない

Bから同じ表と件数を取得します。

print('B:', b.execute('SELECT * FROM stock同じ読み取りを反復

 ORDER BY id').fetchall())
print('B count:', b.execute('

SELECT COUNT(*)行数も同じか確認 FROM stock').fetchone())

Bは(1,20)の一行のままです。同じ読み取りスナップショットでは、既存行の30への変化も新しいid 2も入りません。繰返し読みで値が変わることや、同じ対象へ行が増えるphantomを、この読み取り範囲では観測しません。

7Bの読取りを終了して取得し直す

BをCOMMITしてから再検索します。

b.execute('COMMIT')古い読み取り範囲を終了


print('B:', b.execute('

SELECT * FROM stock新しい範囲で読む ORDER BY id').fetchall())

今度は(1,30)と(2,40)です。見え方の基準が新しい確定状態へ進みました。複数の値の版を扱う考え方はMVCCに関係しますが、SQLiteのWALと、他製品の行バージョンの保存方式を同じ内部構造として扱うことはできません。

8古い読取りから書込みへ進もうとする

Bが読んだ後でAが35へ変更し、Bが更新を試します。

b.execute('BEGIN')Bの読み取り範囲を開始


print('B read:', b.execute('SELECT qty FROM stock WHERE id=1').fetchone())
a.execute('UPDATE stock SET

qty=35Aが後から確定

 WHERE id=1')
try:
    b.execute('UPDATE stock SET qty=qty+1 WHERE id=1')
except sqlite3.OperationalError as error:
    print(

error.sqlite_errorname拒否された具体的なコード

+':', error)

b.execute('ROLLBACK')古い範囲を終了

SQLITE_BUSY_SNAPSHOTで拒否されます。Bの古いスナップショットを基に書き込むと、既に進んだ履歴から分岐してしまうためです。単に同じUPDATEを繰り返すのではなく、古いトランザクションを終了して読み直します。

9新しい状態から更新する

Bが新しいトランザクションで数量を1増やします。

b.execute('BEGIN IMMEDIATE書込みを先に開始する

')
b.execute('UPDATE stock SET

qty=qty+1最新状態へ1を加える

 WHERE id=1')
b.execute('COMMIT')
print(b.execute('SELECT * FROM stock ORDER BY id').fetchall())

現在の35から36になります。BEGIN IMMEDIATEは最初に書込みを開始できるか確認する方法です。複数の書込みを同時に確定させる指定ではなく、SQLiteでは書込みは一度に一接続です。

10共有キャッシュの別DBではまず読み取りが拒否される

共有メモリDBの二接続を作り、Cの未確定更新をDから読みます。

uri = 'file:lbox_visibility?mode=memory&cache=sharedキャッシュを共有する接続構成

'
c = sqlite3.connect(uri, uri=True, isolation_level=None, timeout=0)
d = sqlite3.connect(uri, uri=True, isolation_level=None, timeout=0)
c.executescript('CREATE TABLE sample(qty INTEGER); INSERT INTO sample VALUES(10); BEGIN;

UPDATE sample SET qty=99Cの未確定更新

;')
try:
    print(

d.executeDから読取りを試す

('SELECT qty FROM sample').fetchone())
except sqlite3.OperationalError as error:
    print(error.sqlite_errorname+':', error)

既定ではSQLITE_LOCKED_SHAREDCACHEとなります。この共有キャッシュ構成は先ほどの独立したファイル接続とは別です。未確定99を読めるようになったわけではなく、表のロックで読取りが拒否されました。

11共有キャッシュとread_uncommittedを組み合わせる

Dだけread_uncommittedを有効にします。

d.execute('PRAGMA read_uncommitted=ON共有接続の未確定値を読む設定

')
print('D dirty:', d.execute('SELECT qty FROM sample').fetchone())

c.execute('ROLLBACK')読まれた値が後から取り消される


print('D after rollback:', d.execute('SELECT qty FROM sample').fetchone())

Dは未確定99を読み、Cが取り消すと10になります。確定しなかった値を読んだdirty readの例です。SQLiteでは共有キャッシュとの組合せが条件であり、別々の通常接続へこのPRAGMAだけを付けて同じ挙動になるとは限りません。

まとめ

確定の時点と読み取り範囲の開始・終了を分けて、見える値を判断します。

古いスナップショットで更新が拒否されたら、トランザクションを終了して読み直します。

SQLiteの未確定読取りは共有キャッシュとの組合せであり、接続構成も条件になります。

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

Python / SQLite

import sqlite3, tempfile
from pathlib import Path
tmp = tempfile.TemporaryDirectory()
path = Path(tmp.name)/'visibility.db'
a = sqlite3.connect(path, isolation_level=None, timeout=0)
b = sqlite3.connect(path, isolation_level=None, timeout=0)
print(a.execute('PRAGMA journal_mode=WAL').fetchone())
a.executescript('CREATE TABLE stock(id INTEGER PRIMARY KEY,qty INTEGER); INSERT INTO stock VALUES(1,10);')
print('B:', b.execute('SELECT * FROM stock').fetchall())

('wal',)
B: [(1, 10)]

a.execute('BEGIN')
a.execute('UPDATE stock SET qty=20 WHERE id=1')
print('A:', a.execute('SELECT qty FROM stock WHERE id=1').fetchone())
print('B:', b.execute('SELECT qty FROM stock WHERE id=1').fetchone())

A: (20,)
B: (10,)

a.execute('COMMIT')
print('B:', b.execute('SELECT qty FROM stock WHERE id=1').fetchone())

B: (20,)

b.execute('BEGIN')
print('B snapshot:', b.execute('SELECT * FROM stock ORDER BY id').fetchall())

B snapshot: [(1, 20)]

a.execute('BEGIN')
a.execute('UPDATE stock SET qty=30 WHERE id=1')
a.execute('INSERT INTO stock VALUES(2,40)')
a.execute('COMMIT')
print('A:', a.execute('SELECT * FROM stock ORDER BY id').fetchall())

A: [(1, 30), (2, 40)]

print('B:', b.execute('SELECT * FROM stock ORDER BY id').fetchall())
print('B count:', b.execute('SELECT COUNT(*) FROM stock').fetchone())

B: [(1, 20)]
B count: (1,)

b.execute('COMMIT')
print('B:', b.execute('SELECT * FROM stock ORDER BY id').fetchall())

B: [(1, 30), (2, 40)]

b.execute('BEGIN')
print('B read:', b.execute('SELECT qty FROM stock WHERE id=1').fetchone())
a.execute('UPDATE stock SET qty=35 WHERE id=1')
try:
    b.execute('UPDATE stock SET qty=qty+1 WHERE id=1')
except sqlite3.OperationalError as error:
    print(error.sqlite_errorname+':', error)
b.execute('ROLLBACK')

B read: (30,)
SQLITE_BUSY_SNAPSHOT: database is locked

b.execute('BEGIN IMMEDIATE')
b.execute('UPDATE stock SET qty=qty+1 WHERE id=1')
b.execute('COMMIT')
print(b.execute('SELECT * FROM stock ORDER BY id').fetchall())

[(1, 36), (2, 40)]

uri = 'file:lbox_visibility?mode=memory&cache=shared'
c = sqlite3.connect(uri, uri=True, isolation_level=None, timeout=0)
d = sqlite3.connect(uri, uri=True, isolation_level=None, timeout=0)
c.executescript('CREATE TABLE sample(qty INTEGER); INSERT INTO sample VALUES(10); BEGIN; UPDATE sample SET qty=99;')
try:
    print(d.execute('SELECT qty FROM sample').fetchone())
except sqlite3.OperationalError as error:
    print(error.sqlite_errorname+':', error)

SQLITE_LOCKED_SHAREDCACHE: database table is locked: sample

d.execute('PRAGMA read_uncommitted=ON')
print('D dirty:', d.execute('SELECT qty FROM sample').fetchone())
c.execute('ROLLBACK')
print('D after rollback:', d.execute('SELECT qty FROM sample').fetchone())

D dirty: (99,)
D after rollback: (10,)

演習完了