Isolation levels in Relational Databases

The fact that relational databases are transactional is common knowledge. These transactions provide the Atomicity, Consistency, Isolation and Durability (ACID) capabilities of the database.

In this post, we’ll expand a bit about Isolation and how it is supported with ScaleBase Database Load Balancer.

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 as dirty 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.

If after five seconds of query execution, so many concurrent updates occur that all data comes from the rollback segments instead of from the table index, then table data, indexes and buffers are ignored. An extra random I/O trip is added to each required I/O.

This paints a naïve and simplistic description. If thousands of updates occur, we can safely assume that thousands of queries occur as well. The potential performance hit is exponential! Database’s consistency is uncompromised and isolation is maintained, but the database needs to work very hard to implement it. The bottleneck in this case is not the I/O, memory contention or network. The bottleneck is the database engine itself, struggling to provide the important isolation feature of ACID.

One solution to this problem may be working with a lower level of isolation. The READ UNCOMMITTED level, for example, bypass this problem. However, this level may not fit application requirements for consistency and truthful data queries. Imagine, for example, that in a poker table, each player sees different bets and a different pot size. If you ask Oracle, you’ll get the answer that the READ UNCOMMITTED level is “not a feature, it is a weak attempt to get around a limitation” (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1729145331430), and therefore Oracle does not support this isolation level. In MySQL, this level is supported, but using it includes risks to data query consistency. Usually it is used only for logging or auditing.

So where and how does ScaleBase Database Load Balancer come into the picture? Because ScaleBase Database Load Balancer splits data across several databases, each database holds less data. In addition, ScaleBase Load Balancer also splits concurrent users across several databases, by splitting the  rows these users are searching for (especially when inserting or updating) across databases.

It is true that the total quantity of concurrent users, UPDATEs and SELECTs is the same as it would have been in a large, busy database. It must be. However, a performance gain is obvious here because of the exponential behavior of the isolation performance degradation. For example, our benchmarks have shown that using four backend databases caused each database to run a query across updates not four times faster, but almost eight times faster – a factor of two! The reason is that each database needs to handle fewer concurrent updates and fewer concurrent queries, thus resulting in far less contention over the rollback segments. Each of the four database engines remains in its green zone, where it is still very efficient.

There is no snowball effect. The query runs and terminates much more quickly, and updates do not pile up to slow down the query, causing more updates to pile up and so on.

Summary

Transaction isolation is an important part of ACID., It can result in slow query performance for an online transactional read/write relational database. The bottleneck in this case is the database engine itself, which simply cannot handle isolation with so many concurrent transactions. As a result, scaling up by adding more resources will not change this problem.

Scaling out using ScaleBase however, can definitely assist, in order of magnitude, in solving this problem.