Announcement Announcement Module
Collapse
No announcement yet.
Hsqldb in-process standalone not committing or rolling back when expected Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hsqldb in-process standalone not committing or rolling back when expected

    Gonna try to keep this short. Trying to figure out if what I'm experiencing is root caused from a misconfig or

    With the following config, I'm not able to commit.
    Commenting out the defaultAutoCommit/defaultTransactionIsolation, I'm not able to rollback.

    Debug log for both scenarios follow the config.


    <bean lazy-init="false" scope="prototype" id="localDataSource"
    class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
    init-method="getConnection">
    <property name="driverClassName" value="${server.driver}" />
    <property name="url" value="${server.connection.url}" />
    <property name="username" value="${server.user}" />
    <property name="password" value="${server.password}" />
    <property name="initialSize" value="10" />
    <property name="defaultAutoCommit" value="false" />
    <property name="defaultTransactionIsolation">
    <util:constant static-field="java.sql.Connection.TRANSACTION_READ_COMMIT TED" />
    </property>
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="300" />
    <property name="timeBetweenEvictionRunsMillis" value="5000" />
    <property name="validationQuery" value="select count(*) from xxnpi_transitions" />
    <property name="testOnBorrow" value="true" />
    </bean>

    <bean lazy-init="false" scope="prototype" id="localJdbcTemplate"
    class="org.springframework.jdbc.core.JdbcTemplate" >
    <property name="dataSource" ref="localDataSource" />
    </bean>

    <bean lazy-init="false" id="localNpiTransitionsDAO"
    class="com.mycompany.gntxNPI.database.dao.impl.Def aultNpiTransitionsDAO">
    <property name="jdbcTemplate">
    <ref bean="localJdbcTemplate" />
    </property>
    </bean>

    <bean lazy-init="false" id="localRowSetTableModel"
    class=" com.mycompany.gntxNPI.gui.model.RowSetTableModel">
    <property name="localNpiTransitionsDAO">
    <ref bean="localNpiTransitionsDAO" />
    </property>
    </bean>

    <!-- transaction management -->

    <bean id="localTxManager"
    class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
    <property name="dataSource" ref="localDataSource" />
    </bean>

    <tx:advice id="localTxAdvice" transaction-manager="localTxManager">
    <tx:attributes>
    <tx:method name="get*" propagation="SUPPORTS" read-only="true" />
    <tx:method name="update" propagation="REQUIRED" />
    <tx:method name="update*" propagation="REQUIRED" />
    <tx:method name="delete*" propagation="REQUIRED" />
    <tx:method name="set*" propagation="REQUIRED" />
    <tx:method name="*" />
    </tx:attributes>
    </tx:advice>

    <aop:config>
    <aopointcut id="localDaoOperations"
    expression="execution(* com.mycompany.gntxNPI.database.dao..*.*(..))" />
    <aop:advisor advice-ref="localTxAdvice" pointcut-ref="localDaoOperations" />
    </aop:config>


    Autocommit = false, transaction appears to commit, but update doesn't show in database:

    2011-06-03 11:20:19,056 - DEBUG [AWT-EventQueue-0] (RowSetTableModel.java:213) - setting old part number to 12345678 for id: 0
    2011-06-03 11:20:19,056 - DEBUG [AWT-EventQueue-0] (AbstractPlatformTransactionManager.java:365) - Creating new transaction with name [com.mycompany.gntxNPI.database.dao.impl.DefaultNpi TransitionsDAO.update]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
    2011-06-03 11:20:19,072 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:204) - Acquired Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver] for JDBC transaction
    2011-06-03 11:20:19,072 - DEBUG [AWT-EventQueue-0] (DefaultNpiTransitionsDAO.java:77) - updating oldPartNbr=12345678 for transitionId=0
    2011-06-03 11:20:19,072 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:810) - Executing prepared SQL update
    2011-06-03 11:20:19,072 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:569) - Executing prepared SQL statement [UPDATE XXNPI_TRANSITIONS SET OLD_PART_NBR=? WHERE TRANSITION_ID=?]
    2011-06-03 11:20:19,072 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:110) - Fetching JDBC Connection from DataSource
    2011-06-03 11:20:19,072 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:114) - Registering transaction synchronization for JDBC Connection
    2011-06-03 11:20:19,088 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:819) - SQL update affected 1 rows
    2011-06-03 11:20:19,088 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource
    2011-06-03 11:20:19,135 - DEBUG [AWT-EventQueue-0] (AbstractPlatformTransactionManager.java:843) - Initiating transaction rollback
    2011-06-03 11:20:19,135 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:279) - Rolling back JDBC transaction on Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver]
    2011-06-03 11:20:19,150 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:322) - Releasing JDBC Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver] after transaction
    2011-06-03 11:20:19,150 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource
    2011-06-03 11:20:19,182 - DEBUG [AWT-EventQueue-0] (RowSetTableModel.java:216) - exception in setValueAt():testing rollback
    2011-06-03 11:20:19,182 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:434) - Executing SQL query [SELECT * FROM XXNPI_TRANSITIONS]
    2011-06-03 11:20:19,182 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:110) - Fetching JDBC Connection from DataSource
    2011-06-03 11:20:19,182 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:114) - Registering transaction synchronization for JDBC Connection
    2011-06-03 11:20:19,182 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource

    Autocommit = true (default), transaction appears to rollback after purposely throwing a RuntimeException, but update shows in database:

    2011-06-03 11:28:00,637 - DEBUG [AWT-EventQueue-0] (RowSetTableModel.java:213) - setting old part number to aabcef for id: 1
    2011-06-03 11:28:00,637 - DEBUG [AWT-EventQueue-0] (AbstractPlatformTransactionManager.java:365) - Creating new transaction with name [com.mycompany.gntxNPI.database.dao.impl.DefaultNpi TransitionsDAO.update]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
    2011-06-03 11:28:00,637 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:204) - Acquired Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver] for JDBC transaction
    2011-06-03 11:28:00,637 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:221) - Switching JDBC Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver] to manual commit
    2011-06-03 11:28:00,637 - DEBUG [AWT-EventQueue-0] (DefaultNpiTransitionsDAO.java:77) - updating oldPartNbr=aabcef for transitionId=1
    2011-06-03 11:28:00,653 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:810) - Executing prepared SQL update
    2011-06-03 11:28:00,653 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:569) - Executing prepared SQL statement [UPDATE XXNPI_TRANSITIONS SET OLD_PART_NBR=? WHERE TRANSITION_ID=?]
    2011-06-03 11:28:00,653 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:110) - Fetching JDBC Connection from DataSource
    2011-06-03 11:28:00,653 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:114) - Registering transaction synchronization for JDBC Connection
    2011-06-03 11:28:00,715 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:819) - SQL update affected 1 rows
    2011-06-03 11:28:00,715 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource
    2011-06-03 11:28:00,715 - DEBUG [AWT-EventQueue-0] (AbstractPlatformTransactionManager.java:843) - Initiating transaction rollback
    2011-06-03 11:28:00,715 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:279) - Rolling back JDBC transaction on Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver]
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:322) - Releasing JDBC Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver] after transaction
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (RowSetTableModel.java:216) - exception in setValueAt():testing rollback
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:434) - Executing SQL query [SELECT * FROM XXNPI_TRANSITIONS]
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:110) - Fetching JDBC Connection from DataSource
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:114) - Registering transaction synchronization for JDBC Connection
    2011-06-03 11:28:00,731 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource

  • #2
    How embarrasing... as I'm posting this I see the scope="prototype". Not sure why I put that there, but after taking that out and keeping the defaultAutoCommit=false, defaultTransactionIsolation=READ COMMITTED in the pool config, things operate normally (commit and rollback works as expected). If you have any idea why prototype scope would have messed this up, and would like to post a response, that would be wonderful.

    Comment


    • #3
      Here's the new log when a commit worked with defaultAutoCommit=false. Noticed that the DataSourceUtils.java returns the JDBC connection only after the commit, not before it too which was the situation when this wasn't working.

      2011-06-03 11:48:54,663 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:810) - Executing prepared SQL update
      2011-06-03 11:48:54,663 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:569) - Executing prepared SQL statement [UPDATE XXNPI_TRANSITIONS SET OLD_PART_NBR=? WHERE TRANSITION_ID=?]
      2011-06-03 11:48:54,678 - DEBUG [AWT-EventQueue-0] (JdbcTemplate.java:819) - SQL update affected 1 rows
      2011-06-03 11:48:54,678 - DEBUG [AWT-EventQueue-0] (AbstractPlatformTransactionManager.java:752) - Initiating transaction commit
      2011-06-03 11:48:54,678 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:264) - Committing JDBC transaction on Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver]
      2011-06-03 11:48:54,709 - DEBUG [AWT-EventQueue-0] (DataSourceTransactionManager.java:322) - Releasing JDBC Connection [jdbc:hsqldb:hsql://localhost:32080/file:./gntx-npi-standalone, UserName=SA, HSQL Database Engine Driver] after transaction
      2011-06-03 11:48:54,709 - DEBUG [AWT-EventQueue-0] (DataSourceUtils.java:332) - Returning JDBC Connection to DataSource

      Comment

      Working...
      X