0 Replies Latest reply on Dec 22, 2005 3:10 AM by I. Manie

    C3P0 pooled datasource gives 'fetch out of sequence'

    I. Manie Newbie

      I'm using the C3P0PooledDataSource with my Hibernate in Jboss.

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE server>
       <mbean code="com.mchange.v2.c3p0.mbean.C3P0PooledDataSource" name="jboss:service=CamasC3P0PooledDataSource">
       <attribute name="JndiName">java:/****DS</attribute>
       <attribute name="JdbcUrl">jdbc:oracle:thin:@********:1521:*****</attribute>
       <attribute name="DriverClass">oracle.jdbc.driver.OracleDriver</attribute>
       <attribute name="User">****</attribute>
       <attribute name="Password">****</attribute>
       <!-- Uncomment and set any of the optional parameters below -->
       <!-- See c3p0's docs for more info. -->
       <attribute name="AcquireIncrement">1</attribute>
       <attribute name="AcquireRetryAttempts">10</attribute>
       <attribute name="AcquireRetryDelay">5000</attribute>
       <attribute name="AutoCommitOnClose">false</attribute>
       <attribute name="AutomaticTestTable">C3P0TEST</attribute>
       <attribute name="CheckoutTimeout">5000</attribute>
       <attribute name="Description">C3P0 Pooled Datasource</attribute>
       <attribute name="IdleConnectionTestPeriod">30</attribute>
       <attribute name="InitialPoolSize">1</attribute>
       <attribute name="MaxIdleTime">600</attribute>
       <attribute name="MaxPoolSize">3</attribute>
       <attribute name="MinPoolSize">1</attribute>
       <attribute name="NumHelperThreads">3</attribute>
       <attribute name="TestConnectionOnCheckin">true</attribute>

      Now, when I try to lock an object using LockMode.UPGRADE_NOWAIT, I get an SQL error: 1002 - "fetch out of sequence".

      This message should appear when part of a session is already flushed before getting the lock: http://ora-01002.ora-code.com/
      This may be caused by fetching from a SELECT FOR UPDATE cursor after a commit.

      When I use a normal datasource, I don't have the problem anymore, so it looks like it that c3p0 does some auto commit where we don't want it and not even specified it to do so.

      Has anyone ever came acros this problem yet?