SQLite を使ってみる

SQLite のロック周りの説明です (2014/5/22 新規作成)。

SQLite / 挙動 / 自動 / 暗黙処理 / オートコミット / 分離レベル / ロック解消 / 戻る / トップページ

SQLite
SQLite は、 アプリケーションに組み込んで使える軽量なデータベースです。 SQLite についての解説は他にたくさん有用なサイトがあるので、 ここでは説明は省略させて頂きます。
　
ここではロック関係について説明を書きます。 私が SQLite を使っている時に "database is locked" エラーにぶち当たり、 その解説のあるいくつものサイトを見ても分かりにくかったり、 どうも実際の挙動と合致しなくて困っていました。 やむなく仕様書を読んだり大量のテストコードを書いて調べたところ、 どうやら正しくない解釈で書かれている解説が多くあるようだと分かりました。 そこで、私のように困る人が減ることを願って、説明を残しておこうと思います。
　
仕様書と実際の挙動を元に理解して、実際にその理解に沿って 問題解決できているため、おそらく正しく理解できていると思いますが、 万が一間違いがあった場合は修正したいのでご指摘頂ければ幸いです。 この説明は SQLite バージョン 3 系列に基づいて書かれています。 サンプルコードは Python で書いていますが、単純なことしか書いていないので、 他の言語利用者でも分かると思います。

SQLite の挙動
まずは厳密なロック処理から入るよりも、SQLite がデータベース (=ファイル) をどのように処理しているのかを知ると取っつきやすいので、まずはそこから。
　
SQLite ではデータベースを 1 つのファイルで表現しています。 データベースの操作はこのファイルを読み書きすることで実現されています。 ここではそのファイルを database.sqlite3 として説明します。
　
データベースに読み込み系の処理 (SELECT 文など) が行われると、 database.sqlite3 から読み込みが行われます。当然ですね。 ファイルの読み込み中に、別のプロセスもそのファイルを読み込むことは 普通出来るので、データベースの読み込み系の処理中に 別プロセスも同データベースに読み込み系の処理はできます。
　
データベースに書き込み系の処理 (UPDATE 文など) が行われると、 データベースのファイル名の末尾に "-journal" が付いた ジャーナルファイル database.sqlite3-journal が生成され、そちらに 必要な情報が書き込まれます。 database.sqlite3 は別ファイルであり、何もされていないので、 データベースの書き込み系の処理中に 別プロセスも同データベースに読み込み系の処理はできます。 ジャーナルファイルは一つしか作れず、生成したプロセスからしか触れないので、 データベースの書き込み系の処理中に 別プロセスが同データベースに書き込み系の処理はできません。
　
コミットが実行されると、ジャーナルファイル database.sqlite3-journal の内容がデータベースファイル database.sqlite3 に統合されて、 database.sqlite3-journal は削除されます。 この間、両ファイルは書き込み処理がされている最中なので、 別プロセスは同データベースに何もできません。 またその開始のために、database.sqlite3 を読み込み中のプロセスが ある場合はその完了を待つ必要があります (待っている間にさらに 他のプロセスの読み込み開始は止めています)。
　
つまり、読み込み系の処理中は別プロセスはコミットできず、 書き込み系の処理の開始からコミット完了までの間は、 別プロセスは書き込みできず (書き込みがロックされている)、 コミット実行中は、別プロセスは読み込み・書き込みができません (読み書きがロックされている)。
あるプロセスがこのできない処理にぶち当たると一定時間 (5秒など、変更可能) 状況が改善されるのを待って、それでもダメな時にロックのエラーである "database is locked" エラーが出されるのです。 データベースが 1 ファイルで表現されているので、 違うテーブルや違う行に対しての読み書き同士であっても この制限に引っかかります。

暗黙のトランザクション処理とオートコミット
このように、ロックがかかっている期間を把握するには 書き込み系の処理の開始やコミットのタイミングを見れば良いことが分かります。 一般的なデータベースシステムで SQL で書き込みするには トランザクションの開始やコミットを明示する必要があるため、 それらがロックのタイミングとなって分かりやすいのですが、 SQLite ではデータベースを手軽に扱えるように、 いちいちトランザクション開始やコミットを書かなくても 自動でしてくれる仕組みがあり、 それに頼っているとロックのタイミングを見誤りやすくなります。
　
そこで次はトランザクション開始とコミットがどのタイミングで 発行されるのかを理解しましょう。 ここで大事なのは、暗黙の内に発行されるトランザクション処理と、 オートコミット機能は別々の概念だということです。 これを混同して理解を間違う方が多いようなので、 ご注意下さい (私もここで混乱しました)。

