26.0.x: pool stops handing out connections after the database closes one (background thread dies with StopIteration; failed release() leaks its slot)

[jonatasgomes]

1. What versions are you using?

• python-oracledb 26.0.1 (reproduced below) and 26.0.0 (where we first hit it, in production). 4.0.2 is not affected.
• Thin mode.
• Oracle Autonomous Database, connection.version 23.26.3.3.0, over TCPS.
• Reproduced on macOS arm64 with Python 3.12.8; first seen on Linux aarch64 (glibc 2.39) with Python 3.12.3.

platform.platform: macOS-27.2-arm64-arm-64bit
sys.maxsize > 2**32: True
platform.python_version: 3.12.8
oracledb.__version__: 26.0.1

2. Is it an error or a hang or a crash?

A hang. Once the database or network has closed a pooled connection, the pool can stop handing out connections, and with the default POOL_GETMODE_WAIT every later acquire() blocks forever. In production (26.0.0) this hung our API for 27 hours, until a restart; no exception ever reached the application.

3. What error(s) or behavior you are seeing?

Two separate problems. Both are deterministic with the script below, and neither happens with 4.0.2.

A. The pool's background thread dies. When the pool closes a dropped connection whose network is gone, the thread exits with StopIteration, so the pool never opens another connection:

Exception in thread Thread-2 (_bg_task_func):
Traceback (most recent call last):
  File "src/oracledb/impl/thin/connection.pyx", line 140, in _close
  File "src/oracledb/impl/base/connection.pyx", line 443, in oracledb.base_impl.BaseConnImpl.process_sync_operation
  File "src/oracledb/impl/thin/connection.pyx", line 655, in oracledb.thin_impl.ThinConnImpl._process_sync_operation_sub_op
  File "src/oracledb/impl/thin/protocol.pyx", line 259, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 260, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 250, in oracledb.thin_impl.Protocol._process_round_trip
  File "src/oracledb/impl/thin/protocol.pyx", line 246, in oracledb.thin_impl.Protocol._process_round_trip
  File "src/oracledb/impl/thin/protocol.pyx", line 208, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 183, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 276, in oracledb.thin_impl.Protocol._receive_packet
  File "src/oracledb/impl/thin/packet.pyx", line 728, in oracledb.thin_impl.ReadBuffer.wait_for_packets_sync
  File "src/oracledb/impl/thin/transport.pyx", line 361, in oracledb.thin_impl.Transport.read_packet
  File ".../site-packages/oracledb/errors.py", line 215, in _raise_err
    raise _create_exception(error_num, context_error_message, cause, **args)
oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection
Help: https://python-oracledb.readthedocs.io/en/latest/user_guide/troubleshooting.html#dpy-4011

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File ".../threading.py", line 1075, in _bootstrap_inner
    self.run()
  File ".../threading.py", line 1012, in run
    self._target(*self._args, **self._kwargs)
  File "src/oracledb/impl/thin/pool.pyx", line 614, in oracledb.thin_impl.ThinPoolImpl._bg_task_func
  File "src/oracledb/impl/thin/pool.pyx", line 617, in oracledb.thin_impl.ThinPoolImpl._bg_task_func
  File "src/oracledb/impl/base/connection.pyx", line 452, in oracledb.base_impl.BaseConnImpl.process_sync_operation
  File "src/oracledb/impl/base/connection.pyx", line 448, in oracledb.base_impl.BaseConnImpl.process_sync_operation
StopIteration

B. A failed release() leaks the slot for good. release() raises DPY-4011 (4.0.2 raises too), but the connection is never returned to the pool: pool.busy stays at 1 even after gc.collect(). With 4.0.2 the slot comes back when the connection is garbage collected.

Script output with 26.0.1 (the traceback above printed where marked):

oracledb 26.0.1 | Python 3.12.8 | macOS-27.2-arm64-arm-64bit

1. pool.drop() on a connection whose network is gone
  database 23.26.3.3.0
  [Exception in thread Thread-2 (_bg_task_func): ... StopIteration]
  pool background thread alive: False
  acquire(): DPY-4005: timed out waiting for the connection pool to return a connection (after 10s)

2. pool.release() on a connection whose network is gone
  release(): DPY-4011: the database or network closed the connection
  pool.busy after gc.collect(): 1
  acquire(): DPY-4005: timed out waiting for the connection pool to return a connection (after 10s)

With 4.0.2:

oracledb 4.0.2 | Python 3.12.8 | macOS-27.2-arm64-arm-64bit

1. pool.drop() on a connection whose network is gone
  database 23.26.3.3.0
  pool background thread alive: True
  acquire(): ok

2. pool.release() on a connection whose network is gone
  release(): DPY-4011: the database or network closed the connection
  pool.busy after gc.collect(): 0
  acquire(): ok

Likely cause. BaseConnImpl.process_sync_operation (base/connection.pyx L438-L448) calls generator.throw(e) inside its except BaseException clause and ignores what throw() returns:

