Search code examples
mysqltransactionspersistencerdbmsddl

Auto-COmmit of DDL

I read that "CREATE TABLE" and "DROP TABLE" statements are auto-commit. I am trying to understand their behaviour for the below scenario:

conn.setAutoCommit(false);
stmt = conn.createStatement("CREATE TABLE ...");

conn.commit();

In the above case, would setting autoCommit to false would have any impact on CREATE TABLE auto-commiting. If conn.setAutoCommit(false) does override DDL auto-commit property, then whats the entire point, because by default a new connection is in auto-commit mode.

I believe except MySQL and older versions of ORacle, most major RDBMS allow DDL rollback. My questions concerns If in MySQL DDL rollback is not allowed, what is point of setting auto-commit of connection to false for DDL operations?

I will really appreciate your opinion in clarifying my doubts.

Solution

  • Executing a DDL statement causes an "implicit commit". But the statement has to be executed.

    The scenario you give doesn't make sense to me, the createStatement method doesn't take a string as an argument, does it?

    We typically see 

    stmt = conn.createStatement();
stmt.execute("CREATE TABLE ... ");

    If you actually execute a DDL statement, it causes an implicit commit, as if you had run a COMMIT; statement immediately before you ran the DDL statement.

    But don't take my word for it, try it yourself, and consult the appropriate section of the MySQL Reference Manual for whichever version of MySQL you are running.

    Reference: https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html