[cx-oracle-users] Another Issue in oracledb to cx_Oracle Migration: threading and mutex implementation

[Wong W. Meng-R. <r3...@fr...>]

Hello there,

Below is the message I posted up when I saw the issue of multiple oracle connection object is used in my application whereby the execute(SQL update) and commit() are done by different connection object, causing the commit doesn't commit the SQL update. I have experimented it out and confirmed the case. 

Is there more documentation I can refer to in order to migrate my application in old model using oracledb and mutex to the new model with cx_Oracle? I see some argument can be pass in when we do cx_Oracle.connect()? I think the old model cannot work for cx_Oracle. An example of the RMI old code is like below. I am expecting my application server to have multiple instances of oracle connection and when a client submits a API to the server, the server should use the same Oracle connection object through the entire API, so that what I saw wouldn't happen. 

Thanks in advance for your input.  

class DbQueue(Queue.Queue):
    def __init__(self, maxsize):
        Queue.Queue.__init__(self, maxsize)

    def showQue(self):
        self.mutex.acquire_lock()
        try:
            q=[]
            for item in self.queue:
                q.append((item[0],item[1], item[4]))
        except:
            pass
        self.mutex.release_lock()
        return q 

class DbAccessMgr( TComponent.Server ):
    """Component DbAccessMgr """

    # =========================================================================
    class ConnQueue:
        """"Queue of transactions, one for each database connection."""

        def __init__( self ):
          self.owner = None
          self.lastSQL = None
          self.queue = Queue.Queue(0)   # infinite-size queue

        def setOwner( self, owner ):
          self.owner = owner
          self.lastSQL = None

        def getOwner( self ):
          return self.owner

        def get( self ):
          nextSQL = self.queue.get()
          self.lastSQL = nextSQL[:2]
          return nextSQL

        def put( self, t ):
          self.queue.put(t)
         
        def getLastSQL(self):
          return self.lastSQL

    #    # =========================================================================
    class OutputBuffer:
        """Output buffer used to transfer database transaction result from the
           database processing thread to the API client thread"""

        def __init__( self ):
          self._buffer = None
          self._lock = thread.allocate_lock()
          self._ready = condition.event()

        def post( self, buf ):
          self._lock.acquire()
          self._buffer = buf
          self._lock.release()
          self._ready.post()

        def clear( self ):
          self._lock.acquire()
          self._buffer = None
          self._lock.release()
          self._ready.clear()

        def get( self ):
          self._ready.wait()
          return self._buffer

Regards,
Wah Meng


GThomas,

>
> I am thinking my API might be using different connection objects to do the cursor.execute() and connection.commit(), respectively however the likelihood is low, as the old code using oracledb was working well.
>

I'm sure this is the case. You should use cursor.connection.commit()
for commiting, not the other connection object!
Or put an "assert conn == cursor.commit" somewhere...

Ok I will work on based on this clue. I am thinking my application should use the same connection object throughout a single API. An API can execute x number of SQL query/insert/update/delete statements and if no exception is found, commit is issued at the end, otherwise rollback is issued. What I don't understand is the framework did not change, while I convert oracledb to cx_Oracle, so I wonder how each SQL in the same API transaction could be utilizing different connection objects. 

Another behavior I need to check is the threadSafe mode. I need to understand more on that option as I did not look into that aspect in this migration exercise. 

I only did one change i.e. adding on "threaded=True" to "cx_Oracle.connect()" when I spawn off multiple threads creating the connection objects. 

I will revert after researching and testing. Thanks!

Regards,
Wah Meng