CODEWITHSUNDEEP
javamysqljdbc
Anish B.
Anish B.

Reputation: 16599

Why connection still commits automatically after putting setAutoCommit(false)?

I'm using a MySQL database.

I have a code like this :

public class Test {

    public static void main(String[] args) throws SQLException {
        String url = "....";
        String username = "...";
        String password = "...";
        DriverManager.registerDriver(new Driver());
        Connection connection = DriverManager.getConnection(url, username, password);
        connection.setAutoCommit(false);
        Statement stmt = connection.createStatement();
        stmt.execute("create table if not exists customer1\r\n" + "(\r\n"
                + "customer_name varchar(20) not null primary key,\r\n" + "customer_street varchar(20),\r\n"
                + "customer_city varchar(10)\r\n" + ")");
        // connection.commit();  
        connection.close();

    }

}

Problem: When I execute this, it creates the table and commits it automatically but it should not.

I did connection.setAutoCommit(false) and commented out connection.commit() for testing, then why it is committing ?

This question (jdbc autocommit(false) doesnt work) didn't help.

Upvotes: 0

Views: 575

Answers (3)

Amatya Annamaneni
Amatya Annamaneni

Reputation: 27

Data Manipulation statements are transactional, not Data definition statements.

so create/alter table or drop table are still committed.

Upvotes: 1

rkosegi
rkosegi

Reputation: 14678

Problem: When I execute this, it creates the table and commits it automatically but it should not.

Right, any DDL will always be committed regardless of autocommit setting.

This behavior is not specific to MySQL, see Autocommit.

Most DBMS (e.g. MariaDB) force autocommit for every DDL statement, even in non-autocommit mode. In this case, before each DDL statement, previous DML statements in transaction are autocommitted. Each DDL statement is executed in its own new autocommit transaction.

Maybe TEMPORARY TABLE might help you.

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

Upvotes: 2

danblack
danblack

Reputation: 14761

MySQL DDL, i.e. creating table isn't transactional

ref transactions that cause implicit commit

Upvotes: 1

Related Questions