execute() doesn't return from 'delete from ...'

[codeminkey]

1. What versions are you using?

oracle 19c oraceldb 2.41 (note: same issue on my previous version 1.42)

Give your database version.

Also run Python and show the output of:

import sys
import platform

print("platform.platform:", platform.platform())
print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
print("platform.python_version:", platform.python_version())

-->
platform.platform: Linux-6.5.13-1-MANJARO-x86_64-with-glibc2.38
sys.maxsize > 2**32: True
platform.python_version: 3.11.6

And:

import oracledb
print("oracledb.__version__:", oracledb.__version__)

-->
oracledb.__version__: 2.4.1

2. Is it an error or a hang or a crash?

hangs forever; never returns to caller

4. What error(s) or behavior you are seeing?

When I execute a 'delete from ...' statement, the call never returns. seems to hang forever. (I was running this from a cron job, and I later found a dozen or so hung processes). When I run it from the command line and abort it (^c) after a long wait, I see the following trace:

  File "/home/ray/projects/sapt/src/code/dev-tools/tools/tricklepurge/./tricklepurge.py", line 217, in <module>
    db.execute(sql, [ cutoff_with_margin, args.max_purge ] )
  File "/home/ray/projects/sapt/src/code/Utilities/python/sapt/database.py", line 169, in execute
    I.cursor.execute(sql,vals)
  File "/home/ray/.local/lib/python3.11/site-packages/oracledb/cursor.py", line 710, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 441, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 387, in oracledb.thin_impl.Protocol._process_message
  File "src/oracledb/impl/thin/protocol.pyx", line 454, in oracledb.thin_impl.Protocol._receive_packet
  File "src/oracledb/impl/thin/packet.pyx", line 715, in oracledb.thin_impl.ReadBuffer.wait_for_packets_sync
  File "src/oracledb/impl/thin/transport.pyx", line 328, in oracledb.thin_impl.Transport.read_packet
  File "/usr/lib/python3.11/ssl.py", line 1296, in recv
    return self.read(buflen)
           ^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/ssl.py", line 1169, in read
    return self._sslobj.read(len)
           ^^^^^^^^^^^^^^^^^^^^^^
KeyboardInterrupt
^C

In this example, the delete used a sub-query to select record IDs for deletion. It looked like this:

sql = 'delete from sapttransaction where transactionidentifier in (select transactionidentifier from sapttransaction where db_insertion_date < :1 order by db_insertion_date ASC fetch first :2 rows only)',  (2020-08-30 14:30:40.287928,10)

(The vars types are (datetime,int).)

Wondering if the sub-query was confusing something, I tried an alternate version that used a set:

sql = 'delete from sapttransaction where transactionidentifier in (349474275642100914,813345587017079617,1850298851463328399,2727374878893732406,6533479513975217178,7073348519306240979,7268129203190029085,8867470570615788573,9034103756828504661,9166959945835934515)']

The result was the same.

5. Does your application call init_oracle_client()?

no (thin mode)

6. Include a runnable Python script that shows the problem.

Can't share the whole script (proprietary) but it's pretty basic; essentially just

connection = oracledb.connect(dsn=connect_string, user=username, password=password)
cursor = connection.cursor()
sql = 'delete from sapttransaction where transactionidentifier in (select transactionidentifier from sapttransaction where db_insertion_date < :1 order by db_insertion_date ASC fetch first :2 rows only)'
cutoff = datetime.datetime.now() - datetime.timedelta( days=4*365 )
cursor.execute( sql, [cutoff,10] )

Lastly, note that this seems to happen on only one of my three databases (they are all the same version & schema). But I have no idea what difference could cause this behavior. Appears to me that oracle db is expecting a reply that it is not getting. Of course, a 'delete' has no response.

One more note: running this in a VirtualBox VM wiith 1 CPU configured, the CPU pegs to 100% while it is hung.

[cjbj]

The common cause would be that some other DB operation has occurred on the rows but hasn't been committed.

[codeminkey]

Hi, thanks for that but I'm confident that's not what's happening here. For starters, there is only one statement in my script that changes data, and that's the one that's failing. Second, the exact same script is working just fine on my two other databases (which are theoretically identical). There are a couple things that might be significant, however: (1) the failing database (prod) has a huge amount of data while the other two have a much more modest amount (hence, why we're deleting (old) records from prod); and (2) the table I'm deleting from has a trigger, which deletes related records from a couple other tables. The trigger also exists on my dev and test databases, which, again, are not failing, but, together with the large amount of data, I'm wondering if that could effect things somehow. In particular, since deleting on the prod database will take longer (maybe significantly longer), I'm wondering it there's any kind of timeout that could be at play here.

Thanks again!

PS: I am trying to develop a simple test case that I can share but so far I've been unsuccessful at reproducing the issue. Which makes me (again) think that the size of the database might be the issue.

[anthony-tuininga]

I would still check to see if there are any outstanding locks on the database (look at the dba_locks view) that are preventing the statement from succeeding. You can also issue the statement from SQL*Plus and see if the "hang" occurs there, too. You can set the environment variable PYO_DEBUG_PACKETS to any value before running your script. You should see that the client sends the request to the database but the database never responds. The database sends a response back for every request, even if the response doesn't contain information you would normally find useful!

[codeminkey]

Hi, thanks again for the help. I've been able to narrow this down to a single table, which is named in the trigger I mentioned. We've had trouble with this particular table before (on this database) and our DBA told us at the time there was an outstanding lock on it. I think the lock is bogus and was probably abandoned after a failure of some sort. I'm waiting on our DBA to reconfirm that the lock still exists and whether she can delete it (note: I cannot access dba_lock; I assume for lack of rights). So I think that's going to be it and that this is not a bug. I'll follow-up when I know for sure and close the ticket. Thanks again!

[codeminkey]

I have confirmed what I reported yesterday. So I will now close the ticket. Thanks for your help!