Isolation

Techopedia explains Isolation

A number of isolation levels are defined to control degree of data locking. A high level of isolation might result in locking overhead for the system creating deadlocks. The four major isolation levels are:

• Read Uncommitted: This level deals with dirty reads, where the read data is not consistent with other parts of the table or query and is not committed. Here data is read directly from table blocks without any verification, validation and processing. Hence the data is as dirty as it can be.

• Read Committed: In this case, rows that a query returns are rows already committed when the query started. As commit is completed before the query started, the result is not displayed in the query output.

• Repeated Read: The rows returned by a query in this case are committed when the transaction was started. The changes made are not present in the transaction and hence do not appear in the query result.

• Serializable: In this level, transactions occur in a completely isolated fashion, serially one after another. Databases like Oracle and Postgre SQL sometimes do not guarantee serial ordering of transactions, but do support snapshot isolation where all reads in a transaction are consistent snapshots of the database and transaction commits only if no updates produce conflicts with other concurrent updates made since the snapshot.

Anomalies permitted by snapshot isolations may lead to violation of data consistency by interleaving transactions which maintain consistency. These anomalies can be eliminated by introducing update conflicts or artificial locking. All databases permit users to set their default isolation levels. Perfect isolation levels chosen prevent applications from introducing errors such as dirty reads, repeatable reads and phantom reads. When the first transaction reads uncommitted changes made by the second transaction, it gives rise to dirty reads. When a data read remains the same if read again during the same transaction, it is a repeatable read. Phantom reads occur when new records added are pointed out by transactions prior to insert.

The different database locks isolation levels deal with are:

• Read Locks: Read locks prevents changing data read during a transaction till the transaction ends eliminating cases of repeatable reads. Other transactions can read this data but no write or change access is provided.

• Write Locks: Write locks prevent other transactions from changing data until the transaction ends.

• Exclusive Write Locks: Exclusive write lock prevents other transactions from reading or altering data until the current transaction ends.

• Snapshots: A snapshot is a frozen view of the data when the transaction starts. It prevents dirty reads, nonrepeatable reads and phantom reads.