-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
ulfreich
 Post subject: Help!: ORA-01555: snapshot too old
PostPosted: Thu Feb 10, 2005 5:46 pm 
Beginner
Beginner

Joined: Tue Sep 21, 2004 1:49 pm
Posts: 33
Location: Bogota, Colombia
Hi, I'm trying to perform a massive update (now there are about 137000 records to update) and I'm doing it following Gavin King's instructions about batch processing (http://blog.hibernate.org/cgi-bin/blosxom.cgi/Gavin%20King/batch.html).

I'm trying to commit a block of 10 updates (using batch_size 10) it works nicely until, after a while, I'm getting an error beginning a new transaction, error which I couldnt find in gooooogle and thought you may give me a hint:

(one last thing, we have sufficiently big rollback segments in our DB)

Hibernate version:2.1.8

Code between sessionFactory.openSession() and session.close():

Code:
(Class AlumnoService)
   try
   {
      log.debug("Beginning update...");

      AlumnoDAO dao = new AlumnoDAO();

      String sQry = "select u.id from com.edesa.matri.persistence.Alumno u " +
                 "order by u.id";

      Session session = HibernateUtil.getSession();
      Transaction tx = session.beginTransaction();

      ScrollableResults alumnos = session.createQuery(sQry).scroll();

      tx.commit();

      int count = 0;
      Long id = null;

      log.debug("Beginning Transaction...");
      tx = session.beginTransaction();

      while ( alumnos.next() ) {

         id = (Long)alumnos.get(0);
         Alumno item = (Alumno) session.load(Alumno.class, id);

         log.debug("Updating record #" + count + " [" + item.getId() + "]");

         // SOME UPDATES GO HERE

         session.update(item);

         count++;

         if ( count % 10 == 0 ) {
            //flush a batch of updates and release memory:
            session.flush();
            session.clear();
            log.debug("committing...");
            tx.commit();
            log.debug("Beginning new Transaction...");
            tx = session.beginTransaction();
         }
      }

      log.debug("Doing last commit...");

      // commits last transaction.
      if ( tx != null && !tx.wasCommitted() && !tx.wasRolledBack() )
         tx.commit();

      log.debug("Process ended succesfully.");
   }
   catch(Exception e)
   {
      log.debug("Error in the Process!");
      HibernateUtil.rollbackTransaction();
      throw new BusinessException(ErrorMessage.UPDATE_FAILURE + ErrorMessage.causa(e));
   }
   finally
   {
      HibernateUtil.closeSession();
   }


Name and version of the database you are using:Oracle 9

The generated SQL (show_sql=true):

Exception + Debug level Hibernate log excerpt:

