Transaction on postgresql not comitted, mapped objects are not being persisted with autoCommit=false

Question

Mapped objects are not being persisted in DB (Postgresql 8.4) when calling persist in a running transaction. I am using Spring Transaction Management with the

org.springframework.jdbc.datasource.DataSourceTransactionManager

so everything should be fine. I set the autocommit-mode on the DataSource to "false". When setting the mode to "true" the commit will be done (and objects are persisted), but that leads to bigger problems (eg fetching blobs from db). So I have to set the autocommit-mode to "false", which is also the prefered mode that everybody told me...

This is my persistence configuration (I reduced the code to its necessary stuff):

<bean id="authDatabase" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://localhost:5432/authentication_db" />
    <property name="username" value="test"/>
    <property name="password" value="test"/>
    <property name="defaultAutoCommit" value="false" />               
</bean>

<bean id="serviceInfoSessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="authDatabase" />
    <!-- Very important for transactional usage with org.springframework.jdbc.datasource.DataSourceTransactionManager -->
    <property name="useTransactionAwareDataSource" value="true"/>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
    <property name="annotatedClasses">
        <list>
            <!-- mapped objects, not necessary --->
        </list>
    </property>
</bean>

<!-- defaults to transactionManager -->
<tx:annotation-driven/>

<bean id="authTXManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="authDatabase"/> 
    <qualifier value="auth"/>
</bean>

<!-- more stuff -->

I should also mention that I am using 3 different transaction managers (with of course 3 different datasources)...

My own transaction annotation that will be reflected by the qualifier attribute mentioned above:

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Transactional("auth")
public @interface AuthenticationTX{}

Annotated service class that "should" persist the object...

@AuthenticationTX
@Override
public void loginClient(Client cl) {
    // do stuff
    // call dao.persist(cl);
}

This is the log when calling the service method that invokes a db call:

16:21:24,031 DEBUG DataSourceTransactionManager:365 - Creating new transaction with name [com.example.ILoginManager.loginClient]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
16:21:24,078 DEBUG DataSourceTransactionManager:205 - Acquired Connection [jdbc:postgresql://localhost:5432/authentication_db, UserName=auth_user, PostgreSQL Native Driver] for JDBC transaction
Hibernate: select user0_.id as id14_, user0_.email as email14_, user0_.firstname as firstname14_, user0_.isLocked as isLocked14_, user0_.lastLogin as lastLogin14_, user0_.lastname as lastname14_, user0_.loginname as loginname14_, user0_.organisation_id as organis10_14_, user0_.passwort as passwort14_, user0_.userGUID as userGUID14_ from UserAccount user0_ where user0_.loginname=?
Hibernate: select client0_.id as id3_, client0_.clientId as clientId3_, client0_.clientType as clientType3_, client0_.connectedAt as connecte4_3_, client0_.language as language3_, client0_.screenHeight as screenHe6_3_, client0_.screenWidth as screenWi7_3_, client0_.securityToken as security8_3_, client0_.user_id as user9_3_ from Client client0_ where client0_.user_id=?
Hibernate: select user0_.id as id14_, user0_.email as email14_, user0_.firstname as firstname14_, user0_.isLocked as isLocked14_, user0_.lastLogin as lastLogin14_, user0_.lastname as lastname14_, user0_.loginname as loginname14_, user0_.organisation_id as organis10_14_, user0_.passwort as passwort14_, user0_.userGUID as userGUID14_ from UserAccount user0_ where user0_.loginname=?
Hibernate: select nextval ('hibernate_sequence')
Hibernate: insert into Client (clientId, clientType, connectedAt, language, screenHeight, screenWidth, securityToken, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update UserAccount set email=?, firstname=?, isLocked=?, lastLogin=?, lastname=?, loginname=?, organisation_id=?, passwort=?, userGUID=? where id=?
16:21:24,187 DEBUG DataSourceTransactionManager:752 - Initiating transaction commit
16:21:24,187 DEBUG DataSourceTransactionManager:265 - Committing JDBC transaction on Connection [jdbc:postgresql://localhost:5432/authentication_db, UserName=auth_user, PostgreSQL Native Driver]
16:21:24,187 DEBUG DataSourceTransactionManager:323 - Releasing JDBC Connection [jdbc:postgresql://localhost:5432/authentication_db, UserName=auth_user, PostgreSQL Native Driver] after transaction

As you can see, the transaction is being committed (according to the log), but no object is being persisted in db (although the insert and update are being executed).

When setting the commit mode in my datasource configuration to

property name="defaultAutoCommit" value="true"

everything works fine!

I really dont know what causes this curious problem... I'd be glad if anyone could give me a hint.

Answer 1

To work with Hibernate you need a HibernateTransactionManager:

<bean id="authTXManager" 
    class="org.springframework.orm.hibernate3.HibernateTransactionManager"> 
    <property name="sessionFactory" ref="serviceInfoSessionFactory"/>  
    <qualifier value="auth"/> 
</bean>