暗黙のトランザクション開始と暗黙のコミット
SQLite では SQL 文 INSERT, UPDATE, DELETE, REPLACE の実行の直前に 暗黙の内に (明示しなくても自動的に) トランザクションを開始させます。
また、INSERT, UPDATE, DELETE, REPLACE, SELECT 以外の SQL 文 (CREATE TABLE など) の実行の直前に暗黙の内にコミットを実行します。 
	import sqlite3
	
	database=sqlite3.connect("test.sqlite3")
	database.execute("create table sample (id numeric)")
	database.commit()
	
	time.sleep(5.0)
	
	# ここで暗黙の begin transaction
	database.execute("insert into sample values (?)",(100,))		# insert
	
	time.sleep(5.0)
	
	# ここで暗黙の commit
	database.execute("vacuum")						# vacuum
	
	time.sleep(5.0)
	
	print(database.execute("select count(*) from sample").fetchone()[0])	# select
BEGIN, COMMIT コマンドを明示的に呼んだ場合は当然そちらが優先されます。 他のデータベースシステムでは、トランザクション開始やコミットが必要なのに 呼び忘れているとエラーが出るような状況で、 SQLite は勝手に呼んでくれると考えると良いでしょう。
　
非常にシンプルな状況で SQLite を使う場合はこの機能に頼っても構いませんが、 そうでない場合はこの機能に頼らずに自分で明示的に BEGIN, COMMIT コマンドを 書くことをお勧めします。 なお、自分で明示的に使っている時も、暗黙のコミットによって 意図しないタイミングでコミットが発行される可能性がある点は注意です。

オートコミット
データベース接続時にオートコミットを有効にする指定をすると、 SQLite はオートコミットモードで動作します。
オートコミットモード中は書き込み系の処理 (UPDATE 文など) の直前に トランザクション開始、直後にコミットを暗黙の内に実行します。 
	import sqlite3
	
	database=sqlite3.connect("test.sqlite3",isolation_level=None)		# オートコミットを有効に指定
	database.execute("create table sample (id numeric)")
	database.commit()
	
	time.sleep(5.0)
	
	# ここで暗黙の begin transaction
	database.execute("insert into sample values (?)",(100,))		# insert
	# ここで暗黙の commit
	
	time.sleep(5.0)
	
	database.execute("vacuum")						# vacuum
	
	time.sleep(5.0)
	
	print(database.execute("select count(*) from sample").fetchone()[0])	# select
トランザクションもコミットも意識することなく、 データベースに読み書きできるので便利ですが、一貫性を保ったり 処理を巻き戻しできるトランザクションのメリットは享受できません。
　
オートコミットモード中に、BEGIN コマンドを明示的に呼んだ場合は、 COMMIT コマンドを明示的に呼ぶまで、オートコミット処理は一時停止されます。 この一時停止中、暗黙のコミットは働きません (BEGIN〜COMMIT の間に INSERT, UPDATE, DELETE, REPLACE, SELECT 以外の SQL 文を挟むとエラー) ので、 オートコミットを有効にした接続では 暗黙のトランザクション処理は働かないと理解して良いでしょう。
　
大量のデータを書き込む時に、オートコミットモードだとその件数回だけ トランザクションの開始やコミットが発生し、ジャーナルファイルの生成と削除が 繰り返されるため、トランザクションでくくって一括して処理する場合に比べて 処理が大幅に遅くなります。 しかし、個々のジャーナルファイルの存在期間とサイズはコンパクトになるため、 他のプロセスがロック解除待ちをしている間に ロックが解除される可能性が高くなり、結果的に "database is locked" エラーが 起こりにくくなります。 オートコミットにするとロック問題が起こりにくくなるのはこのためです。

