If you’ve ever encountered this error with the Microsoft SQL Server JDBC Driver, you may be left scratching your head, wondering a) what it means, and b) why it is occurring. I’ll tackle these in order… But if you’re not interested in the details, skip the next two paragraphs.
Transactions, excluding XA transactions (which are different beasts entirely), are scoped to a connection. Once a connection is put in a transaction, either through a call to Connection.setAutoCommit(false) followed by some DDL or DML, or through execution of a BEGIN TRANSACTION statement, everything done on that connection should happen within that transaction until it is committed or rolled back. SQL Server forces drivers like the JDBC driver to honor that contract by passing a transaction ID back to the driver when the transaction is started and requiring the driver to pass that ID back to the server when executing subsequent statements. If the driver continues to use a transaction ID after the transaction has been committed or rolled back, that’s when you get the “failed to resume the transaction” error.
So how does the driver end up using a transaction ID for a transaction that is no longer active? SQL Server sends “transaction started” and “transaction rolled back/committed” messages to the driver “in band” with a query’s execution results (update counts, result sets, errors). The driver can’t “see” the messages until the results that precede them have been processed. So once a transaction has been started, if a statement’s execution causes a commit or rollback, the driver will think the transaction is still active until the statement’s results have been processed. Now that you understand what’s going on and why, the next question is: who should be processing those results? You guessed it: the app.
How then to avoid the “failed to resume the transaction” error (and welcome back to those folks who didn’t enjoy the detour above…) :
1) Always process ALL execution results before executing another statement on the same connection – especially on another thread. This is generally a good idea anyway, so that you don’t miss other kinds of errors. If your statement does anything more than a simple INSERT, UPDATE or DELETE, use Statement.execute() to execute it and Statement.getMoreResults() to iterate through the results.
2) If you truly aren’t interested in the execution results, feel free to ignore them by reexecuting the statement or closing it. Just dropping the app’s reference to a Statement object doesn’t count as closing it. You need to call Statement.close().
3) Avoid mixing JDBC API transaction control (Connection methods: setAutoCommit, commit, rollback) with T-SQL transaction control statements. If possible, avoid T-SQL transaction control, and nested transaction statements in particular, altogether. If a stored procedure does an unexpected ROLLBACK inside a nested transaction, it can be difficult to locate.
4) Understand that SQL Server sometimes rolls back transactions for its own reasons. One of those reasons is type conversion errors. For example: INSERT INTO myTable VALUES (‘bar’) will roll back the transaction if the table column is an INTEGER, because SQL Server does not know how to convert the literal value ‘bar’ to an INTEGER. This happens often with DATETIME data, when the server is asked to convert from a textual value that it doesn’t recognize as a timestamp.
–David Olix [SQL Server]
This post is provided ‘as is’ and confers no express or implied warranties or rights.