Understanding Oracle transactions

All changes of data in an Oracle database can only be done within a transaction. A transaction must either be committed or rolled back. Data changed within a transaction is not visible to another session until it is commited.

All SQL that is submitted by a user is considered an individual statement. One or more statements that signify a complete business transaction and signify a unit of work. Once you have determined a statement or group of statements (unit of work) has been executed properly you can then issue the commit command to tell Oracle to make the alterations permanent and visible to other users. Be aware that DDL operations implicitly issue a commit for your session.

If you want to reverse the proposed changes you have issued to Oracle, the rollback statement will back out those statements to the last issued commit statement.

Within Oracle you can place a marker within your transaction stream that will allow you to rollback to that marker. This functionality allows you to segment a long transactions into sub-units of work giving you the flexibility to recover from a failure or code breakdown and replay the work after the savepoint until it is done properly.

Oracle also allows you to name a transaction with the SET TRANASACTION statement. This allows you to track all the statements related to a transaction and simplifies some performance monitoring issues around determining what application is consuming resources. This information can be viewed in the V$TRANASACTION view and related back to SQL and SESSION information.

When a user issues a commit statement or terminates successfully from Oracle it is Normal completion. If a user session disconnects abnormally from Oracle, ie. Network or Database goes down, it is Abnormal completion. When the server starts up the recovery will be done using information from redo logs.

A two-phase commit is a mechanism that allows for a hand-shake between distributed transactions and the Oracle database server to verify what was committed was actually applied on the database.

Autonomous transactions are called from within another transaction (unit of work) and executed independently of the calling transaction. After the autonomous transaction completes, control is returned to the calling transaction and does not play a role in a commit or rollback scenario.

Transaction Isolation Levels

The ANSI/ISO SQL standard defines four levels of transaction isolation, with different possible outcomes for the same transaction scenario. That is, the same work performed in the same fashion with the same inputs may result in different answers, depending on your isolation level. These levels are defined in terms of three phenomena that are either permitted or not at a given isolation level:

Dirty read: The meaning of this term is as bad as it sounds. You're permitted to read uncommitted, or dirty, data. You can achieve this effect by just opening an OS file that someone else is writing and reading whatever data happens to be there. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored.
Nonrepeatable read: This simply means that if you read a row at time T1 and try to reread that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.
Phantom read: This means that if you execute a query at time T1 and re-execute it at time T2, additional rows may have been added to the database, which may affect your results. This differs from a nonrepeatable read in that with a phantom read, data you already read hasn't been changed, but instead, more data satisfies your query criteria than before.

Note that the ANSI/ISO SQL standard defines transaction-level characteristics, not just individual statement-by-statement-level characteristics.

The SQL isolation levels are defined based on whether they allow each of the preceding phenomena. It's interesting to note that the SQL standard doesn't impose a specific locking scheme or mandate particular behaviors, but rather describes these isolation levels in terms of these phenomena—allowing for many different locking/concurrency mechanisms to exist .

Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Table 1: ANSI isolation levels
READ UNCOMMITTED Permitted Permitted Permitted

READ COMMITTED -- Permitted Permitted

REPEATABLE READ -- -- Permitted

SERIALIZABLE -- -- --

Let us consider some cases related to serializable transactions

Session 1 Session 2

1
SQL> select salary from employees where 2 employee_id=100 for update; SALARY ---------- 1100
SQL> update employees set salary=1500 2 where employee_id=100;

2
SQL> rollback; Rollback complete.
SQL> 1 row updated.

3
SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1100
4
SQL> commit; Commit complete.
5
SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1500 SQL> set transaction isolation level serializable; Transaction set.
6
SQL> set transaction isolation level serializable; Transaction set. SQL> update employees set salary=1800 2 where employee_id=100; 1 row updated.
7
SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1500
7
SQL> commit; Commit complete.
8
SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1500

9
SQL> commit; Commit complete. SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1800


Your Name:
Email:
Subject:
Comments:

	Session 1	Session 2
1	SQL> select salary from employees where 2 employee_id=100 for update; SALARY ---------- 1100	SQL> update employees set salary=1500 2 where employee_id=100;
2	SQL> rollback; Rollback complete.	SQL> 1 row updated.
3	SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1100
4		SQL> commit; Commit complete.
5	SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1500 SQL> set transaction isolation level serializable; Transaction set.
6		SQL> set transaction isolation level serializable; Transaction set. SQL> update employees set salary=1800 2 where employee_id=100; 1 row updated.
7	SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1500
7		SQL> commit; Commit complete.
8	SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1500
9	SQL> commit; Commit complete. SQL> select salary from employees 2 where employee_id=100; SALARY ---------- 1800

Understanding Oracle transactions

See Also