Search code examples
sqliteproducer-consumermultiple-processes

Reader and writer process SQLite

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 :)

Solution

  • 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