SQLite and concurrency

Help & Support
1

Hi,

What is the correct way to handle concurrency with SQLite?

Is it safe to do something like this? or should I keep the database connection in one fiber and use channels to send read/write tasks to it?

@db = DB.open "sqlite3://./data.db"
@db.exec "PRAGMA journal_mode=WAL;"

spawn(name: "task_1") do
  @db.transaction do |tx|
    conn = tx.connection
    conn.exec "..."
  end
end

spawn(name: "task_2") do
  @db.transaction do |tx|
    conn = tx.connection
    conn.exec "..."
  end
end

sleep
@db.close
2

DB is thread and fiber safe, the code above is fine :+1:

(I don’t know if that’s documented. If not, it should…)

1 Like
3

It’s safe.

Note that you will potentially use main connections to the database so, if you want the PRAGMA ... to be executed on every connection you should db.setup_connection { |conn| conn.exec "PRAGMA..." }. I am not sure if that is needed in this case though.

On multi-threading (-Dpreview_mt) you should also check you sqlite is compiled https://sqlite.org/threadsafe.html. The default is Serialized which allow you to use the same database from multiple threads, it should be fine.

If you use Crystal with single-thread, you are fine with either way the sqlite is compiled.

2 Likes
4

Thanks,

I couldn’t find anywhere in the docs that stated that DB is thread/fiber safe although on second reading the first paragraph on the connection pool page does hint at it.

WAL journal_mode is persistent across connections. It is the only SQLite journal_mode that is I think.