Announcement Announcement Module
Collapse
No announcement yet.
Close session of a database link with Hibernate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Close session of a database link with Hibernate

    Hi there,

    I'm having the following situation:

    * in my Dao layer (HibernateTemplate) I'm doing a query to a view which is using a database link to get some of its data from a remote db.
    * this all works fine, the problem is that the remote db closes its session afer some minutes
    * as the oracle session (the db where my view resides) is never closed, it expects the db link session also to be open
    * when a user after some time does another select on the same view an exception is thrown that the rpc connection was cancelled (because the db link session was closed without Oracle knowing it)

    So what i was thinking to do, is closing the db link session myself after i have retrieved the data from the remote db.
    But now the TransactionManager gets into my way.
    When I do

    Code:
    find("from Beschaffung");
    Statement statement = session.connection().createStatement();
    statement.execute("Alter session close database link mawi.bmw");
    statement.close();
    It throws an exception because it says that the connection is still being used.

    If I'm doing following:

    Code:
    find("from Beschaffung");
    session.getTransaction().commit();
    Statement statement = session.connection().createStatement();
    statement.execute("Alter session close database link mawi.bmw");
    statement.close();
    it throws an exception when the TransactionManagers tries to commit the transaction and the transaction has already been commited.

    The only solution I found till now is closing the db link session in my service layer after the dao transaction of getting my view data has been done, but I don't find this very transparent and would like to handle it in my dao layer.

    Is there a solution to this problem?

    Thanks for any hints.

    Dominik

  • #2
    Generally it is not recommended to keep database connections open a long time. Definitely not in combination with UI interaction (what if the user leaves the dialog open and goes to lunch?).

    Have a look at the hibernate reference which also mentions this case.

    Regards,
    Andreas

    Comment


    • #3
      Hi Andreas,

      thank you very much for your fast reply.

      I'm not sure if I got misunderstood. My hibernate session gets closed after the view has been rendered. (OpenSessionInViewFilter)

      So, the session which isn't closed is the Oracle session not the Hibernate session, in order not to block as you mentioned correctly any other user transaction.

      It might be more of a database than a spring issue, but perhaps spring is offering some help even in this regards...

      Thanks for your help again...

      Dominik

      Comment


      • #4
        Indeed I misunderstood the situation a bit. So is it the case, that the session/ connection is kept open by OpenSessionInViewFilter, but the database itself closes the connection?

        Do you use OSIF in singleSession mode? Maybe it could help to change that. Otherwise I see no other option as trying to increase the timeout in the database.

        Regards,
        Andreas

        Comment


        • #5
          The basic problem is that my db link is closed without my oracle session knowing it and still thinking that the session to the db link is open. So it has basically nothing to do with hibernate or spring.

          I'm just wondering if there is a way to tell hibernate to tell oracle to close the db link session explicitly. And then the problem arises where and how can I tell hibernate that. Right now I'm doing it in my service layer, after the query of my view. So I think that it has nothing to do with OSIF either, because my hibernate session is not the problem, only the oracle session.

          The timeout in the database regarding the db link session is like a couple of minutes, so it doesn't pose any problem for the request. Just when the user get's back to the application and starts the same query again, the db link session has expired (i can't change that parameter, it's a host db) and oracle is throwing the exception.

          The perfect thing would be if i could handle the 'closedblinksession' in my dao layer, transparently right after my query of the view, but there the aop transactionmanager gets in the way and therefore i have to wait until it gets back in the service layer..

          kind of difficult i find... thanks again for your interest in my problem..

          Comment


          • #6
            What exactly do you mean with "oracle session", "db link" and "db link session"?
            As I know it, the oracle session (in the database) is connected with the java Connection, which in turn is attached to a hibernate session. If the database session is closed, then the Connection is broken.

            Perhaps a good connection pool implementation (e.g. C3P0) might help you in detecting broken connections.

            Regards,
            Andreas

            Comment


            • #7
              By oracle session I mean the session in the database.

              By db link I mean a database link defined in the oracle db pointing to another remote db.

              And by db link session I mean the session which oracle opens on that remote db in order to process the query.

              I'm not a DB guru, that's what i understood talking yesterday with a oracle db specialist.

              I'm using a the c3p0 connection pool implementation via hibernate, so i think that this is not the problem either. I guess that it is a Oracle specific problem, but right now it is my problem... :-)

              Comment


              • #8
                Thanks for the explanations. I have gathered a bit knowledge in the database area, but I must confess that this is somewhat beyond my expertise. So, yes, I am not a DB guru, too
                I fear that I am unable to help you further and hope that someone else might bring in a solution.

                Regards,
                Andreas

                Comment


                • #9
                  Dominik,

                  Did you already find a solution for this problem ?
                  We also have this problem in our project and cannot find a working solution to solve it...

                  Regards,
                  David

                  Comment


                  • #10
                    Hi david,

                    no, I couldn't solve it in the way to make it transparent for the service layer which method needs to close a db link.

                    It's ugly, I know, but at least it works. So in every method call of the service layer which is calling a dao method which is opening a db link, after the method returns from the dao I close the db link session in the service layer.

                    Code:
                    	public List getBeschaffungen()
                    	{
                    		List beschaffungList = beschaffungDao.getBeschaffungList(suchFilterManager.getSuchFilter());
                    		beschaffungDao.closeDbLinkSession("dllinkname");
                    		return beschaffungList;
                    	}
                    Code:
                    	public void closeDbLinkSession(final String dbLinkName)
                    	{
                    		getHibernateTemplate().execute(new HibernateCallback(){
                    			public Object doInHibernate(Session session) throws HibernateException, SQLException
                    			{
                    				Statement statement = session.connection().createStatement();
                    				statement.execute("Alter session close database link "+dbLinkName);
                    				logger.info("closing "+dbLinkName+" dblink session");
                    				statement.close();
                    				return null;
                    			}
                    		});
                    	}
                    If you can come up with a better solution, it would be nice to have your post in here. I hope that I could help nonetheless. :-)

                    Dominik

                    Comment


                    • #11
                      Possible bug?

                      Hi Dominik!
                      Your code piece was very useful for me to find out the "real" problem.
                      It seems that, when you read something a database link (in my case it is a synonym to a view through a dblink), you need either to commit or to rollback the connection!
                      In fact, when I call your "alter session" command, it gives me an ORA-02080 error:
                      http://ora-02080.ora-code.com/
                      So I decided to use pure JDBC for my DAO and I noticed that the oracle session close is not necessary: it is enough to commit the connection!
                      In fact, without the session closing I have these cases:
                      - with "connection.commit()" the process goes fine;
                      - without it, I have a ORA-24784:
                      http://ora-24784.ora-code.com/
                      It seems that not committing after a select through a db link causes the transaction to remain somewhat "active".

                      HTH
                      Antonio

                      Comment

                      Working...
                      X