How to stop gracefully a long running transaction in Hana DB

Question

I am using a hana-client tool from https://www.npmjs.com/package/@sap/hana-client Essentially I have a callback that takes long time so I decide to run it in parallel in Promise.race to throw an Error after 1000 ms(Timeout in async/await)

This won't break the currently running operation in callback, but I hoped that rollback will terminate it once the catch block is reached after 1000 ms. This rollback doesn't seem to work though and it just waits until the callback will be completed. This blocks a table in my DB for quite some time.

My next idea was to use db.abort() from https://help.sap.com/viewer/f1b440ded6144a54ada97ff95dac7adf/2.12/en-US/b98b880fc6a344aa85ef0b471fa19af4.html However this also does not seem to break the long taking callback. Am I doing something wrong?

   try {
      db = await this.acquire();
      db.connection.setAutoCommit(false);
      const wrapper = new HanaClientAsyncWrapper(db);
      await Promise.race([
        callback(wrapper),
        new Promise((_, reject) => setTimeout(
          async () => {
            reject(new Error("Timeout due to a long-running operation"));
            // await db.abort(); <- does not work
          }, 1000,
        )),
      ]);
      await db.commit();
    } catch (err) {
      if (db) {
        await db.rollback();
      }
      throw err;
    } finally {
      if (db) {
        db.connection.setAutoCommit(true);
        await this.release(db);
      }
    }

Answer 1

Stopping active statements during execution may be delayed in HANA. The internal task management in HANA is a "cooperative" multitasking approach, broadly speaking. And some parts of the query execution engine do not check for a cancellation flag often enough. This is usually the case, when checking for such a flag more often would incur a higher performance cost - and the baseline assumption is to not cancel statements.

In case, excessive runtime leads to locks that, in turn, lead to actual application blockages, it may be a good choice to consider using workload management classes for those statement. See the documentation for details on that.