Clarification on SQLite3::BusyException

[forthrin]

Referring to #261, etc. is SQLite3::BusyException warranted and unavoidable, even if hitting up the database from two different processes, as opposed to two threads in the same process?

sqlite> create table foo (bar);

irb> db = SQLite3::Database.open('db')                 # adding "busy_timeout: 1000" does not help
irb> loop { db.query('insert into foo values (1)') }   # run from one shell
irb> loop { db.query("select * from foo") }            # run from another shell
database is locked (SQLite3::BusyException)

Is there no way around everyone having to write their own busy handler?

# Foo
module Foo
  refine SQLite3::Database do
    def query(query, *values)
      super
    rescue SQLite3::BusyException => e
      warn e.message
      sleep 1
      retry
    end
  end
end

using Foo

Or use Write-Ahead Logging?

sqlite> PRAGMA journal_mode=WAL;

[flavorjones]

There are a few built-in options for busy handlers

• https://www.rubydoc.info/gems/sqlite3/SQLite3/Database#busy_handler-instance_method
  • allows you to implement your own handler
• https://www.rubydoc.info/gems/sqlite3/SQLite3/Database#busy_handler_timeout=-instance_method
  • tells sqlite3 to use its internal busy handler (which won't release the GVL)
• https://www.rubydoc.info/gems/sqlite3/SQLite3/Database#busy_timeout=-instance_method
  • use a busy handler that releases Ruby's GVL

@fractaledmind has written a few blog posts on this topic, e.g. https://fractaledmind.github.io/2024/01/11/sqlite-in-ruby-non-blocking-timeout/ if that's interesting.

[forthrin]

@flavorjones: Thanks for reaching out and pointing to available options. Some followup-questions:

1. All three approaches you suggest still throw an exception in one process or the other. User error?

SQLite3::Database.open('db', busy_handler: lambda { |e| puts e; sleep 1; true })
SQLite3::Database.open('db', busy_timeout: 1000)
SQLite3::Database.open('db', busy_handler_timeout: 1000)

2. Assuming it was possible to make a queue -- so that when request B happens when request A has locked the db, request B would run immediately after A was finished -- this would be a built-in feature, which it evidently is not. What is the underlying reason?

3. When the quoted article says "no other Ruby code can run while SQLite is running the busy_timeout function", does that mean Ruby code in the same thread, process or OS?

[forthrin]

@flavorjones: Still need a hand here. Puzzled why running two simple connections to the same db breaks so easily.

[flavorjones]

I can't reproduce what you're describing in your OP. I'm using sqlite3 v2.0.2 (precompiled x86_64-linux-gnu) on linux and ruby 3.3.3 (2024-06-12 revision f1c7b6f435) [x86_64-linux]

[forthrin]

Thanks for confirming. Will try to pinpoint the issue.

[forthrin]

@flavorjones: Can you reproduce this? (I'm on macOS 14.5)

$ sqlite3 hello
sqlite> create table foo (bar);
sqlite> insert into foo values (1);

irb(main):001> db = SQLite3::Database::open('hello', busy_timeout: 1000)
irb(main):002> db.query('select * from foo where bar = ?', [1]).any?
=> true

Another shell:

irb(main):001> db = SQLite3::Database::open('hello', busy_timeout: 1000)
irb(main):002> db.query('insert into foo values (?)', [1])
/opt/homebrew/lib/ruby/gems/3.3.0/gems/sqlite3-2.0.2-arm64-darwin/lib/sqlite3/statement.rb:84:in `step': database is locked (SQLite3::BusyException)

[flavorjones]

Yes, I can reproduce that. Setting journal mode to WAL will allow you to have reads concurrent with writes.

See @fractaledmind's excellent blog post on configuring SQLite3 for concurrent access here: https://fractaledmind.github.io/2023/09/07/enhancing-rails-sqlite-fine-tuning/

And the SQLite docs themselves: https://www.sqlite.org/pragma.html#pragma_locking_mode

[forthrin]

OK, this is actually quite simple. Assuming what happens:

1. Database gets “select”, but does not emit any actual data until client iterates.
2. Database gets an “insert”.
3. Database objects, because the “insert” might affect the “select” data.
4. Database does no assessment of actual collision, eg. if “insert” writes to a different table than “select” reads, neither must wait.

Now, collision does not occur if removing .any? from the example, so collision seems to happen because database assumes iteration is imminent -- which makes sense.

Questions:

1. Is there a way to check for row existence without calling .any?? Or to explicitly tell the database “I’m done reading, you may let other clients go about their business”.
2. Is there any other way to achieve concurrency without littering the file system with .wal and .shm files?

[flavorjones]

Look, I'm not an expert in using sqlite3, I'm just the person who took over maintenance of this project rather late in its life. You've got a few options I can think of:

• find a user forum with more experienced sqlite3 users who can give you well-grounded advice
• use a higher-level abstraction like ActiveRecord that will provide well-grounded opinions on the sqlite3 configuration

Please understand that when I refer you to work @fractaledmind and @oldmoe are doing it's not because I'm trying to be difficult, it's because they are the experts and have written a ton of posts and code on using this library and sqlite3 already.

[forthrin]

@flavorjones: I really appreciate this clarification. You're right. One assumes a project maintainer is God and knows everything. I'll take this incident as a reminder that this might -- and should necessarily -- not be the case.

[forthrin]

@fractaledmind: Could you reply to the posting above ("OK, this is actually quite simple...")

[forthrin]

@flavorjones + @fractaledmind + @oldmoe:

OK, the solution is actually quite obvious. If a ResultSet is not going to be iterated, it must be closed, lest the database is locked forever waiting for iteration that never comes. If one never does iteration after .any?, a generalized solution may be as follows:

  refine SQLite3::ResultSet do
    def any?
      super.tap(&:close)
    end
  end

Could the API be changed or extended in accordance?

Note: Updated code example to return the object as expected

[flavorjones]

Documentation for Enumerable#any? says in part

With no argument and no block, returns whether any element is truthy

so I think it makes sense to address what you're seeing by closing the resultset under that condition.

[flavorjones]

I think we probably should try to close the resultset once we've read results from the database (and not particularly in the #any? method). I'll explore.

[flavorjones]

Yeah, this is tricky, because the thing that you want to close in your example is the Statement, but we're calling #any? on ResultSet. We don't want to break code like this:

db = SQLite3::Database.new(":memory:")
db.execute('create table "foo" ("a" string, "b" int)')

stmt = db.prepare("select * from foo where a = ?")
unless stmt.execute("bar").any? # if we close the statement here ...
  stmt.execute("baz") # ... then this errors in `reset!': cannot use a closed statement
end

[forthrin]

Good point. How about the following approach:

Introduce a way the clients to differentiate between "we want to get row data" and "we want no data, just a true/false boolean indicating row existence".

In the latter case, locking would be unnecessary, as no transmission of possibly-dubious data from server to client occurs.