Can you explain why @Transactional not rolling back

Question

I am trying to create two tables(ABC, XYZ) in Database. XYZ syntax intentionally has wrong syntax and is suppose to fail. The idea is that table creation of ABC should be rolled back when XYZ is tried. I am new to Spring Transaction and have searched around to come up with this simple example the logs seem to show that TransactionManager is rolling back but at the end I see that the table ABC is present in Database. Can anybody explain why @Transactional is not rolling back here...

    package com.tango.db.dao;

    import org.springframework.context.support.ClassPathXmlApplicationContext;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.transaction.annotation.Transactional;

    public class DBDAO {

            private JdbcTemplate jdbcTemplate;

            public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
              this.jdbcTemplate = jdbcTemplate;
            }

            @Transactional(rollbackFor = Exception.class)
            public void createTables(){
              jdbcTemplate.execute("CREATE TABLE ABC(ID NUMBER NOT NULL,  NAME VARCHAR2(35), primary key(ID))");
              jdbcTemplate.execute("CREATE TABLE XYZ(ID NUMBER NOT NULL, NAME VARCHAR2(35), UPDATE DATE(7), primary key(ID))");
            }

            public static void main(String[] args) {
              ClassPathXmlApplicationContext ctx = new         ClassPathXmlApplicationContext("spring-context.xml");
              DBDAO dbdao = (DBDAO)ctx.getBean("dbDAO");
              dbdao.createTables();
            }    
    }

spring-context.xml

    <bean id="local.oracle.dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
            <property name="url" value="jdbc:oracle:thin:@10.211.55.6:1521:xe"/>
            <property name="username" value="abc"/>
            <property name="password" value="1234"/>
            <property name="defaultAutoCommit" value="false"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <constructor-arg ref="local.oracle.dataSource"/>
    </bean>

    <bean id="dbDAO" class="com.tango.db.dao.DBDAO">
            <property name="jdbcTemplate" ref="jdbcTemplate"/>

    </bean>

    <tx:annotation-driven transaction-manager="txManager"/>

    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="local.oracle.dataSource"/>
    </bean>

Following are the logs:

    DEBUG SQLErrorCodeSQLExceptionTranslator - Translating SQLException with SQL state '42000', error code '904', message [ORA-00904: : invalid identifier
    ]; SQL was [CREATE TABLE XYZ(ID NUMBER NOT NULL, NAME VARCHAR2(35), UPDATE DATE(7), primary key(ID))] for task [StatementCallback]
    DEBUG DataSourceTransactionManager - Initiating transaction rollback
    DEBUG DataSourceTransactionManager - Rolling back JDBC transaction on Connection [jdbc:oracle:thin:@10.211.55.6:1521:xe, UserName=DBO, Oracle JDBC driver]
    DEBUG DataSourceTransactionManager - Releasing JDBC Connection [jdbc:oracle:thin:@10.211.55.6:1521:xe, UserName=DBO, Oracle JDBC driver] after transaction
    DEBUG DataSourceUtils - Returning JDBC Connection to DataSource
    Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE TABLE XYZ(ID NUMBER NOT NULL, NAME VARCHAR2(35), UPDATE DATE(7), primary key(ID))]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier

        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
        at com.tango.db.dao.DBDAO.createTables(DBDAO.java:19)
        at com.tango.db.dao.DBDAO$$FastClassByCGLIB$$112527a.invoke(<generated>)
        at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
        at com.tango.db.dao.DBDAO$$EnhancerByCGLIB$$7b0ac5f7.createTables(<generated>)
        at com.tango.db.dao.DBDAO.main(DBDAO.java:25)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
        ... 16 more

I ended up using CREATE SCHEMA to create multiple tables in one transaction. http://docs.oracle.com/cd/E11882_01/server.112/e25494/general001.htm — programForFun, Jan 02 '13 at 04:28

gerrytan · Accepted Answer · 2013-01-03T00:37:01.803

In your case the transaction boundary is not entirely up to Spring. If you dig a bit deeper into Oracle documentation, it defines the transaction boundary as follow:

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.

http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm#i6564

CREATE TABLE is a DDL (Data Definition Language) statement. Hence when you issued the first CREATE TABLE, Oracle took it as an implicit end-of-transaction. Therefore when your second CREATE TABLE is issued and failed, it's too late to revert the first one.

YOu gotta go back to basic sometimes... Thank you very much. — programForFun, Jan 02 '13 at 03:56

score 1 · Answer 2 · answered Jan 01 '13 at 23:28

1

This is because a create table is a DDL (data definition language) statement and only DML (data modification language) statements are rolledback. There is an implicit commit at every DDL statement, so they cannot be rolledback.

To be complete, DML statements are: insert, update and delete.

answered Jan 01 '13 at 23:28

Kru

4,195
24
31

Your answer was correct too It's just that @gerrytan had comments from oracle documentation to answer the question better. – programForFun Jan 02 '13 at 03:59

score 0 · Answer 3 · answered Jan 01 '13 at 23:27

0

Looks like you're using JDK proxies but do not have any interface declared for Spring to use to create a proxy against. DBDAO needs to be an interface that has the createTables method on it, so Spring has something to create the runtime implementation of that includes the transaction code.

answered Jan 01 '13 at 23:27

Affe

47,174
11
83
83

the actual code does have interface for the DAO.. it was too much of a hassle to change the proprietary code. I didn't get what was the relationship of interface implementation to this problem. – programForFun Jan 02 '13 at 04:01
the interface is required in order for @Transactional to work with JDK proxies. Even on a database that supports DDL rollback, your example as posted would still not work. – Affe Jan 03 '13 at 17:20

Can you explain why @Transactional not rolling back

3 Answers3

Linked