In my first post of the series, I have mentioned that SQL Servers before version 2008 implemented transaction isolation levels by using locking only. In the newer version of SQL Server, this mechanism is NOT changed. Snapshots related isolation levels are the functionalities added to the existing framework. In general, transaction isolation levels controls the reading,SELECT, behaviour of SQL Server. I am going to explain it from locking perspective.

When you connect to SQL Server, by default, you get Read Committed isolation level. You can use Set Transaction Isolation Level command to change the default isolation level. There are two ways you can verify isolation level for current session:

dbcc useroptions
/*
Set Option               Value
---------------------- ----------------
textsize                 2147483647
language                 us_english
dateformat               mdy
datefirst                7
lock_timeout             -1
quoted_identifier        SET
arithabort               SET
ansi_null_dflt_on        SET
ansi_warnings            SET
ansi_padding             SET
ansi_nulls               SET
concat_null_yields_null  SET
isolation level          read committed
*/
select transaction_isolation_level from sys.dm_exec_sessions where session_id = @@spid
/*
transaction_isolation_level
---------------------------
2
*/

There are 4 transaction isolation level supported, Read Uncommitted, Read Committed, Repeatable Read, Snapshot, and Serializable.

Read Uncommitted
Regardless which table in which database you are accessing, the session will always apply schema stability on the object and Shared lock on the partition (for HEAP), like NoLock hint. Uncommitted data from other sessions will be visible to the session with Read Uncommitted transaction isolation level. This session will not block other session and vice versa for the data access. Any operations at table level might still block this session because these kind of operation may require schema modification lock or exclusive lock on the partition.

Read Committed
This is the default transaction isolation level. While you are accessing a table in a database with Read Committed Snapshot turned off, conceptually, SQL Server will get IS lock on the table, IS lock on the page, get Shared lock on a row, if the row should be returned, then return it. otherwise or after the row is returned, the Shared lock released; then get Shared lock on next records…and so on so forth until reaches the end of the set. If any record is being modifying before a Share lock granted, you will be blocked until the modification is done. Also, while you are reading a record, you have the Shared lock on the record, other sessions will not be able to modify it until you release the Shared lock.
While you are accessing a tble in a database with Read Committed Snapshot turned on, the behaviours are totally different. SQL Server will take a snapshot on the table that all the records are in the last committed transaction state within the table. You will be accessing the snapshot (conceptually) directly rather than the base table (conceptually). So the locking behaviours are same as Read Uncommitted but only applies to the table that has snapshot taken.

Repeatable Read
Repeatable Read can guarentee if you access a table twice within a transaction, the returning records from the first run are the same in the returning records after the second run. If any new records are added in between by other sessions, those records will be returned by the second run. The reason is that under this isolation level, any returning records will have a Shared lock on it until the transaction complete(rollback or commit). What if you don’t have a transaction? Then it will release all locks at the end of the statement.

Serializable
It uses Range lock. Let’s say if you have a range lock on number 1 and 3, you lock all numbers between 1 and 3, any number in between can’t be added. Serializable is using such mechanism. If your SQL accessing range of record, range locks are used to prevent the record in the range you are accessing not being modified if indexes can be utilized. If the index cannot be utilized, a Share lock will be applied to the object — which means nobody can add, inser, and delete records from this table.

Snapshot
Snapshot isolation level apples to the databases that have Allow Snapshot Isolation turned on. If a session have Snapshot isolation level on, whenever beging transaction starts, SQL Server will conceptually take snapshot from all the database that has Allow Snapshot Isolation turned on. The locking behavor is same as Read Uncommitted transaction isolation level.

You can verify this by the tool, which uses Exetended Evennt, I posted.