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.

  Table 1. Summary of 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.