Code:
13:35:57,497 DEBUG BatcherImpl:28 - Adding to batch
13:35:57,497 DEBUG BatcherImpl:50 - Executing batch size: 10
13:35:57,497 DEBUG BatcherImpl:58 - success of batch update unknown: 0
13:35:57,497 DEBUG BatcherImpl:58 - success of batch update unknown: 1
13:35:57,497 DEBUG BatcherImpl:58 - success of batch update unknown: 2
13:35:57,497 DEBUG BatcherImpl:58 - success of batch update unknown: 3
13:35:57,497 DEBUG BatcherImpl:58 - success of batch update unknown: 4
13:35:57,513 DEBUG BatcherImpl:58 - success of batch update unknown: 5
13:35:57,513 DEBUG BatcherImpl:58 - success of batch update unknown: 6
13:35:57,513 DEBUG BatcherImpl:58 - success of batch update unknown: 7
13:35:57,513 DEBUG BatcherImpl:58 - success of batch update unknown: 8
13:35:57,513 DEBUG BatcherImpl:58 - success of batch update unknown: 9
13:35:57,513 DEBUG BatcherImpl:210 - done closing: 1 open PreparedStatements, 1 open ResultSets
13:35:57,513 DEBUG BatcherImpl:272 - closing statement
13:35:57,513 DEBUG SessionImpl:2844 - post flush
13:35:57,513 DEBUG AlumnoService:486 - Haciendo commit...
13:35:57,513 DEBUG JDBCTransaction:59 - commit
13:35:57,513 DEBUG SessionImpl:2266 - flushing session
13:35:57,513 DEBUG SessionImpl:2459 - Flushing entities and processing referenced collections
13:35:57,513 DEBUG SessionImpl:2800 - Processing unreferenced collections
13:35:57,513 DEBUG SessionImpl:2814 - Scheduling collection removes/(re)creates/updates
13:35:57,513 DEBUG SessionImpl:2290 - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
13:35:57,513 DEBUG SessionImpl:2295 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
13:35:57,513 DEBUG SessionImpl:2379 - executing flush
13:35:57,513 DEBUG SessionImpl:2844 - post flush
13:35:57,513 DEBUG SessionImpl:594 - transaction completion
13:35:57,513 DEBUG AlumnoService:488 - Beginning New Transaction...
13:35:57,513 DEBUG JDBCTransaction:37 - begin
13:35:57,513 DEBUG JDBCTransaction:41 - current autocommit status:false
13:35:57,622 DEBUG JDBCExceptionReporter:49 - error performing next()
java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too small

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
   at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198)
   at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:277)
   at oracle.jdbc.driver.ScrollableResultSet.cacheRowAt(ScrollableResultSet.java:2086)
   at oracle.jdbc.driver.ScrollableResultSet.isValidRow(ScrollableResultSet.java:2060)
   at oracle.jdbc.driver.ScrollableResultSet.next(ScrollableResultSet.java:347)
   at net.sf.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:94)
   at com.edesa.matri.service.AlumnoService.crearFoneticos(AlumnoService.java:456)
   at com.edesa.matri.test.services.AlumnoTest.testCrearFoneticos(Unknown Source)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at com.edesa.matri.test.TestCase.runTest(Unknown Source)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
13:35:57,622  WARN JDBCExceptionReporter:57 - SQL Error: 1555, SQLState: 72000
13:35:57,622 ERROR JDBCExceptionReporter:58 - ORA-01555: snapshot too old: rollback segment number 5 with name "_SYSSMU5$" too small

13:35:57,622 DEBUG AlumnoService:503 - Error in the Process!
13:35:57,622 DEBUG HibernateUtil:? - Cerrando la sesion de este Thread.
13:35:57,622 DEBUG SessionImpl:576 - closing session
13:35:57,622 DEBUG SessionImpl:3371 - disconnecting session
13:35:57,622 DEBUG BatcherImpl:210 - done closing: 0 open PreparedStatements, 0 open ResultSets
13:35:57,622 DEBUG BatcherImpl:272 - closing statement
13:35:57,622 DEBUG DriverManagerConnectionProvider:120 - returning connection to pool, pool size: 1
13:35:57,622 DEBUG HibernateUtil:? - Sesion cerrada.
13:35:57,653 DEBUG HibernateUtil:? - Conexion nula o ya habia sido cerrada.
13:35:57,653 ERROR AlumnoTest:? - Error en la prueba.


Thanks in advance for any pointer. :)
Top
 Profile  
 
snpesnpe
 Post subject:
PostPosted: Fri Feb 11, 2005 8:00 am 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
try increase your rollback tablespace

regards
Top
 Profile  
 
ulfreich
 Post subject: Already increased..
PostPosted: Fri Feb 11, 2005 12:27 pm 
Beginner
Beginner

Joined: Tue Sep 21, 2004 1:49 pm
Posts: 33
Location: Bogota, Colombia
Thanks, but we already did, its size is 5 Gb... :S

The thing is, after increasing the rollback tablespace size, the exception kept ocurring indistinctly.. it was small at first, but didnt change much by increasing it.. strange.
Top
 Profile  
 
snpesnpe
 Post subject:
PostPosted: Fri Feb 11, 2005 12:36 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
increase commit count, for instance
if ( count % 100 == 0 ) {

except

if ( count % 10 == 0 ) {

regards
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.