Hibernate, Sybase, and the Unchained Transaction Mode Error

by Michael Scepaniak on May 7, 2013 in software development

Shenandoah National Park

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.

Mike
Want to be notified when new articles are posted?
Enter your email address:

{ 5 comments… read them below or add one }

Chris George May 8, 2013 at 7:49 am

Mike,
We could have also changed the mode of the stored procedure to anymode within SYBASE and I bet your error would have went away also or you would have gotten a different error that may have been more helpful. This is what we have done for this error in SYBASE in the past when using Java.

Reply

Michael Scepaniak May 8, 2013 at 8:19 am

Awesome, Chris. 🙂 I’m doubting that the error would have actually gone away. I’d surmise, though, that changing the mode of the stored procedure would have allowed Sybase to report back a more informative error message. Thanks for the input!

Mike…

Reply

garaei May 22, 2013 at 2:13 am

Mike,
I have the same problem as you but i don’t know how to solve it.
I have 5 columns in my table . the first column in the table will be created automatically by Sybase. the rest of columns will be added by stored procedure.
how can i say to stored procedure that the first argument is belong to second column in the table ?

Reply

Michael Scepaniak May 25, 2013 at 11:42 am

How is this the same problem? Regardless, I wouldn’t say I’m any more extra-knowledgeable about Sybase than I am about any other database product. As such, I’m not able to answer your question. Sorry. Good luck.

Mike…

Reply

Vaibhav September 27, 2013 at 1:23 pm

Hi,
I face the same issue in my application. Application is working fine at offshore. but, at onsite it generates the same error.
there are around 20 procedures are executed in particular process.. its getting very dificult to find the reason.

Reply

Leave a Comment

Previous post:

Next post:

Member of The Internet Defense League