here is an interesting thread related to this subject:
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:
>
>
> On Nov 15, 6:39 pm, Michael Bayer <[EMAIL PROTECTED]> 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()
>>
>> Alternatively, as I noted previously
>> inhttp://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_
>> ...
>> , 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()
>
>
> I have updated my code per your directions, I believe:
>
> 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).
>
> Well, I am not sure about the details, but the log messages say that
> 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.
>
> 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?
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
