A Quick Primer on Isolation Levels and Dirty Reads

Recently MongoDB found itself at the top of Reddit again when developer David Glasser learned the hard way that MongoDB performs dirty reads by default. In this article we will explain what isolation levels and dirty reads are and how they are implemented in popular databases.

In ANSI SQL, there are four standard isolation levels: Serializable, Repeatable Reads, Read Committed, and Read Uncommitted.

The default for many databases is Read Committed, which only guarantees that you won’t see data from a transition while that transaction is in progress. It does this by briefly acquiring locks during reads, while maintaining write locks until the transaction is committed.

If you need to repeat the same read multiple times during a transaction, and want to be reasonably certain that it always returns the same value, you need to hold a read lock for the entire duration. This is automatically done for you when using the Repeatable Reads isolation level.

We say “reasonably certain” for Repeatable Reads because of the possibility of “phantom reads”. A phantom read can occur when you perform a query using a where clause such as “WHERE Status = 1”. Those rows will be locked, but nothing prevents a new row matching the criteria from being added. The term "phantom" applies to the rows that appear the second time the query is executed.

To be absolutely certain that two reads in the same transaction return the same data, you can use the Serializable isolation level. This uses “range-locks”, which prevent new rows from being added if they match a WHERE clause in an open transaction.

Generally speaking, the higher your isolation level the worse your performance is due to lock contention. So to improve read performance, some databases also support Read Uncommitted. This isolation level ignores locks (and is in fact called NOLOCK in SQL Server). As a result, it can perform dirty reads.

The Problem with Dirty Reads

Before we discuss dirty reads, you have to understand that tables don’t actually exist in databases. A table is just a logical construct. In reality your data is stored in one or more indexes. The primary index is known as a “clustered index” or “heap” in most relational databases. (The terminology varies for NoSQL databases.) So when you perform an insert, it needs to insert a row into each index. When performing an update, the database engine only needs to touch the indexes that reference the column(s) being changed. However, it often has to perform two operations per index, a delete from the old location and an insert into the new location.

In the image below, you can see a simple table and an execution plan wherein two objects are updated, IX_Customer_State and PK_Customer. Since full name wasn’t changed, the IX_Customer_FullName index was skipped.

(Click on the image to enlarge it)

Note: In SQL Server, the PK prefix refers to the primary key, which is usually also the key used for the clustered index. IX is used for the non-clustered indexes. Other databases have their own conventions.

With that out of the way, let’s look at the many ways a dirty read can result in inconsistent data.

Uncommitted reads are the easiest to understand. By ignoring the write lock, a SELECT statement using Read Uncommitted can see a newly inserted or updated row before the transaction in it is fully committed. If that transition is then rolled back, the SELECT operation will return data that, logically speaking, never existed.

Double reads occur when data is moved during an update operation. Let’s say you are reading all of your customer records by state. If the aforementioned update statement is executed between the time you the California records and the time you read the Texas records, you can see customer 1253 twice; once with the old value and once with the new value.

Missed reads happen the same way. If we take customer 1253 and move it from Texas to Alaska, again while selecting the data by state, you can miss the record entirely. This is what happened to David Glasser’s MongoDB database. By reading from an index during an update operation, the query missed the record.

Depending on how the database is designed, and the specific execution plan, dirty reads can also interfere with sorting. For example, this could happen if the execution engine collects a set of pointers to all of the rows of interest, then a row is updated, and then the execution engine actually copies the data from the original location using said pointers.

Snapshot Isolation or Row Level Versioning

In order to offer good performance while avoid the problems of dirty reads, many databases support Snapshot isolation semantics. When running under Snapshot isolation, the current transaction cannot see the results of any other transaction that was started before the current one.

This is done by making temporary copies of the rows being modified rather than relying solely on locks. This is often referred to as “row level versioning”.

Most databases that support snapshot isolation semantics use it automatically when Read Committed isolation is requested.

Isolation Levels in SQL Server

SQL Server supports all four of the ANSI SQL isolation levels plus an explicit Snapshot level. Read Committed may also use Snapshot semantics depending on how the database is configured using the READ_COMMITTED_SNAPSHOT option.

Thoroughly test your database before and after turning on this option. While it can improve read performance, it may slow down writes. This is especially true if your tempdb is on a slow drive, as that’s where the old versions of the rows are stored.

The infamous NOLOCK directive, which can be applied to SELECT statements, has the same effect as running inside a transaction that is set to Read Uncommitted. This was used heavily in SQL Server 2000 and earlier, as they didn’t yet offer row level versioning. Though no longer necessary or advisable, the habit still remains.

For more information see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Isolation Levels in PostgreSQL

While officially PostgreSQL supports all four ANSI isolation levels, in reality it only has three. Whenever a query requests Read Uncommitted, PostgreSQL silently upgrades it to Read Committed. Thus PostgreSQL doesn’t allow for dirty reads.

