Spring @Transactional timeout does not work as expected with DB2

Question

We have a spring application which integrates with a DB2 (LUW) database.

In a specific flow we have a method annotated with @Transactional(timeout=60)

On database heavy load we have observed that the above timeout of 60 seconds fails to throw an exception on time. It does it only when the database processing finishes either successfully or with an error.

The failing messages are like the one below:

2020-02-21 18:45:32,463 ERROR ... Transaction timed out: deadline was Fri Feb 21 18:40:14 EET 2020

Notice that the exception was thrown after the resources got released by the databases, with a lock timeout error in the specific case, about 5 minutes later than I would expect due to the configured transaction timeout.

I tried to reproduce this behaviour by causing manually a delay in the database. Specifically, I call the sleep DB2 procedure from my app, for a period that is longer than the configured transaction timeout. My tests have the same result, the exception is thrown only after the sleep operation ends successfully.

I wanted to check a similar scenario with another database so I created a simple Spring boot project with 2 different profiles, one for DB2 and one for Postgres. Running this example I observe similar behaviour for DB2, i.e. transaction timeout does not cause any error or it does it only after the sleep time configured for DB2 (30s), which is larger than the configured transaction timeout (10s), ends.

On the contrary Postgres behaviour is more or less what I would expect. Connection with the DB ends with an exception the exact moment that the transaction timeout time has passed (10s), without waiting the sleep operation to finish (30s).

The example project is here. A sample of what described here follows:

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class DemoService {

    @Autowired
    private DemoRepository repository;

    @Transactional(timeout = 10)
    public void sleep() {
        repository.sleep();
    }
}

package com.example.demo;

public interface DemoRepository {
    void sleep();
}

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
@Profile("db2")
public class Db2DemoRepository implements DemoRepository {

    @Autowired
    private JdbcTemplate template;

    @Override
    public void sleep() {
        template.execute("call SYSIBMADM.DBMS_ALERT.SLEEP(30)");
    }
}

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
@Profile("postgres")
public class PostgresDemoRepository implements DemoRepository {

    @Autowired
    private JdbcTemplate template;

    @Override
    public void sleep() {
        template.execute("select pg_sleep(30);");
    }
}

I guess that transaction timeout sets the query timeout in Postgres and fails to do so in DB2. Also I have tried with several values for the following DB2 configuration properties, without any luck: timerLevelForQueryTimeOut, interruptProcessingMode, queryTimeout.

So to my questions:

1. Does the way that I am trying to reproduce the problem and test the several DBs makes sense or am I missing something?
2. This is more important,: Is there a way to make DB2 connection fail the exact moment the transaction time out reaches its limit?

Answer 1

Since there has been a while since I posted the question and no one posted an answer I ll move my findings from the edited section of my question, here:

The solution seems to be setting DB2 config property queryTimeoutInterruptProcessingMode=2 (and not interruptProcessingMode as I originally thought)

example: jdbc:db2://localhost:50000/demo:queryTimeoutInterruptProcessingMode=2;

With this modification, an exception is thrown when the transaction timeout ends. I would like to hear an opinion from the experts though. For example, is it safe to modify the specific property? What is the impact?