Oracledb 2.4.1 Error DPY-4005 occurs when program works for a some time

[xsemashko]

1. What versions are you using? Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production platform.platform: Linux-4.18.0-553.el8_10.x86_64-x86_64-with-glibc2.28 sys.maxsize > 2**32: True platform.python_version: 3.12.3 oracledb.version: 2.4.1

3. Is it an error or a hang or a crash? Error

4. What error(s) or behavior you are seeing? At the beginning program works fine for a first ~1-2 minutes or 1-3 pool.acquire() requests. Then error "DPY-4005: timed out waiting for the connection pool to return a connection" occurs. When I check pool on db with "select * from v$session where MACHINE = 'server name' and USERNAME = 'username'" I see 5 free connections from pool, it's fine.

[cjbj]

Creating a pool in one process and trying to use it in others isn't going to work.

[xsemashko]

Thank you for answer. I made this issue because previosly I made this code on Python 3.6.8 oracledb 1.1.1 and it works fine but with cursor leaks and I updated on new version. In result no cursor leaks but pool aquire errors. As I understood it's just luck that it works on oracledb 1.1.1.

[cjbj]

@xsemashko what are you trying to parallelize and save resources on?

[xsemashko]

@cjbj I have high load app which read data from IBM MQ and every messange from it processing in new process. During processing every process sends 1-3 fast (<1 sec) requests to DB. And I considered pool as a good idea for save resouces on connection and as benefit got pool's session reconnect features. But maybe better solution will be use pool with 1 session for every process and start pool async at the beginning of every process if multiprocessing for global pool not supported.

[cjbj]

Getting the availability benefits of pooling is a good idea. And if you need the additional "high availability" e.g Oracle's TAC, then you can get that by using a pool in Thick mode (and connecting to an appropriately enabled database service).

I assume your processes stay open and handle a number of messages? For this use case, try single-connection pools as you suggest. Perhaps use a min of 0, depending whether connections are frequently used, how memory sensitive your DB server is to having multiple connections open, and whether you can live with the cost of recreating a connection if it gets closed.

DRCP might also be an option.

Finally, I would be curious if the new (Thin mode) Pipeline functionality could benefit the "1-3 fast" database statements that you need to execute?

[xsemashko]

No, every process processing only 1 message then it eliminates. During processing it can sends 1-3 db requests it depends of request type. Ok, thanks. I will look into the pipeline functionality.

[cjbj]

That kind of single-use process is not great for performance and scalability, because it means frequent connection. This means the DB has to create a process, allocate session & memory, and authenticate. And then close it all down when the app process finishes. This is relatively expensive. If you can't reuse processes (perhaps via multi-threading), then consider connecting via DRCP, which at least will remove the overhead of process creation.

[xsemashko]

Ok, thank you for help.

[xsemashko]

@cjbj Tell me please. What do you mean about this method? I used shared class object where init is pool creation and every class method are pool.acqure() and make request to db. In my prototype code it looks like this:

import oracledb, time, traceback
from multiprocessing import Process
import concurrent.futures as pool
from itertools import repeat
import multiprocessing.managers as m

class MyManager(m.BaseManager):
    pass

class dbworker:
    def __init__(self):
        self.ora_pp = oracledb.PoolParams(min=5, max=10, increment=0)
        self.ora_pool = oracledb.create_pool(user='', password="", host='', port=, service_name="", params=self.ora_pp, getmode=oracledb.POOL_GETMODE_TIMEDWAIT)
        time.sleep(1)
        self.ora_pool.wait_timeout=4000

    def make_select(self,record):
        try:
            with self.ora_pool.acquire() as conn:
                with conn.cursor() as cur:
                    print('connected')
                    select = f"select ID from ows.acnt_contract where id = '{record}'"
                    cur.execute(select)
                    data = cur.fetchone()
                return(data)
        except:
            print('disconnected')
            print(traceback.format_exc())

def oracle_processing(dbw):
    record = 5
    data = dbw.make_select(record)
    return(data)

def inqBalanceList(dbw):
    data = oracle_processing(dbw)
    print(data)

