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

Isolation levels and transactions

I wrote about transactions in databases a while back, along with the ACID
properties that govern them. The whole idea of writing that post was to give an
introduction to the post that I am about to write now.

I stumbled upon a legacy piece of code at work, well, it is not legacy but more
of a layer underlying the piece of code I write. Here, I noticed that the
transaction was started with a parameter passed to the method "BeginTransaction"
to indicate an isolation level to be used with the transaction. Unfortunately,
I did not know anything about "isolation levels". This led me to go on a journey
to learn about Isolation levels in a transaction.
Most of the stuff I write about are something that I learnt newly.


What is Isolation ?
A transaction in a database is a sequence of DB operations that is performed as
a single logical unit of work. A transaction satisfies all the ACID properties.
One of the ACID properties is "Isolation". A piece of data, which a transaction
must work on, either for reading or modification, must either be in the state it
was in before another concurrent transaction modified it, or after the second
transaction has completed, but it never will be in an intermediate state. This
is referred to as "Serializability". If 3 transactions run concurrently, and, they
produce the same outcome, as if they were run in some serial order, then it
means that they satisfy the serializability property.

Drawbacks of Serializablility
Although serializability gives us predicable and understandable behaviour along
with the consistency of the database, it also adds a considerable amount of
overhead. This is because of reduced concurrency. Serializablility is most
commonly achieved by using locking mechanisms.

Overcoming reduced concurrency
To overcome reduced concurrency, database systems allows the user to make
a choice to use a weaker level of isolation. This means that, it is under the
application programmer's control to increase concurrency if the overhead is very
high. Since Isolation is most commonly achieved by using locking mechanisms,
reducing overhead implies reducing the isolation levels, which means
a reduced degree of locking will be used. The application programmer must
carefully analyze database access patterns in the software to ensure that
any reduction of isolation level does not cause software bugs that are
difficult to find. On the other hand, if higher isolation levels are used,
the possibility of deadlock is increased, which also requires careful
analysis.

Isolation levels
Four isolation levels are usually provided by database systems as governed by
SQL standard.

        - Serializable
        - Repeatable read
        - Read committed
        - Read uncommitted

"Serializable" is the highest isolation level, where as "read uncommitted" is
the lowest. This means, as one traverses the above list from top to bottom, the
overhead reduces. If one traverses the list from bottom to top, then the
consistency increases. Stronger isolation levels are at the top of the list and
weaker isolation levels are at the bottom of the list. "Read committed" is
usually the default value of isolation level in database systems. As already
mentioned by me, I found that the isolation level can be set by passing the
appropriate parameter in the "BeginTransaction" call. This implies that
isolation levels are per transaction. Also, an isolation level set on one
transaction doesn't affect any other transaction. For e.g. if transaction t1
uses isolation level "Repeatable Read" and transaction t2 uses "Read
uncommitted", then the isolation level "Repeatable Read" set by transaction t1
is guaranteed to be applied on the transaction t1 and it will not affect the
behaviour of the other transaction t2 which will get its own isolation level
"Read uncommitted".

More about the different isolation levels soon....

Transactions in a Databases

A transaction in a database is a sequence of DB operations that is performed as
a single logical unit of work. Typically this means that the logical sequence of
operations must happen together or not at all. But there is more to this than
just that. What is the meaning of this logical unit of work ? For a long time
I thought transaction means making a bunch of actions an atomic operation, but
it is not.  This logical unit of work must exhibit four properties called the
atomicity, consistency, isolation, and durability (ACID) properties. Only when
a sequence of operations satisfy the "ACID test" ( or should I say litmus test,
pardon the pun) they can be collectively a "transaction".
 
Atomicity
A transaction must be an atomic unit of work; This means
if the operations A,B,C,D are performed in a transaction then all of A,B,C and
D are done, or none of them are performed. So, while performing operation
B, if there is a failure, due to a wrong query, for example, then whole
transaction is aborted and none of the result of actions A,B,C and D are
persisted. But what about the operation A which was already performed ? This
operation A is "rolled-back". However, If all of them are completed without any
errors, then the transaction is committed and the its effects are persisted.

Consistency
This brings us to the next item, which is consistency.
When completed, a transaction must leave all data in a consistent state. This
means that whatever modifications are performed in the operations A,B,C and D;
the data integrity of the relational database must be maintained. All the rules
that govern this consistency in the database must be passed. Moreover, All
internal data structures, such as B-tree indexes or doubly-linked lists, must be
correct at the end of the transaction.

Isolation
In today's world filled with massive software which are driven by high data
processing needs, concurrent access to databases and data is not uncommon.
In-fact it is foolish in today's world to not allow concurrent access to data.
Thus it not weird to have concurrent transactions running on the same database.
It is also common sense to understand that one transaction, if it has to have
the "Atomicity" and "Consistency" property, must be impervious to the
modifications made by another concurrent transaction. A piece of data, which
a transaction must work on, either for reading or modification, must either be
in the state it was in before another concurrent transaction modified it, or
after the second transaction has completed, but it never will be in an
intermediate state. This is referred to as serializability. What is
Serializability ? Consider a situation in which there are 3 transactions tA, tB
and tC that can run. Assuming that there may be dependencies among them, if the
3 transaction run concurrently and they produce the same outcome, as if they
were run in serial in the same order they completed when they were run
concurrently, then it means that they satisfy the serializability property.

Durability
After a transaction has completed, its effects are permanently stored in the
system. The modifications persist even in the event of a system failure. For
instance, if a transaction is already completed, then even if there is a system
crash, the transaction will remain in the state in which it was completed and
its state can be recovered from the database.