On 04/18/2017 10:19 PM, JinRong Cai wrote:
Hi:
SQLA is used in my python project. And in my code, I have the green
thread used. And the green thread was killed in some of my code. After
the the thread was killed, and the transaction was just began. And the
database connection was still in postgresql server side with status
"idle in transaction". This caused my connection was exhausted in the pool.
Traceback (most recent call last): File
"/usr/lib/python2.7/dist-packages/eventlet/hubs/hub.py", line 457, in
fire_timers timer() File
"/usr/lib/python2.7/dist-packages/eventlet/hubs/timer.py", line 58, in
*call* cb(/args, *kw) File
"/usr/lib/python2.7/dist-packages/eventlet/greenthread.py", line 214, in
main result = function(*args, *kwargs) File "/tmp/test.py", line 48, in
operate_db query = session.query(Test).all() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2588,
in all return list(self) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2736,
in *iter* return self._execute_and_instances(context) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2749,
in _execute_and_instances close_with_result=True) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2740,
in _connection_from_session /*kw) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 905,
in connection execution_options=execution_options) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 910,
in _connection_for_bind engine, execution_options) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 334,
in _connection_for_bind conn = bind.contextual_connect() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2039,
in contextual_connect self._wrap_pool_connect(self.pool.connect, None),
File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line
2074, in _wrap_pool_connect return fn() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 376, in
connect return _ConnectionFairy._checkout(self) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 714, in
_checkout fairy = _ConnectionRecord.checkout(pool) File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 480, in
checkout rec = pool._do_get() File
"/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 1054, in
_do_get (self.size(), self.overflow(), self._timeout)) TimeoutError:
QueuePool limit of size 2 overflow 0 reached, connection timed out,
timeout 30
Questions:
1. When will the connection returned to the pool?
In my understanding, when the rollback/commit was listened, the
connection was returned.
>
> 2. Will the connection returned to the pool if my green thread was
killed?
>
if you kill a greenlet, that doesn't imply any action with the
connection pool. You should catch GreenletExit in your greenlet that
expects to be killed and end the transaction.
From the code, we can see the connection object seems still in the pool
with active status.
Also there is no greenexit exception here , if just kill the green
thread safely.
why not ? Python doesn't have many options for control flow. if a
series statements is to be interrupted, there needs to be an exception.
This is exactly how "kill" works:
http://eventlet.net/doc/modules/greenthread.html#eventlet.greenthread.kill
"Terminates the target greenthread by raising an exception into it.
Whatever that greenthread might be doing; be it waiting for I/O or
another primitive, it sees an exception right away."
3. Will the connection returned to the pool if the db transaction(id in
transaction) was rolled back by my
database(idle_in_transaction_session_timeout).
no because there is no event system in the Python DBAPI that would alert
something in SQLAlchemy to the fact that the connection status has
changed. You must invoke code to make anything happen.
If not, how can we handle
the connection whose status is in "idle in transaction"?
you need to roll back transactions when you no longer need them.
I also kill the database transaction process in PG server, but the
connection still not returned to the pool.
this will have no effect because there is no event generated from the
server to the client.
The code was updated with Monkey patching.
Thanks.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
