SQLiteで複数のDBを扱う（ATTACH)

SQLiteで複数のDBを使う要件出てきたから自分なりにまとめてみた。

正確なことは、オフィシャル(Atomic Commit In SQLite)の”5. Multi-file Commit”を読んでおかないとなと思ってる

ATTACHとは

現在のデータベースファイルの接続に、別のデータベースファイルを接続する機能。

複数のDBファイルをATTACHして、複数のデータベースファイルを1つであるかのように使うものという認識である。

SQLiteを使用する開発者はおそらく、用途や同時アクセス性、ファイルサイズ等、色々と検討して複数のデータベースファイルにテーブルを構築していくと思う。

別のデータベースファイルに作成したテーブルを結合するためにATTACHは使用すると、使用する例によく書かれている。

ATTACHした複数のデータベースに対して1つのトランザクションで扱えることをもっと広めてほしい。下の図のように、2つのデータベースファイル（DB AとDB B)の操作を1つのトランザクションで行える。

これの何が良いかって、 DB Aの登録結果の内容をDB Bに登録するシステムのとき、DB Bへの登録が失敗したらROLLBACKすれば、DB Aへの登録も無かったことになるので原子性(Atomic)が保たれる。

ATTACHせずにしようとすると、 DB AをCOMMIT後にDB Bが失敗したらDB Aの登録結果がDB Bに登録されないタイミングが発生してしまう。

アプリをスタンドアローンで動かすならATTACHでは何も気にしないでいいんだけど、同時アクセスが発生する場合、いろいろと気を付けなければいけない様子。

以下、ジャーナルモードで実施した動作を記載する。WALモードだと動作が異なる。

あと、ATTACH先のデータベースファイルが排他（EXCLUSIVE）ロック取得中はATTACHできない。

上記の図のように、ATTACHしたプロセスがDB Bに対して何も処理を行っていない時、別プロセスからのDML、DCL操作を受け付けてしまう。

トランザクションが実行されているからDB Bに対して他のアクセスをブロックできるわけではないようだ。以下のパターンでも同様だが、ATTACHしてもBEGINではATTACH先のDB Bにはロックを取得せず、DML実行時にロックを取得する様子。

上記の図のように、DB BにATTACHしているプロセスが、DB BにSELECTなどのSQLを実行するとSELECTなら読み込み（SHARED)ロック、INSERTなどは書き込み（IMMEDIATE)ロックを取得する。接続元（DB A）と同じロック粒度を取得しないようだ。