def getter():
    MyManager.register("dbworker", dbworker)
    manager = MyManager()
    manager.start()
    dbw = manager.dbworker()

    while 1:
        producer_process = Process(target=inqBalanceList, args=[dbw])
        producer_process.start()
        time.sleep(3)

if __name__ == '__main__':
    p = Process(target=getter)
    p.start()

Looks like it can works. I just can share this dbw object between my sub processes.

[cjbj]

Can you share a runnable testcase, including SQL to create the schema?

Also, please don't do f"select ID from ows.acnt_contract where id = '{record}'" since this is a security risk and also impacts performance and scalability - use a bind variable.

[xsemashko]

@cjbj Yes, sure. In my usual projects I use bind vars, this code just for example and because I dont care about it. SQL code

create user pool_test identified by password;
grant create session to pool_test;
grant create table to pool_test;
alter user pool_test quota unlimited on users;
create table test_table (
    ID integer
);
insert into test_table values (1);
insert into test_table values (2);
commit;

Python code

import oracledb, time, traceback
from multiprocessing import Process
import multiprocessing.managers as m

class MyManager(m.BaseManager):
    pass

class dbworker:
    def __init__(self):
        self.ora_pp = oracledb.PoolParams(min=5, max=10, increment=0)
        self.ora_pool = oracledb.create_pool(user='pool_test', password="", host='', port=, service_name="", params=self.ora_pp, getmode=oracledb.POOL_GETMODE_TIMEDWAIT)
        time.sleep(1)
        self.ora_pool.wait_timeout=4000

    def make_select(self,record):
        try:
            with self.ora_pool.acquire() as conn:
                with conn.cursor() as cur:
                    print('connected')
                    select = f"select * from pool_test.test_table where id = '{record}'"
                    cur.execute(select)
                    data = cur.fetchone()
                return(data)
        except:
            print('disconnected')
            print(traceback.format_exc())

def oracle_processing(dbw,record):
    data = dbw.make_select(record)
    return(data)

def inqBalanceList(dbw,record):
    data = oracle_processing(dbw,record)
    print(data)

def getter():
    MyManager.register("dbworker", dbworker)
    manager = MyManager()
    manager.start()
    dbw = manager.dbworker()

    while 1:
        producer_process = Process(target=inqBalanceList, args=[dbw,1])
        producer2_process = Process(target=inqBalanceList, args=[dbw,2])
        producer_process.start()
        producer2_process.start()
        time.sleep(3)

if __name__ == '__main__':
    p = Process(target=getter)
    p.start()

[cjbj]

@xsemashko thanks.

[xsemashko]

@cjbj Hi. I checked this on my stress testing stand and it works good. But some times, maybe once a day i got error below. It haven't any effect on my app but I can't understand why this error occurs.

Exception ignored in: <function Connection.del at 0x7f5fb49c0900> Traceback (most recent call last): File "/home/way4/.local/lib/python3.12/site-packages/oracledb/connection.py", line 569, in del self._impl.close(in_del=True) File "src/oracledb/impl/thin/connection.pyx", line 402, in oracledb.thin_impl.ThinConnImpl.close File "src/oracledb/impl/thin/protocol.pyx", line 156, in oracledb.thin_impl.Protocol._close File "src/oracledb/impl/thin/protocol.pyx", line 186, in oracledb.thin_impl.Protocol._close File "src/oracledb/impl/thin/pool.pyx", line 566, in oracledb.thin_impl.ThinPoolImpl._return_connection File "src/oracledb/impl/thin/pool.pyx", line 567, in oracledb.thin_impl.ThinPoolImpl._return_connection File "src/oracledb/impl/thin/pool.pyx", line 319, in oracledb.thin_impl.BaseThinPoolImpl._return_connect> ValueError: list.remove(x): x not in list

[anthony-tuininga]

@xsemashko, python-oracledb 2.5 was just released which addressed a number of pool issues. Can you try with that version and let me know if you are still getting the same problem?

[xsemashko]

@anthony-tuininga Hi. Looks like upgrade helped.