# 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:

- Cursors

  You cannot use auto-commit if you do any positioned updates or deletes (that is, an update or delete statement with a WHERE CURRENT OF clause) on cursors which have the `ResultSet.CLOSE_CURSORS_AT_COMMIT` holdability value set.

  Auto-commit automatically closes cursors that are explicitly opened with the `ResultSet.CLOSE_CURSORS_AT_COMMIT` value, when you do any in-place updates or deletes.

  An updatable cursor declared to be held across commit (this is the default value) can execute updates and issue multiple commits before closing the cursor. After an explicit or implicit commit, a holdable forward-only cursor must be repositioned with a call to the `next` method before it can accessed again. In this state, the only other valid operation besides calling `next` is calling `close`.

- Database-side JDBC routines (routines using nested connections)

  You cannot execute functions within SQL statements if those functions perform a commit or rollback on the current connection. Since in auto-commit mode all SQL statements are implicitly committed, Derby turns off auto-commit during execution of database-side routines and turns it back on when the statement completes.

  Routines that use nested connections are not permitted to turn auto-commit on or off.

- Table-level locking and the SERIALIZABLE isolation level

  When an application uses table-level locking and the SERIALIZABLE isolation level, all statements that access tables hold at least shared table locks. Shared locks prevent other transactions that update data from accessing the table. A transaction holds a lock on a table until the transaction commits. So even a SELECT statement holds a shared lock on a table until its connection commits and a new transaction begins.

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.

Parent topic: Transactions
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