Transaction Isolation Levels

The transaction isolation level is understood as a state within databases that specifies the amount of data that is visible to a statement in a transaction, especially when the same data is accessed by many transaction at the same time. Let’s imagine a situation when we have a Customers table of 1 millions rows taking 10 GB of disk space. At 9 o’clock we started a query “SELECT * FROM Customers”, which queries all the rows of the table. In our case, this query takes approximately 5 minutes to complete. This time is needed to fully scan our table to end and retrieve the rows. This type of query performs a full table scan and can’t be recommended to perform from the performance point of view. At 9:01 UserB updates the last row in the Customers table and commits the change. A short time later our query arrives at the last row modified by UserB. So what will happen? Will we see the original row value or the modified row value? The new row value is legitimate and committed, but it was updated after our query started.

The result of our query depends on the isolation level of the transaction. Basically, there are 4 isolation levels explained below:

1. Read uncommitted. We will see the changes made by UserB. This isolation level is also called “dirty reads” meaning that read data is not consistent with other parts of the table or the query, and may not yet have been committed. “Dirty reads” ensures the quickest performance, as data is read directly from the table blocks without validation.
2. Read committed. We will not see the changes made by UserB. That happens because with this level of query isolation the rows retrieved by a query are the rows that had been committed before the query was started. It means that the changes made by UserB weren’t there when the query started, so the changes will not be included in the query result.
3. Repeatable read. We will not see the changes made by UserB. This happens due to the fact that in the repeatable read isolation level, the rows retrieved by a query are the rows that were committed when the transaction was started. It means that the changes made by UserB were not there when the transaction started, and therefore will not be included in the query result. “All consistent reads within the same transaction read the snapshot established by the first read” (from MySQL documentation)
4. Serializable. This isolation level means that all database transaction occur in a completely isolated way. In this isolation level all transactions are 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 very much alike repeatable read but uses a different implementation technique for each database engine. For example, in Oracle, the repeatable read isolation level is not supported and Serializable provides the highest isolation level. This isolation level is like repeatable read, but InnoDB implicitly coverts all plain SELECT statements to “SELECT … LOCK IN SHARE MODE”.