Bounty: Help with multiple processes

[gottlike]

I'm sorry for such a noob question, but it's my first time using SQLite and I'm still learning. Since I don't want to waste anyone's time, I am willing to offer a bounty of $100 for someone answering my questions and enabling me to actually use SQLite in production.

So, usually I use Redis and I never have to worry about a database being locked, regardless of how many reads and writes I have. The worst that could happen is, that a command takes a few milliseconds longer (but doesn't throw an error).

So, consider this example:

# frozen_string_literal: true

require 'json'
require 'parallel'
require 'sqlite3'

module DB
  @db = SQLite3::Database.new('test.db')
  @db.execute('CREATE TABLE IF NOT EXISTS everything (id TEXT UNIQUE, data BLOB)')

  def self.set(id, type, data)
    import = { type: }.merge(data)
    @db.execute('INSERT OR REPLACE INTO everything (id, data) VALUES (?, jsonb(?));', "#{type}/#{id}", import.to_json)
  rescue StandardError => e
    raise("#{name}.#{__method__} > #{e.message}")
  end
end

Parallel.map(10.times.to_a, in_processes: 2) do
  DB.set(rand(1000), 'some/type', { test: 'value' })
end

I'm using Roda with Puma (5-250 threads, 4 workers), so I have multiple processes and threads.

What am I doing wrong? Is there some obvious setting or a best practice for this?

Thank you 🙏

[gottlike]

Okay, so I found an answer, which is pretty simple (thankfully):

# frozen_string_literal: true

require 'json'
require 'parallel'
require 'sqlite3'

module DB
  @db = SQLite3::Database.new('test.db')
  @db.execute('PRAGMA journal_mode = WAL')
  @db.execute('PRAGMA synchronous = normal')
  @db.execute('PRAGMA temp_store = memory')
  @db.execute('PRAGMA mmap_size = 30000000000')
  @db.execute('PRAGMA busy_timeout = 5000')
  @db.execute('CREATE TABLE IF NOT EXISTS everything (id TEXT UNIQUE, data BLOB)')

  def self.set(id, type, data)
    import = { type: }.merge(data)
    @db.execute('INSERT OR REPLACE INTO everything (id, data) VALUES (?, jsonb(?));', "#{type}/#{id}", import.to_json)
  rescue StandardError => e
    raise("#{name}.#{__method__} > #{e.message}")
  end
end

Parallel.map(10.times.to_a, in_processes: 2) do
  DB.set(rand(1000), 'some/type', { test: 'value' })
end