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.

5. Does your application call init_oracle_client()?
   NO

6. Include a runnable Python script that shows the problem.
   Here I made 2 examples which doesn't have any sense but shows structure of my application. First example with concurrent.futures second just with multiprocessing. I acquire connection and use it inside current process and declare pool as global and in my oppinion it must work.

First example

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

ora_pp = oracledb.PoolParams(min=5, max=10, increment=0)
ora_pool = oracledb.create_pool(user='', password="", host='', port=, service_name="", params=ora_pp, getmode=oracledb.POOL_GETMODE_TIMEDWAIT)
#Time for pool creation. It dial with DB futures
time.sleep(1)
ora_pool.wait_timeout=4000

def parallel_oracle_processing(record,i):
	try:
		with 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 inqBalanceList():
	data_list = range(0, 5)
	records = [5,6,8,11,14]
	for record in records:
		with pool.ThreadPoolExecutor(max_workers=5) as executer:
			res = executer.map(parallel_oracle_processing, repeat(record), data_list)
		results = list(res)
		print(results)

def getter():
	while 1:
		producer_process = Process(target=inqBalanceList)
		producer_process.start()
		time.sleep(3)

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

Second example

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

ora_pp = oracledb.PoolParams(min=5, max=10, increment=0)
ora_pool = oracledb.create_pool(user='', password="", host='', port=, service_name="", params=ora_pp, getmode=oracledb.POOL_GETMODE_TIMEDWAIT)
#Time for pool creation. It dial with DB futures
time.sleep(1)
ora_pool.wait_timeout=4000

def oracle_processing():
	record = 5
	try:
		with 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 inqBalanceList():
	data = oracle_processing()
	print(data)

def getter():
	while 1:
		producer_process = Process(target=inqBalanceList)
		producer_process.start()
		time.sleep(3)

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

[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()