How can I execute a stored procedure with JDBC / jTDS without using a transaction?

We run a website written in Java that uses JDBC with jTDS to access an SQL Server database.

Our database contains a complex stored procedure that typically takes 10 minutes to run. The stored procedure works fine if we execute it directly (say from SQL Server Management Studio) because it does not run in a transaction. But if we execute it using jTDS then it locks up the entire website for 10 minutes. This happ开发者_开发知识库ens because jTDS runs it in a transaction, and so all the website requests are on hold waiting for the transaction to finish.

For example, the following locks up the website due to the transaction:

Connection connection = DriverManager.getConnection("jdbc:jtds:sqlserver://example/example");
CallableStatement callableStatement = connection.prepareCall("exec dbo.procTest");
callableStatement.execute();

Is there any way that we can run a stored procedure using JDBC / jTDS without it running in a transaction?

Please note that calling this on the jTDS connection is not valid:

connection.setTransactionIsolation(Connection.TRANSACTION_NONE);

That throws an exception stating that the Connection.TRANSACTION_NONE parameter is not supported by jTDS.

EDIT: I probably could have asked the question better: the core problem is not the transaction itself, the problem is that the transaction causes database locks to be held for 10 minutes. Either I need to get rid of the transaction, or I need locks released during the transaction.

In our case, the StoredProcedure had to be called, was throwing an exception when called from jtds:

Cannot perform a backup or restore operation within a transaction.

What I found @ https://communities.bmc.com/docs/DOC-66239 is exec call need to be prepend with "SET IMPLICIT_TRANSACTIONS OFF;"

here's what spring-based snippet looked like:

try {
  jdbcTemplate.getJdbcOperations.execute("SET IMPLICIT_TRANSACTIONS OFF;")
  //noinspection ConvertExpressionToSAM   // we have 1.6 code version
  jdbcTemplate.getJdbcOperations.call(new CallableStatementCreator {
    override def createCallableStatement(con: Connection): CallableStatement = {
      val callableStatement = con.prepareCall(
        s""" EXEC dbo.RestoreLatestCopy
           |      @ID = ?
           |""".stripMargin)
      callableStatement.setInt(1, getMappedRestoreId(fromDbName))
      callableStatement
    }
  }, parameters ++ rsParams)
} catch {

Calling Connection#close() or Connection#commit() should submit and end the transaction. Do you close DB resources like Connection in the finally block?

I ended up using the following ugly workaround. I'm still interested if anyone can explain how I might do this properly; but the workaround will do for the moment.

The database is running on the same machine on the webserver, so I am able to use the standard SQL Server command line tools to run the stored procedure. The Java code to trigger it is:

    try {
        Process process = Runtime.getRuntime().exec("sqlcmd -E -d \"example\" -Q \"EXEC dbo.procTest;\"");
        process.waitFor();
    } catch (IOException e) {
        // Handler here
    } catch (InterruptedException e) {
        // Handler here
    }

So exactly the same stored procedure is run — the difference is that the webserver doesn't lock up because sqlcmd isn't running it in a single transaction. Yes it's ugly... but it's the only option I know of that works!