ACID Transaction Properties, Transaction Locking and Transaction Isolation Levels : Introduction « Transaction « Oracle PL/SQL Tutorial

Database theory has a more rigorous definition of a transaction and states that a transaction has four fundamental properties, known as ACID properties:

1. Atomicity Transactions are committed or rolled back as a group, and are atomic, meaning that all SQL statements contained in a transaction are considered to be a single indivisible unit.
2. Consistency Transactions ensure that the database state remains consistent, meaning that the database starts at one consistent state and ends in another consistent state when the transaction finishes.
3. Isolation Separate transactions should appear to run without interfering with each other.
4. Durability Once a transaction has been committed, the database changes are preserved, even if the machine on which the database software runs later crashes.

A transaction cannot get a lock on a row while another transaction already holds the lock on that row.

The easiest way to understand default locking is: readers don't block readers, writers don't block readers, and writers only block writers when they attempt to modify the same row.

The transaction isolation level is the degree to which the changes made by one transaction are separated from other transactions running concurrently.

Phantom reads

T1 reads a set of rows returned by a specified WHERE clause.

T2 then inserts a new row, which also happens to satisfy the WHERE clause of the query previously used by T1.

T1 then reads the rows again using the same query, but now sees the additional row just inserted by T2.

This new row is known as a 'phantom' because to T1 this row seems to have magically appeared.

Nonrepeatable reads

T1 reads a row, and T2 updates the same row just read by T1.

T1 then reads the same row again and discovers that the row it read earlier is now different.

This is known as a 'nonrepeatable' read, because the row originally read by T1 has been changed.

Dirty reads

T1 updates a row, but doesn't commit the update.

T2 reads the updated row.

T1 then performs a rollback, undoing the previous update.

Now the row just read by T2 is no longer valid (it's 'dirty') because the update made by T1 wasn't committed when the row was read by T2.

To deal with these potential problems, databases implement various levels of transaction isolation to prevent concurrent transactions from interfering with each other.

The SQL standard defines the following transaction isolation levels, shown in order of increasing isolation:

READ UNCOMMITTED Phantom reads, nonrepeatable reads, and dirty reads are permitted.

READ COMMITTED Phantom reads and nonrepeatable reads are permitted, but dirty reads are not.

REPEATABLE READ Phantom reads are permitted, but nonrepeatable and dirty reads are not.

SERIALIZABLE Phantom reads, nonrepeatable reads, and dirty reads are not permitted.

The Oracle database supports the READ COMMITTED and SERIALIZABLE transaction isolation levels.

It doesn't support READ UNCOMMITTED or REPEATABLE READ levels.

The default transaction isolation level defined by the SQL standard is SERIALIZABLE,

but the default used by the Oracle database is READ COMMITTED, which is usually acceptable for nearly all applications.

Although you can use SERIALIZABLE with the Oracle database, it may increase the time your SQL statements take to complete, so you should only use SERILIZABLE if you absolutely have to.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813