SQLiteのトランザクション

xguru · 2024-07-31T10:31:01+09:00

ここ数年、SQLiteはサーバープロセスのバックエンド用途において、強力なインプロセス高信頼SQLデータベースエンジンとして注目を集めている従来のクライアントまたはエッジアプリケーション向けという役割ではなく、このような用途でSQLiteが使われることをSQLite開発者たちがほとんど積極的に思いとどまらせているにもかかわらず、その人気は急上昇している私がSQLiteに関心を持つ主な理由概念的に単純: 主キーでパーティション化された行/タプルのB-treeを思い浮かべればよい。それをディスクに安定して永続化するために広範にテストされ、さらにSQLの対話レイヤーが追加されている Litestreamによって実用的なバックアップ戦略を構築できる。WALをリモートロケーションにバックアップし、継続的にレプリケーションできる。バックアップは単純なコマンドで起動時に自動復元できるオフラインで実行可能な完全な開発環境を今でも好んでいる file::memory: によりインメモリでの動作が可能で、必要に応じてテストコードがインスタンスを簡単に起動・終了できる Single-writer制限 SQLite開発者によって「サーバーにおけるSQLiteの制限」は十分に文書化されており、最良のサーバーサイド設定も分析されている。しかし目立つ制限は高トラフィックWebサイト、つまり書き込みの多いWebサイトである WALモードのSQLiteは設計上、単一のWriterを使用する。これにより、同時に最大1つの書き込みトランザクションと複数の読み取り専用トランザクションを許可するこの設計では、高トラフィックで書き込み集中型のWebサイトのボトルネックは、その単一Writerのスループット管理に置かれる。これは現代技術の中核的構成要素の1つへと再び立ち返ることになる SQLite SQLiteはデフォルトで厳格な SERIALIZABLE 分離トランザクションを提供する。これは分離保証の中で最も強いレベルである単一Writerを採用することで、SQLiteは悲観的同時実行制御の一形態を使い、書き込みトランザクションの実行中に基礎データが変更されていないことを容易に保証できる Postgres Postgresは実際にはSQL標準で定義された SERIALIZABLE のデフォルトとは異なり、より緩やかな READ COMMITTED を選んでいる（はるかに複雑な多版同時実行制御を備えているにもかかわらず）この厳密さの低下には、non-repeatable reads のリスクがある。つまり、同じトランザクション内であっても、別のCOMMITTEDトランザクションによってバックグラウンドで値が変更されると、同じ読み取りクエリを複数回実行した際に異なる結果を取得しうるこの分離レベルを選ぶことで、Postgresはトランザクションが古いデータに基づいて動作するリスクを開いたままにしている。開発者はこの点を念頭に置く必要がある SERIALIZABLE に設定すると、Postgresは楽観的同時実行制御の仕組みを用い、トランザクション中にアクセスしたデータを追跡し、コミット前に変更されていないことを確認する Postgresはメモリ使用量を管理するため、トランザクションに応じて行レベルまたはページレベルのロックに基づいてこれを実行するこのパターンが optimistic と呼ばれるのは、トランザクションがコミットされる時点で、そのトランザクションが監視しているデータがより細かい粒度であるほど変更される可能性が低く、基礎データは変更されていないと期待するためである FoundationDB トランザクションはリレーショナルデータベースに限られない。分散キーバリューストアでも SERIALIZABLE 保証を達成するために楽観的同時実行制御が使われる NoSQLが登場した当時、ACID保証を備えた分散NoSQLストアは一般的ではなかった。FoundationDBはトランザクション・マニフェストを作成し、開発者がACID保証から大きな恩恵を受けられることを強調した FoundationDBは、楽観的同時実行制御向けのコードをどのように書くか、また同時トランザクションの競合によってデータが変更され、トランザクションが自動的に再試行されることがある、という点について助言を提供している Idempotence 冪等トランザクションとは、1回コミットしても2回コミットしても同じ効果を持つトランザクションのこと FoundationDBは、競合によってトランザクションを複数回再試行しなければならない場合に問題を防ぐため、トランザクションを冪等にするパターンを提供しているでは、これらすべてを踏まえると、SQLiteはどのような選択肢を提供しているのだろうか? BEGIN … SQLiteは、開発者がトランザクションの動作方法をエンジンに示すための複数の手段を提供しており、WALモードではIMMEDIATE、EXCLUSIVE、DEFERREDというキーワードの形で、実質的にはDEFERREDとIMMEDIATEへと集約される DEFERRED トランザクションは、他の読み取りまたは書き込みトランザクションと同時に実行できるREADモードで開始される DB状態を変更するクエリ（INSERT、UPDATE、DELETE）が実行された場合にのみ、ブロッキングするREAD-WRITEトランザクションへアップグレードされるアップグレード時にDBが他のトランザクションによってロックされている場合、SQLITE_BUSY エラーが返る。クライアント側でこれを処理する必要がある IMMEDIATE トランザクションはただちにREAD-WRITEモードで開始されるすでに書き込みトランザクションが実行中であれば、即座に SQLITE_BUSY が返るどう処理するかはクライアントが決定しなければならない CONCURRENT SQLiteには、トランザクションを悲観的方式から限定的な楽観的方式へ移す実験的な分岐がある限定的といえるのは、楽観的ロックが行/タプル単位ではなくDBページ単位（デフォルト4096 bytes）で動作するためである CONCURRENTモードでは、SQLiteは複数の書き込みトランザクションを同時に有効化できるが、コミット前に、トランザクション実行中にアクセスしたページがトランザクション開始以降変更されていないことを確認する競合が発生しなければ変更は順次コミットされ、厳格な SERIALIZABLE 保証を達成する。競合が発生した場合は SQLITE_BUSY が返る HC-Tree SQLiteのもう1つの実験的ブランチが [HC-Tree] であり、楽観的な行/タプルレベルロックの提供を目指す進行中の作業である。興味深い成果の1つとして、そのような設計の性能上の利点を BEGIN CONCURRENT ブランチと比較して示す優れたベンチマーク群が提供されているそれなら、彼らのベンチマーク手法を取り入れて、標準オプションに対して実行してみてはどうだろうか? ベンチマーク nUpdate=1, nScan=0 この書き込み専用トランザクションは、IMMEDIATEとDEFERREDの利点の違いを明確に示している。ロックは即座に発生し、トランザクションはアップグレードコストの影響を受けない CONCURRENTは、スレッド数が増え競合が増加するにつれても、スループットの向上を示す nUpdate=10, nScan=0 予想どおり、writeバッチ処理は16スレッド時に更新行数の面で大きく効いている。CONCURRENTはおよそ ~12k/sec から ~19k/sec に増加する IMMEDIATEとDEFERREDの違いはそれほど重要ではなくなる。トランザクションのアップグレードコストよりも更新そのもののコストのほうが重要になるためだ nUpdate=1, nScan=10 このトランザクションは、random read によってページレベルのCONCURRENTロックの弱点を露呈させるはずである更新を行うトランザクションでIMMEDIATEを使うことが、DEFERREDのアップグレードコストより重要である理由をすぐに示している CONCURRENTについては、基礎的な競合が実際には大きく増えていなかったため、これらの結果は非常に堅牢である nUpdate=0, nScan=10 この読み取り専用のバッチトランザクションは、悲観的同時実行制御の影響を示しているすべてのトランザクションでIMMEDIATEをデフォルトにすべきではない理由を示している CONCURRENTとIMMEDIATEの比較は、CONCURRENTモード使用時にわずかな不利があることを示している。「すべてのケースで少しだけ性能が低下する」それでもCONCURRENTは良いデフォルトオプションになりそうだ

