search

oracle / python-cx_Oracle

Python interface to Oracle Database now superseded by python-oracledb
https://oracle.github.io/python-cx_Oracle
Other
887 stars 360 forks source link

Does cx_Oracle support dequeue operation and table queries concurrently? #652

Closed patecone closed 1 year ago

patecone commented 1 year ago
  1. What versions are you using?

platform.platform: Linux-4.18.0-372.16.1.el8_6.x86_64-x86_64-with-glibc2.28 sys.maxsize > 2**32: True platform.python_version: 3.6.8 cx_Oracle.version: 8.3.0 cx_Oracle.clientversion: (12, 2, 0, 1, 0)

  1. Describe the problem

my question is about an application I'm trying to build which is supposed to:

a. dequeue message from a single consumer oracle AQ (using cx_Oracle AQ support) b. carry out scheduled tasks that involve querying the database (using APScheduler on top of an asyncio.loop) c. serve some REST requests coming from the outside world (using a simple HTTPServer running in a separate Thread)

the issue I'm facing is that if any of task a or b fires a select statement while dequeue is running - especially if I set DEQ_WAIT_FOREVER - I get the following errors

cx_Oracle.OperationalError: ORA-01013: user requested cancel of current operation [...] cx_Oracle.DatabaseError: ORA-03117: two-task save area overflow

Full traceback example:

27-03-2022 12:04:58.094 castore 3746515 CRITICAL [dsa.fsm ] Eccezione non gestita: Traceback (most recent call last): File "/usr/lib/python3.6/site-packages/engineadm-22.3.34-py3.6-linux-x86_64.egg/dsa/cluster/fsm.py", line 298, in __dispatch block=True) File "/usr/lib/python3.6/site-packages/engineadm-22.3.34-py3.6-linux-x86_64.egg/dsa/cluster/queue.py", line 137, in receive message = queue.deqOne() cx_Oracle.OperationalError: ORA-01013: user requested cancel of current operation 27-03-2022 12:04:58.095 castore 3746515 ERROR [apscheduler.executors.default] Error running job 424ee375515e4b4692444f4b719d73bd Traceback (most recent call last): File "/usr/lib/python3.6/site-packages/apscheduler/executors/asyncio.py", line 43, in callback events = f.result() concurrent.futures._base.CancelledError 27-03-2022 12:04:58.096 castore 3746515 ERROR [sqlalchemy.pool.impl.QueuePool] Exception during reset or similar Traceback (most recent call last): File "/usr/lib64/python3.6/site-packages/sqlalchemy/pool/base.py", line 671, in _finalize_fairy fairy._reset(pool) File "/usr/lib64/python3.6/site-packages/sqlalchemy/pool/base.py", line 858, in _reset pool._dialect.do_rollback(self) File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 502, in do_rollback dbapi_connection.rollback() cx_Oracle.DatabaseError: ORA-03117: two-task save area overflow 27-03-2022 12:04:58.097 castore 3746515 ERROR [apscheduler.executors.default] Job "DsaDispatcher.execute (trigger: interval[0:00:15], next run at: 2022-03-27 12:05:13 CEST)" raised an exception Traceback (most recent call last): File "/usr/lib/python3.6/site-packages/apscheduler/executors/base.py", line 125, in run_job retval = job.func(*job.args, job.kwargs) File "/usr/lib/python3.6/site-packages/DecoratorStateMachine.py", line 210, in objCall rtn = state_var.getFunc(func, self)(self, *args, *kwargs) File "/usr/lib/python3.6/site-packages/DecoratorStateMachine.py", line 143, in wrappd2 retn = funky( args, kwargs) File "/usr/lib/python3.6/site-packages/engineadm-22.3.34-py3.6-linux-x86_64.egg/dsa/cluster/fsm.py", line 1166, in execute health_state = self.health_check() File "/usr/lib/python3.6/site-packages/engineadm-22.3.34-py3.6-linux-x86_64.egg/dsa/cluster/fsm.py", line 254, in health_check expiration=SCT_UPDATE_TIMEOUT // 1000) File "/usr/lib/python3.6/site-packages/engineadm-22.3.34-py3.6-linux-x86_64.egg/dsa/cluster/queue.py", line 147, in emit msg_type = conn.gettype(QueueNameTypes.MESSAGE_Q.value) cx_Oracle.InterfaceError: not connected 27-03-2022 12:10:58.417 castore 3746515 INFO [dsa ] Interruzione del ciclo di analisi DSA

Now I don't need, nor I want, to make the app fully async, all I want is to be able to dequeue messages, either by using a high frequency scheduled task or a while loop, and fire database queries safely.

As you may have noticed in my example the connection is provided by sqlalchemy.pool.impl.QueuePool, but I have already checked that I might use cx_Oracle.SessionPool as connection pooling implementation (supported by SQLAlchemy) so what I'm really questioning is if cx_Oracle does support dequeue operation and queries concurrently.

I haven't tried using cx_Oracle.SessionPool so far because I have a lot of queries all over the app and I'd like to be sure it solves my queue issue before taking the risk of refactoring.

Any comment suggestion much appreciated.

Cristiano

cjbj commented 1 year ago

Fundamentally, each connection can only do one DB operation at a time (see footnote below), so if you want to be doing multiple things you will need to have multiple connections open, and do each operation (listening, sending queries) on different connections. Generally using a SessionPool is a good idea.

Your error sounds like something in the architecture isn't quite right.

Footnote: Oracle Database 23c supports pipelining at the C interface, but this hasn't been exposed to Python. The asyncio support planned for python-oracledb mode will probably be done differently, particularly because python-oracledb Thin mode doesn't use the Oracle Database C libraries.

patecone commented 1 year ago

Fundamentally, each connection can only do one DB operation at a time (see footnote below), so if you want to be doing multiple things you will need to have multiple connections open, and do each operation (listening, sending queries) on different connections.

Your error sounds like something in the architecture isn't quite right.

Thank you @cjbj, following your comment I gave a long good look to the architecture. You were right, something wasn't quite right. Just for the records - hoping it might help somebody playing with Alchemy and Queues - I think the issue was on the connection handling.

I mean, first I chose to leave the ORM framework manage active connections, so I had no control on which connection was used to dequeue a message or to fire a query. This seems to enable some sort of connection sharing/reuse between dequeueing and selecting. Probably SQLAlchemy doesn't trace dequeueing ops so the connection seems available for reuse. This might trigger the exception.

By explicitely acquiring a connection from the pool (even without refactoring with cx_Oracle.SessionPool) I've seen no execption in the last 12 hour.

I think this closes the issue. Ciao