Closing active transactions

Hi,

I have a web application that connects to DB2 using Hibernate. I've been testing my app and when i'm trying to stop the server (Tomcat) appears this error message:


INFO  [2007-03-20 12:48:16,828] (close [org.hibernate.impl.SessionFactoryImpl:767]) - closing
INFO  [2007-03-20 12:48:16,828] (close [org.hibernate.connection.DriverManagerConnectionProvider:147]) - cleaning up connection pool:
WARN  [2007-03-20 12:48:16,859] (close [org.hibernate.connection.DriverManagerConnectionProvider:155]) - problem closing pooled connection
com.ibm.db2.jcc.b.SqlException: [ibm][db2][jcc][10251][10308] Se ha solicitado java.sql.Connection.close() mientras  una transacción está en curso en la conexión.
La transacción permanece activa y la conexión no puede cerrarse.

Someone has any idea? How can I close all the transactions?

Thanks in advance
puntoipTecnologiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
hi

the following message

problem closing pooled connection
com.ibm.db2.jcc.b.SqlException: [ibm][db2][jcc][10251][10308] Se ha solicitado java.sql.Connection.close() mientras  una transacción está en curso en la conexión.
La transacción permanece activa y la conexión no puede cerrarse.

says that you were trying to close a connection that is being used by a transaction and that connection can not be closed

i'm not an hibernate expert, but i think you need to close the connection through hibernate (that is in your java code') before you shut down tomcat

momi
0
elfe69Commented:
I think that you have done some updates and forgot to commit or rollback the transaction before closing it
0
puntoipTecnologiaAuthor Commented:
I have done some updates but I don't forget to commit the transaction. Anyway, I have done a commit at the database now and the problem go on.
The application start and it's like a table is locked but I don't know why
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

elfe69Commented:
If a table is locked, it is due to improper release of the transaction (a commit/rollback is missing somewhere...)
Look at the table that is locked and find where in your code you have locked it.
If your program throws exceptions, you should not forget to rollback your transaction as well.
0
momi_sabagCommented:
wait a sec

what do you mean by - i have a done a commit at the database ?
you have to do the commit from within the same thransaction that does the update statements

and what do you mean by -  the application start ? i though you tried to shutdown tomcat
0
elfe69Commented:
With hibernate, you should have something like this:

Session sess = factory.openSession();
 Transaction tx;
 try {
     tx = sess.beginTransaction();
     //do some work
     ...
     tx.commit();
 }
 catch (Exception e) {
     if (tx!=null) tx.rollback();
     throw e;
 }
 finally {
     sess.close();
 }

You should never omit the tx.commit() for normal behaviour and the tx.rollback() in the catch block, otherwise the transaction is in pending mode and cannot be released (that is exactly what your error message says).
0
ghp7000Commented:
to release your connection you have to force the application off the database, that will rollback automatically any transaction not committed. Like Momi said, you cant rollback application A transactions from application B session, only force application can do that. Once you have cleared the connection from the database, the Tomcat server will shut down.
0
puntoipTecnologiaAuthor Commented:
That is what i want to do: cleared the connection and release the database but i don't know how to do it :-(
0
momi_sabagCommented:
if you just want to disconnect all application you can enter this command from the command line

db2 force application all

but i don't think this should be a standard procedure...
0
elfe69Commented:
You can use:
- DB2 LIST APPLICATIONS:  to find your application handle
- DB2 FORCE APPLICATION [handle]: to disconnect your application
- DB2 FORCE APPLICATION ALL: to disconnect all the clients of your database
0
puntoipTecnologiaAuthor Commented:
I've just done it and it doesn't work. When I start my application Hibernate stands doing a select (as if was a infinite loop or similar), then, when i restart the server (Tomcat),  appears the error "problem during closing connection..."
0
elfe69Commented:
Are you sure you killed the tomcat process before restarting ? And what kind of select do you perform with hibernate ?
0
puntoipTecnologiaAuthor Commented:
Yes. I'm sure that I killed Tomcat and the select is against an only  table without joins, subselects...just to obtain the menu elements of the application
0
momi_sabagCommented:
hi

is this a consistent problem or a one time thing ?
0
puntoipTecnologiaAuthor Commented:
Until yesterday, everything works ok, but suddenly it begins to hung up and now i cannot enter to my application.
I've executed that select :
SELECT TABSCHEMA, TABNAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS,
   LOCK_ESCALATION FROM SYSIBMADM.LOCKS_HELD WHERE AGENT_ID IN (140, 134)

and the result is this
 tabschema  tabname  lock_object_type            lock_mode  lock_status   lock_escalation
                                       INTERNALV_LOCK           S               GRNT                0
                                      INTERNALP_LOCK           S               GRNT                0
                                      INTERNALP_LOCK           S               GRNT                0
                                      INTERNALP_LOCK           S               GRNT                0


