search

elixir-sqlite / exqlite

An SQLite3 driver for Elixir
https://hexdocs.pm/exqlite
MIT License
217 stars 48 forks source link

Database is locked investigation #138

Closed kevinlang closed 3 years ago

kevinlang commented 3 years ago

I still see this now and again in tests, and generally only when the Repo is first set up and connections are getting initialized.

I added a line to Sqlite3.execute to log the SQL when it hits an error from the NIF, and was able to find that the error is only hit when the PRAGMA JOURNAL_MODE=WAL statement is executed.

My initial thought around this issue was it being an issue around opening the literal database connection, but now I see it is an issue with configuring the database connection after the actual db handle is open.

I was able to replicate this with my local sqlite3 with the following command:

sqlite3 "wow10.db" "PRAGMA JOURNAL_MODE=WAL" 
& sqlite3 "wow10.db" "PRAGMA JOURNAL_MODE=WAL" 
& sqlite3 "wow10.db" "PRAGMA JOURNAL_MODE=WAL" 
( and so on )

Intermittently getting the same "database is locked" error.

Not sure what the long term solution here is. Looks like concurrently setting the journal mode can result in a database is locked issue.

My first thought was to make the journal mode set at compile time, since it is the "default" for exqlite anyway, but that doesn't seem to be a compile time option.

If I cannot think of anything I'll probably create a thread in the SQLite3 forum to see if they have a proposed solution.

warmwaffles commented 3 years ago

So I was actually thinking about this the other day.

When WAL is enabled, it stays enabled even after the sqlite handle is closed. So what we could do is work in a check and set method instead of just blanket setting everything.

~Although the pragma setting is done primarily in ecto_sqlite3~

kevinlang commented 3 years ago

With that PR and also with https://github.com/elixir-sqlite/ecto_sqlite3/pull/25 I no longer see this issue. I think we found the root cause and have the fixes in place, closing.