"Application must execute a rollback."

[Ian Boyd]

We're encountering a situation where we're encountering a deadlock, and
someone's been made the deadlock victim. But after that, DB2 refuses to run
any SQL, and instead we get the error message:

"Application must execute a rollback. The unit of work has already been
rolled back in the database but other resource managers involved in this
unit of work might not. To ensure integrity of this application, all SQL
requests will be rejected until the application issues a rollback."

What's odd is that we do issue a rollback, but DB2 then throws an error
saying that there is no transaction in progress. So the question is: If we
must issue a rollback, but when we do we get a warning, how do we issue a
rollback without issuing a rollback.
Here's the detailed pseudo-code sequence of events:

Start a transaction
try {
Open a connection to the database
try {
Execute an INSERT statement
}
catch {
Caught the following exception:
DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 68

Execute a ROLLBACK; which triggered another exception:

"An exception occurred while invoking rollback on an XA Resource
Adapter from dataSource jdbc/OLGCSURV, within transaction ID {XID:
formatId(57415344), gtrid_length(29), bqual_length(25), data(00000001
E0280D0D 145270CB 48E5DF26 D2DCFB89 374466CC F99E946C F9015270 CB48E5DF
26D2DCFB 89374466 CCF99E94 6CF9BAE4 0073)}: com.ibm.db2.jcc.a.bh: XAER_NOTA"

}
}
finally {
Before closing the connection to the database, run a stored procedure to
do some required cleanup. This triggered another exception:
"Application must execute a rollback. The unit of work has already been
rolled back in the database but other resource managers involved in this
unit of work might not. To ensure integrity of this application, all SQL
requests will be rejected until the application issues a rollback. "

}
Why are we getting an error when we issue the required rollback. And how do
we issue a rollback without issuing a rollback? What can we do differently?
How can we break the database connection out of the state where it thinks
the application needs to execute a rollback?

[Knut Stolze]

Ian Boyd wrote:

We're encountering a situation where we're encountering a deadlock, and
someone's been made the deadlock victim. But after that, DB2 refuses to
run any SQL, and instead we get the error message:

"Application must execute a rollback. The unit of work has already
been
rolled back in the database but other resource managers involved in this
unit of work might not. To ensure integrity of this application, all SQL
requests will be rejected until the application issues a rollback."

What's odd is that we do issue a rollback, but DB2 then throws an error
saying that there is no transaction in progress. So the question is: If
we must issue a rollback, but when we do we get a warning, how do we issue
a rollback without issuing a rollback.

That sounds as if your are using XA/Xopen or type 2 connections? If so, you
will have to make sure that your TP monitor rolls back the distributed
transaction and starts a new one. With XA/Xopen, you'd have to issue an
xa_start call to all transaction participants (DB2 in your case).

Adapter from dataSource jdbc/OLGCSURV, within transaction ID {XID:
formatId(57415344), gtrid_length(29), bqual_length(25), data(00000001
E0280D0D 145270CB 48E5DF26 D2DCFB89 374466CC F99E946C F9015270 CB48E5DF
26D2DCFB 89374466 CCF99E94 6CF9BAE4 0073)}: com.ibm.db2.jcc.a.bh:
XAER_NOTA"

The "XAER" will only occur with distributed transactions. So check your TPM
logic (see above).

Why are we getting an error when we issue the required rollback.

A single transaction participant rolled back its transaction (part).
Therefore, the TPM has to take care that _all_ other participants roll back
their transaction. Once that is done, you can start a new transaction.
Regular XA processing stuff...

And how
do we issue a rollback without issuing a rollback? What can we do
differently? How can we break the database connection out of the state
where it thinks the application needs to execute a rollback?

Fix your application/TPM.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[Ian Boyd]

It's fair to say that i have no idea what most of what you said means. We
use an object-relational mapping object persistance framework called
"Hibernate" do all the database interaction. We don't do anything special
with distributed transactions.

Unfortunatly, when Hibernate is told to connect to Websphere, all this
maddness happens. If Hibernate is pointed directly at a simpler Tomcat
server, this doesn't happen.

We had no choice but to use Hibernate.
We had no choice but to use Websphere.
We had no choice but to use JNDI.
We had no choice but to use DB2.
The problem is not specific to DB2, but a subtle and impossible to diagnose
symptom of the interaction between the above four components. Unfortunatly,
the only thing we can do to "rollback" our tranasction is call "rollback",
which generates the error from Websphere; there's nothing more we can do.

So it's not DB2's fault, and no longer a question for a db2 newsgroup.

Thank you for your insights.