0
elfe69Commented:
stop DB2 and start it again to begin with a clean environment
0
puntoipTecnologiaAuthor Commented:
it doesn't work :-(

The list of the appilcations is this:

Name of the app            estado                                            descriptor
db2taksbd                   Conected                                                  9
db2stmm                      Conected                                                  8
db2jcc_application      UOW en espera de la aplicación              17
javaw.exe                  UOW en espera de la aplicaci´´on            7
0
momi_sabagCommented:
and did you try the
force application all
command ?
0
puntoipTecnologiaAuthor Commented:
yes
0
momi_sabagCommented:
and what was the output you got from the command ?
0
puntoipTecnologiaAuthor Commented:
When i force all applications to disconnect, all applications are closed and the message is this


     SQL1611W  El Supervisor de Bases de Datos no ha devuelto datos.
    Explicación:
          No se dispone de ninguna información de supervisión solicitada por los usuarios en el momento
          en que se ha emitido la llamada API del Supervisor de Bases de Datos.  Suele ocurrir cuando una
          base de datos o aplicación solicitada no está activa, o cuando un grupo de supervisión como un
         grupo de Tablas, se desconecta y se solicita la información de Tabla.  
    Respuesta del usuario:
          El mandato se completa satisfactoriamente, pero no se devuelve ningún dato al usuario.  
          El usuario debería asegurarse de que las bases de datos o las aplicaciones que se desea  
          supervisar estén activas en el momento de llamar a la API del Supervisor del Sistema de Bases
         de Datos, y que estén activos también los grupos de supervisión correspondientes.  

The databse is disconnected, i conected again and the problems still go on :-(
0
momi_sabagCommented:
have you tried to kill the java.exe process through the windows task manager ?
0
ghp7000Commented:
what you are describing is normal if I understand the sequence of events to be as follows:
-you force all the applications off the database with force application all command
-after issuing the force applications all command, you issue again list applications and you confirm that all applications are indeed forced off
-after confirming that there are no applications connected to the database, you shut down and restart the Tomcat server
-after the Tomcat server is successfully restarted, you reconnect to the database and enter again the hung state when you try and disconnect
is this the correct sequence of events?
if your answer is yes, then there is nothing wrong with the db2 system, there is an error somewhere else, because when you use Tomcat in the way you are using, it connects to the database using pooled connections, that means it will start up multiple connections to the database IN ANTICIPATION OF USER DEMAND. If the number of connections established is exceeded by the user demand, more connections will be opened.
So its entirely normal that your lock snapshot will show internal P or V table locks-these are the esential locks that an application needs to query a table. What you need to do is examine your code at the point when you want to disconnect and step through that to see what it is doing. It looks like the disocnnect part of your code is affecting only ONLY ONE OF THE OPEN SESSIONS AND NOT ALL
0
elfe69Commented:
I agree with ghp7000: the problem seemed to be in your code, the initial error was that tomcat tried to close a connection that you leaved in pending state (waiting for a commit or rollback).
0
momi_sabagCommented:
and that is what i wrote in the first reply... :-)
0
ghp7000Commented:
yes, more or less the answer is the same, it is a distributed transaction problem
could you tell me what the values are for the following dbm parameters:
INDEXREC
TM_DATABASE
RESYNC_INTERVAL
FEDERATED
TP_MON_NAME



0
puntoipTecnologiaAuthor Commented:
INDEXREC = use system value
TM_DATABASE = 1ST_CONN
RESYNC_INTERVAL= 180
FEDERATED = YES
TP_MON_NAME=

I'm inspecting my code to see the way to disconnect
0
elfe69Commented:
Does your java program conform to the following hibernate template ?

Session sess = factory.openSession();
Transaction tx;
try {
     tx = sess.beginTransaction();
     // ...
     // do some work
     // ...
     tx.commit();
}
catch (Exception e) {
     if (tx!=null) tx.rollback();
     throw e;
}
finally {
     sess.close();
}
0
puntoipTecnologiaAuthor Commented:
Yes, all hibarnate methods have something like that

Session session=HibernateLoader.getSessionFactory().openSession();
session.beginTransaction();
result = session.createQuery(sqlQuery).list();
session.close();

And if the methos is an update, delete or an insert, there is a commit at the end of the code
0
elfe69Commented:
Ok, the try ... catch ... finally block is really important, make sure you always:
- call rollback() in the catch block
- call session.close() in the finally block
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
puntoipTecnologiaAuthor Commented:
ok, I've modified all hibernate methods adding the try..catch..finally block and it works

many thanks to everybody.
0
elfe69Commented:
That was my first suggestion... lol
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.