1.8 Database Interaction

PL/SQL is tightly integrated with the underlying SQL layer of the Oracle database. You can execute SQL statements (UPDATE, INSERT, DELETE, MERGE, and SELECT) directly in PL/SQL programs. You can also execute Data Definition Language (DDL) statements through the use of dynamic SQL. In addition, you can manage transactions with COMMIT, ROLLBACK, and other Data Control Language (DCL) statements.

1.8.1 Transaction Management

The Oracle RDBMS provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, specify ROLLBACK FORCE). A transaction begins with the first change to data and ends with either a COMMIT or a ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or there can be multiple transactions in a single PL/SQL block. The PL/SQL-supported transaction statements include COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE, described in the following sections.

1.8.1.1 COMMIT

COMMIT [WORK] [COMMENT text];

COMMIT makes the database changes permanent and visible to other database sessions. The WORK keyword is optional and only aids readability it is rarely used. The COMMENT text is optional and can be up to 50 characters in length. It is only germane to in-doubt distributed (two-phase commit) transactions. The database statement COMMIT FORCE, also for distributed transactions, is not supported in PL/SQL.

1.8.1.2 ROLLBACK

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

ROLLBACK undoes the changes made in the current transaction either to the beginning of the transaction or to a savepoint. A savepoint is a named processing point in a transaction, created with the SAVEPOINT statement. Rolling back to a savepoint is a partial rollback of a transaction, wiping out all changes (and savepoints) that occurred later than the named savepoint.

1.8.1.3 SAVEPOINT

SAVEPOINT savepoint_name;

SAVEPOINT establishes a savepoint in the current transaction. savepoint_name is an undeclared identifier you do not declare it. More than one savepoint can be established within a transaction. If you reuse a savepoint name, that savepoint is moved to the later position and you will not be able to roll back to the initial savepoint position.

1.8.1.4 SET TRANSACTION

SET TRANSACTION READ ONLY; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION USE ROLLBACK SEGMENT rbseg_name;

SET TRANSACTION has three transaction control functions:

READ ONLY

Marks the beginning of a read-only transaction. This indicates to the RDBMS that a read-consistent view of the database is to be enforced for the transaction (the default is for the statement). This read-consistent view means that only changes committed before the transaction begins are visible for the duration of the transaction. The transaction is ended with either a COMMIT or a ROLLBACK. Only LOCK TABLE, SELECT, SELECT INTO, OPEN, FETCH, CLOSE, COMMIT, or ROLLBACK statements are permitted during a read-only transaction. Issuing other statements, such as INSERT or UPDATE, in a read-only transaction results in an ORA-1456 error.

ISOLATION LEVEL SERIALIZABLE

Similar to a READ ONLY transaction in that transaction-level read consistency is enforced instead of the default statement-level read consistency. Serializable transactions do allow changes to data, however.

USE ROLLBACK SEGMENT

Tells the RDBMS to use the specifically named rollback segment rbseg_name. This statement is useful when only one rollback segment is large, and a program knows that it needs to use the large rollback segment, such as during a month-end close operation. For example, if we know that our large rollback segment is named rbs_large, we can tell the database to use it by issuing the following statement before our first change to data:

SET TRANSACTION USE ROLLBACK SEGMENT rbs_large;

1.8.1.5 LOCK TABLE

LOCK TABLE table_list IN lock_mode MODE [NOWAIT];

This statement bypasses the implicit database row-level locks by explicitly locking one or more tables in the specified mode. The table_list is a comma-delimited list of tables. The lock_mode is one of the following: ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, or EXCLUSIVE. The NOWAIT keyword specifies that the RDBMS should not wait for a lock to be released. If there is a lock when NOWAIT is specified, the RDBMS raises the exception "ORA-00054: resource busy and acquire with NOWAIT specified." The default RDBMS locking behavior is to wait indefinitely.

1.8.2 Autonomous Transactions

Autonomous transactions, introduced in Oracle8i, execute within a block of code as separate transactions from the outer (main) transaction. Changes can be committed or rolled back in an autonomous transaction without committing or rolling back the main transaction. Changes committed in an autonomous transaction are visible to the main transaction, even though they occur after the start of the main transaction. Those changes committed in an autonomous transaction are visible to other transactions as well. The RDBMS suspends the main transaction while the autonomous transaction executes:

PROCEDURE main IS BEGIN    UPDATE ... -- Main transaction begins here.    DELETE ...    at_proc;   -- Call the autonomous transaction.    SELECT ...    INSERT ...    COMMIT;    -- Main transaction ends here. END; PROCEDURE at_proc IS    PRAGMA AUTONOMOUS_TRANSACTION; BEGIN         -- Main transaction suspends here.    SELECT ...    INSERT ... -- Autonomous transaction begins here.    UPDATE ...    DELETE ...    COMMIT;    -- Autonomous transaction ends here. END;          -- Main transaction resumes here.

So, changes made in the main transaction are not visible to the autonomous transaction, and if the main transaction holds any locks that the autonomous transaction waits for, a deadlock occurs. Using the NOWAIT option on UPDATE statements in autonomous transactions can help to minimize this kind of deadlock. Functions and procedures (local program, standalone, or packaged), database triggers, top-level anonymous PL/SQL blocks, and object methods can be declared autonomous via the compiler directive PRAGMA AUTONOMOUS_TRANSACTION. In addition, there must be a commit or a rollback at each exit point in the autonomous program.