Database Isolation Levels And Their Effects on Performance and Scalability

Some of us are not aware of the tremendous job databases perform, particularly their efforts to maintain the Isolation aspect of ACID. For example, some people believe that transactions are only related to data manipulation and not to queries, which is an incorrect assumption. Transaction Isolation is all about queries, and the consistency and completeness of the data retrieved by queries. This is how it works:

Isolation gives the querying user the feeling that he owns the database. It does not matter that hundreds or thousands of concurrent users work with the same database and the same schema (or even the same data). These other uses can generate new data, modify existing data or perform any other action. The querying user must be able to get a complete, consistent picture of the data, unaffected by other users’ actions.

Let’s take the following scenario, which is based on an Orders table that has 1,000,000 rows, with a disk size of 20 GB:

1. 8:00: UserA started a query “SELECT * FROM orders”, which queries all the rows of the table. In our scenario, this query usually takes approximately five minutes to complete, as the database must fully scan the table’s blocks from start to end and extract the rows. This is called a FULL TABLE SCAN query, and is not recommended from a performance perspective.
2. 8:01: UserB updates the last row in the in the Orders table, and commits the change.
3. 8:04: UserA’s query process arrives at the row modified by UserB. What will happen?

Any guess? Will UserA get the original row value or the new row value? The new row value is legitimate and committed, but it was updated after UserA’s query started.

The answer is not clear cut, and depends on the isolation level of the transaction. There are four isolation levels, as follows (see more information at: http://en.wikipedia.org/wiki/Isolation_(database_systems):

1. READ UNCOMMITTED: UserA will see the change made by UserB. This isolation level is called dirty reads, which means that read data is not consistent with other parts of the table or the query, and may not yet have been committed. This isolation level ensures the quickest performance, as data is read directly from the table’s blocks with no further processing, verifications or any other validation. The process is quick and the data is asdirty as it can get.
2. READ COMMITTED: UserA will not see the change made by UserB. This is because in the READ COMMITTED isolation level, the rows returned by a query are the rows that were committed when the query was started. The change made by UserB was not present when the query started, and therefore will not be included in the query result.
3. REPEATABLE READ: UserA will not see the change made by UserB. This is because in the REPEATABLE READ isolation level, the rows returned by a query are the rows that were committed when the transaction was started. The change made by UserB was not present when the transaction was started, and therefore will not be included in the query result.
   This means that “All consistent reads within the same transaction read the snapshot established by the first read” (from MySQL documentation. See http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html).
4. SERIALIZABLE: This isolation level specifies that all transactions occur in a completely isolated fashion, meaning as if all transactions in the system were executed serially, one after the other. The DBMS can execute two or more transactions at the same time only if the illusion of serial execution can be maintained.
   In practice, SERIALIZABLE is similar to REPEATABLE READ, but uses a different implementation for each database engine. In Oracle, the REPEATABLE READ level is not supported and SERIALIZABLE provides the highest isolation level. This level is similar to REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to “SELECT … LOCK IN SHARE MODE.

The default isolation level in MySQL’s InnoDB is REPEATABLE READ, which provides a rather high isolation. Consider again this key sentence for the REPEATABLE READ isolation level: “All consistent reads within the same transaction read the snapshot established by the first read”.

Since old values of row data are required for current queries, databases use a special segment to store old row values and snapshots. MySQL calls this segment a Rollback Segment. Oracle once called it this as well, but now calls it an Undo Segment. The premise is the same.

During query execution, each row is examined and if it is found to be too new, an older version of this row is extracted from the rollback segment to comprise the query result. This examination‑lookup‑comprise action chain takes time to complete, resulting in a performance penalty. It also produces a snowball effect. Updates occur during a query, which makes that query slower so that it takes more time. During the time it takes to process the query, more updates come in, making query execution time even longer!

This is why a query that executes in 10 seconds in our testing environment may take a full 10 minutes to execute in a much stronger production environment.

You can read the entire post here - http://www.scalebase.com/isolation-levels-in-relational-databases/

Comments

[Richard]

For most projects, COMMITTED READ is far better than REPEATABLE READ. It also significantly reduces the issues arising from long-running transactions. Its worth pointing out that, if you have REPEATABLE READ switched on and start a transaction (even a read), the rollback log cannot be purged until your transaction ends - various DMBSs will either abort the read transaction, deny write transactions, or just helplessly log until the limits of available space and then implode. None of those solutions are particularly good.

[Gary]

You've got an extra closing bracket in your link to Wikipedia.
Also READ COMMITTED isn't as clear cut as you make out.
The ANSI definition of READ COMMITTED is that is allows non-repeateable reads, which it defines as
"Non-repeatable read: SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes
that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value
or discover that the row has been deleted."
It doesn't go down to the level of what happens within an individual query.

Under READ COMMITTED, SQL Server has two options and the default one is to wait until the blocking update transaction commits, then return the updated value. The newer (non-default) option is to return the pre-update value.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

I believe IBM also uses a locking approach rather the multi-versioning

[Doron Levari]

Hi Richard,

You are absolutely right.

Note that REPEATABLE READ isolation level, restrictive as it is, is the default for INNODB of MYSQL http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html...

Not such a good choice for a default, yet very "conservative" and might fit most cases.

Thanks,
Doron

[Markus]

Maybe you find a better url than this:
[l2] file:///C:/Users/liran/Documents/My%20Box%20Files/Management/Marketing/Site/blogs/03_Isolation/Isolation_Edited.doc#_msocom_2

;-)

[Liran Zelkha]

Thanks for the remarks. Fixed the broken links, and improved the URL - thanks Markus ;-)

[alexis coudeyras]

