Script hangs with busy_timeout set for conflicting transactions

[fractaledmind]

While this is certainly an edge-case in that you can only get in this state by not using the gem's provided #transaction method, which requires a block, it feels to me like a problem worth fixing nonetheless.

If you create a script like this, which sets a busy_timeout on a connection and tries to being IMMEDIATE transactions to the same database in 2 separate connections, that script will simply indefinitely hang when run.

require 'extralite'

puts 'Connecting to database...'

connection_1 = Extralite::Database.new("test.sqlite3")
puts "#{connection_1} connected"
connection_2 = Extralite::Database.new("test.sqlite3")
connection_2.busy_timeout = 0
puts "#{connection_2} connected"

[connection_1, connection_2].each do |connection|
  puts "#{connection} beginning transaction..."
  connection.execute "begin immediate transaction"
end

[connection_1, connection_2].each do |connection|
  puts "#{connection} rolling back transaction..."
  connection.execute "rollback transaction"
end

Output:

$ ruby ../repro.rb
Connecting to database...
#<Extralite::Database:0x00000001052c4268> connected
#<Extralite::Database:0x00000001052c5938> connected
#<Extralite::Database:0x00000001052c4268> beginning transaction...
#<Extralite::Database:0x00000001052c5938> beginning transaction...

It does not matter what the value of the busy_timeout is, only that it is set.

[noteflakes]

Does not hang for me. What version of SQLite are you using? Can you check with the latest version by installing extralite-bundle?

[fractaledmind]

I can't get my system to not use 3.37.0. My brew-installed SQLite is 3.42, but running this script just won't use it. I'm even tried adding it to the Extralite test suite. That test suite also uses my system 3.37.0 version. Not sure how to get Extralite to use 3.42

[noteflakes]

In your Gemfile replace extralite with extralite-bundle. That should fix the problem.

[fractaledmind]

I updated the script to this:

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem "extralite-bundle"
end

require "extralite"
require "fileutils"

p sqlite_version: Extralite.sqlite3_version
puts 'Connecting to database...'

fn1 = '/tmp/db1'

FileUtils.rm(fn1) rescue nil

p fn1: fn1

connection_1 = Extralite::Database.new(fn1)
puts "#{connection_1} connected"
connection_2 = Extralite::Database.new(fn1)
connection_2.busy_timeout = 0
puts "#{connection_2} connected"

[connection_1, connection_2].each do |connection|
  puts "#{connection} beginning transaction..."
  connection.execute "begin immediate transaction"
end

[connection_1, connection_2].each do |connection|
  puts "#{connection} rolling back transaction..."
  connection.execute "rollback transaction"
end

And it uses version 3.44.2, and now it doesn't hang, it returns an error. Is this what you saw as well? An immediate BusyError? That is what I would expect, but just double checking

 $ ruby hangs.rb
Fetching gem metadata from https://rubygems.org/.
Resolving dependencies...
{:sqlite_version=>"3.44.2"}
Connecting to database...
{:fn1=>"/tmp/db1"}
#<Extralite::Database:0x000000010e1d9778> connected
#<Extralite::Database:0x000000010e1d9368> connected
#<Extralite::Database:0x000000010e1d9778> beginning transaction...
#<Extralite::Database:0x000000010e1d9368> beginning transaction...
hangs.rb:33:in `execute': Database is busy (Extralite::BusyError)
    from hangs.rb:33:in `block in <main>'
    from hangs.rb:31:in `each'
    from hangs.rb:31:in `<main>'

[noteflakes]

Is this what you saw as well? An immediate BusyError? That is what I would expect, but just double checking

Yes that's the correct behaviour.