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 ResultSet, the statement completes when the last row of the ResultSet has been retrieved or 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 close cursors on commit option set.

  Auto-commit automatically closes cursors, which are explicitly opened with the close on commit option, when you do any in-place updates or deletes.

  A cursor declared to be held across commit can execute updates and issue multiple commits before closing the cursor, but the cursor must be repositioned before any statement following the commit. If this is attempted with auto-commit on, an error is generated.

• Database-side JDBC Procedures (procedures using nested connections)

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

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

• 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() begins a transaction.
  Database-side JDBC procedures (routines using nested connections)	Auto-commit is turned off.	Works (no explicit commits or rollbacks are allowed).
  Updatable cursors	Does not work.	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. Not required by the JDBC program.