• A: ThinPoolImpl._close_connection (thin/pool.pyx L167-L176) swallows the error, so the generator finishes and throw() raises StopIteration. Since that is raised inside the except BaseException handler, the sibling except StopIteration does not catch it, and it escapes process_sync_operation. The drop branch of _bg_task_func (L614-L620) has no handler, unlike 4.0.2's try: conn_impl._close() except: pass (4.0.2 L554-L562).
• B: ThinPoolImpl.return_connection (L478-L489) yields a ReturnToPoolSubOp from its finally. When the error is thrown in, that sub-op is what throw() returns, and it is discarded, so the connection is never returned. Connection._close (connection.py L105-L117) has already set self._impl = None, so __del__ cannot return it either.

Treating the value throw() returns as the next sub-op, and its StopIteration as completion, would fix both. A sketch:

exc = None
while True:
    try:
        sub_op = next(generator) if exc is None else generator.throw(exc)
    except StopIteration as e:
        result = e.value
        break
    exc = None
    if sub_op is not None:
        try:
            self._process_sync_operation_sub_op(sub_op)
        except BaseException as e:
            exc = e

process_async_operation (L403-L413) has the same pattern, so async pools are probably affected too. I have not tested them.

4. Does your application call init_oracle_client()?

No, Thin mode.

5. Include a runnable Python script that shows the problem.

The pool connects through a local TCP forwarder, which the script then cuts, as if the database or network had closed the connection. No schema is needed.

"""A connection pool stops working once the database or network closes a
pooled connection (python-oracledb 26.0.x, Thin mode).

The pool connects through a local TCP forwarder, which is then cut as if the
database or the network had closed the connection. Environment variables:
PYO_USER, PYO_PASSWORD, PYO_HOST, PYO_PORT (1521), PYO_SERVICE_NAME,
PYO_PROTOCOL (tcp or tcps).
"""
import gc
import os
import platform
import socket
import sys
import threading
import time

import oracledb


class Tunnel:
    """Forwards 127.0.0.1:<port> to the database; cut() closes every
    connection made through it so far."""

    def __init__(self):
        self.target = (os.environ["PYO_HOST"], int(os.environ.get("PYO_PORT", "1521")))
        self.pairs = []
        self.lock = threading.Lock()
        self.listener = socket.create_server(("127.0.0.1", 0))
        self.port = self.listener.getsockname()[1]
        threading.Thread(target=self._serve, daemon=True).start()

    def _serve(self):
        while True:
            client, _ = self.listener.accept()
            upstream = socket.create_connection(self.target)
            with self.lock:
                self.pairs.append((client, upstream))
            for src, dst in ((client, upstream), (upstream, client)):
                threading.Thread(target=self._pipe, args=(src, dst), daemon=True).start()

    @staticmethod
    def _close(*socks):
        for s in socks:
            try:
                s.shutdown(socket.SHUT_RDWR)
            except OSError:
                pass
            s.close()

    def _pipe(self, src, dst):
        try:
            while data := src.recv(65536):
                dst.sendall(data)
        except OSError:
            pass
        finally:
            self._close(src, dst)

    def cut(self):
        with self.lock:
            for pair in self.pairs:
                self._close(*pair)
            self.pairs.clear()


def make_pool(tunnel):
    return oracledb.create_pool(
        user=os.environ["PYO_USER"],
        password=os.environ["PYO_PASSWORD"],
        host="127.0.0.1",
        port=tunnel.port,
        service_name=os.environ["PYO_SERVICE_NAME"],
        protocol=os.environ.get("PYO_PROTOCOL", "tcp"),
        ssl_server_dn_match=False,  # tcps only: the certificate is not for 127.0.0.1
        min=0,
        max=1,
        increment=1,
        # The default, POOL_GETMODE_WAIT, makes the final acquire() hang forever.
        getmode=oracledb.POOL_GETMODE_TIMEDWAIT,
        wait_timeout=10_000,
    )


def first_line(exc):
    return str(exc).splitlines()[0]


def background_thread_alive():
    return any(t.name.endswith("(_bg_task_func)") for t in threading.enumerate())


def try_acquire(pool):
    start = time.monotonic()
    try:
        conn = pool.acquire()
        conn.ping()
        print("  acquire(): ok")
        pool.release(conn)
    except oracledb.Error as exc:
        print(f"  acquire(): {first_line(exc)} (after {time.monotonic() - start:.0f}s)")


print("oracledb", oracledb.__version__, "| Python", platform.python_version(), "|", platform.platform())

print("\n1. pool.drop() on a connection whose network is gone")
tunnel = Tunnel()
pool = make_pool(tunnel)
conn = pool.acquire()
print("  database", conn.version)
tunnel.cut()
pool.drop(conn)
time.sleep(2)
print(f"  pool background thread alive: {background_thread_alive()}")
try_acquire(pool)

print("\n2. pool.release() on a connection whose network is gone")
tunnel = Tunnel()
pool = make_pool(tunnel)
conn = pool.acquire()
conn.ping()
tunnel.cut()
try:
    pool.release(conn)
    print("  release(): ok")
except oracledb.Error as exc:
    print(f"  release(): {first_line(exc)}")
del conn
gc.collect()
time.sleep(2)
print(f"  pool.busy after gc.collect(): {pool.busy}")
try_acquire(pool)
sys.stdout.flush()

[anthony-tuininga]

Thanks for the report! I will look into this and get it corrected.