Python / cx_Oracle / Oracle hang issue while updating record

Question

I have a class in Python using cx_oracle to make DML in the database. eg method of the class:

def executesimple_dml(self, sql):
    self.cursor.execute(sql)
    self.connection.commit()

DML works well for tables where column headers are defined without quotation marks. however, I have one table where I had to use case sensitive column headers with spaces (defined with quotation marks)

when I run following code it works well:

...
connector.executesimple_dml("update shssdalan_auto_que set action = 'test255' where sw_service_offering = 'SHS-MMSA.SDLAN.SK.BTS LB.WlanAP__w-sk-btslb-0-4-004_SW-SO' ")

but when I run this piece of code it runs forever without an error:

...
connector.executesimple_dml("""update ds_so set "Operation" = 'test3' where "Name" = 'SHS-MMSA.SDLAN.SK.BTS LB.WlanAP__w-sk-btslb-0-4-004_SW-SO'""")

In Oracle, we see that my connection sends correct line of code but its execution takes forever. When I run the same DML directly in Oracle SQL Developer it gets executed instantly. Also, we notices that underscores causes some problem. eg, when I remove underscores from where criteria's ('SHS-MMSA.SDLAN.SK.BTS LB.WlanAPw-sk-btslb-0-4-004SW-SO') it works via Python!

To summarize, the problem exist only for one table defined with quotation marks, only using Python code, only if underscores are included in the where statement. When I use similar code with same underscores ot stated for different table it works well. I know, it's weird...

It should simply process a DML

Answer 1

In Oracle, we see that my connection sends correct line of code but its execution takes forever. When I run the same DML directly in Oracle SQL Developer it gets executed instantly.

The row that you are trying to modify has probably been UPDATEd by the SQL Developer session but that session has not issued a COMMIT or ROLLBACK so the row is locked and the other session is waiting for the lock to be released.

Try running the Python code and if the code is waiting "forever" then go to SQL Developer (or whichever tool you have that has an open session with uncommitted data) and run the COMMIT (or ROLLBACK) command and see if the Python code then suddenly completes. If it does then it was waiting for the lock to be released on the row.