Database Isolation Level Cross Reference

Every time you access a relational database for query purposes, a key decision must be made: which isolation level do I use? Getting this wrong could lead to deadlocks, dirty reads or reduced performance – making a bad choice could cause deadlocks, dirty reads or poor performance issues.

There are four standard isolation levels you can select, to help avoid many of the common database concurrency bugs and maximize query performance.

Read Uncommitted

There are various standard database isolation levels designed to safeguard against corruption of certain types of data and to offer distinct performance benefits. Each level aims to eradicate specific forms of data corruption and provide specific advantages in terms of performance.

READ UNCOMMITTED: As one of the least restrictive isolation levels, READ UNCOMMITTED allows transactions to read rows that have been modified but not committed by other transactions, which compromises data integrity and can result in foreign key violations and other errors. This dirty read should be treated seriously since it compromises data integrity and can lead to foreign key violations and errors.

This isolation level is suitable for limited environments that can tolerate some dirty reads, as it helps avoid locking contention and improve performance.

Noting the potential risk that read queries may block or even deadlock when modified queries run is essential for optimal performance; especially if they take advantage of object-level shared locks.

If you want to prevent lock contention and return correct data, consider switching to Snapshot isolation level which guarantees every query will see the same set of data throughout its duration.

Read Committed Repeatable and Serializable are standard database isolation levels designed to guarantee that any transaction viewing the same data throughout its existence, even when other transactions make changes to it.

Though recursion should generally be minimized, transactions may still encounter non-repeatable reads and phantoms; this usually happens when they reexecute search criteria but receive different rows than before.

Write Skew is another common use case, in which two or more queries return the same set of rows but one or more is inconsistent with its search criteria. Two or more queries also return this same set with different data in each row – similar to what happens with queries that return identical results but differing rows containing information.

JE supports three standard database isolation levels, READ COMMITTED, Read Uncommitted, and Repeatable. Each of these isolation levels may be chosen at either startup or runtime.

Read Committed

Database isolation levels in PostgreSQL allow you to balance data locking and versioning for improved performance. Use the SET TRANSACTION ISOLATION LEVEL command to change one transaction’s isolation level, or set it globally across all sessions that connect to your database.

Read Committed is the most prevalent isolation level, as it helps avoid Dirty Reads and other anomalies associated with reading uncommitted data. Unfortunately, however, this may also lead to Non-repeatable Reads or Phantom Rows if other transactions modify data between executions of individual SELECT statements within one transaction.

This isolation level is suitable in situations with few or no conflicts among concurrent transactions, as it does not permit dirty reads orphan rows to take place like other isolation levels do.

With this isolation level, each SELECT statement sees only the database as it was when its own command started; any changes committed during their own command or any committed by other SELECT commands post-start cannot be seen by this SELECT command; neither do uncommitted data or changes committed post-start affect this SELECT statement; also, serialization failures cannot be completely addressed with this isolation level; be prepared to retry transactions as serialization fails occur.

Under this isolation level, transactions acquire shared locks on all of the rows it references before acquiring exclusive locks for each row it references; this approach offers the strongest isolation guarantee of all transaction isolation levels.

Serializable isolation level ensures that a SELECT or UPDATE transaction sees a stable view of the database throughout its duration, regardless of changes from other transactions committing after it began. Furthermore, every transaction must be executed sequentially so that no dirty reads, or non-repeatable reads or orphaned rows occur.

SERIALIZABLE is ideal when you want to protect a data row from multiple concurrent SELECT transactions, preventing them from making changes that modify it simultaneously. Please be aware that it cannot protect against DELETE or UPDATE transactions – applications must be aware of this limitation when selecting this isolation level. In multi-user environments with multiple database servers available it may also be recommended.

Repeatable Read

Standard Database Isolation Levels Explained

A standard database allows transactions to run at various isolation levels to restrict how much of its data other transactions can read or modify, set by the administrator depending on which DBMSes they run on. These isolation levels exist to protect data integrity by preventing blocking, deadlocking and commit-time aborts – designed by administrators as preventative measures against these incidents.

Repeatable Read is an isolation level designed to ensure that a transaction always sees the same data, regardless of other transactions modifying it during its duration. Phantom reads may still occur as can dirty reads.