トランザクション分離レベル
始めにジャーナルファイルの挙動でロックのされ方を説明しましたが、 厳密にはトランザクションの種類 (分離レベル=isolation level) によって違いがあります。 その違いを詳しく見てみましょう。
　
トランザクション分離レベル
分離レベル デフォルトに設定されているバージョン 概要
DEFERRED 3.0.8〜 トランザクションの開始ではなく、始めの SQL 文実行時にロックをかける
IMMEDIATE 3.0.0〜3.0.7 トランザクション開始時にロックをかける
EXCLUSIVE 〜2.8 トランザクション開始時に排他ロックをかける
　
データベース接続時にどの分離レベルを使うか指定しますが、指定しない場合は SQLite のバージョンごとに決められた分離レベルが使用されます。 デフォルト値が EXCLUSIVE→IMMEDIATE→DEFERRED と変更されてきているように、 この順番にだんだんとロックがかち合う可能性が減っています。 特に理由がなければ最新のデフォルトの DEFERRED を指定するのが良いでしょう。 なお、トランザクション開始時にその回の分離レベルを何にするか 指定することもできます。
　
最も古い EXCLUSIVE はトランザクション開始からコミット完了までの間、 ジャーナルファイルを作らずにデータベースファイルを直接触ります。 このため、その間は他のプロセスは読み込みもできません。
　
次に新しい IMMEDIATE は、トランザクション開始時に ジャーナルファイルを作ります。 始めに説明したように、書き込み系の処理の開始からコミット完了までの間は、 別プロセスは書き込みできず、 コミット実行中は、別プロセスは読み込み・書き込みができません。
　
最新の DEFERRED はトランザクション開始時には何もせず、始めの SQL 文が 実行されるタイミングでジャーナルファイルを作ります。 SQL 文の直前に BEGIN 文を発行していたり、暗黙のトランザクション開始が された場合は IMMEDIATE と同じになりますが、そうでない場合は ジャーナルファイルの存在期間が減るため、ロックがかち合いにくくなります。 
	import sqlite3
	
	# 変数 level に "EXCLUSIVE", "IMMEDIATE", "DEFERRED" のいずれかを設定しておく
	database=sqlite3.connect("test.sqlite3",isolation_level=level)	# 分離レベルを level に設定
	database.execute("create table sample (id numeric)")
	database.commit()
	
	time.sleep(5.0)
	
	database.execute("begin transaction")				# begin transaction (A)
	
	time.sleep(5.0)
	
	database.execute("insert into sample values (?)",(100,))	# insert (B)
	
	time.sleep(5.0)
	
	database.commit()						# commit (C)
	
	time.sleep(5.0)
	
	if level=="EXCLUSIVE":
		print(u"(A)〜(C) の間は他のプロセスは読み書き不可")
	elif level=="IMMEDIATE":
		print(u"(A)〜(C) の間は他のプロセスは書き込み不可")
	elif level=="DEFERRED":
		print(u"(B)〜(C) の間は他のプロセスは書き込み不可")
	
	print(u"(C) の実行開始〜終了の間は他のプロセスは読み書き不可")

ロック問題の解消方法
ここまで読んで頂ければ、ロックによって引き起こされる "database is locked" エラーをどのように解消していくか およそ想像がつくと思います。
　
まず、"database is locked" エラーが出力されている SQL 文を見て、 書き込み系しかないならば他のプロセスが限界時間以上 ジャーナルファイルを操作していることが分かります。 読み書き両方で出ているならば、他のプロセスが コミット (ジャーナルファイルとデータベースファイルの統合) に 限界以上の時間がかかっています。 コミット時にエラーが出ているなら、他のプロセスの読み込みに 限界以上の時間が掛かっています。
　
単純な解決方法としてはロック解消待ちの限界時間を延ばしたり、 CPU を早くしたり、データベース保存先を SSD に変えたりすれば おそらくひとまずの解消はできるでしょう。 よりまっとうな対処としては、他のプロセスの時間が掛かっている、 トランザクション開始〜コミット完了や読み込みの処理時間を 縮めれば良いことになります。 何とかしてその原因箇所を見付けたら、高速化を試みます。
　
トランザクション開始〜コミット完了の範囲を狭めるなら、 まずその接続のトランザクション分離レベルを確認し、 (特別な意図があれば別ですが、そうでなければ) DEFERRED に変更を。 次にトランザクション分離レベルごとに範囲が変わりますが、 ロックがかかっている (ジャーナルファイルの存在している) 間に データベース操作以外の重い処理があればそれをトランザクションの 外へ動かすと良いでしょう。 それでもダメなら保つべき一貫性を崩さない範囲で トランザクションを複数に分割できないか検討を。
　
読み込みの処理時間を狭めるなら、 まずは LIMIT 句の追加や、インデックスが効くように SQL 文を 改良することで高速化を図りましょう。 次に複数件のクエリー結果を取り出す間に データベース操作以外の重い処理があればそれを外へ動かすと良いでしょう。 それでもダメなら保つべき一貫性を崩さない範囲で クエリーを分けられないか検討を。
　
処理時間を短縮することがそれ以上できず、 ロックのエラーを発する SQL と時間のかかっている箇所の SQL が 違うテーブルを触っているようなら、 テーブルごとにデータベースを分けるのも良いでしょう。 そこまでやってもロックが多発するのであれば、SQLite で扱える並列性の限界を 超えているかもしれませんので、他のデータベースシステムへの変更を 検討すべきかも知れません。

戻る