SQLite connections shared across threads (test case and logs)
Randy Syring
connections:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/5f742fdd313f3da9/
I went ahead and produced what I hope is a very narrow test case to
show that I am not explicitly holding onto connections (unless I
completely misunderstand, which is possible). Here is my test code:
http://paste.pocoo.org/show/91285/
When I run this through a native Python wsgi server, I get the
following log:
http://paste.pocoo.org/show/91286/
Which has some ProgrammingErrors related to SQLite connections, but
they are caught and don't propagate to my application and don't
interfere with anything (as far as I can tell).
When I run the exact same code using PyISAPIe, I get the following log
file, which shows many exceptions all propagating up to my application
and hosing it:
http://paste.pocoo.org/show/91287/
The log files show 100 requests using apache benchmark tool. In the
logs, the number in parentheses just before the message is the thread
id.
Please help me with this. I have a web application that I have spent
~150 hours on that I need to get working for a customer. Everything
was ready to go, and I move it to the production box and start getting
these errors.
Thank you!
Michael Bayer
On Nov 15, 2008, at 4:53 PM, Randy Syring wrote:
> I went ahead and produced what I hope is a very narrow test case to
> show that I am not explicitly holding onto connections (unless I
> completely misunderstand, which is possible). Here is my test code:
>
> http://paste.pocoo.org/show/91285/
>
Your Session is binding to the same thread local connection that your
"contextual_connect()" method is returning, however while you are
closing out the connection explicitly, you aren't closing the Session,
which keeps the connection opened outside the boundaries of the WSGI
method regardless of the type of pool and configuration in use. The
Session is then garbage collected via asynchronous gc, the connection
is returned to the pool, and the pool's attempt to rollback() the
connection before returning to the pool raises the exception. The
exception does not propagate outwards since it is during garbage
collection. This is why the program keeps running without overall
issue (except for your ISAPI plugin which probably cannot handle that
kind of thing gracefully).
The explicit connection as well as the "threadlocal" strategy are all
unnecessary here. Configuring the sessionmaker() with a bind to a
plain engine i.e. create_engine('sqlite:///mydb.sql'), and making sure
sess.close() is called within the WSGI method are all that's needed.
Pattern here is:
Session = sessionmaker(bind=engine)
sess = Session()
try:
< work with session>
finally:
sess.close()
Alternatively, as I noted previously in http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_lifespan
, using scoped_session in conjunction with Session.remove() at the end
of the request works here as well, as I mentioned this is the practice
that is standard among popular web frameworks such as Pylons.
Pattern here is :
Session = scoped_session(sessionmaker(bind=engine))
sess = Session()
try:
< work with session>
finally:
Session.remove()
Still another way to do this is to eliminate the source of the error
at the pool level - ultimately, the SingletonThreadPool is attempting
to return the connection to the pool and call rollback() on it, which
is why the threaded access fails. If you use NullPool, the connection
is thrown away entirely when closed and nothing is done to it. Any
version of your program will run without errors if NullPool is used -
you'll just get a little overhead in opening more connections which in
the case of file-based sqlite is extremely miniscule. In that case
you can even reuse the same Session object repeatedly across requests
as long as scoped_session is in place to enforce one-thread-per-session.
Randy Syring
On Nov 15, 6:39 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
Thank you so much for your response, I am extremely grateful.
However, I am still getting exceptions thrown from SQLite for sharing
connections across threads.
> The explicit connection as well as the "threadlocal" strategy are all
> unnecessary here. Configuring the sessionmaker() with a bind to a
> plain engine i.e. create_engine('sqlite:///mydb.sql'), and making sure
> sess.close() is called within the WSGI method are all that's needed.
>
> Pattern here is:
>
> Session = sessionmaker(bind=engine)
> sess = Session()
> try:
> < work with session>
> finally:
> sess.close()
>
> of the request works here as well, as I mentioned this is the practice
> that is standard among popular web frameworks such as Pylons.
>
> Pattern here is :
>
> Session = scoped_session(sessionmaker(bind=engine))
> sess = Session()
> try:
> < work with session>
> finally:
> Session.remove()
http://paste.pocoo.org/show/91318/
I wasn't 100% sure where Session should be instantiated. At the
module level or at the request level. Look at the docs and also at
how Pylons does it, it seems that it should be instantiated at the
module/application level. My example above shows it that way, but I
tried it the other way as well, with similar results.
I also tried using a non-contextual session and ended up with the same
results.
Note in the code above that I have added some exception logging to
tell where the exceptions are being generated.
> The
> Session is then garbage collected via asynchronous gc, the connection
> is returned to the pool, and the pool's attempt to rollback() the
> connection before returning to the pool raises the exception. The
> exception does not propagate outwards since it is during garbage
> collection. This is why the program keeps running without overall
> issue (except for your ISAPI plugin which probably cannot handle that
> kind of thing gracefully).
the exceptions are being thrown by my query() call, not during garbage
collection. Out of 100 requests, 14 ended in failures, and all of
them were from my query() call. The log files also give some more
information which I hope will be helpful:
2008-11-15 21:53:51,015 INFO (5548) Connection <sqlite3.Connection
object at 0x01F41AA0> checked out from pool
2008-11-15 21:53:51,015 INFO (5548) Connection <sqlite3.Connection
object at 0x01F41AA0> being returned to pool
<snip>
2008-11-15 21:53:51,515 INFO (5412) start response
2008-11-15 21:53:51,515 INFO (5412) Connection <sqlite3.Connection
object at 0x01F41AA0> checked out from pool
Ok, note above that thread 5548 checks out a connection object. Then
a little while later, thread 5412 also checks out the *same*
connection object (technically, the connection object at that memory
location could have been closed and replaced by a new one, but the
logs don't show the connection being closed and the errors below would
seem to confirm its the same object). My understanding of what you
have said about the SingletonThreadPool is that that should not
happen. Once 5412 checks out the connection object created in 5548,
the following log output is generated:
2008-11-15 21:53:51,515 INFO (5412) Invalidate connection
<sqlite3.Connection object at 0x01F41AA0> (reason:
ProgrammingError:SQLite objects created in a thread can only be used
in that same thread.The object was created in thread id 5548 and this
is thread id 5412)
2008-11-15 21:53:51,515 INFO (5412) Closing connection
<sqlite3.Connection object at 0x01F41AA0>
2008-11-15 21:53:51,515 INFO (5412) Connection <sqlite3.Connection
object at 0x01F41AA0> threw an error on close: SQLite objects created
in a thread can only be used in that same thread.The object was
created in thread id 5548 and this is thread id 5412
2008-11-15 21:53:51,515 INFO (5412) Connection None being returned to
pool
2008-11-15 21:53:51,515 INFO (5412) query exception:
(ProgrammingError) SQLite objects created in a thread can only be used
in that same thread.The object was created in thread id 5548 and this
is thread id 5412 None [{}]
2008-11-15 21:53:51,515 INFO (5412) end response
Are you sure this isn't a problem with pulling the wrong connection
from the pool?
> Still another way to do this is to eliminate the source of the error
> at the pool level - ultimately, the SingletonThreadPool is attempting
> to return the connection to the pool and call rollback() on it, which
> is why the threaded access fails. If you use NullPool, the connection
> is thrown away entirely when closed and nothing is done to it. Any
> version of your program will run without errors if NullPool is used -
> you'll just get a little overhead in opening more connections which in
> the case of file-based sqlite is extremely miniscule. In that case
> you can even reuse the same Session object repeatedly across requests
> as long as scoped_session is in place to enforce one-thread-per-session.
However, it still doesn't seem right that I can't get singleton thread
pooling to work. Am I still doing something wrong?
Michael Bayer
On Nov 15, 2008, at 10:33 PM, Randy Syring wrote:
> Thank you so much for your response, I am extremely grateful.
> However, I am still getting exceptions thrown from SQLite for sharing
> connections across threads.
Unfortuantely I cant reproduce your error now, whereas it was quite
frequent earlier. Attached is a full test script. If the error
you're getting is only with the ISAPI library you're using, that may
be part of the problem.
>
> Are you sure this isn't a problem with pulling the wrong connection
> from the pool?
> I was able to use a NullPool successfully to alleviate my problems.
> However, it still doesn't seem right that I can't get singleton thread
> pooling to work. Am I still doing something wrong?
The SingletonThreadPool in SQLA 0.5 uses Python's threading.local() to
maintain the connection and is extremely simple. SQLA doesn't spawn
any threads and only works within the thread from which is was
invoked, with the huge asterisk that Python's gc.collect() can invoke
cleanup operations in an asynchronous thread, but even in that case
the SingletonThreadPool never associates the connection with the
threading.local() a second time. Just read the source and try
experimenting with it.
If you do test with too many threads, the Python interpreter itself
may be failing in some way. If I run ab against this script with more
than 20 or 30 threads I get a broken socket almost immediately.
Michael Bayer
http://www.modpython.org/pipermail/mod_python/2006-August/021854.html
this person reported problems with threading.local() in conjunction
with mod_python. Its unfortunate that I can't locate any Python bug
reports or anything to confirm it. But it suggests that if this is
the problem, then this patch on your end would resolve the issue:
Index: lib/sqlalchemy/pool.py
===================================================================
--- lib/sqlalchemy/pool.py (revision 5293)
+++ lib/sqlalchemy/pool.py (working copy)
@@ -484,7 +484,8 @@
def __init__(self, creator, pool_size=5, **params):
params['use_threadlocal'] = True
Pool.__init__(self, creator, **params)
- self._conn = threading.local()
+ from _threading_local import local
+ self._conn = local()
self._all_conns = set()
self.size = pool_size
If threading.local() is actually broken in native embedded
environments, that's pretty disturbing.
On Nov 15, 2008, at 10:33 PM, Randy Syring wrote:
>
>
Michael Bayer
On Nov 15, 2008, at 4:53 PM, Randy Syring wrote:
>
> When I run the exact same code using PyISAPIe, I get the following log
> file, which shows many exceptions all propagating up to my application
> and hosing it:
Reading Graham's comments on threading:
There are obviously fine grained details when interfacing native C
code with Python threads. Note that the issue he's discussing there
involves the state of a threading.local() being *lost*, which would
not cause the problem we're seeing. We're seeing a mismatch between
the ID of the thread and the threading object itself which is a much
more severe situation.
I've looked into PyISAPIe. Suffice to say this seems to be an
extremely new project. Their homepage is blank:
http://pyisapie.sourceforge.net/
Bug tracker is empty:
http://sourceforge.net/tracker/?group_id=142454
but they do have a google group, with 20 members and about 40 posts:
http://groups.google.com/group/pyisapie/topics
It would be worthwhile to ask about threading.local() there, but it
seems very possible that some interaction on their end is incompatible
with threading.local(). I certainly wouldn't trust native python-
embedded code with that low of a user base on a production site.
Randy Syring
On Nov 16, 11:30 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
> I've looked into PyISAPIe. Suffice to say this seems to be an
> extremely new project. Their homepage is blank:
http://web.archive.org/web/*/http://pyisapie.sourceforge.net/
But I agree there isn't much of a user base. I have been in contact
with the author of the software and he has been great to work with. I
hope more people will begin to use the software so it can get tested.
> It would be worthwhile to ask about threading.local() there, but it
> seems very possible that some interaction on their end is incompatible
> with threading.local(). I certainly wouldn't trust native python-
> embedded code with that low of a user base on a production site.
thread local model is broken:
http://groups.google.com/group/pyisapie/browse_thread/thread/f82ea13b8481d3eb
I ran that same test with a Python WSGI server and with isapi-wsgi and
I didn't get any id clashes. In addition, I ran the DB test we
referenced in previous posts with the singleton pool against isapi-
wsgi and didn't have any problems there either. The evidence is
pointing at PyISAPIe at this point.
But its really a shame, as PyISAPIe has isapi-wsgi beat pretty bad on
speed. I have emailed Phillip and hopefully he will have some time to
look into the problem.
I agree that running something without much of a user base on a
production system is not a good idea. But as far as I am aware, there
are only two WSGI projects that interface with IIS: PyISAPIe and isapi-
wsgi. Both are relatively new and untested products, but I am stuck
with IIS at this point. If PyISPIe's thread model doesn't get fixed,
I will have to stick with isapi-wsgi.
Thank you so much for all your help on this problem, especially since
it turns out not to have had anything to do with sqlalchemy. I am
truly grateful.