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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
