dpiStmt_execute never returns after dpiConn_breakExecution

[hego-fdc]

The following program creates a long-running statement and in a separate thread cancels it. I would expect that the dpiStmt_execute function returns after the cancellation, but it never returns. When not spawning the thread, the function returns as expected.

#include <dpi.h>
#include <stdio.h>
#include <pthread.h>
#include <unistd.h>

void* breakConn(void *arg) {
  printf("waiting...\n");
  sleep(1);
  printf("canceling\n");
  if(dpiConn_breakExecution((dpiConn*)arg)!=DPI_SUCCESS) {
    printf("failed to cancel\n");
  } else {
    printf("canceled\n");
  }
}

int main(int argc, char** argv) {
  dpiContext* ctx;
  dpiErrorInfo err;
  if(dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &ctx, &err)!=DPI_SUCCESS) {
    printf("failed to create context\n");
    return -1;
  }
  printf("context created\n");
  dpiConn* conn;
  if(dpiConn_create(ctx, ...., NULL, NULL, &conn)!=DPI_SUCCESS) {
    printf("failed to connect\n");
    return -1;
  }
  printf("connected\n");
  dpiStmt* stmt;
  if(dpiConn_prepareStmt(conn, 0, "BEGIN DBMS_LOCK.SLEEP(5); END;", 31, NULL, 0, &stmt)!=DPI_SUCCESS) {
    printf("failed to prepare statement\n");
    return -1;
  }
  pthread_t t;
  pthread_create(&t, NULL, &breakConn, conn);
  printf("statement prepared\n");
  if(dpiStmt_execute(stmt, 0, NULL)!=DPI_SUCCESS) {
    printf("failed to execute statement\n");
    return -1;
  }
  printf("statement executed\n");
  return 0;
}

I'm using the dpi.h from the master branch and I'm using driver version 11.2 both on the client and on the server side (I have also tested with other client driver versions, without any change). Compiler is gcc 8.3.0.

[cjbj]

The first thing I'd try is to put:

DISABLE_OOB=ON

in a sqlnet.ora file on the machine where you are running your application. If this file is new to you, info on where to put it is in doc like https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#optional-oracle-configuration-files

The second thing I'd try is some other (non-PL/SQL statement) long running statement.

[hego-fdc]

Wow, ok, you exactly got it right it seems: Disabling OOB prevents the dpiStmt_execute function from hanging. While it does not cancel the DBMS_LOCK.SLEEP function, it does cancel other long-running queries, which is what I need. Can you explain what might be going on here?

[cjbj]

The OOB setting is just working around a network configuration which is inlining or dropping OOBs. When using 19c client & server, the correct setting will be automatically determined.

I don't know specifics about DBMS_LOCK.SLEEP but I know that way, way back in the past there was an effort to improve the break flag checking in the PL/SQL layer, hence my suspicions when I saw your use. Feel free to log bugs with Oracle Support - but it's probably best to check a newer Oracle version first.

[kubo]

dpiConn_breakExecution doesn't break DBMS_LOCK.SLEEP on Windows. See https://github.com/rsim/oracle-enhanced/issues/763#issuecomment-187744870

[cjbj]

@kubo good reference.

To quote a bit more of the (old) support note that @yahonda gave:

With the DBMS_LOCK.SLEEP package, the sleep means that the session thread is not given any CPU time. Hence no check is made for break packets.

The issue does not reproduce with long running queries, as the session thread is given plenty of CPU time. This enables the checking for break packets.

This issue is specific to DBMS_LOCK.SLEEP and other blocking calls such as DBMS_PIPE.

@krismohan I suspect 18c's call timeout (dpiConn_setCallTimeout) is impacted the same?

[cjbj]

It seems that 18c's call timeout (e.g. dpiConn_setCallTimeout) on OCIStmtExecute() does break DBMS_LOCK.SLEEP, as least with recent DB versions.

[anthony-tuininga]

It appears this question has been answered so will close. Feel free to reopen if you have further questions, however!