Database Isolation Levels In Layman Terms

In my career, I have had to deal with database isolation levels on a handful of occasions.

I can never remember them on top of my head, and had always resort to googling, which is fine.. but most of the time I just want to know in a sentence or two what are the isolation levels and how are they different to each other.

So here it is (in increasing level of isolation and slowness..):

READ UNCOMMITTED
This means the data you read may not have been committed.  I.e. a transaction may have done an update or insert, but it hasn't actually committed it.  I.e. you may have a "Dirty Read"


READ COMMITTED
Guarantees data read has been committed at the moment it is read.  Prevents "Dirty Read".

This has 2 options in MSSQL - snapshot on/off
On - Puts a version number on the rows you are reading, so effectively every transaction sees a SNAPSHOT of the data at the time of read.

Off - Locks the rows you are reading from being edited by another transaction.

REPEATABLE READ
In addition to Read Committed, it also guarantees that any data already read CANNOT change, even in the same transaction.  I.e. with in a transaction you can read the data any time, and you are guaranteed you that the rows you read before is UNCHANGED (but you may see MORE rows added)


SERIALIZABLE
In addition to READ COMMITTED and REPEATABLE READ, it also guarantees NO NEW data can be seen by a subsequent read.  I.e. once you did a select * on a table, no concurrent transaction can UPDATE/INSERT/DELETE on that table!

A very good example I pulled from Stack Overflow:

BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
SELECT * FROM T;
COMMIT;

• under READ COMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.
• under REPEATABLE READ the second SELECT is guaranteed to see the rows that has seen at first select unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.
• under SERIALIZABLE reads the second select is guaranteed to see exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.