Skip to content

connection record not immediately checked back in when connection fails after failed checkout event handler #3419

Closed
@sqlalchemy-bot

Description

@sqlalchemy-bot
Collaborator

Migrated issue, originally created by Michael Bayer (@zzzeek)

from sqlalchemy import event
import mock
import sys
import sqlalchemy as sa

engine = sa.create_engine(
    "postgresql://scott:tiger@localhost/test",
    pool_size=1, max_overflow=0, pool_timeout=0, pool_recycle=3600)


@event.listens_for(engine, 'checkout')
def handle_checkout_event(dbapi_con, con_record, con_proxy):
    try:
        with dbapi_con.cursor() as cur:
            cur.execute("SELECT 1")
            cur.fetchone()
    except Exception:
        raise sa.exc.DisconnectionError()

# act as though the DB is turned off
conn = engine.connect()
dbapi_conn = conn.connection.connection
conn.close()

dbapi_conn.close()


def shutdown_backend():
    raise dbapi_conn.OperationalError("closed the connection")

patcher = mock.patch.object(engine.pool, "_creator", shutdown_backend)
patcher.start()

try:
    with engine.begin() as conn:
        pass
except sa.exc.OperationalError as e:
    print >>sys.stderr, "Got an (expected) error: ", e

# sys.exc_clear()

try:
    with engine.begin() as conn:
        pass
except sa.exc.OperationalError as e:
    print >>sys.stderr, "Got an (expected) error: ", e

assert True



the pool checkout fails on the second run because the single ConnectionRecord hasn't been checked in.

diff --git a/lib/sqlalchemy/pool.py b/lib/sqlalchemy/pool.py
index 0a4cdad..b38aefb 100644
--- a/lib/sqlalchemy/pool.py
+++ b/lib/sqlalchemy/pool.py
@@ -732,7 +732,13 @@ class _ConnectionFairy(object):
                 pool.logger.info(
                     "Disconnection detected on checkout: %s", e)
                 fairy._connection_record.invalidate(e)
-                fairy.connection = fairy._connection_record.get_connection()
+                try:
+                    fairy.connection = \
+                        fairy._connection_record.get_connection()
+                except:
+                    with util.safe_reraise():
+                        fairy._connection_record.checkin()
+
                 attempts -= 1
 
         pool.logger.info("Reconnection attempts exhausted on checkout")

Activity

sqlalchemy-bot

sqlalchemy-bot commented on May 14, 2015

@sqlalchemy-bot
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

  • Fixed bug where in the case that a pool checkout event handler is used
    and the database can no longer be connected towards, that the checkout
    handler failure is caught, the attempt to re-acquire the connection
    also raises an exception, but the underlying connection record
    is not immediately re-checked in before the exception is propagated
    outwards, having the effect that the checked-out record does not close
    itself until the stack trace it's associated with is garbage collected,
    preventing that record from being used for a new checkout until we
    leave the scope of the stack trace. This can lead to confusion
    in the specific case of when the number of current stack traces
    in memory exceeds the number of connections the pool can return,
    as the pool will instead begin to raise errors about no more checkouts
    available, rather than attempting a connection again. The fix
    applies a checkin of the record before re-raising.
    fixes connection record not immediately checked back in when connection fails after failed checkout event handler #3419

4a0e51e

sqlalchemy-bot

sqlalchemy-bot commented on May 14, 2015

@sqlalchemy-bot
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
added this to the 1.0.5 milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions

          connection record not immediately checked back in when connection fails after failed checkout event handler · Issue #3419 · sqlalchemy/sqlalchemy