Transactions::JAVA-JDBC

Transaction Boundaries and Autocommit

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 SQL99.

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes the JDBC driver to do a transaction commit after each individual SQL statement as soon as it 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 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. The result set is closed as soon as one of the following occurs:
• all of the rows have been retrieved
• the associated Statement object is re-executed
• For CallableStatement objects, the statement is complete when all of the associated result sets have been closed.

Disabling Auto-commit Mode

CODE EXAMPLE shows how to disable auto-commit mode.

// Assume con is a Connection object
con.setAutoCommit(false);

CODE EXAMPLE Setting auto-commit off When auto-commit is disabled, each transaction must be explicitly commited by calling the Connection method commit or explicitly rolled back by calling the Connection method rollback, respectively. This is appropriate for cases where transaction management is being done in a layer above the driver, such as:

• when the application needs to group multiple SQL statements into a single transaction
• when the transaction is being managed by the application server

The default is for auto-commit mode to be enabled when the Connection object is created. If the value of auto-commit is changed in the middle of a transaction, the current transaction is committed. It is an error to enable auto-commit for a connection participating in a distributed transaction, as described in “Distributed Transactions”.