cx_Oracle >=8.2.0 spawns new threads on connection and does not close them on connection release.

[iamdbychkov]

Greetings!

1. What versions are you using?

Database version is 12.1.0.2.0
platform.platform: Linux-4.1.12-124.26.10.el6uek.x86_64-x86_64-with-redhat-6.10-Santiago
sys.maxsize > 2**32: True
platform.python_version: 3.6.15
cx_Oracle.version: 8.2.0
cx_Oracle.clientversion: (12, 1, 0, 2, 0)

2. Is it an error or a hang or a crash?

It's an error, I believe

3. What error(s) or behavior you are seeing?

Acquiring a connection takes a long time and also creates a new thread, which are not closed on connection release.

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

import cx_Oracle

input("New connection?")
conn = cx_Oracle.connect(dsn="main", user="username", password="password")
cursor = conn.cursor()
cursor.execute("alter session set current_schema = new_schema")
while True:
    input("New connection?")
    conn.close()
    conn = cx_Oracle.connect(dsn="main", user="username", password="password")
    cursor = conn.cursor()
    cursor.execute("alter session set current_schema = new_schema")

I’m not sure about SQL, seems like tables are not needed.
If there is no schema change, only the initial connection takes a long time, and no new threads are created on subsequent loop iterations.

Running this script (let’s say we name it “test_threads.py”) and the command `watch -n 1 'ps -eLf | grep "test_threads.py" | wc -l' in a separate terminal will show an increasing number of threads on each iteration, as well as long delays.

This issue is not reproduced in cx_Oracle versions 8.1.0, 8.0.0, 7.3.0.

Initially, we discovered the issue after updating from cx_Oracle 7.3.0 to the latest 8.3.0 and seeing the error “RuntimeError: can’t start new thread” in the logs after application has been running for a while.

Unfortunately, I'm not able to check if the issue is reproduced in oracledb library, hence the issue is here, even though new releases are not planned.

[cjbj]

Thanks for reporting this. However cx_Oracle was renamed/replaced by python-oracledb in 2022, see the release announcement. No further work on cx_Oracle is planned. Please try with the new version and report any issues on the new repo https://github.com/oracle/python-oracledb/issues. If you use the 'thick mode' (ie. with Oracle Client libraries), make sure to test with the same client library version in old & new scenarios. Thanks again.

[iamdbychkov]

Hi Christopher! Totally understandable.

I'll leave the info on the "solution" in case someone is still using cx_Oracle and are facing the same issue. Replacing Oracle Database Client with Oracle Instant Client has fixed the issue for us! :)

[cjbj]

Replacement of the full client with Instant Client isn't necessarily the solution, since they contain the same libraries. What I expect is that you now have a different (newer?) version of the libraries. Do you have version details?