Dirty read, Non-repeatable, Phantom Read in SQL

date

‣

slug

dirty-read-non-repeatable,-phantom-read-in-sql

status

Published

Dirty read

When a transaction is allowed to read a row that has been modified by an another transaction which is not committed yet that time Dirty Reads occurred. It is mainly occurred because of multiple transaction at a time which is not committed.

Non repeatable

Database concurrency ensures that when multiple operations are occurring at once, the final result is still in agreement — that they concur

A non-repeatable read is one in which data read twice inside the same transaction cannot be guaranteed to contain the same value. Depending on the isolation level, another transaction could have nipped in and updated the value between the two reads.

Non-repeatable reads occur because at lower isolation levels reading data only locks the data for the duration of the read, rather than for the duration of the transaction. Sometimes this behavior might be completely desirable. Some applications may want to know the absolute, real-time value, even mid transaction, whereas other types of transactions might need to read the same value multiple times.

Phantom read

A Phantom read occurs when one user is repeating a read operation on the same records, but has new records in the results set:

READ UNCOMMITTED

Also called a Dirty read. When this isolation level is used, a transaction can read uncommitted data that later may be rolled back. A transaction that uses this isolation level can only fetch data but can't update, delete, or insert data.

READ COMMITTED

With this isolation level, Dirty reads are not possible, but if the same row is read repeatedly during the same transaction, its contents may be changed or the entire row may be deleted by other transactions.

REPEATABLE READ

This isolation level guarantees that a transaction can read the same row many times and it will remain intact. However, if a query with the same search criteria (the same WHERE clause) is executed more than once, each execution may return different set of rows. This may happen because other transactions are allowed to insert new rows that satisfy the search criteria or update some rows in such way that they now satisfy the search criteria.

SERIALIZABLE

This isolation level guarantees that none of the above happens. In addition, it guarantees that transactions that use this level will be completely isolated from other transactions.

Based on this information, we can provide basic guidelines for choosing the proper isolation level for the ODBC connection that is going to be used by Crystal Reports:

READ UNCOMMITTED should be used with reports that do not rely on data accuracy. Usually these same reports also process/access a high number of records. This optimizes performance while executing your report with a minimum number of database locks. Examples of reports in this category:

Statistical information at the end of a month.

Sales report covering a previous year.

Reports running daily that accesses data which is rarely updated.

Reports running daily if the values displayed are only used as indicators.

COMMITTED READ should be used with reports running daily on data that is frequently modified. This enables good performance while executing reports on a "live" database with an average number of record locks that are immediately released. Examples of reports in this category include:

Daily reports on regularly updated data requiring 100 percent accuracy at the time the report is processed.

Reports that provide snapshots of an operation at any time during the day. Such reports are used for monitoring purposes, such as stock exchange status reports.

REPEATABLE READ and SERIALIZABLE should not be used with reports as they do not add value at the time the report is generated, especially when compared to COMMITTED READ.

Having reviewed the transaction isolation levels, you can now configure your ODBC driver