Stalling on db.execute with INSERT OR REPLACE

[abirmingham]

Hello!

I'm running into an issue where my DB connection seems to intermittently freeze on INSERT OR REPLACE calls. I haven't figured out whether this is an issue with sqlite, sqlite3-ruby, or my own application. I've been hitting my head against this one for awhile, so I thought I'd reach out for help and see if this issue is something y'all have seen in the past, or if it rings any bells.

I'm running Windows 11, Ruby 3.3.0, and sqlite3-ruby 1.7.0. SQLite3.threadsafe? returns true in my environment.

I have two statements which stall indefinitely:

md5 = Digest::MD5.hexdigest(@@vars.to_s)
blob = SQLite3::Blob.new(Marshal.dump(@@vars))
begin
  echo("1 - INSERT|REPLACE")
  db.execute(
    'INSERT OR REPLACE INTO uservars(scope,hash) VALUES(?,?);',
    "#{XMLData.game}:#{XMLData.name}".encode('UTF-8'),
    blob
  )
  echo("1 - INSERT|REPLACE finished")
rescue SQLite3::BusyException
  echo("1 - BusyException")
  sleep 0.1
  retry
end

begin
  echo("2 - BEGIN")
  db.execute('BEGIN')
rescue SQLite3::BusyException
  echo("2 - BusyException")
  sleep 0.1
  retry
end

begin
  blob = SQLite3::Blob.new(Marshal.dump(data))
  echo("2 - INSERT|REPLACE")
  db.execute(
    'INSERT OR REPLACE INTO script_auto_settings(script,scope,hash) VALUES(?,?,?);',
    script_name.encode('UTF-8'),
    scope.encode('UTF-8'),
    blob
  )
  echo("2 - INSERT|REPLACE finished")
rescue SQLite3::BusyException
  echo("2 - BusyException")
  sleep 0.1
  retry
end

The most recent reproduction showed the following printout before stalling indefinitely (I waited 10 minutes before killing the process):

06:59:08: 2 - BEGIN
06:59:08: 2 - INSERT|REPLACE
06:59:08: 1 - INSERT|REPLACE

(note the absence of BusyException echos)

Note that these code blocks are in separate threads and the db object is shared. Unfortunately I have not been able to create a minimal reproduction of the issue, and it only reproduces every 3 days or so, despite this code being executed hundreds of times per day on my local windows machine. Note that I do not own this code, but I can edit it locally and if I can identify the specific issue then I can submit a change to the maintainers.

My first concern was that thread 1's insert/replace statement is interleaving with thread 2's BEGIN. However, while this is odd and should probably be fixed as it may result in an unintentional rollback of thread 1's data, I am wondering how this could be the root of the issue. Could this interleaving explain the behavior I'm seeing? Does this ring any bells for y'all? Thank you in advance!

[abirmingham]

One more thing - I have noticed that this issue most often (>95% of the time) occurs in the minutes after I first wake my computer up from sleep. Note that I am still starting the application from scratch after wakeup, so I'm not sure whether that's relevant, but the >95% correlation is high enough that I feel obliged to mention it here.

[abirmingham]

Oh! One more thing. I should also note that multiple instances of this process are running. Each initiates the database handle like so:

db = SQLite3::Database.new("/tmp/lich.db3")

So this is not just a question of thread concurrency, but of process concurrency as well.