Wednesday, 25 September 2013

Using sqlite in multithreaded environment

Using sqlite in multithreaded environment

I've been trying to wrap my head around SQLite docs about multithreading,
but still not quite get it. Let say I have 2 threads that read and write
from/to DB. My write consists of multiple sql statements, so I need wrap
them in explicit transaction, something like this:
sqlite3_exec (myDb, "BEGIN TRANSACTION", 0, 0, 0);
// write stuff here
sqlite3_exec (myDb, "COMMIT TRANSACTION", 0, 0, 0);
First question: do I need wrap read operations in transaction the main
requirement for me that until write transaction finishes it should not be
possible to read half written data
Second: can I use one database connection in both threads? - looks like I
can't because if both threads are writing to DB and therefore both open
transactions second "BEGIN TRANSACTION" statement will fail.
So what is right approach here: Use own locking mechanism? Use one
connection per thread? - what will happen in that case, should I worry
about SQL_BUSY/SQL_LOCKED?
Thanks!

No comments:

Post a Comment