Read Uncommitted and Read Committed Isolation Level

In last post, I wrote about the different isolation levels of a transaction.

Now, I would like to write more about each one of them. But before we get into
that I want to define certain terms. You will find that the terms I want to
define are all relating to reading. This is because the isolation levels also
pertain to how a data is read by a given transaction. Hence, ANSI SQL standard
defines the following read phenomena:

    - Dirty Read
    - Non-repeatable reads
    - Phantom reads

Understanding how the read phenomena works, will lead us to understanding how
the different isolation level work.


Dirty Read
A data is deemed as "dirty" when it has been modified by a transaction and that
transaction has not yet been committed. When a transaction reads an item that is
made dirty by another transaction, a "dirty read" is said to have occurred.

Consider the table below :

                                        items
        + -- + --------- + ----- +
        | id | item      | price |
        + -- + --------- + ----- +
        | 1  | Colgate   | 5.23  |
        + -- + --------- + ----- +
        | 2  | Pepsodent | 4.33  |
        + -- + --------- + ----- +
        | 3  | Sensodyne | 12.33 |
        + -- + --------- + ----- +


In the above example, let us consider a situation in which transaction t1
changes a row, but does not commit these changes and Transaction t2 then reads
the uncommitted data. Now if Transaction t1 rolls back its changes, which has
already read by Transaction t2, or updates data again with different values,
then the view of the data may be wrong in the data structures of
Transaction t2. This is shown below :

 + ----------------------------- + ------------------------- +
 | transaction t1 statements     | transaction t2 statements |
 + ----------------------------- + ------------------------- +
 | UPDATE items SET price = 6.66 |                           |
 | WHERE item = 'Colgate';       |                           |
 + ----------------------------- + ------------------------- +
 |                               | SELECT price FROM items   |
 |                               | WHERE item = 'Colgate';   |
 |                               | // this reads 6.66        |
 + ----------------------------- + ------------------------- +
 | ROLLBACK;                     |                           |
 | // because of DB crash or     |                           |
 | // application error          |                           |
 + ----------------------------- + ------------------------- +

This brings us to the first Isolation level of "Read Uncommitted". When the
isolation level is "Read Uncommitted", a transaction can perform dirty reads.
One has to be extremely careful when setting the isolation level of
a transaction to this level, as the values read by the transaction may not be
accurate, since these values that are read may not actually exist, as the other
transaction may have rolled it back. The apparent advantage that we gain by this
isolation level is higher level of concurrency at the expense of data
consistency. This is the lowest/weakest isolation level, as in, one transaction
is not well isolated from the other.

In the example that I have mentioned above, transaction t2 has an isolation
level of "read uncommitted". It doesn't matter what the isolation level of t1
is. As already mentioned isolation level pertain to reading data.

To overcome this problem of reading inconsistent data, we can restrict the
transaction to only read the data that has been committed. This prevents reading
intermediate data. This is the next isolation level "Read Committed".
A transaction with isolation level set to "Read Committed" will not perform
dirty reads. "Read Committed" isolation is stronger than "Read Uncommitted" but
allows for less concurrency.

Using a "Read committed" isolation level still doesn't guarantee global
serialization. I will write about that soon...