O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQL Server Concurrency: Locking, Blocking and Row Versioning

Book Description

If you've designed your SQL code intelligently, and implemented a sensible indexing strategy, there's a good chance your queries will "fly", when tested in isolation. In the real world, however, where multiple processes can access the same data at the same time, SQL Server often has to make one process wait, sacrificing concurrency and performance, in order that in order that all can succeed, without destroying data integrity. Your application can have world-class indexes and queries, but they won't help you if you can't get your data, because another application has it locked. That's why every DBA and developer must understand SQL Server concurrency, and how to troubleshoot any issues.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Table of Contents
  5. About the Author
  6. Introduction
  7. Chapter 1: Concurrency and Transactions
    1. Pessimistic Versus Optimistic Concurrency
    2. Transactions
      1. Transaction Properties
      2. Transaction Scope
      3. Transaction Isolation
    3. The Lost Update Problem
    4. Summary
  8. Chapter 2: Locking Basics
    1. Locking Overview
      1. Lock Resources
      2. Lock Modes
      3. Lock Duration
      4. Lock Ownership
      5. Locking Metadata
    2. Locking Examples
      1. Example 1: Select with Read Committed Isolation Level
      2. Example 2: Select with Repeatable Read Isolation Level
      3. Example 3: Select with Serializable Isolation Level
      4. Example 4: Update with Read Committed Isolation Level
      5. Example 5: Update with Serializable Isolation Level (with an Index)
      6. Example 6: Update with Serializable Isolation Level not using an Index
      7. Example 7: Creating a Table
      8. Example 8: Rid Locks
    3. Summary
  9. Chapter 3: Advanced Locking Concepts
    1. Lock Compatibility
    2. Lock Mode Conversion
    3. Special Intent Locks
      1. Shared Intent Exclusive (Six)
      2. Update Intent Exclusive (Uix)
      3. Shared Intent Update (Siu)
    4. Key-Range Locks
      1. RangeS-S (Shared Key-Range and Shared Resource Lock)
      2. RangeS-U (Shared Key-Range and Update Resource Lock)
      3. RangeX-X (Exclusive Key-Range and Exclusive Resource Lock)
      4. RangeI-N (insert key-range and no resource lock)
      5. Conversion Key-Range Locks
    5. Lock Escalation
      1. Escalation Based on Sql Server Instance Resource Usage
      2. Escalation Based on Number of Locks Held by a Single Statement
    6. Other Types of Locks
      1. Latches
      2. Compile Locks
    7. Non-Lock-Related Causes of Blocking
    8. Summary
  10. Chapter 4: Controlling Locking
    1. Controlling Concurrency and Locking Via the Isolation Level
    2. Setting a Lock Timeout
    3. Locking Hints
    4. Sharing Locks Across Connections
      1. Bound Connections in Action
      2. Bound Connection Metadata
    5. User-Defined Locks
    6. Summary
  11. Chapter 5: Troubleshooting Pessimistic Concurrency
    1. Troubleshooting Locking
      1. Detecting Lock Escalation
      2. Resolving Lock Escalation
      3. Controlling Escalation
    2. Troubleshooting Blocking
      1. Detecting Blocking Problems
      2. Finding the Cause of Blocking
      3. Resolving Blocking Problems
    3. Troubleshooting Deadlocking
      1. Types of Deadlock
      2. Automatic Deadlock Detection
      3. Finding the Cause of Deadlocks
      4. Minimizing Deadlocks
    4. Summary
  12. Chapter 6: Optimistic Concurrency
    1. Overview of Row Versioning
    2. How Row Versioning Works
    3. Snapshot-Based Isolation Levels
      1. Enabling Snapshot-Based Isolation
      2. Working with RCSI
      3. Working with SI
      4. Viewing Database State
      5. Update Conflicts
      6. Summary of Snapshot-Based Isolation Levels
    4. The Version Store
      1. Management of the Version Store
      2. Snapshot Transaction Metadata
    5. Choosing a Concurrency Model
    6. Final Recommendations