2

Many articles and documents says that by setting Auto Commit off, You can start a transaction in JDBC. This Topic also ask same question but It doesn't answer to the question and just said:

Changing the auto-commit mode triggers a commit of the current transaction (if one is active).

Ok. but next?

for finding an answer, I searched and found this:

  • Autocommit transactions:

Each individual statement is a transaction.

  • Explicit transactions:

Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

  • Implicit transactions:

A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

And then I found this:

Committing Transactions

After the auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.

Therefore I conclude that after the auto-commit mode is disabled, we are in Implicit mode and we also know that for disabling auto-commit, a COMMIT statement has been run so after setting the Auto-commit off, we've started a new transaction.

Can we draw such a conclusion based on these cases? is it a right conclusion?

Mohammad Mehdi Sarfejoo
  • 434
  • 1
  • 9
  • 22

1 Answers1

3

No, you cannot. In JDBC, auto-commit only governs when a transaction is to end. A driver is expected to start a transaction when it is needed. Specifically the JDBC 4.3 specification says in section 10.1 Transaction Boundaries and Auto-commit:

When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source. Although some data sources implement an explicit “begin transaction” statement, there is no JDBC API to do so. Typically, a new transaction is started when the current SQL statement requires one and there is no transaction already in place. Whether or not a given SQL statement requires a transaction is also specified by SQL:2003.

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes a transaction commit after each individual SQL statement as soon as that statement is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:

  • For Data Manipulation Language (DML) statements such as Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing.
  • For Select statements, the statement is complete when the associated result set is closed.
  • For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved.

In other words, when you call connection.setAutoCommit(false) no transaction will be started. Only when a statement is executed (or another operation that requires a transaction), a transaction will be started if there is no active transaction.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197