Hello, While working on Rails application with SQLite3 gem and pool size larger than 1 I noticed that timeout parameter in database.yml does not have expected effect. Even when the value is increased to something large the process is still raising "database is locked (SQLite3::BusyException)
Here is an example script without ActiveRecord: require 'bundler/inline' require "thread" require "tempfile" gemfile do source 'https://rubygems.org' gem 'sqlite3', '1.3.13' gem 'pry' end puts 'Gems installed and loaded!' puts "The SQLite3 version #{SQLite3::SQLITE_VERSION}" begin database_path = Tempfile.open("testdb") db = SQLite3::Database.new(database_path.path) db.transaction do db.execute("CREATE TABLE `test_1` (connection_role VARCHAR (255))") db.execute("INSERT INTO test_1 VALUES ('main-1')") end thread = Thread.new do begin thread_db = SQLite3::Database.new(database_path.path) thread_db.transaction(:immediate) do puts "puts:bg:1 #{thread_db.execute("SELECT * FROM test_1")}" thread_db.execute("INSERT INTO test_1 VALUES ('bg-1')") puts "puts:bg:2 #{thread_db.execute("SELECT * FROM test_1")}" sleep 2 thread_db.execute("INSERT INTO test_1 VALUES ('bg-2')") puts "puts:bg:3 #{thread_db.execute("SELECT * FROM test_1")}" end puts "puts:bg:4 transaction closed" ensure thread_db.close if thread_db end end puts "puts:main:1 #{db.execute("SELECT * FROM test_1")}" sleep 1 puts "puts:main:2 #{db.execute("SELECT * FROM test_1")}" db.busy_timeout 3000 # <-- raises: .rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:108:in `step': database is locked (SQLite3::BusyException) # busy_handler works as expected # db.busy_handler do # true # end puts "puts:main:3 #{db.execute("SELECT * FROM test_1")}" db.execute("INSERT INTO test_1 VALUES ('main-2')") puts "puts:main:4 #{db.execute("SELECT * FROM test_1")}" thread.join ensure database_path.close database_path.unlink end It outputs: Gems installed and loaded! The SQLite3 version 3.22.0 puts:main:1 [["main-1"]] puts:bg:1 [["main-1"]] puts:bg:2 [["main-1"], ["bg-1"]] puts:main:2 [["main-1"]] puts:main:3 [["main-1"]] puts:bg:3 [["main-1"], ["bg-1"], ["bg-2"]] puts:bg:4 transaction closed Traceback (most recent call last): 8: from sqlite3_busy_timeout.rb:54:in `<main>' 7: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:137:in `execute' 6: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:95:in `prepare' 5: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:156:in `block in execute' 4: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:156:in `to_a' 3: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:107:in `each' 2: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:107:in `loop' 1: from /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:108:in `block in each' /home/krists/.rbenv/versions/2.6.1/lib/ruby/gems/2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:108:in `step': database is locked (SQLite3::BusyException) If you comment out "db.busy_timeout 3000" and uncomment "db.busy_handler do.." it waits as expected and does not raise SQLite3::BusyException. Is this a bug or I haven't understand how to use these methods? Thanks, Krists -- You received this message because you are subscribed to the Google Groups "sqlite3-ruby" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