(reorchestrate.com)

15 ポイント投稿者 xguru 2024-07-31 | まだコメントはありません。 | WhatsAppで共有

ここ数年、SQLiteはサーバープロセスのバックエンド用途において、強力なインプロセス高信頼SQLデータベースエンジンとして注目を集めている
従来のクライアントまたはエッジアプリケーション向けという役割ではなく、このような用途でSQLiteが使われることをSQLite開発者たちがほとんど積極的に思いとどまらせているにもかかわらず、その人気は急上昇している

私がSQLiteに関心を持つ主な理由

概念的に単純: 主キーでパーティション化された行/タプルのB-treeを思い浮かべればよい。それをディスクに安定して永続化するために広範にテストされ、さらにSQLの対話レイヤーが追加されている
Litestreamによって実用的なバックアップ戦略を構築できる。WALをリモートロケーションにバックアップし、継続的にレプリケーションできる。バックアップは単純なコマンドで起動時に自動復元できる
オフラインで実行可能な完全な開発環境を今でも好んでいる
file::memory: によりインメモリでの動作が可能で、必要に応じてテストコードがインスタンスを簡単に起動・終了できる

Single-writer制限

SQLite開発者によって「サーバーにおけるSQLiteの制限」は十分に文書化されており、最良のサーバーサイド設定も分析されている。しかし目立つ制限は高トラフィックWebサイト、つまり書き込みの多いWebサイトである
WALモードのSQLiteは設計上、単一のWriterを使用する。これにより、同時に最大1つの書き込みトランザクションと複数の読み取り専用トランザクションを許可する
この設計では、高トラフィックで書き込み集中型のWebサイトのボトルネックは、その単一Writerのスループット管理に置かれる。これは現代技術の中核的構成要素の1つへと再び立ち返ることになる

