Connections remain in busy connection list and can't be reused in pool

[golubovai]

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

2. Is it an error or a hang or a crash?
   It's an error

3. 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.

4. Does your application call init_oracle_client()?
   Thin mode.

5. 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

[anthony-tuininga]

With some tweaks to the timing I was able to replicate the issue intermittently. I am looking at the best way to address the issue. Thanks for the report and the suggested fix!

[anthony-tuininga]

I have pushed a patch that corrects this issue and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. If you are able to confirm that the patch corrects the issue for you that would be appreciated!

[anthony-tuininga]

This was included in python-oracledb 2.5.0 which was just released.