O'Reilly logo

Oracle SQL*Plus Pocket Reference, 3rd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required