Transaction isolation levels

The four levels, in ascending order of strictness, are:

* READ UNCOMMITTED:
* READ COMMITTED:
* REPEATABLE READ:
* SERIALIZABLE:

There is a big difference between READ COMMITTED and REPEATABLE READ. Basically READ COMMITED means that within one transaction, you will be able to see *commited* changes by another transaction. So within the same transaction you can get different values for the same query. For example, we have transaction T1 which reads a row:

BEGIN
SELECT row FROM t WHERE id=1; ---> this returns row="my row"

Now transaction T2 does an update and commits it:

BEGIN
UPDATE t SET row="updated row" WHERE id=1;
COMMIT

Back to T1, if we issue the query again we would get:

SELECT row FROM t WHERE id=1; ---> this returns row="updated row"

So we have different values of "row" within the same transaction.


The case is different in a repeatable read. As the name suggests, every read you do will be "repeated". So in the T1 transaction above, the SELECT statement will always return row="my row" even if other transactions (T2) change the value of row.
With REPEATABLE READ, your T1 transaction is guaranteed to read the same value every time it does an identical SELECT (even if another T2 transaction changes those SELECTED values in the meantime).
This will not happen in READ COMMITTED. Within the T1 transaction it is possible that the same SELECT will give different results (because T2 changed them in the meantime), ie, the isolation level is lower.

I'll discuss the remaining two in my next post...