Basic Transaction Control

This section discusses how to manage your changes to the database.

For information about SQLJ support for more advanced transaction control functions--access mode and isolation level--see "Advanced Transaction Control".

About Transactions

A transaction is a sequence of SQL statements that Oracle treats as a single unit. A transaction begins with the first executable SQL statement after any of the following:

• connection to the database

• commit (committing changes to the database, either automatically or manually)

• rollback (canceling changes to the database)

A transaction ends with a commit or rollback.

(Note that in Oracle SQLJ, all DDL commands such as CREATE... and ALTER... include an implicit commit.)

Automatic Commits vs. Manual Commits

In using SQLJ or JDBC, you can either have your changes automatically committed to the database or commit them manually. In either case, each commit starts a new transaction. You can specify automatic commits by enabling the auto-commit flag, either when you define a SQLJ connection, or by using the setAutoCommit() method of the underlying JDBC Connection object of an existing connection. You can use manual control by disabling the auto-commit flag and using SQLJ commit and rollback statements.

Enabling auto-commit may be more convenient but gives you less control. You have no option to roll back changes, for example. You also cannot save a batch of updates to be committed all at once--operations are committed as soon as they are executed. This results in slower overall execution because every individual operation incurs the full overhead of a transaction, instead of this overhead being spread over several operations.

There are also some SQLJ or JDBC features that are incompatible with automatic commits. For example, you must disable the auto-commit flag for update-batching or SELECT FOR UPDATE syntax to work properly.

Specifying Auto-Commit As You Define a Connection

When you use the Oracle.connect() or Oracle.getConnection() method to create a DefaultContext instance and define a connection, the auto-commit flag is set to false by default. There are signatures of these methods, however, that allow you to set this flag explicitly. The auto-commit flag is always the last parameter.

The following is an example of instantiating DefaultContext and using the default false setting for auto-commit:

Oracle.getConnection (
   "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

Or you can specify a true setting:

Oracle.getConnection (
   "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);

For the complete list of signatures for Oracle.connect() and Oracle.getConnection(), see "More About the Oracle Class".

If you use a constructor to create a connection context instance, either of DefaultContext or of a declared connection context class, you must specify the auto-commit setting. Again, it is the last parameter, as in the following example:

DefaultContext ctx = new DefaultContext (
   "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", false);

For the complete list of signatures for DefaultContext constructors, see "More About the DefaultContext Class".

If you have reason to create a JDBC Connection instance directly, then the auto-commit flag is set to true by default if your program runs on a client, or false by default if it runs in the server. (You cannot specify an auto-commit setting when you create a JDBC Connection instance directly, but you can use the setAutoCommit() method to alter the setting, as described in "Modifying Auto-Commit in an Existing Connection".)

Modifying Auto-Commit in an Existing Connection

There is typically no reason to change the auto-commit flag setting for an existing connection, but you can if desired. You can do this by using the setAutoCommit() method of the underlying JDBC Connection object.

You can retrieve the underlying JDBC Connection object by using the getConnection() method of any SQLJ connection context instance (whether it is an instance of the DefaultContext class or of a connection context class you have declared).

You can accomplish these two steps at once, as follows. In these examples, ctx is a SQLJ connection context instance:

ctx.getConnection().setAutoCommit(false);

or:

ctx.getConnection().setAutoCommit(true);

Using Manual COMMIT and ROLLBACK

If you disable the auto-commit flag, then you must manually commit any changes to the database.

To commit any changes (such as updates, inserts, or deletes) that have been executed since the last commit, use the SQLJ commit statement, as follows:

#sql { commit };

To rollback (cancel) any changes that have been executed since the last commit, use the SQLJ rollback statement, as follows:

#sql { rollback };

Do not use the commit or rollback commands when auto-commit is enabled. This will result in unspecified behavior (or perhaps SQL exceptions).

Effect of Commit and Rollback on Iterators and Result Sets

Commits (either automatic or manual) and rollbacks do not affect open result sets and iterators. The result sets and iterators will still be open, and all that is relevant to their content is the state of the database at the time of execution of the SELECT statements that populated them.

This also applies to UPDATE, INSERT, and DELETE statements which are executed after the SELECT statements--execution of these statements does not affect the contents of open result sets and iterators.

Consider a situation where you SELECT, then UPDATE, then COMMIT. A result set or iterator populated by the SELECT statement will be unaffected by the UPDATE and COMMIT.

As a further example, consider a situation where you UPDATE, then SELECT, then ROLLBACK. A result set or iterator populated by the SELECT will still contain the updated data, regardless of the subsequent ROLLBACK.

---