Transactions

A transaction is a set of one or more SQL statements that make up a logical unit of work that you can either commit or roll back and that will be recovered in the event of a system failure. All the statements in the transaction are atomic. A transaction is associated with a single Connection object (and database). A transaction cannot span Connections (or databases).

Derby permits schema and data manipulation statements (DML) to be intermixed within a single transaction. If you create a table in one transaction, you can also insert into it in that same transaction. A schema manipulation statement (DDL) is not automatically committed when it is performed, but participates in the transaction within which it is issued. Because DDL requires exclusive locks on system tables, keep transactions that involve DDL short.

When auto-commit is disabled (see Using Auto-Commit), you use a Connection object's commit and rollback methods to commit or roll back a transaction. The commit method makes permanent the changes resulting from the transaction and releases locks. The rollback method undoes all the changes resulting from the transaction and releases locks. A transaction encompasses all the SQL statements executed against a single Connection object since the last commit or rollback.

You do not need to explicitly begin a transaction. You implicitly end one transaction and begin a new one after disabling auto-commit, changing the isolation level, or after calling commit or rollback.

Committing a transaction also closes all ResultSet objects excluding the ResultSet objects associated with cursors with holdability true. The default holdability of the cursors is true and ResultSet objects associated with them need to be closed explicitly. A commit will not close such ResultSet objects. (See Holdable Cursors for more information.) It also releases any database locks currently held by the Connection, whether or not these objects were created in different threads.

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. For more information about cursors, see SQL and JDBC ResultSet/Cursor Mechanisms.

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

You can disable auto-commit with the Connection class's
setAutoCommit method.

//using the JDBC setAutoCommit method
conn.setAutoCommit(false);

You should explicitly close Statements, ResultSets, and Connections when you no longer need them. Connections to Derby are resources external to an application, and the garbage collector will not close them automatically.

For example, close a Statement object using its close method; close a Connection object using its close method. If auto-commit is disabled, active transactions need to be explicitly committed or rolled back before closing the connection

When an SQL statement generates an exception, this exception results in a runtime rollback. A runtime rollback is a system-generated rollback of a statement or transaction by Derby, as opposed to an explicit rollback call from your application.

Extremely severe exceptions, such as disk-full errors, shut down the system, and the transaction is rolled back when the database is next booted. Severe exceptions, such as deadlock, cause transaction rollback; Derby rolls back all changes since the beginning of the transaction and implicitly begins a new transaction. Less severe exceptions, such as syntax errors, result in statement rollback; Derby rolls back only changes made by the statement that caused the error. The application developer can insert code to explicitly roll back the entire transaction if desired.

Derby supports partial rollback through the use of savepoints. See the Derby Reference Manual for more information.