Phantom reads occur when another transaction writes data item matching the predicate in an executed statement while it’s still active, violating database invariants or potentially leading to data corruption in the data set. This may violate database invariants and lead to corruption.

An isolated transaction must prevent phantom reads by retrying any statements attempting to change a row committed by another transaction before starting its own. TiDB uses optimistic concurrency control when an attempt is made at updating such rows already modified by other transactions.

This may incur a performance penalty but is necessary to prevent an erroneous read and enable applications to safely access rows modified by other transactions.

When a transaction updates a column, it acquires a table-level write lock to prevent other transactions from reading the results before the commit occurs. After completion, this write lock is released and retried row.

Retries will fail both times if a row was changed by an updating transaction before starting their original transaction, thus eliminating serialized access anomalies.

As with Repeatable Read isolation, Serializable isolation ensures that the visible state of a database reflects an imagined scenario that could have existed had there been no concurrent transactions taking place – providing a stronger guarantee than that required by SQL standard.

SERIALIZABLE isolation is one of the strongest of the standard isolation levels and provides a robust guarantee of ACID integrity. However, it should be kept in mind when selecting this isolation level to avoid potential difficulties that might arise when choosing it.

Serializable

The SQL standard specifies various isolation levels for database transactions, balancing correctness and performance. When these levels are insufficiently met, applications may experience subtle and serious bugs that negatively impact user experiences and result in data inconsistencies that negatively affect data consistency and integrity.

Read Committed, Repeatable Read, and Serializable are typical isolation levels that a transaction must adhere to for accurate data transfer. Each level comes with its own set of rules that a transaction must abide by to guarantee data integrity.

Read Committed – This isolation level guarantees that any data read is committed when it is read, preventing dirty reads. However, it may still cause non-repeatable reads orphan reads to occur.

Repeatable Read – This isolation level is the strictest, as it guarantees all rows read by a transaction will be identical. This occurs because a transaction holds read locks on each row it refers to and writes locks when updates and deletes occur on those references. Although Repeatable Read effectively prevents non-repeatable reads, enforcing business logic using this isolation level may prove more challenging.

Serializable – This isolation level ensures that each transaction runs as though it were the only one in the system, thus avoiding dirty reads, non-repeatable reads, and phantom reads. It does this by setting range locks to cover all key values used by statements executed within that transaction to search the key values in that range; these locks remain held until its completion and prevent other transactions from reading or changing any data in them.

Locks can be acquired using either the SELECT statement or the INSERT statement to insert values. Both these statements also acquire range locks to secure key values containing their insert value, preventing other transactions from updating or deleting it.

Some databases support additional options or features beyond the three standard isolation levels, such as snapshot isolation and multi-version concurrency control (MVCC). These isolation levels offer greater flexibility for preventing consistency anomalies than their standard counterparts.

Here is a table quick reference of some common database and/or connection types that use connection and equivalent isolation levels. This quick reference may prove useful as a job aid reference when working with and making decisions about isolation level usage.

Database isolation levels

Data sources	Most restrictive isolation level	More restrictive isolation level	Less restrictive isolation level	Least restrictive isolation level
Amazon SimpleDB	Serializable	Repeatable read	Read committed	Read Uncommitted
dashDB	Repeatable read	Read stability	Cursor stability	Uncommitted read
DB2® family of products	Repeatable read	Read stability*	Cursor stability	Uncommitted read
Informix®	Repeatable read	Repeatable read	Cursor stability	Dirty read
JDBC	Serializable	Repeatable read	Read committed	Read Uncommitted
MariaDB	Serializable	Repeatable read	Read committed	Read Uncommitted
Microsoft SQL Server	Serializable	Repeatable read	Read committed	Read Uncommitted
MySQL	Serializable	Repeatable read	Read committed	Read Uncommitted
ODBC	Serializable	Repeatable read	Read committed	Read Uncommitted
Oracle	Serializable	Serializable	Read committed	Read committed
PostgreSQL	Serializable	Repeatable read	Read committed	Read committed
Sybase	Level 3	Level 3	Level 1	Level 0