1

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

Sohail
  • 3,020
  • 1
  • 25
  • 23
  • Writing to a read-only database? Sir, I believe that is an oxymoron. Why, if I may, does this need to be threaded? – MPelletier Feb 25 '10 at 02:35
  • Yeah, I guess that was kind of silly :) I meant read-only to the second process. This is not threaded, but has multiple OS-level processes. One is a background service, the other is the UI. – Sohail Feb 25 '10 at 05:25

2 Answers2

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

ravenspoint
  • 19,093
  • 6
  • 57
  • 103
  • Also see http://stackoverflow.com/questions/78801/sqlite3busyexception/95255#95255 – ravenspoint Feb 25 '10 at 03:19
  • Ah, this seems like the right thing to do. It is what I implemented in any case but would be good to have sqlite manage it instead. – Sohail Feb 25 '10 at 05:23
  • Hmm, I read the documentation but I don't get it. It seems to say that the busy handler will just sleep for the specified time and then return 0. Doesn't seem to say that it will retry... Confusing to say the least. – Sohail Feb 25 '10 at 05:31
  • Oh, I understand! A busy handler returns 1 to try again, 0 to fail. – Sohail Feb 25 '10 at 05:33
0

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.

Community
  • 1
  • 1
MPelletier
  • 16,256
  • 15
  • 86
  • 137
  • Sure, but in this case it's multiple processes not threads. One is a background service, the other is a UI. – Sohail Feb 25 '10 at 04:51
  • Well, ok then. :) There might be a wrapper that has that already, but it might be cleaner to go with ravenspoint's solution. – MPelletier Feb 25 '10 at 15:11