Ch 05: Transactions

Transactions take the database from one consistent state to another.

Can begin a transaction with:

Implicitly started by first DML

Transaction control statements:

Statements are implicitly wrapped in Savepoints. Rollbacks to that savepoint are done if the statement fails.

PL/SQL blocks are considered statements.

It is bad programming to do COMMIT or ROLLBACK inside a PL/SQL program.

WHEN OTHERS
    RAISE;

or use RAISE_APPLICATION_ERROR

ALTER SESSION SET plsql_warnings='enable:all';

DDL always commits

10G R2+

Normally COMMIT is synchronous (a physical I/O to disk)

COMMIT WRITE NOWAIT

Used for:

PL/SQL does asynchronous commits:

IMMEDIATE - checked immediately after entire SQL statement has been processed.

SQL not PL/SQL

DEFERRABLE e.g. cascadable update of PK in parent

In a child table, use:

FOREIGN KEY CONSTRAINT child_fk
    REFERENCES parent(pk)
    DEFERRABLE
    INITIALLY IMMEDIATE

Most applications do not check for constraint violations on COMMIT.

SET CONSTRAINT child_fk DEFERRED;
...
SET CONSTRAINT child_fk IMMEDIATE;

Do not create all constraints as deferrable as PK and UK indexes would be structured differently to allow for non-uniqueness.

NOT NULL DEFERRABLE is treated by CBO as NULLABLE.

Data integrity determines transaction boundaries.

Restartable processes require complex logic.

Simplicity: SQL, PL/SQL

Autocommit is BAD!

In-doubt distributed transaction

Over DB link, no COMMIT, DDL, SAVEPOINT.

COMMIT_POINT_STRENGTH increases probability of becoming transaction controller.

Autonomous transactions should be rare!

PRAGMA AUTONOMOUS_TRANSACTION;

Only valid use is to log errors.

Data integrity drives the transaction size.