Safe file I/O to shared file (or SQLite) from multi-threaded web server

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Diez B. Roggisch wrote:
> AFAIK, sqlite ensures process-serialization via locking, and threads
> synchronize themselves as well.

SQLite versions prior to 3.5 did not support using the same connection or
cursors in different threads.  (You needed to allocate, use, and close all
in the same thread.)  Since then SQLite objects can be used in any thread
you want at any time.  The SQLite error handling API is not threadsafe and
requires a lock to be held otherwise you can get incorrect errors or worst
case program crashes.  The sqlite3/pysqlite code does not hold that lock
(API introduced in SQLite 3.6.5) so you are only safe if you continue to
only use objects in the same thread.  If you use APSW then you can use any
SQLite object at any time in any thread (it holds the lock amongst other
things).

> So you shouldn't need to worry at all.

The main gotcha is that SQLite uses file locking and the default behaviour
when unable to get a lock is to immediately return an error.  SQLite does
have an API to specify how long it should wait to acquire the lock (it keeps
retrying until the time expires).

sqlite3/pysqlite only lets you specify this maximum time when opening the
connection and defaults to 5 seconds.  On a busy server this may be too
short so you'll end up getting busy errors.  (Remember that writes require
at least two disk syncs and that the default behaviour for Linux is to flush
all outstanding writes not just for the file requested.)

If you use APSW then you get default SQLite behaviour and two APIs - one
lets you set/change the timeout period and the other lets you install your
own busy handler which can do whatever it wants in order to prod things along.

(Disclosure: I am the author of APSW.)

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAks+17QACgkQmOOfHg372QSiCwCgpr6fSOr6UcUUZqTDoFA4RBcK
zb8An21zZCr30AQ7VGP/Q/CsQ3z+2EVs
=55MC
-----END PGP SIGNATURE-----