SQLintersection session – Anatomy of a Data Modification: Locking and Isolation

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.