SQLite

SQLiteはデフォルトで厳格な SERIALIZABLE 分離トランザクションを提供する。これは分離保証の中で最も強いレベルである
単一Writerを採用することで、SQLiteは悲観的同時実行制御の一形態を使い、書き込みトランザクションの実行中に基礎データが変更されていないことを容易に保証できる

Postgres

Postgresは実際にはSQL標準で定義された SERIALIZABLE のデフォルトとは異なり、より緩やかな READ COMMITTED を選んでいる（はるかに複雑な多版同時実行制御を備えているにもかかわらず）
- この厳密さの低下には、non-repeatable reads のリスクがある。つまり、同じトランザクション内であっても、別のCOMMITTEDトランザクションによってバックグラウンドで値が変更されると、同じ読み取りクエリを複数回実行した際に異なる結果を取得しうる
- この分離レベルを選ぶことで、Postgresはトランザクションが古いデータに基づいて動作するリスクを開いたままにしている。開発者はこの点を念頭に置く必要がある
SERIALIZABLE に設定すると、Postgresは楽観的同時実行制御の仕組みを用い、トランザクション中にアクセスしたデータを追跡し、コミット前に変更されていないことを確認する
- Postgresはメモリ使用量を管理するため、トランザクションに応じて行レベルまたはページレベルのロックに基づいてこれを実行する
- このパターンが optimistic と呼ばれるのは、トランザクションがコミットされる時点で、そのトランザクションが監視しているデータがより細かい粒度であるほど変更される可能性が低く、基礎データは変更されていないと期待するためである

FoundationDB

トランザクションはリレーショナルデータベースに限られない。分散キーバリューストアでも SERIALIZABLE 保証を達成するために楽観的同時実行制御が使われる
NoSQLが登場した当時、ACID保証を備えた分散NoSQLストアは一般的ではなかった。FoundationDBはトランザクション・マニフェストを作成し、開発者がACID保証から大きな恩恵を受けられることを強調した
FoundationDBは、楽観的同時実行制御向けのコードをどのように書くか、また同時トランザクションの競合によってデータが変更され、トランザクションが自動的に再試行されることがある、という点について助言を提供している

Idempotence

