Transaction Isolation

IsolationConcept http://en.wikipedia.org/wiki/Isolation_(database_systems)

The code simulates two users i and you. "You" uses TRANSACTION_READ_COMMITTED (unless explicitly specified) isolation level while "I" uses different isolation levels.

In all of the examples below both you and I have started the transaction. Oracle has only two levels of isolation TRANSACTION_READ_COMMITTED and TRANSACTION_SERIALIZABLE.

DIRTY READ

read_Uncommited (SqlServerOnly)

I == TRANSACTION_READ_UNCOMMITTED

You update customer's email.

I can read customer's email you updated even before you have committed.

read_Commited (Both)

I == TRANSACTION_READ_COMMITTED isolation

You update customer's email.

I cannot read the customer's email you have updated.

When you read the customer's email you get the value you have updated.

IsolationConceptAlt.read_Commited_When_Snapshot_Mode_Is_Off (SqlServerOnly)

I == TRANSACTION_READ_COMMITTED

You update a customer's email.

I try to get the same customer.

I get blocked.

[This is behavior only shown by SQLServer in default read committed isolation mode, still supported perhaps for legacy reasons. This can (and should) be changed to setting the READ_COMMITTED_SNAPSHOT value, by something like ALTER DATABASE $(DatabaseName) SET READ_COMMITTED_SNAPSHOT ON. You don't need to do this for this example as we are using two database which has different settings.]

update_On_Same_Row_Blocks (Both)

I == TRANSACTION_READ_COMMITTED isolation

You update customer's email.

I try to update same customer's email (it can be any other column as well).

I get blocked by you.

non_Repeatable_Read (Both)

I == TRANSACTION_READ_COMMITTED isolation

I get a customer (email).

You update same customer's email.

You commit.

I read the same customer's email again in same transaction.

I get a different value for customer's email.

REPEATABLE READ

repeatable_Read (SqlServer)

I == TRANSACTION_REPEATABLE_READ isolation

I get a customer (email).

You update same customer's email.

You get blocked by me.

I read the same customer's email again in same transaction.

I get the same value.

repeatable_Read (Oracle)

I == TRANSACTION_SERIALIZABLE isolation

I get a customer (email).

You update same customer's email.

You do not get blocked.

I read the same customer's email again in same transaction.

I get the same value as before.

[Note the difference in interpretation of repeatable read. SqlServer doesn't allow your updates because in which case the second read by me would not be possible. Oracle (at TRANSACTION_SERIALIZABLE) looks at the same thing differently. It allows your update but at the same time ensures that the value I have read remains the same although possibly old.]

LOCK ESCALATION

lock_Escalation (SqlServer)

I == TRANSACTION_REPEATABLE_READ

I get all customers whose email have "bollywood" in it.

You try to update customer whose email doesn't have "bollywood" in it.

You get blocked by me.

[I have read more than certain percentage of rows in the table. I am also demanding repeatable read for each of them. SQLServer locks all the entire table instead of locking majority of rows. In other words SQLServer has escalated the lock from row level to the entire table.]

no_Lock_Escalation

I == TRANSACTION_REPEATABLE_READ

I get all customers whose email have "thoughtworks" in it.

You try to update customer whose email doesn't have "thoughtworks" in it.

You do not get blocked by me.

[Since the number of customers who have "thoughtworks" in their email is much smaller, the lock is not escalated.]

no_Lock_Escalation_Even_When_Majority_Of_Rows_Read (Oracle)

I == TRANSACTION_SERIALIZABLE

I get all customers whose email have "bollywood" in it.

You try to update customer whose email doesn't have "bollywood" in it.

No blocking!

[I have read more than certain percentage of rows in the table. I am also demanding repeatable read (serializable is higher than repeatable) for each of them. There is no lock escalation in Oracle.]

REPEATABLE READ IMPLEMENTATION

repeatable_Read_Blocked_By_Others_Inserts_Inefficient (SqlServer)

I == TRANSACTION_REPEATABLE_READ

I get all customers whose name have "Ashok" in it.

You create a customer.

I again try to get all customers whose name have "Ashok" in it.

I get blocked.

[This looks like inefficient implementation of repeatable read in SQLServer, at serializable it would have been understandable. For Oracle look for the Phantom Read comparison.]

PHANTOM READ

phantom_Read (SqlServer)

I == TRANSACTION_REPEATABLE_READ

I get all customers whose name have "Ashok" in it.

You create a customer who also has Ashok in his name.

You commit.

I again get all customers whose name have "Ashok" in it.

This time I get a different list which I has one more customer than last time.

no_Phantom_Read (SqlServer)

I == TRANSACTION_SERIALIZABLE

I get all customers whose name have "Ashok" in it.

You try to create a customer who also has Ashok in his name.

You get blocked.

I again get all customers whose name have "Ashok" in it.

I get the same list of customers again (of course because the your create failed).

no_Phantom_Even_When_The_Database_Has_Changed (Oracle)

I == TRANSACTION_SERIALIZABLE

I get all customers whose name have "Ashok" in it.

You create a customer who also has Ashok in his name.

You commit.

I get all customers whose name have "Ashok" in it.

I get the same number of customers.

[Note again the difference in implementation]

PESSIMISTIC LOCK

SqlServer

I >= TRANSACTION_REPEATABLE_READ

YOU >= TRANSACTION_REPEATABLE_READ

I get a customer.

You get the same customer.

I update customer's email.

I get blocked.

Oracle

I == TRANSACTION_SERIALIZABLE

YOU == TRANSACTION_SERIALIZABLE

I get a customer.

You get the same customer.

I update customer's email.

You update same customer's email.

You get blocked.

[Note: Oracle tends towards letting operation go through and return old results when asked for. SqlServer blocks the update because it doesn't want to return stale results]