Explore the impact of polling for the SQLite write lock on performance under various contention scenarios. Run via `ruby meta.rb`, where meta.rb and bench.rb are in the same directory

bench.rb

require "sqlite3"
require "benchmark"

DB_NAME = "test.sqlite"

parent_connection = SQLite3::Database.new(DB_NAME)
parent_pid = Process.pid

mode = ARGV[0] || "constant"
process_count = ARGV[1] == "--processes" ? ARGV[2].to_i : 1
thread_count = ARGV[3] == "--threads" ? ARGV[4].to_i : 1
parent_slow_queries = ARGV[5] == "--slow_queries" ? ARGV[6].to_i : 1

slow_query = <<~SQL
  WITH RECURSIVE r(i) AS (
    VALUES(0)
    UNION ALL
    SELECT i FROM r
    LIMIT 10000000
  )
  SELECT i FROM r WHERE i = 1;
SQL
fast_query = "SELECT 1;"

def assign_busy_handler(connection, mode)
  if mode == "constant"
    # always sleep 1 ms
    connection.busy_handler { |_count| sleep 0.001 }
  elsif mode == "backoff"
    # sleep the number of milliseconds equal to the number of times the busy handler has been called
    connection.busy_handler { |count| sleep count * 0.001 }
  else
    raise "Unknown mode: #{mode}"
  end
end

puts %Q[{ "event": "STARTED", "type": "parent", "pid": #{parent_pid}, "mode": "#{mode}", "process_count": #{process_count}, "thread_count": #{thread_count} }]

started_at = Time.now

process_count.times do
  if fork # parent
    # `fork` creates a new process and returns the child process's PID to the parent process
    # We don't want child processes to fork again, so we add this condition to prevent grandchild processes
    exit unless Process.pid == parent_pid
  else # child
    sleep 0.2
    threads = []
    thread_count.times do
      threads << Thread.new do
        child_connection = SQLite3::Database.new("test.sqlite")
        assign_busy_handler(child_connection, mode)

        time_in_seconds = Benchmark.realtime do
          child_connection.transaction(:exclusive) do
            child_connection.execute fast_query
          end
        end

        puts %Q[{ "event": "FINISHED", "type": "child", "pid": #{Process.pid}, "tid": #{Thread.object_id}, "ms": #{(time_in_seconds*1000).round} }]
      end
    end
    threads.each { |it| it.join }
    exit
  end
end

assign_busy_handler(parent_connection, mode)

time_in_seconds = Benchmark.realtime do
  parent_slow_queries.times do
    parent_connection.transaction(:exclusive) do
      parent_connection.execute slow_query
    end
    sleep 0.001
  end
end
puts %Q[{ "event": "FINISHED", "type": "parent", "pid": #{parent_pid}, "ms": #{(time_in_seconds*1000).round} }]

puts %Q[{ "event": "FINISHED", "type": "script", "ms": #{((Time.now - started_at)*1000).round} }]

meta.rb

require "json"

modes = %w[constant backoff]
processes = [4, 8]
threads = [2, 4]
slow_queries = [0, 1, 2]

thead = [
  "MODE".center(10),
  "CONTENTION".center(12),
  "PARENT".center(8),
  "FASTEST".center(9),
  "SLOWEST".center(9),
  "SCRIPT".center(8),
  "DELTA".center(7)
]
puts thead.join(" | ")

modes.each do |mode|
  processes.each do |process_count|
    threads.each do |thread_count|
      slow_queries.each do |slow_query_count|
        output = %x( ruby bench.rb #{mode} --processes #{process_count} --threads #{thread_count} --slow_queries #{slow_query_count} )
        parent_finished_in = nil
        fastest_child_finished_in = nil
        slowest_child_finished_in = nil
        script_finished_in = nil
        output.each_line do |line|
          data = JSON.parse(line)
          if data["type"] == "parent" && data["event"] == "STARTED"
            mode = data["mode"]
            process_count = data["process_count"]
            thread_count = data["thread_count"]
          elsif data["type"] == "parent" && data["event"] == "FINISHED"
            parent_finished_in = data["ms"]
          elsif data["type"] == "child" && data["event"] == "FINISHED"
            if fastest_child_finished_in.nil? || data["ms"] < fastest_child_finished_in
              fastest_child_finished_in = data["ms"]
            end
            if slowest_child_finished_in.nil? || data["ms"] > slowest_child_finished_in
              slowest_child_finished_in = data["ms"]
            end
          elsif data["type"] == "script" && data["event"] == "FINISHED"
            script_finished_in = data["ms"]
          end
        end

        tbody = [
          mode.center(10),
          "#{process_count+thread_count+slow_query_count} (#{process_count}/#{thread_count}/#{slow_query_count})".to_s.center(12),
          parent_finished_in.to_s.rjust(7) + " ",
          fastest_child_finished_in.to_s.rjust(8) + " ",
          slowest_child_finished_in.to_s.rjust(8) + " ",
          script_finished_in.to_s.rjust(7) + " ",
          (parent_finished_in - slowest_child_finished_in).to_s.rjust(6) + " "
        ]

        puts tbody.join(" | ")
      end
    end
  end
end

results.txt

   MODE    |  CONTENTION  |  PARENT  |  FASTEST  |  SLOWEST  |  SCRIPT  |  DELTA
 constant  |  6 (4/2/0)   |       0  |        1  |        3  |       2  |     -3
 constant  |  7 (4/2/1)   |    1071  |      869  |      875  |    1073  |    196
 constant  |  8 (4/2/2)   |    2142  |      871  |     1944  |    2144  |    198
 constant  |  8 (4/4/0)   |       0  |        0  |        9  |       1  |     -9
 constant  |  9 (4/4/1)   |    1077  |      876  |      893  |    1078  |    184
 constant  |  10 (4/4/2)  |    2144  |      871  |     1952  |    2146  |    192
 constant  |  10 (8/2/0)  |       0  |        1  |        8  |       3  |     -8
 constant  |  11 (8/2/1)  |    1077  |      877  |      891  |    1080  |    186
 constant  |  12 (8/2/2)  |    2141  |      867  |     1953  |    2143  |    188
 constant  |  12 (8/4/0)  |       0  |        0  |       27  |       3  |    -27
 constant  |  13 (8/4/1)  |    1076  |      878  |      902  |    1079  |    174
 constant  |  14 (8/4/2)  |    2163  |      883  |     1984  |    2166  |    179
 backoff   |  6 (4/2/0)   |       0  |        0  |        4  |       2  |     -4
 backoff   |  7 (4/2/1)   |    1064  |      878  |      942  |    1066  |    122
 backoff   |  8 (4/2/2)   |    2127  |     1926  |     2044  |    2129  |     83
 backoff   |  8 (4/4/0)   |       0  |        0  |       26  |       2  |    -26
 backoff   |  9 (4/4/1)   |    1069  |      868  |     1018  |    1071  |     51
 backoff   |  10 (4/4/2)  |    2132  |     1998  |     2204  |    2134  |    -72
 backoff   |  10 (8/2/0)  |       0  |        0  |       26  |       3  |    -26
 backoff   |  11 (8/2/1)  |    1071  |      875  |     1173  |    1074  |   -102
 backoff   |  12 (8/2/2)  |    2143  |     1952  |     2324  |    2145  |   -181
 backoff   |  12 (8/4/0)  |       0  |        0  |      107  |       3  |   -107
 backoff   |  13 (8/4/1)  |    1066  |      873  |     1424  |    1069  |   -358
 backoff   |  14 (8/4/2)  |    2133  |     1992  |     2475  |    2135  |   -342