The other week, the following error got raised in our testing environment - against some rather new code that we deployed (into our existing Java/Hibernate/Spring-based web application running against a Sybase database):

Stored procedure [procedure name] may be run only in unchained transaction mode.

The fuller (abbreviated) stacktrace:

130411 11:29...|org.hibernate.SQL.logStatement(111) | update order set foo_dt=?, foo_logi...
...
130411 11:29...|type.descriptor.sql.BasicBinder.bind(82)| binding parameter [1] as [TIMESTAMP] - Thu Apr 11 11:29:00 EDT 2013
130411 11:29...|type.descriptor.sql.BasicBinder.bind(82)| binding parameter [2] as [VARCHAR] - bar
130411 11:29...|type.descriptor.sql.BasicBinder.bind(82)| binding parameter [3] as [VARCHAR] - BAZ
...
130411 11:29...|JDBCExceptionReporter.logExceptions(233)| SQL Error: 7713, SQLState: ZZZZZ
130411 11:29...|JDBCExceptionReporter.logExceptions(234)| Stored procedure [procedure name] may be run only in
unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
130411 12:28...|JDBCExceptionReporter.logExceptions(233)| SQL Error: 2748, SQLState: ZZZZZ
130411 12:28...|JDBCExceptionReporter.logExceptions(234)| Message number 40033, passed to RAISERROR, does not exist in the sysusermessages catalog.
130411 12:28...|hingEventListener.performExecutions(324)| Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not update: [com.foo.model.Order#99953]
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)...
...
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Stored procedure [procedure name] may be run only in unchained transaction mode.
The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
at com.sybase.jdbc3.tds.Tds.a(Unknown Source) ~[jconn3-6.05.jar:na]
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source) ~[jconn3-6.05.jar:na]
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source) ~[jconn3-6.05.jar:na]
...
view raw unchained transaction mode stacktrace hosted with ❤ by GitHub

This error wasn't exactly new to me. Having been building a system using Hibernate against a Sybase database for several years now, these have come up in the past. Regardless, my initial reaction definitely wasn't good. I had visions in my head of twiddling esoteric JDBC driver settings and sparsely-documented properties in the Hibernate/Spring config files.

The first thing I set out to do was create an SQL-only test case. I interpolated the parameters found in the log into the generated SQL statement. Executing that update statement manually using an SQL client worked as expected. However, during the construction of this test case, I had glossed over the fact that I had had to fabricate a couple unlogged parameter values. Take a look at the log snippets below:

type.descriptor.sql.BasicBinder.bind(82)| binding parameter [25] as [VARCHAR] - 02CSET99900
type.descriptor.sql.BasicBinder.bind(82)| binding parameter [26] as [VARCHAR] - 11-9999599
type.descriptor.sql.BasicBinder.bind(82)| binding parameter [28] as [VARCHAR] - Foo
type.descriptor.sql.BasicBinder.bind(70)| binding parameter [29] as [VARCHAR] -
...
type.descriptor.sql.BasicBinder.bind(70)| binding parameter [55] as [VARCHAR] -
type.descriptor.sql.BasicBinder.bind(70)| binding parameter [56] as [VARCHAR] -
type.descriptor.sql.BasicBinder.bind(70)| binding parameter [58] as [VARCHAR] -
type.descriptor.sql.BasicBinder.bind(70)| binding parameter [59] as [VARCHAR] -
view raw unchained transaction mode params hosted with ❤ by GitHub

Notice what's missing? The values for the parameters at indexes #27 and #57 were not logged. In this scenario, those parameters are typed as enum values (in Hibernate/Java). For whatever reason, the logging didn't include them, so I had to pick reasonable values myself.

Now, if you're anything like me and found yourself in this situation, you'd interpolate legal values into the corresponding positions in the SQL statement. But this is where the test case stopped reflecting the error condition. In this scenario, what was happening was an illegal value was being passed into either one or both of these positions/columns. This violated a foreign key constraint, which Sybase oh-so-helpfully reported back as Stored procedure [procedure name] may be run only in unchained transaction mode.. As soon as the code was corrected to prevent illegal values from getting passed into these positions of the update statement, the error went away.

I can't say that this is a common or frequent cause of the unchained transaction mode error, but I can imagine it being the cause of some of them. Regardless, checking your update statement for illegal parameter values is a much simpler initial troubleshooting step than researching the JDBC/Hibernate/Spring configuration. Hopefully, this is your case and you just saved yourself a lot of time and frustration.

Did this help you? If so, please let me know in the comments. Thanks.