Reader and writer process SQLite

Question

I have two processes:

1. Writes to two tables every second (ish)
2. Reads from said tables periodically

I know that with SQLite, any writes lock the whole database and so sometimes the second process can fail with a locked database.

Is there anything you can suggest that would completely remove the need for these two processes to touch the same database? For example, could I atomically transfer the data from the database being written to a second read-only database?

Thanks :)

Answer 1

May I suggest reading the FAQ on SQLite thread safety?

Threads are evil. Avoid them.

SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1.

Answer 2

You can configure the connection to the database in the second process to wait for a certain time when it encounters a busy database, waking up periodically to check for a free database, before giving up.

sqlite3_busy_timeout(sqlite3*, int ms);

http://www.sqlite.org/c3ref/busy_timeout.html