This test script demonstrates how the busy_timeout holds the GVL while retrying, while a busy_handler timeout will release the GVL between retries

test_sqlite3_busy_timeouts.rb

require 'sqlite3'
require 'minitest/autorun'

puts "info: gem version: #{SQLite3::VERSION}"
puts "info: sqlite version: #{SQLite3::SQLITE_VERSION}/#{SQLite3::SQLITE_LOADED_VERSION}"
puts "info: sqlcipher?: #{SQLite3.sqlcipher?}"
puts "info: threadsafe?: #{SQLite3.threadsafe?}"

class TestCase < Minitest::Test
  def setup
    @db = SQLite3::Database.new("test.db")
    @db.transaction do
      @db.execute "create table foo ( a integer primary key, b text )"
      @db.execute "insert into foo ( b ) values ( 'foo' )"
      @db.execute "insert into foo ( b ) values ( 'bar' )"
      @db.execute "insert into foo ( b ) values ( 'baz' )"
    end
  end

  def teardown
    @db.close
    File.delete( "test.db" )
  end

  def test_busy_timeout_holds_gvl
    # this is where we will store work done by our "working thread"
    work = []
    # initialize the "working thread" which does work every tenth of a second
    Thread.new do
      while true
        sleep 0.1
        work << '.'
      end
    end
    # sleep for one second to ensure that our "working thread" has started running before proceeding
    sleep 1

    # set the busy_timeout for our primary database connection
    @db.busy_timeout 1000
    # initialize a mutex and lock it to give us sufficient control to ensure the busy exception is raised
    busy = Mutex.new
    busy.lock

    # initialize the "busy thread" which keeps our database busy
    t = Thread.new do
      begin
        db2 = SQLite3::Database.open( "test.db" )
        db2.transaction( :exclusive ) do
          busy.lock
        end
      ensure
        db2.close if db2
      end
    end
    # sleep for one second to ensure that our "busy thread" has started running before proceeding
    sleep 1

    assert_raises( SQLite3::BusyException ) do
      # mark where in the "work stream" we begin attempting to execute a SQL query against a busy database
      work << '>'
      @db.execute "insert into foo (b) values ( 'from 2' )"
    end

    # clean up the mutex and "busy thread"
    busy.unlock
    t.join

    # busy_timeout only ever allows at most one more round of "work" from the "working thread"
    # while attempting to connect to the database
    p ['busy_handler_timeout', work]
    assert 2 == work.size - work.find_index(">")
  end

  def test_busy_handler_timeout_releases_gvl
    # this is where we will store work done by our "working thread"
    work = []
    # initialize the "working thread" which does work every tenth of a second
    Thread.new do
      while true
        sleep 0.1
        work << '.'
      end
    end
    # sleep for one second to ensure that our "working thread" has started running before proceeding
    sleep 1

    # set the busy_handler for our primary database connection, which is a GVL releasing timeout
    @db.busy_handler do |count|
      now = Process.clock_gettime(Process::CLOCK_MONOTONIC)
      if count.zero?
        @timeout_deadline = now + 1
      elsif now > @timeout_deadline
        next false
      else
        sleep(0.001)
      end
    end
    # initialize a mutex and lock it to give us sufficient control to ensure the busy exception is raised
    busy = Mutex.new
    busy.lock

    # initialize the "busy thread" which keeps our database busy
    t = Thread.new do
      begin
        db2 = SQLite3::Database.open( "test.db" )
        db2.transaction( :exclusive ) do
          busy.lock
        end
      ensure
        db2.close if db2
      end
    end
    # sleep for one second to ensure that our "busy thread" has started running before proceeding
    sleep 1

    assert_raises( SQLite3::BusyException ) do
      # mark where in the "work stream" we begin attempting to execute a SQL query against a busy database
      work << '>'
      @db.execute "insert into foo (b) values ( 'from 2' )"
    end

    # clean up the mutex and "busy thread"
    busy.unlock
    t.join

    # busy_handler timeout allows many rounds of "work" from the "working thread"
    # while attempting to connect to the database
    p ['busy_handler_timeout', work]
    assert 2 < work.size - work.find_index(">")
  end
end

A simpler test for gvl could also look like this

dbs = []
2.times do
  db = SQLite3::Database.new('./test.db')
  db.set_timeout = 2000
  dbs << db
end
threads = []
2.times do
  db = dbs.pop
  threads << Thread.new do
    db.execute("BEGIN IMMEDIATE")
    sleep 1
    db.execute("END")
  end
end
threads.each(&:join)

One of these threads will throw a busy exception as it spinlocks trying to acquire the lock already held by the other thread, preventing the other thread from running and finishing the sleep statement. Only after it timeouts that the other thread can proceed, commit the transaction and release the write lock.

That was approximately what my previous setup was. The tricky part is the test assertion. I was using a timing assertion, which was just flaky. I like my current assertion much more as I find it clearer. Does the work thread get to do work while the connecting thread is trying to connect.