Sqlite db remains locked / unavailable

Question

I have a problem with sqlite3 db that remains locked / unavailable after a certain access.

The behavior is still happening on Ubuntu 10.4 and on user (OpenEmbedded) Linux. The sqlite version is 3.7.7.1). Db is a local file.

One C ++ application periodically accesses db (5s). Each time multiple insert statements are executed wrapped in a pending transaction. This happens in only one thread. Connection with db is maintained throughout the life of the application. Used statements are also stored and reused through sqlite3_reset . sqlite_threadsafe is set to 1 (serialized), logging is set to WAL.

Then I open in parellel sqlite db using the sqlite command line tool. I enter BEGIN IMMEDIATE; wait> 5 seconds and commit with END; .

after that, the db-access of the application does not work: BEGIN TRANSACTION returns return code 1 ("SQL error or missing database"). If I started ROLLBACK TRANSACTION just before the start, just to make sure that there is no active transaction yet, it does not work with return code 5 ("Database file is locked").

Does anyone have an idea how to approach this problem or have an idea that might cause it?

EDIT: Workaround: if the error described occurs, I close and reopen the db connection. This fixes the problem, but I don’t understand why this is so.

Answer 1

Sqlite is a database with fewer servers. As far as I know, it does not support simultaneous access from multiple design sources. You are trying to access the same support file both from your application and from a command tool, so you are trying to perform concurrent access. That is why he fails.

Answer 2

SQLite joins should be used from only one thread, because among other things, they contain mutexes that are used to ensure proper concurrent access. (Remember that SQLite also supports only one stream of updates anyway and without simultaneous readings at the time, which is a limitation on the lack of a server database.)

Fortunately, SQLite joins are relatively cheap when they do nothing, and the cost of things like cached prepared statements is actually quite small; open as much as you need.

[EDIT]: Moreover, this explains the closing and reopening of connections: it creates a connection in a new thread (and completes all locks, etc. in the old one).