Two errors :
- READ COMMITTED: UserA will see changes made by UserB, because UserB has commited is transaction. The difference with READ UNCOMMITED is that UserA would have seen UserB changes, even if UserB hasn't yet commited is transaction.
- REPEATABLE READ: UserA will not see the change made by UserB ok, but for : "This is because in the REPEATABLE READ isolation level, the rows returned by a query are the rows that were committed when the transaction was started". No, you can have phantom reads. Replace your test with an INSERT in place of an UPDATE, UserA will see changes made by UserB.

[Doron Levari]

Hi Alexis,

Great comments.

With READ COMMITTED, userA will not see the change, even if it's committed, because in READ COMMITTED userA will only see the committed snapshot when the tx started. The update were not there, committed, when the tx started.

You are right about READ UNCOMMITTED. Even if the change was not committed, it will be included for userA.

You are also right about REPEATABLE READ. An INSERT will be shown as a phantom read.

[vermas]

Just wanted to get better understanding of phantom read phenomenon ...

From wikipedia

In the other strategy, which is used in multiversion concurrency control, Transaction 2 is permitted to commit first, which provides for better concurrency. However, Transaction 1, which commenced prior to Transaction 2, must continue to operate on a past version of the database — a snapshot of the moment it was started. When Transaction 1 eventually tries to commit, the DBMS looks to see if the result of committing Transaction 1 would be equivalent to the schedule T1, T2. If it is, then Transaction 1 can succeed. If it cannot be seen to be equivalent, however, Transaction 1 must roll back with a serialization failure.

If there exists a past version of database shouldn't the range queries also be repeatable?

Thanks

[Doron Levari]

Your quote from Wikipedia is about "Non-Repeatable-Reads" like in SERIALIZABLE isolation level for example. Repeatable-Reads means that any repeated reads in the same transaction will return the same results, consistently according the snapshot they were in the beginning of the transaction.

Phantom reads means that committed updates done by other transactions are shown to subsequent repeated queries by TX-A, but committed inserts are not. No new rows are added to TX-A's snapshot from the beginning of the transaction, while updates might sneak in...

Hope I helped!

Thanks,
Doron

[Dave Birdsall]

The descriptions are not quite correct. Isolation levels deal with serializability of transactions; they do not dictate an order in chronological time. For example, with SERIALIZABLE access, User A in the example may well see User B's update if User A's query has not reached User B's row until after User B commits. User A still sees a consistent view, but the view is as if User A's query were executed after User B's update. Some DBMSs that use locking will behave in exactly this way; rows are not locked until first access.

[Doron Levari]

Dave, great comment.
Isolation is all about to what extent (isolation level) a transaction is isolated from other transactions.

SERIALIZABLE is the highest level of isolation. A transaction as a whole "gets the feeling" it runs alone in the DB at a certain point in time, and all other transactions are run serially before or after it.

It is true that there are DBs that lock in this isolation level. Anyway, we talked only about queries, but what about DML (update, insert, delete)? If the database does not lock, then in case of a DML is done on an old snapshot during a transaction in SERIALIZABLE isolation level, we'll get a serialization error from the database

[Dave Birdsall]

Doron,

A DBMS that uses locking must lock rows touched for write DML (update/insert/delete) at least at first access to the written row. It's a bit more complicated that that actually, e.g. in a b-tree implementation one must lock the gaps between a row and the next row to guard against phantoms. The locks must be held until transaction commit or abort.

A SERIALIZABLE read would conflict with such locks. If the read gets there first, the write DML cannot acquire the lock until the read commits (releasing its lock). If the write gets there first, the read waits until the write commits.

For READ UNCOMMITTED, the read would ignore any locks; you get to see the uncommitted writes.

READ COMMITTED is in-betwen; the read could get there first, briefly lock the row, then unlock it when it moves on to the next row, allowing the write to proceed. If the write gets there first, the write lock is held until end of transaction, preventing the read until then.

[vermas]

Is repeatable read guaranteed only if a single row is read twice in the same transaction?

[Moshe Kaplan]

Great Post Liran!

I already had the chance to implement it :-)

Keep Performing,
Moshe Kaplan

[Dzmitry Kashlach]

Do you know, what's the difference in performance of database depending on different isolation level? What do you think about results, that are described in the following article?
http://community.blazemeter.com/knowledgebase/articles/65143-using-jdbc-sampler-in-jmeter-2-6
What do you think about it?

[Binh Nguyen Thanh]

Thanks, nice post

[Shail]

Thanks for providing so usefull information..

[rgeorgiev]

"In Oracle, the REPEATABLE READ level is not supported" - NOT TRUE.
It was supported ever since the SET TRANSACTION READ ONLY times (just google what's this)

[Manish Rai]

Nice Post, Adding some more points-

If we put your query in a Transaction -

BEGIN TRANSACTION

SELECT *FROM ORDERS

WAIT DELAY "00:02:00" //2 mintues delay

SELECT *FROM ORDERS

Now,

READ UNCOMMITTED - Your Explanation is sufficient, As even uncomitted data will be displayed.

READ COMMITTED - Any commited data add/update/delet before starting of second query(between 5 min of first query + 2 min delay) will be included in second SELECT.

REPEATABLE READ - Any committed addition of rows before starting of second query(netween above 7 mintues) will be included in second SELECT But existing updated or deleted records between these 7 mintues will not be available in second SELECT. So, It guarantee that second SELECT within the same transaction will fetch the existing same records.

SERIALIZABLE - This is even stronger than the REPEATABLE READ, In addition to the everything REPEATABLE READ guarantess, It also gauarantees that no new data can be seen by subsequent read. So, Any commited add/update/delete records within these 7 minutes will not be avaiable in second select