Re: [SQLObject] SQLObject using SQLite and Multithreading

[Alex Le D. <al...@ps...>]

David Creemer wrote:
> FYI, As of SQLite 3.3.1, some of the threading restrictions on  
> database connection sharing have been relaxed. I have not yet tried  
> it with SQLObject, but it looks like threaded connection pooling  
> might now work. See:
> 
> http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
> 

I am using the latest version (pysqlite 2.3.1) which I believe is 
against the latest or near latest sqlite so probably is better than 
3.3.1. But the connection pooling still doesn't work as expected. I 
guess it comes down to what the connection pool does and how it works 
when interfaced with cherrypy.

 From the cherrypy side I think a separate thread is created to handle 
incoming requests (ie the threadPool). Those requests are passed to 
handler objects and calls made to handler methods. Within these methods 
the SQLObjects are accessed.

Thus if there are 10 threads on the cherrypy side then all 10 will have 
the same ConnectionHub instance, but have separate connectionForURI 
instances as shown::

hub = dbconnection.ConnectionHub()
def connect (threadIndex) :
      hub.threadConnection = connectionForURI('sqlite:///c|db/books.db')
      cherrypy.threadData.db = hub
cherrypy.server.onStartThreadList = [connect]

Now my reading of this code is that a hub instance is created and it's 
threadConnection attribute is set to the SQLite connection instance, 
then the the hub instance is stored as thread specific data. This hub 
object's threadConnection is the connection that is retrieved when 
accessing the data within the cherrypy methods. What I don't understand 
here is what the threadConnection attribute is doing. I read this as the 
threadConnection attribute being overwritten each time the connect 
methods is called. But the code does imply that each time connect is 
called a new thread specific connection is created and stored ... does 
the hub handle each 'setting' of threadConnection itself (placing it 
into the pool)?

Is the mistake to have the instantiation of the connectionForURI within 
the connect method? Should the code instead be::

hub = dbconnection.ConnectionHub()
hub.threadConnection = connectionForURI('sqlite:///c|db/books.db')
def connect (threadIndex) :
      cherrypy.threadData.db = hub
cherrypy.server.onStartThreadList = [connect]

The way I read this code then, is that a single connection would be 
shared between all the threads, however I don't think this will stop the 
exception because the access is within the cherrypy thread and not the 
thread used to create the SQLite connection (which is what the error 
complains about : created in thread x, but used in thread y).

For thread unsafe databases, what I think should happen is the 
connection hub should maintain a map of threadConnection (ie the thread 
id when the thread is created) and a single connectionForURI within it's 
own thread (ie thread 11). All database transactions would use thread 11 
and communications between the handler methods would be via the 
connection hub. Can anyone tell me if this is so?

cheers, Alex.

--