Hello all.
I have a pretty basic question about threads and SQLite, here
is the short version:
When opening multiple connections to the same database using
SQLITE_OPEN_SHAREDCACHE, will SQLite automatically serialize
write transactions to the same DB while multiple connections
are attempting to perform writes ?
Here is a more descriptive version of the question:
In the past I've used a setup where I have a dedicated process
with a domain specific IPC interface used to issue any calls
which write to the SQLite (and also used to propagate change
notifications over the IPC). While clients to this daemon
would open direct connections to the SQLite for reading...
this one-writer-many-readers setup has worked well specifically
in combination with WAL journaling.
Now I'm doing something a little different, which is a threaded
environment inside a single application, I'm using a similar
approach to the one described in this old blog post[0], i.e.
"Connection Pooling".
Now from what I understand, using serialized threading mode[1] will
automatically serialize writes on the same shared SQLite connection
so that writes will not happen concurrently but reads can still
happen concurrently - all magically out of the box - so I'm wondering
now that we have this level of thread safety over a single connection,
do I have the same guarantees when using multiple connections, provided
they are all opened in the same process and using the sharedcache
connection mode[2] ?
Or should I ensure that only one connection is processing a write
transaction ever at a time (and do that write serialization myself) ?
Cheers,
-Tristan
PS: I know the first question you will ask is "Why not just use
serialized threading mode over the same shared connections ?"
So I'll answer that up front, the reason is that there are no
sqlite3_exec_with_progress_handler() or
sqlite3_step_with_progress_handler() APIs available, and that
the function sqlite3_progress_handler() can only be used to
abort progress globally over the entire connection.
I realize this would probably be ridiculously easy to implement
in SQLite - but currently the absence of call-contextual progress
callbacks means that I can't reliably run a routine in the background
and optionally abort it - so I'm working around that little shortcoming
by using the connection pool approach.
[0]:http://dev.yorhel.nl/doc/sqlaccess#Connection_pooling
[1]:http://sqlite.org/threadsafe.html
[2]:http://www.sqlite.org/sharedcache.html
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
