Connections remain in busy connection list and can't be reused in pool · Issue #392 · oracle/python-oracledb (original) (raw)

1. What versions are you using?
   Oracle Database 19c

>>> import sys
>>> import platform
>>>
>>> print("platform.platform:", platform.platform())
platform.platform: Windows-10-10.0.19045-SP0
>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True
>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.12.3
>>>

>>> import oracledb
>>> print("oracledb.__version__:", oracledb.__version__)
oracledb.__version__: 2.4.1

1. Is it an error or a hang or a crash?
   It's an error
2. What error(s) or behavior you are seeing?
   When using POOL_GETMODE_TIMEDWAIT and intensive concurrent operation with acquiring and closing connections, during which ERR_POOL_NO_CONNECTION_AVAILABLE is occured sometimes where is nonzero probability to create busy connection not used by any coroutine. This connection can't be used anymore because no one will close it.
   Sequence begin from block in BaseThinPoolImpl._return_connection_helper which pushes closed connection to waiting request.
   Just after _return_connection finished timeout in _acquire_helper occurs, and CancelledError error is propagating into acquire, and coroutine don't get connection which remains in busy list.
3. Does your application call init_oracle_client()?
   Thin mode.
4. Include a runnable Python script that shows the problem.
   This test failed:

async def test_5530(self):
        "5529 - test create timeout action"
        proc_name = test_env.get_sleep_proc_name()
        async def work(pool: oracledb.AsyncConnectionPool):
            for i in range(100):
                conn = None
                try:
                    conn = await pool.acquire()
                    async with conn.cursor() as cursor:
                        await cursor.callproc(proc_name, [0.2])
                except Exception as e:
                    continue
                finally:
                    if conn is not None:
                        await conn.close()
        pool = test_env.get_pool_async(min=0, max=60, increment=1, wait_timeout=100, getmode=oracledb.POOL_GETMODE_TIMEDWAIT)
        tasks = [asyncio.create_task(work(pool)) for _ in range(15)]
        await asyncio.wait(tasks)
        self.assertEqual(pool.busy, 0)

I suggest this fix in AsyncThinPoolImpl._acquire_helper:

async def _acquire_helper(self, PooledConnRequest request):
        """
        Helper function for acquiring a connection from the pool.
        """
        async with self._condition:
            try:
                await self._condition.wait_for(request.fulfill)
            except asyncio.CancelledError:
                if not request.completed:
                    raise
            finally:
                request.waiting = False
            if not request.completed:
                errors._raise_err(errors.ERR_POOL_NO_CONNECTION_AVAILABLE)
            return request.conn_impl

Probably synchronous version also is affected