Oracle Concepts - 10 Transactions

ACID refers to the basic properties of a database transaction: Atomicity, Consistency, Isolation, and Durability.

All Oracle database, Oracle RDB and InnoDB transactions comply with these properties. However, Oracle's Berkeley DB database is not ACID-compliant.

Atomicity

The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.

Consistency

The transaction takes the resources from one consistent state to another.

Isolation

A transaction's effect is not visible to other transactions until the transaction is committed.

Durability

Changes made by the committed transaction are permanent and must survive system failure.

SCN can refer to:

• System Change Number - A number, internal to Oracle that is incremented over time as change vectors are generated, applied, and written to the Redo log.

• System Commit Number - A number, internal to Oracle that is incremented with each database COMMIT.

INSERT INTO tab1 VALUES ('val1', 'val2', 'val3');
COMMIT; Sync COMMITs
COMMIT WRITE IMMEDIATE WAIT; Async COMMITs
COMMIT WRITE BATCH NOWAIT; COMMIT performance

• don't COMMIT after each record, batch COMMITS together

• put your redo logs on faster disks

• ensure the DB doesn't switch too many logs (increase redo log size)

• use Async COMMITs

 Example

SQL> DELETE FROM dept WHERE deptno = 123;
SQL> SAVEPOINT step1;

INSERT INTO dept VALUES (123, 'Editors', Oracle FAQ');
SAVEPOINT step2;
ROLLBACK TO SAVEPOINT step1;
COMMIT; ROLLBACK
INSERT INTO tab1 VALUES ('val1', 'val2', 'val3');
ROLLBACK; Partial rollback
DELETE FROM emp WHERE empno = 123;
SAVEPOINT x;
UPDATE emp SET sal = sal * 1.1;
ROLLBACK TO SAVEPOINT x;
COMMIT;
- Pragma is a directive that instructs the compiler to perform a compilation option.
- Distributed database is a set of databases in a distributed system that can appear to applications as a single data source.
- Distributed transaction is a transaction that includes one or more statements that update data on two or more distinct nodes of a distributed database, using a schema object called a database link
- in-doubt distributed transaction occurs when a two-phase commit was interrupted by any type of system or network failure.

Note: System Commit Numbers and System Change Numbers share the same internal sequence generator.

COMMIT

COMMIT is an SQL command used to instruct the database to save all changes made and end the current transaction. Use the ROLLBACK command to undo changes.

Example:

This is the default behavior.

Example:

Example:

Async commits are normally a bad idea (transactions may be lost if the system crashes) and should only be allowed under very limited and controlled conditions.

When doing batch loads, you should rather do batch commits. This feature should only be used if batch commits cannot be done (which, in general should not be the case).

To increase COMMIT performance, look at:

A savepoint is a marker or point in an transaction to which you can later roll back.

ROLLBACK is an SQL command used to undo changes made to the database (restore data to its state prior to the user making changes).

Example:

One can specify savepoints to mark a point in a transaction to rollback to. Example:

- Autonomous transaction is an independent transaction that can be called from another transaction, called the main transaction

- Autonomous scope, which is a routine marked with the pragma AUTONOMOUS_TRANSACTION. In this context, routines include top-level anonymous PL/SQL blocks and PL/SQL subprograms and triggers. 

- Database link describes how one database instance can log in to another database instance.