Broken connections in pool become busy indefinitely · Issue #221 · oracle/python-oracledb (original) (raw)

1. What versions are you using?

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

DEBUG:    platform.platform: Linux-5.15.0-1042-azure-x86_64-with-glibc2.35
DEBUG:    sys.maxsize > 2**32: True
DEBUG:    platform.python_version: 3.10.9
DEBUG:    oracledb.__version__: 1.4.0

1. Is it an error or a hang or a crash?
   Hang (acquiring a connection is not possible if all connections are busy)
2. What error(s) or behavior you are seeing?

Some network interruptions cause pool connection(s) to remain in busy list indefinitely.
Unfortunately I was unable to simulate the network interruption that reproduces the issue.

1. Does your application call init_oracle_client()?

No

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

Following call is made every 10s:

await oracleStorage.ping()

class AsyncHelper: @staticmethod async def execute_io_bound(func: Callable[..., T], *args, **kwargs) -> T: """Execute an io-bound (sync) function in a separate thread pool so it does not block the asyncio thread."""

    loop = asyncio.get_running_loop()
    # default asyncio executor is a ThreadPoolExecutor, appropriate for io-bound tasks like DB queries.
    return await loop.run_in_executor(None, functools.partial(func, *args, **kwargs))

class OracleStorage(): def init(self): self._pool = None

@property
def pool(self) -> oracledb.ConnectionPool:
    if self._pool is None:
        self._connect()

    return self._pool

def _connect(self):
    logging.debug(f"oracledb.__version__: {oracledb.version}")

    # Create a connection pool
    self._pool = oracledb.create_pool(
        connection_id_prefix="firewall-api_",
        dsn="...", # DSN with ewallet and failover options
        user="...",
        password="...",
        min=1,
        max=1,
        getmode=oracledb.POOL_GETMODE_TIMEDWAIT,
        wait_timeout=30000,
        purity=oracledb.PURITY_NEW,
        expire_time=1,
        timeout=900,
    )

    logging.info("Connected to Oracle DB")

async def ping(self, timeout=900):
    return await AsyncHelper.execute_io_bound(self._ping_sync, timeout=timeout)

def _ping_sync(self, timeout=900):
    with self.pool.acquire() as connection:
        prev_timeout = connection.call_timeout
        connection.call_timeout = timeout
        connection.ping()
        connection.call_timeout = prev_timeout