Tuesday, 5 August 2014

Oracle Phantom, non-repeatable, Dirty Reads


There are a few basics on how transactions operate within the relational database model. In terms of how database isolation levels function, the ANSI/ISO SQL standard (SQL92) breaks these down into four levels of transaction isolation. Each isolation level contains various degrees of impact on transaction processing. The fourth level of transaction isolation, read uncommitted, is not supported by Oracle. Dirty reads, fuzzy or non-repeatable reads and phantom reads are three phenomena of concern to Oracle database performance.

The isolation levels are explained in terms of dirty reads, fuzzy or non-repeatable reads, or phantom reads, that must be prevented between concurrently executing transactions.
• Dirty reads: Dirty reads occur when a transaction reads data that has been written by another transaction that has not yet been committed.
• Fuzzy or non-repeatable reads: Fuzzy reads occur when a database transaction re-reads data it has already read and then finds that another committed transaction has modified or deleted the same data.
• Phantom reads: Phantom reads occur when a transaction in Oracle re-executes a query that returns the rows that satisfy a particular search and discovers that another committed transaction has already inserted additional rows that meet the condition.

The database standard for SQL92 expounds these four levels of isolation and whether or not dirty reads, non-repeatable reads or phantom reads are possible within the level of isolation, as shown in the below table.

The Oracle database uses the read committed and serializable isolation levels in addition to a read-only mode. By default, Oracle uses the read committed isolation level for database transactions.

Thus, according to the information in the table, dirty reads are only possible at the read uncommitted isolation level; however, the serializable isolation level is sufficient to prevent dirty reads as well as non-repeatable and phantom reads.

At the Oracle default level of read committed, dirty reads are not possible, but fuzzy or phantom reads are.

Photo: Oracle Phantom, non-repeatable, Dirty Reads There are a few basics on how transactions operate within the relational database model. In terms of how database isolation levels function, the ANSI/ISO SQL standard (SQL92) breaks these down into four levels of transaction isolation. Each isolation level contains various degrees of impact on transaction processing. The fourth level of transaction isolation, read uncommitted, is not supported by Oracle. Dirty reads, fuzzy or non-repeatable reads and phantom reads are three phenomena of concern to Oracle database performance. The isolation levels are explained in terms of dirty reads, fuzzy or non-repeatable reads, or phantom reads, that must be prevented between concurrently executing transactions. • Dirty reads: Dirty reads occur when a transaction reads data that has been written by another transaction that has not yet been committed. • Fuzzy or non-repeatable reads: Fuzzy reads occur when a database transaction re-reads data it has already read and then finds that another committed transaction has modified or deleted the same data. • Phantom reads: Phantom reads occur when a transaction in Oracle re-executes a query that returns the rows that satisfy a particular search and discovers that another committed transaction has already inserted additional rows that meet the condition. The database standard for SQL92 expounds these four levels of isolation and whether or not dirty reads, non-repeatable reads or phantom reads are possible within the level of isolation, as shown in the below table. The Oracle database uses the read committed and serializable isolation levels in addition to a read-only mode. By default, Oracle uses the read committed isolation level for database transactions. Thus, according to the information in the table, dirty reads are only possible at the read uncommitted isolation level; however, the serializable isolation level is sufficient to prevent dirty reads as well as non-repeatable and phantom reads. At the Oracle default level of read committed, dirty reads are not possible, but fuzzy or phantom reads are.