I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada. I am including my notes from the sessions in this blog, primarily for my own reference later. For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.
Session Title: Anatomy of a Data Modification: Locking and Isolation
Instructor: Kimberly Tripp
- Versioning is a powerful and underused mode that can solve many problems
- SQL uses locks on resources as indicators (i.e., intent shared, intent exclusive)
- Use SSD for most random IO drives; for server consolidation scenarios, this may be a common log drive
- SQL Server can be in 1 of 4 states at any given time
- Locking only
- Locking + versioning
- RCSI (statement-level read consistency)
- Snapshot isolation (transaction-level read consistency)
- RCSI + snapshot isolation
- Lock compatibility
- X – not compatible with IX, IS, S
- IX – not compatible with S; is compatible with IS
- IS – compatible with S
- dm_exec_sessions: isolation level + session setting
- 1 = Read uncommitted
- 2 = Read committed (no distinction between locking vs. versioning)
- 3 = Repeatable reads
- 4 = Serializable
- 5 = Snapshot
- Whitepapers