When you select the level Read Uncommitted you really get Read Committed, and phantom reads are not possible in the PostgreSQL implementation of Repeatable Read, so the actual isolation level might be stricter than what you select. This is permitted by the SQL standard: the four isolation levels only define which phenomena must not happen, they do not define which phenomena must happen.

PostgreSQL doesn’t explicitly offer Snapshot isolation. Rather, that happens automatically when using Read Committed. This is because PostgreSQL was designed with multiversion concurrency control from the beginning.

Prior to version 9.1, PostgreSQL didn’t offer Serializable transactions and would silently downgrade them to Repeatable Read. No currently supported version of PostgreSQL still has this limitation.

For more information see 13.2. Transaction Isolation.

Isolation Levels in MySQL

InnoDB defaults to Repeatable Read, but offers all four ANSI SQL isolation levels. Read Committed uses Snapshot isolation semantics.

For more information on InnoDB, see 15.3.2.1 Transaction Isolation Levels.

When using the MyISAM storage engine, transactions are not supported at all. Instead it uses a single reader-writer lock at the table level. (Though in some cases, insert operations can bypass the lock.)

Isolation Levels in Oracle

Oracle only supports 3 transaction levels: Read Committed, Serializable, and Read-only. In Oracle, Read Committed is the default and it uses Snapshot semantics.

Like PostgreSQL, Oracle doesn’t offer Read Uncommitted; dirty reads are never permitted.

Also missing from the list is Repeatable Read. If you need that behavior in Oracle, you need to set your isolation level to Serializable.

An isolation level unique to Oracle is Read-only. It is not well documented, with the manual only saying,

Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

 For more information on the other two isolation levels, see 13 Data Concurrency and Consistency.

Isolation Levels in DB 2

DB 2 has 4 isolation levels named Repeatable Read, Read Stability, Cursor Stability, and Uncommitted Read. However, these do not map directly to ANSI terminology.

Repeatable Read is what ANSI SQL refers to as Serializable. Which is to say, phantom reads are not possible.

Read Stability maps to ANSI SQL’s Repeatable Read.

Cursor Stability, which is the default, is used for Read Committed. As of Version 9.7, Snapshot semantics are in effect. Previously it would use locks similar to SQL Server.

Uncommitted Read allows for dirty reads much like SQL Server’s Read Uncommitted. The manual recommends it only for read-only tables, or when “seeing data that has not been committed by other applications is not a problem”.

For more information see Isolation levels.

Isolation Levels in MongoDB

As mentioned before, MongoDB doesn’t support transactions. From the manual,

Because only single-document operations are atomic with MongoDB, two-phase commits can only offer transaction-like semantics. It is possible for applications to return intermediate data at intermediate points during the two-phase commit or rollback.

In real terms this means MongoDB uses dirty read semantics, which includes the possibility for doubled or missing records.

Isolation Levels in CouchDB

CouchDB doesn’t support transactions either. But unlike MongoDB, it does use multiversion concurrency control to prevent dirty reads.

A read request will always see the most recent snapshot of your database at the time of the beginning of the request.

This gives CouchDB the equivalent to the Read Committed isolation level with Snapshot semantics.

For more information see Eventual Consistency.

Isolation Levels in Couchbase Server

Though often confused with CouchDB, Couchbase Server is a very different product. It has no concept of isolation when it comes to indexes.

When you perform an update it only updates the primary index, the “real table” if you prefer. All of the secondary indexes are updated lazily.

The documentation isn’t clear, but it appears to use snapshots when building its indexes. If so, dirty reads should not be a problem. But because of the lazy index updates, you still cannot get true Read Committed isolation level.

Like many NoSQL databases, it doesn’t directly support transactions. You do, however, have the ability to use explicit locks. These can only be maintained for 30 seconds before automatically being discarded.

For more information see Locking items, Everything You Need To Know About Couchbase Architecture, and Couchbase View Engine Internals.

Isolation Levels in Cassandra

In Cassandra 1.0, not even writes to a single row are isolated. Fields were updated one-by-one, so you could end up reading a record with a mixture of old and new values.

Starting with version 1.1, Cassandra offers “Row Level Isolation”. This brings it up to the same level of isolation that other databases refer to as Read Uncommitted. Higher levels of isolation are not possible.

For more information see About transactions and concurrency control.

Know Your Database's Isolation Levels

As you can see from the above example, it isn't enough to think of your database as ACID or non-ACID. You really need to know what isolation levels it supports and under which circumstances.

About the Author

Jonathan Allen got his start working on MIS projects for a health clinic in the late 90's, bringing them up from Access and Excel to an enterprise solution by degrees. After spending five years writing automated trading systems for the financial sector, he became a consultant on a variety of projects including the UI for a robotic warehouse, the middle tier for cancer research software, and the big data needs of a major real estate insurance company. In his free time he enjoys studying and writing about martial arts from the 16th century.