The server failed to resume the transaction... Why?

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.

Comments

• Anonymous
  March 11, 2009
  Talking about transactions, we are facing the following issue when using JDBC and CLR together..could you throw some light on this? This is in regards to the auto commit issue that we are facing when using CLR based stored procedures and invoking them through JDBC.

• Here’s what we observe: o    There is a CLR stored procedure (Create_) which inserts some data into a table. This stored procedure has been deployed on the server. o    On the client side, we use JDBC and the original code we use is follows: o     Connection conn = datasource.getConnection();            conn.setAutoCommit(false);            {                  CallableStatement cstmt = conn                              .prepareCall("{call repo.createRow(?)}");                  cstmt.registerOutParameter(1, java.sql.Types.INTEGER);                  cstmt.execute();                  int bytes = cstmt.getInt(1);                  System.out.println(bytes);            }            conn.commit(); conn.close(); With this code above, the data is not committed to the database though the insert statements are executed (We know this because the identity value we use is incremented and returned from the stored procedure). Now, if we set the autoCommit to true in the above sequence of statements, then the data is written to the database. o    After trying out different scenarios, we could get the following code to work and make the stored procedure write the data to the database. Connection conn = DriverManager.getConnection(…);            conn.setAutoCommit(false);            {                  Statement st = conn.createStatement();                  st.execute("BEGIN TRANSACTION");            }            {                  CallableStatement cstmt = conn                              .prepareCall("{call repo.createRow(?)}");                  cstmt.registerOutParameter(1, java.sql.Types.INTEGER);                  cstmt.execute();                  System.out.println(cstmt.getInt(1));            }            {                  Statement st = conn.createStatement();                  st.execute("COMMIT");            }            conn.commit();            conn.close(); In the above code, if we remove either the explicit COMMIT statement or conn.commit(), it does not commit the data. We tried this code as we thought that the driver is dropping the commit but looks like we need both the above to make it work… This definitely sounds like a bug and we would really appreciate if you can throw more light on this.

• Anonymous
  July 16, 2009
  I am also having the same issue as Naren, I have tried version 1.2 and 2.0 of the SQLServer JDBC Driver and version 1.2.2 of the Jdts JDBC driver. Is there a solution to this problem?

• Anonymous
  July 17, 2009
  How is the victim for deadlock transaction unroll is selected and is there a parameter for establishing a connection to specify if it's a user facing connection or if it's a connection used for a batch processing ? thanks

• Anonymous
  July 09, 2013
  The comment has been removed