sqlite3 を使っていると遭遇するロック・エラー
sqlite3 を使っていると、トランザクションでデータベースがロックされて、書き込みに失敗することがある。php や python や ruby でもなんでも良いけど、トランザクションを使ってるときに、ロックが行われてdatabase is locked という無情なエラーになる。
これは、SQLite3のエラーであり、プロセス間でもロックすることがある。
たとえば、次のエラーはruby で再現したものである。
sqlite3/database.rb:281:in `step': database is locked (SQLite3::BusyException)
再現コードを作る
サクッとruby で再現したものである
mkdir lock-test cd lock-test ## bundle init bundle config set --local path 'vendor/bundle' bundle config set --local disable_shared_gems true bundle add sqlite3
ソースを書く lock-error-sample.rb
require 'sqlite3' require 'openssl' require "thread" DSN = 'lock-test.db' def create_table db = SQLite3::Database.new(DSN) ct = <<~SQL DROP TABLE IF EXISTS log; CREATE TABLE IF not Exists log ( id integer primary key autoincrement, name string unique ); INSERT INTO log ( name ) values( 'aaaa' ); SQL db.execute_batch(ct) end def insert_test_data ## sqlite3/database.rb:281:in `step': database is locked (SQLite3::BusyException) db = SQLite3::Database.new(DSN) db.transaction a = [OpenSSL::Random.random_bytes(12)].pack("m*").strip ct = <<~SQL INSERT INTO log ( name ) values( '#{a}' ); SQL db.execute_batch(ct) Thread.pass sleep 1 db.commit end create_table threads = [] threads << Thread.new{|t|insert_test_data } threads << Thread.new{|t|insert_test_data } # SQLite3::BusyExceptionになる。 threads.each{|t| t.join }
実行すると。
bundle exec ruby lock-error-sample.rb
エラーになる。
`step': cannot commit - no transaction is active (SQLite3::SQLException)
デッドロックが起きる。
スレッドを使って、Thread.passするタイミングでロックしたまま、別スレッドに実行を切り替えている。
db.transaction db.execute_batch(ct) sleep 1 # Thread.passが起きる db.commit
sleep 時には Thread.passが実行されるので、確実にロックしたまま別のトランザクションに入ることになる。別のトランザクションでもロックしたままなので双方がデッドロックする。これにより、双方でエラーが起きて実行が終了する。
ロックが開放されるのを待つ。
sqlite3 を使う場合は、ロック解放を待つことができる。
db.busy_timeout = 1000
初期値では０らしいので、ロック解放を待たずに、ロック取得に失敗したらイコール即エラーになる。
リトライを入れる。
エラーになる箇所をはロックしたままになる箇所なので。
db.transaction sleep 1.0 db.execute_batch(ct) sleep 1.0 db.commit
排他ロックが取れるまで、再起動すればいいと思われる。
db = SQLite3::Database.new(DSN) db.busy_timeout = 1000*3 db.transaction begin sleep 1.0 db.execute_batch(ct) sleep 1.0 rescue SQLite3::BusyException => e retry ensure db.commit end
ruby の場合は、retry という便利すぎるエラー処理があるので、処理は楽かもしれない。phpだとwhile でロック状態を認識して再起動しないと駄目だろうね。
解決策1
ロック待ちをしつつリトライもする。
db.busy_timeout = 1000
解決先２
そもそも、トランザクションを使わない。
実行順番が確実であるひつようがないのであれば（たとえばログなど）、念のためのトランザクションなど不要であり、SQLiteの実装に任せれば良いのである。とてもいい加減な解決と思うかもしれないが、案外良い解決方法です。
解決策３
EXCLUSIVE / IMMEDIATE / DEFERRED を変える。
トランザクションに引数を与えればロックレベルを変えられる。
db.transaction :immediate ... db.transaction :deffered ... db.transaction :exclusive ...
ただ、sqltie のトランザクションはEXCLUSIVEで別プロセスからもロックしたりできる。とあるが、sqlite3 sample.db を複数起動してもとくにロックされなかった。
(上下でユニーク違反になるに同一レコードを登録したが、どちらも正常にCommitできてしまった。）
細かいところを調べるのは時間がないので後回しにする。
余計なトランザクションを書かない。
SQLiteをつかう限りにおいてログなど単なる追記に、保険なトランザクションを書かないのが良いかもしれない。
