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.

Hibernate Flush vs Commit

Flush syncs your hibernate session state to your database, BUT it still can be rolled back.

One example of where I had to flush the database is when I use HQL to query the database expecting that some of the previous changes I made to already be in the database.

E.g.
Assuming entity.A = 0 before the hibernate session began.

1. entity.setA(10);
2. invoke HQL to getA (select A from entity .. or something like that)

Line 2 will actually return 0.  NOT 10.

entity.A is 10 in the hibernate session, but not in the database.
to sync the current state to the database but still allowing the transaction to be rolled back if something goes wrong, we can perform a flush.

1. entity.setA(10);
2. session.flush()
3. invoke HQL to getA (select A from entity .. or something like that)

Line 3 should now return 10.


How does it work?
Not really sure.. I am guessing database as an extra layer to store flushed changes, before committing..


Is Flush Isolated?
i.e. If in one hibernate transaction I perform a flush, will I be able to see the changes in SSRS?

The answer depends on the database ISOLATION level.  If it's set to READ_COMMITTED, then you wouldn't be able to see it... if it's set to READ_UNCOMMITTED then you probably can..


Warning
Flush can be expensive.. hibernate probably has to figure out all the entities which has been changed/created/deleted and then write it to the database...

I wonder if that means if a flush is called 2x consecutively will the 2nd time be really quick??