Oracle SQL*Plus Pocket Reference, 3rd Edition

Transaction Management

Oracle implements several statements to help you manage transactions. By default, a transaction begins whenever you issue your first SQL statement. Once a transaction begins, you end it by doing one of the following:

• Issue a COMMIT

• Issue a ROLLBACK

• Issue a DDL statement

DDL statements (the ALTER and CREATE statements, for example) are special in that they implicitly end any open transaction. Thus, when issuing a DDL statement, it’s possible to both begin and end a transaction with the same statement.

SET TRANSACTION

Use SET TRANSACTION explicitly to begin a transaction, especially when you want to specify transaction attributes such as isolation level.

SET TRANSACTION [attribute [,attribute...] ]
    NAME 'transaction_name';

attribute :=
    {READ {ONLY | WRITE}
    | ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
    | USE ROLLBACK SEGMENT  segment_name }

The READ COMMITTED isolation level is Oracle’s default. It allows you to see changes made by other transactions as soon as they have been committed. Isolation-level SERIALIZABLE is more strict. With SERIALIZABLE, you can’t modify any data that has been modified by others (but not committed before your transaction started). SERIALIZABLE also gives a consistent view of the data. You won’t see changes committed by other users after your transaction begins. The following statement gives you a serializable transaction:

SET TRANSACTION
   ISOLATION LEVEL SERIALIZABLE
   NAME 'Jonathan''s Transaction';

READ ONLY transactions allow you to issue ...