The four standard SQL Server isolation levels that SQL Server provides are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. (I leave aside the row-versioning levels for another day). SQL Server implements these isolation levels using locks.

In fact I was listening to Kimberly Tripp’s MCM Preparation Video “Snapshot Isolation“. She gives a recap of isolation levels and describes the locks taken during an index scan using READ COMMITTED. She says:

“(For readers) READ COMMITTED uses shared locks … the shared locks are only held for the life of the resource being read. You can almost think of shared locks kind of trickling through the table but not being kept.”

She invites us to visualize this scenario and so I did,  literally. Enjoy!

(Update June 15, 2012: Remember that with every isolation level (and with NOLOCK in particular) schema stability locks are taken and held. This affects concurrency of DDL statements like CREATE INDEX or ALTER TABLE. See Brent’s comment in the comment section for more)

Other reading

• Kimberly Tripp’s MCM preparation video Snapshot Isolation
• My own series on transaction isolation levels.
• The definitive resource page on Isolation Levels compiled by Kendra Little.