Query timeout when calling from different threads

Question

Server receives http request in servlet, from servlet calls method in ejb component.

public void ejbMethodVariant1(...) {
    //calling stored proc
    ...
    //calling same stored proc
}

public void ejbMethodVariant2(...) {
    //calling stored proc
    ...
    Thread t = new Thread(() -> {
             //calling same stored proc
        });
    t.start();
    try {
        t.join();
    } catch (InterruptedException e){
        ...
    }
}

Stored proc is always the same. "Calling stored proc" means:

1. Getting connection from data source
2. Creating callable statement
3. Executing callable statement
4. Closing statement
5. Closing connection

In variant 1 - all works perfectly, without errors. Connections in first and second call have autoCommit=false.

In variant 2 - first call completes successfully, second - time out after 2 minutes (com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.). Connection in first call has autoCommit=false, in second call have autoCommit=true.

Answer 1

You're starting a new thread which doesn't have the transaction context, security context, etc copied to it. If you want to use a new thread to run the statement consider using the EE Concurrency utilities in Java EE 7.