Coordinating access to a non-thread-safe db

[Steven Taschuk]

Quoth VanL:
> I am writing some code that needs to respond to two circumstances:
> 1. User input via an event-driven interface
> 2. The passing of a certain amount of time (a la cron)
> 
> I am planning to use the sched and threading modules to do this.  
> However, I need to access a database that is only threadsafe at level 1 
> (sqlite) and so I am unsure how to coordinate database access such that 
> nothing gets corrupted.  [...]

The simplest strategy is to open a new connection in each thread
which needs one.  Of course, this is undesirable if opening a
connection takes a long time or is resource intensive.

If you want to open a connection just once, and run all database
interaction through that one connection, then you need a lock (see
threading.RLock) to enforce the mutual exclusion.  Code which
needs to use the connection first locks the lock, then uses the
connection, then releases the lock.  If another thread has locked
the lock, the first step will block until the lock is released.

If you trust all your code to do this dance right, it's easy to
implement.  For safety, though, some extra work is required; a
thread could release the lock but continue to make use of the
connection object, causing chaos.  To guard against this, the
function which locks the lock and provides the connection object
to the caller can instead return a wrapper with a release method
which not only releases the lock but sets a flag in the wrapper
which makes all its methods raise an exception.  Note that this
wrapper also needs to wrap objects returned by the backing
connection, such as cursors; also it should have a __del__ method
which calls release(), in case a caller forgets to release it
explicitly.

(If some database operations take longer than other threads can
wait, you probably need the middle ground: a connection pool.)

-- 
Steven Taschuk                             staschuk at telusplanet.net
"I may be wrong but I'm positive."  -- _Friday_, Robert A. Heinlein