Is pconnect thread-safe?

[isphus1973]

It seems that the ibm_db_dbi.pconnect is not thread-safe. When I run a program with some threads, the number of rows in the threaded part of the code returns a random number of rows. The ibm_db_dbi.connect does not have this issue.

Sample code:

import time
import pandas as pd
import threading
import logging
import ibm_db_dbi
from os import getenv
logging.basicConfig(level=logging.INFO)

TIME = 40
M = 40
dsn = getenv('DSN')

sql = "SELECT * FROM TABLE LIMIT {}"

def run_query_p(num=10):
    t0 = time.monotonic()

    conn = ibm_db_dbi.pconnect(dsn, '', '')
    data_length = len(pd.read_sql(sql.format(num), conn))
    conn.close()

    return data_length, time.monotonic() - t0

def run_in_thread():
    logging.info("inside thread")
    data_length, delta_t = run_query_p(10_000)

    # Expects 10_000 results
    logging.info(f"Time elapsed {delta_t}, with data length {data_length}")

if True:
    thread = threading.Thread(target=run_in_thread, daemon=True)

    thread.start()

for _ in range(M):
    print(run_query_p())

time.sleep(TIME)

logging.info("finished.")

data length should be 10,000 but it replies 53

INFO:root:inside thread
(10, 2.3418354999994335)
INFO:root:Time elapsed 2.2440200000019104, with data length 53
(10, 0.9644281000000774)
(10, 0.764582399999199)
(10, 0.7773687000008067)
(10, 0.7674879999976838)
(10, 0.7907990999992762)
(10, 0.7845421999991231)
(10, 0.780439899997873)
(10, 0.7734416999992391)
(10, 0.7764458000019658)

[isphus1973]

It is indeed not thread safe. It uses os.getpid() to return different connections. All threads for the same python process get the same pid, so the same connection.

If one thread closes the connection, the pconnect creates a new connection, but it doesn't restore the cursors from other threads. Normally, it raises an error. But if the rows where queried in read_sql from pandas, it returns partial values

[imavo]

ibm_db_dbi module has ibm_db_dbi.py:threadsafety = 0

According to PEP249 this means "Threads may not share the module.".

See also #284

[isphus1973]

Thanks

[zenflow]

@imavo Is it possible to be made thread safe?
If so I could try to make a simple PR to achieve that.

@bimalkjha Would you be able to review and merge it?

Thanks!

[bimalkjha]

@zenflow Feel free to open PR with your changes. Thanks.