冪等トランザクションとは、1回コミットしても2回コミットしても同じ効果を持つトランザクションのこと
FoundationDBは、競合によってトランザクションを複数回再試行しなければならない場合に問題を防ぐため、トランザクションを冪等にするパターンを提供している

では、これらすべてを踏まえると、SQLiteはどのような選択肢を提供しているのだろうか?

BEGIN …

SQLiteは、開発者がトランザクションの動作方法をエンジンに示すための複数の手段を提供しており、WALモードではIMMEDIATE、EXCLUSIVE、DEFERREDというキーワードの形で、実質的にはDEFERREDとIMMEDIATEへと集約される

DEFERRED

トランザクションは、他の読み取りまたは書き込みトランザクションと同時に実行できるREADモードで開始される
DB状態を変更するクエリ（INSERT、UPDATE、DELETE）が実行された場合にのみ、ブロッキングするREAD-WRITEトランザクションへアップグレードされる
アップグレード時にDBが他のトランザクションによってロックされている場合、SQLITE_BUSY エラーが返る。クライアント側でこれを処理する必要がある

IMMEDIATE

トランザクションはただちにREAD-WRITEモードで開始される
すでに書き込みトランザクションが実行中であれば、即座に SQLITE_BUSY が返る
どう処理するかはクライアントが決定しなければならない

CONCURRENT

SQLiteには、トランザクションを悲観的方式から限定的な楽観的方式へ移す実験的な分岐がある
限定的といえるのは、楽観的ロックが行/タプル単位ではなくDBページ単位（デフォルト4096 bytes）で動作するためである
CONCURRENTモードでは、SQLiteは複数の書き込みトランザクションを同時に有効化できるが、コミット前に、トランザクション実行中にアクセスしたページがトランザクション開始以降変更されていないことを確認する
競合が発生しなければ変更は順次コミットされ、厳格な SERIALIZABLE 保証を達成する。競合が発生した場合は SQLITE_BUSY が返る

HC-Tree

SQLiteのもう1つの実験的ブランチが [HC-Tree] であり、楽観的な行/タプルレベルロックの提供を目指す進行中の作業である。興味深い成果の1つとして、そのような設計の性能上の利点を BEGIN CONCURRENT ブランチと比較して示す優れたベンチマーク群が提供されている

それなら、彼らのベンチマーク手法を取り入れて、標準オプションに対して実行してみてはどうだろうか?

ベンチマーク

nUpdate=1, nScan=0

この書き込み専用トランザクションは、IMMEDIATEとDEFERREDの利点の違いを明確に示している。ロックは即座に発生し、トランザクションはアップグレードコストの影響を受けない
CONCURRENTは、スレッド数が増え競合が増加するにつれても、スループットの向上を示す

nUpdate=10, nScan=0

予想どおり、writeバッチ処理は16スレッド時に更新行数の面で大きく効いている。CONCURRENTはおよそ ~12k/sec から ~19k/sec に増加する
IMMEDIATEとDEFERREDの違いはそれほど重要ではなくなる。トランザクションのアップグレードコストよりも更新そのもののコストのほうが重要になるためだ

nUpdate=1, nScan=10

このトランザクションは、random read によってページレベルのCONCURRENTロックの弱点を露呈させるはずである
更新を行うトランザクションでIMMEDIATEを使うことが、DEFERREDのアップグレードコストより重要である理由をすぐに示している
CONCURRENTについては、基礎的な競合が実際には大きく増えていなかったため、これらの結果は非常に堅牢である

nUpdate=0, nScan=10

この読み取り専用のバッチトランザクションは、悲観的同時実行制御の影響を示している
すべてのトランザクションでIMMEDIATEをデフォルトにすべきではない理由を示している
CONCURRENTとIMMEDIATEの比較は、CONCURRENTモード使用時にわずかな不利があることを示している。「すべてのケースで少しだけ性能が低下する」
- それでもCONCURRENTは良いデフォルトオプションになりそうだ