APScheduler Threading design pattern

Question

I'm currently writing an application to schedule some metrics with APScheduler (3.6.0) and python3.6

After 5-6 days running, the scheduler stops without any error. So I thought it could be a ressource / concurrency problem.

I have different schedules like every 15 minutes, every 30 minutes, every hour etc...

My Scheduler is initilized as following and I am only using threads right now :

    executors = {
    'default': ThreadPoolExecutor(60),
    'processpool': ProcessPoolExecutor(5)
}

scheduler = BackgroundScheduler(timezone="Europe/Paris", executors=executors)

My question is the following : when multiple jobs start, passing through the same code part, can this be a potential conception mistake ? Do I need to put some lock then. For example, they use the same code part to connect to a database (same module) and retrieve a result

def executedb(data):
    cursor = None
    con = None

    try:
        dsn = cx.makedsn(data[FORMAT_CFG_DB_HOST], data[FORMAT_CFG_DB_PORT],
                         service_name=data[FORMAT_CFG_DB_SERVICE_NAME])
        con = cx.connect(user=data[FORMAT_CFG_DB_USER], password=data[FORMAT_CFG_DB_PASSWORD], dsn=dsn)

        cursor = con.cursor()
        sql = data[FORMAT_METRIC_SQL]

        cursor = cursor.execute(sql)

        rows = rows_to_dict_list(cursor)


        if len(rows) > 1:
            raise Exception("")

    except Exception as exc:
        raise Exception('')
    finally:
        try:
            cursor.close()
            con.close()
        except Exception as exc:
            raise Exception('')

    return rows

Can this lead to threading concurrency ? And what is the best design pattern to do this ?

Answer 1

If you have multiple threads in your process, then when you connect to the database you should ensure that threaded mode is enabled, like this:

con = cx.connect(user=data[FORMAT_CFG_DB_USER], password=data[FORMAT_CFG_DB_PASSWORD], dsn=dsn, threaded=True)

Without that you run the very real risk of corrupting memory or causing other issues. Perhaps this is the source of your issues?

Another option is to use the new module python-oracledb which always has threaded mode enabled.