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:

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:

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.