Solved

Closing active transactions

Posted on 2007-03-20
33
1,638 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:puntoipTecnologia
  • 11
  • 10
  • 8
  • +1
33 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
I think that you have done some updates and forgot to commit or rollback the transaction before closing it
0
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
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
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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
 

Author Comment

by:puntoipTecnologia
Comment Utility
That is what i want to do: cleared the connection and release the database but i don't know how to do it :-(
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
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
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
Are you sure you killed the tomcat process before restarting ? And what kind of select do you perform with hibernate ?
0
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
hi

is this a consistent problem or a one time thing ?
0
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
stop DB2 and start it again to begin with a clean environment
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
and did you try the
force application all
command ?
0
 

Author Comment

by:puntoipTecnologia
Comment Utility
yes
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
and what was the output you got from the command ?
0
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
have you tried to kill the java.exe process through the windows task manager ?
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
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
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
and that is what i wrote in the first reply... :-)
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
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
 

Author Comment

by:puntoipTecnologia
Comment Utility
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
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
0
 
LVL 11

Accepted Solution

by:
elfe69 earned 500 total points
Comment Utility
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
 

Author Comment

by:puntoipTecnologia
Comment Utility
ok, I've modified all hibernate methods adding the try..catch..finally block and it works

many thanks to everybody.
0
 
LVL 11

Expert Comment

by:elfe69
Comment Utility
That was my first suggestion... lol
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
computer science syllabus 3 52
pairs challenge 5 44
compre toata in where clue oracle 4 41
backtracking recursion  code 19 39
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now