CODEWITHSUNDEEP
javajdbcconnectionautocommit
eatSleepCode
eatSleepCode

Reputation: 4637

what happens on connection.setAutoCommit = false

what happens if I do connection.setAutoCommit(false); does it creates a new transaction at database side?

Upvotes: 9

Views: 63285

Answers (4)

Nallamachu
Nallamachu

Reputation: 1488

Let me put it with simple explanation with code. When we have applied

Connection.setAutoCommit(false);

in our source code, it will disable the autocommit option, which by default enable in database.

So, you have to call

Connection.commit();

method explicitly to persist any changes to the database.

Class.forName(drivers);
Connection dbConnnection=DriverManager.getConnection(connectionURL,username,password);
dbConnection.setAutoCommit(false); //Disabling the Autocommit
Statement selectStatement = dbConnection.createStatement("Select Query");
ResultSet rs = selectStatement.execute();
while(rs.next()){
    Statement updateStatement = dbConnection.createStatement("update Query");
    //Apply some changes to update record
    statement.execute();
    dbConnection.commit();  //Mandatory to execute to persist changes into database
}

Upvotes: 5

Mick Mnemonic
Mick Mnemonic

Reputation: 7956

According to the documentation, connection.setAutoCommit(false) will allow you to group multiple subsequent Statements under the same transaction. This transaction will be committed when connection.commit() is invoked, as opposed to after each execute() call on individual Statements (which happens if autocommit is enabled).

Changing the auto-commit mode through connection.setAutoCommit() will implicitly commit the active transaction and create a new one. From the Javadocs:

NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.

Upvotes: 9

jfcorugedo
jfcorugedo

Reputation: 10051

The implementation of each method inside JDBC API depends on each driver. Oracle may do something very different than MySql does.

However, only calling connection.setAutoCommit(false); doesn't creates a transaction. It only means that any statement created using this connection will be committed together when you call connection.commit();.

Take a look at this Oracle tutorial.

Upvotes: 4

drgPP
drgPP

Reputation: 936

The JavaDocs provide a nice explanation of this use case in the Using Transactions Section

Disabling Auto-Commit Mode

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)

The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection:

con.setAutoCommit(false);

Upvotes: 3

Related Questions