Using auto-commit

A new connection to a Derby database is in auto-commit mode by default, as specified by the JDBC standard.

Auto-commit mode means that when a statement is completed, the method commit is called on that statement automatically. Auto-commit in effect makes every SQL statement a transaction. The commit occurs when the statement completes or the next statement is executed, whichever comes first. In the case of a statement returning a forward only ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed explicitly. In the case of a statement returning a scrollable ResultSet, the statement completes only when the ResultSet has been closed explicitly.

Some applications might prefer to work with Derby in auto-commit mode; some might prefer to work with auto-commit turned off. You should be aware of the implications of using either model.

You should be aware of the following when you use auto-commit:

The following table summarizes how applications behave with auto-commit on or off.

Table 1. Application behavior with auto-commit on or off
Topic Auto-Commit On Auto-Commit Off
Transactions Each statement is a separate transaction. Commit() or rollback() completes a transaction.
Database-side JDBC routines (routines that use nested connections) Auto-commit is turned off. Works (no explicit commits or rollbacks are allowed).
Updatable cursors Works for holdable cursors; does not work for non-holdable cursors. Works.
Multiple connections accessing the same data Works. Works. Lower concurrency when applications use SERIALIZABLE isolation mode and table-level locking.
Updatable ResultSets Works. Works.
Savepoints Does not work. Works.
Related concepts
Transactions when auto-commit is disabled
Explicitly closing Statements, ResultSets, and Connections
Statement versus transaction runtime rollback
Result set and cursor mechanisms
Using savepoints
Related tasks
Turning off auto-commit