Allow reuse of SQLite3ConnectionFactory across multiple threads
I am using SpringPython to develop a webapp with an embedded SQLite3 database. Here are the details of my environment: The initial code for the app was generated by coily.
OS: Mac OS X (Snow Leopard)
In the app_context.py, I have defined a dataSource as follows:
This is being injected into various classes, which then create a DatabaseTemplate and do SQL operations on it. The problem is (was) that since this is a web application, the connection is created by the container thread, but is being used from different request threads, which SQLite did not like, and would throw an exception saying that the connection was created in Thread X but was being used from Thread Y.
Based on the information on this page:
I changed the Sqlite3ConnectionFactory connect method to add the check_same_thread=False argument, and since then the above code is running great without complaints (I initially attempted to work around the problem by scoping the dataSource to PROTOTYPE, but that would error out with the same complaint as above once the max number of connections were consumed).
My local Sqlite3ConnectionFactory.connect method looks like this:
....return sqlite3.connect(self.db, check_same_thread=False)
....self.using_sqlite3 = False
....return sqlite.connect(self.db, check_same_thread=False)
This may also only be a Mac OS X thing, going by the statement:
"On some operating systems, a database connection should always be used in the same thread in which it was originally created. "
in this page: http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
Would it make sense to update the SpringPython code to include this extra parameter?