Hello all,
I am writing a server program that reads and writes several different
sqlite databases. Each client program can do one of the following at a
time 1) send a file with a bunch of SQL statements that the server will
run on the appropriate database, or 2) request an entire database file.
Multiple clients will be connecting randomly and trying to read or write
to a database. There should be roughly about the same number of reads and
writes, although some more reads.
I have implemented and tested the following scheme:
Retrieving a database file doesn't invoke sqlite at all, but merely opens
the sqlite database file for reading, reads the file into the socket, and
then closes the file.
For writing, I open the database, run a "BEGIN TRANSACTION", run the
individual SQL statements, run a "COMMIT", and then close the database.
I configured the databases with "PRAGMA synchronous = OFF" and "PRAGMA
journal_mode = MEMORY" to increase speed, and set sqlite3_busy_timeout(db,
5000); to retry if busy.
In testing, everything seems to work ok. I wrote some sample clients that
read and wrote as fast as they could to the server at the same time and
achieved acceptable throughput.
I am concerned however about corner cases, best use, and does things more
efficiently and quickly if possible.
SO... Here is my main question:
I'm assuming here that (Linux) file caching and sqlite are making sure
that when I read a file, the database won't be in a 'partially updated and
invalid state'. In testing, I wasn't able to create an invalid database,
but I want to be sure that this doesn't happen. So, I'm assuming that if
the file is opened for read by my program, sqlite won't write to it until
the file is closed. Or conversely, that if sqlite has the file open to
write, my program will read a cached version (if reading and writing
happen at the same time, I'm fine with the reader getting a slightly stale
version). But I'm not completely clear on how Linux file locking works...
So do I need to add a mutex to my program to make sure that a reader
doesn't get a corrupt database file?
Any other suggestions or thoughts? Thanks!
Josh
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
