Repeatable read Isolation Level

Continuing with my previous topics(1,2,3), I would now like to write about the
problem with the "read committed" isolation level. Read Committed guarantees
that a data that is read by a transaction is already committed by the time it is
read. However, it doesn't guarantee that, if the transaction performing the
read, issues a re-read, it will find the same data.

This is called a non-repeatable read, one of the 3 read phenomenons mentioned in
the SQL standard.

Non Repeatable Read
A Non Repeatable read is said to have occurred, if a transaction performs two
reads of the same data withing a transaction, and the values of the data differs
between the two reads. This means some other transaction has committed the data
in between the two reads.

Let us consider the following transactions, on the same table as used in the
previous post.

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

+ ------------------------- + ------------------------- +
| transaction t1 statements | transaction t2 statements |
+ ------------------------- + ------------------------- +
| SELECT * FROM items       |                           |
| WHERE price = 12.33       |                           |
| //selects Sensodyne       |                           |
+ ------------------------- + ------------------------- +
|                           | UPDATE items set price    |
|                           | = 5.66 WHERE item =       |
|                           | 'Sensodyne';              |
|                           | Commit;                   |
+ ------------------------- + ------------------------- +
| SELECT * FROM items       |                           |
| WHERE price = 12.33       |                           |
| //now row is found        |                           |
| Commit;                   |                           |
+ ------------------------- + ------------------------- +

If t1 makes a read before the commit of t2 and then makes another read after the
commit of t2. The second time t1 reads the value, the "Read Committed" isolation
guarantees that the value that is read is already committed one. It can easily
be noted that the second time, the read of t1 finds no item, whereas the same
read had one item in the first read.

We can ensure that this doesn't happen, if we use a higher isolation level
called "Repeatable Read". If a transaction is having isolation level Repeatable
Read, it means that the transaction cannot perform dirty reads, but also
multiple reads of values in the same transaction results in the same values. So,
the second read statement of t1 will also return the value Sensodyne, even
though the value was changed by a commit due to another transaction.

This isolation level is usually the default value in most database systems.

Note that even "Repeatable Read" Isolation level doesn't guarantee the global
serialization. I will write about this soon...