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