Idle in transaction connections can not be returned to pool

[caijinrong]

Hi Michael,

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? From the code, we can see the connection object seems still in the pool with active status.

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). If not, how can we handle the connection whose status is in "idle in transaction"?

SQLA version: 1.0.11 PostgreSQL version: 9.6 See attachment for test case. Please let me know if you need more information. Thanks.

[Mike Bayer]

hello -

this code is not correct. You can't throw SQLAlchemy's engine and pool into a greenlet without monkeypatching for threading constructs like threading.Lock; they will deadlock in a greenlet. Additionally, psycopg2 supports greening as well using postgresql's async API, eventlet supports this as well and must be enabled:

### VERY TOP OF PROGRAM ###
import eventlet
eventlet.monkey_patch(psycopg=True, thread=True)

output (I haven't taken the time to try to follow the specifics of the test here, however the stack trace no longer occurs when the above patch is in place):

#!

Pool size: 2  Connections in pool: 0 Current Overflow: 0 Current Checked out connections: 2 before sleep dump_table1 connection object id  <function connect at 0x7ff8cee551b8>
2017-04-18 10:00:12,255 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2017-04-18 10:00:12,256 INFO sqlalchemy.engine.base.Engine SELECT test.f1 AS test_f1, test.f2 AS test_f2, test.f3 AS test_f3 
FROM test
INFO:sqlalchemy.engine.base.Engine:SELECT test.f1 AS test_f1, test.f2 AS test_f2, test.f3 AS test_f3 
FROM test
2017-04-18 10:00:12,256 INFO sqlalchemy.engine.base.Engine {}
INFO:sqlalchemy.engine.base.Engine:{}
Pool size: 2  Connections in pool: 0 Current Overflow: 0 Current Checked out connections: 2 before sleep dump_table1 connection object id  <function connect at 0x7ff8cee551b8>
kill success!
Pool size: 2  Connections in pool: 0 Current Overflow: 0 Current Checked out connections: 2 in main dump_table1 connection object id  <function connect at 0x7ff8cee551b8>
in operate_db3

background:

http://eventlet.net/doc/patching.html

[Mike Bayer]

Will the connection returned to the pool if my green thread was killed?

it looks like not, in this case. you'd need to catch the GreenletExit exception here and clean up appropriately.

There's no "bug" here, this is a usage question, so please continue on the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy.

also, to see pool checkins/checkouts:

engine = create_engine('postgres://scott:tiger@localhost/test',pool_size=2, echo=True,echo_pool='debug',pool_recycle=5,max_overflow=0)

I haven't had time to follow the full flow in this test as greenlet stuff can be tricky when monkeypatching, but work with the correct patching to start with otherwise things definitely aren't going to work correctly.