When using OCCI deleting runs forever

Question

the same line:

DELETE(SELECT * FROM tablename WHERE id=12)

on SQL Developer runs normally and when using the occi API takes forever.

I have checked that the query "SELECT * FROM tablename WHERE id=12" matches a non empty sets of rows.

More specifically I use the following syntax:

oracle::occi::Statement *deleteStm = con->createStatement("DELETE(SELECT * FROM tablename WHERE id=12)");
oracle::occi::ResultSet *rs = deleteStm->executeQuery();

Answer 1

I suspect that in your case you've simply got uncommitted transaction. It goes that way:

session1                   session2
DELETE ...                                        table/rows is locked

                           SELECT * FROM ...      you will see all data

                           DELETE ...             and now you will wait and wait
                                                  until lock is released

COMMIT;
                           SELECT * FROM ...      now resultset is empty

I strongly encourage to read Data Concurrency and Consistency