Python: SQLite & Multiple Threads
Still working on Freezerburn & SQLite, and I ran into an interested quirk yesterday involving SQLite’s interoperation with threads.
My application uses 3 threads:
- The Main Application (not really in a thread, but separate from the other 2)
- The Web Server
- The Communications Server
Establishing a Database connection can take a little bit of time, so I wrote a simple wrapper function to check if a connection was already established and use that one instead. That was where my problems began.
My first pass at the function, was simply this:
def MasterDBConnection():
global MasterDBCon
global MasterDBFile
if not MasterDBCon:
con = sqlite.connect(database=MasterDBFile, isolation_level=None)
con.row_factory = sqlite.dbapi2.Row
MasterDBCon = con
return MasterDBCon
This simply uses a global “MasterDBCon” variable to save the connection, and prevents me from unnecessarily establishing new connections. It worked great, until I tried to use it within the multi-thread design. When that happened, I got an error that the SQLite Connection object must be specifically instantiated in each thread.
After some research, I discovered the “threading.local()” procedure, for storing data unique to each thread. I rewrote the function like so:
def MasterDBConnection():
global ThreadLocalData
global MasterDBFile
ThreadLocalData = threading.local()
try:
return ThreadLocalData.MasterDBCon
except AttributeError:
print “Establishing Database connection to %s” % MasterDBFile
con = sqlite.connect(database=MasterDBFile)
con.row_factory = sqlite.dbapi2.Row
ThreadLocalData.MasterDBCon = con
return ThreadLocalData.MasterDBCon
This one uses the “threading.local()” feature to establish a new connection for each thread, if it doesn’t exist. Again, testing seemed to work great at first but then I ran into a strange snag. In the master thread, every call to MasterDBConnection would result in a new connection. It seems that local() returns a new object for every call when called from the Main Thread. With that knowledge, I was able to rewrite the function one more time.
def MasterDBConnection():
global MasterDBFile
global MasterDBCon
if type(threading.currentThread()) == threading._MainThread:
if MasterDBCon == None:
print “Establishing GLOBAL Database connection to %s” % MasterDBFile
con = sqlite.connect(database=MasterDBFile)
con.row_factory = sqlite.dbapi2.Row
MasterDBCon = con
return MasterDBCon
ThreadLocalData = threading.local()
try:
return ThreadLocalData.MasterDBCon
except AttributeError:
print “Establishing Thread Database connection to %s” % MasterDBFile
con = sqlite.connect(database=MasterDBFile)
con.row_factory = sqlite.dbapi2.Row
ThreadLocalData.MasterDBCon = con
return ThreadLocalData.MasterDBCon
This solution is a hybrid of the previous two. If called from the Master Thread (threading._MainThread) then it returns the global Master variable. If it’s called from a different thread, then it establishes a new connection and returns it. So far, this function seems to work just fine.
I do have one more issue that I may simply ignore: Using the Threaded HTTPServer class, each webpage hit is in a separate thread, meaning each webpage hit requires a new database connection. It’s not a huge deal, but I’m a bit concerned about how well the garbage collection will work. I’m afraid some of these connections and queries might wind up floating around for an unnecesarily long time, consuming memory and resources. But for now, it’s all working just fine.
