SQL Server Concurrency: Locking, Blocking, and Row Versioning

✍ Scribed by Kalen Delaney

• Publisher: Red Gate Books
• Tongue: English
• Leaves: 181
• Category: Library

✦ Synopsis

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. I hope my book helps!

✦ Table of Contents

_GoBack
Introduction
Chapter 1: Concurrency and Transactions
Pessimistic Versus Optimistic Concurrency
Transactions
Transaction properties
Transaction scope
Transaction isolation
The Lost Update Problem
Summary
Chapter 2: Locking Basics
Locking Overview
Lock resources
Lock modes
Lock duration
Lock ownership
Locking metadata
Locking Examples
Example 1: SELECT with READ COMMITTED isolation level
Example 2: SELECT with REPEATABLE READ isolation level
Example 3: SELECT with SERIALIZABLE isolation level
Example 4: Update with READ COMMITTED isolation level
Example 5: Update with SERIALIZABLE isolation level (with an index)
Example 6: Update with SERIALIZABLE isolation level not using an index
Example 7: Creating a table
Example 8: RID locks
Summary
Chapter 3: Advanced Locking Concepts
Lock Compatibility
Lock Mode Conversion
Special Intent Locks
Shared intent exclusive (SIX)
Update intent exclusive (UIX)
Shared intent update (SIU)
Key-Range Locks
RangeS-S (shared key-range and shared resource lock)
RangeS-U (shared key-range and update resource lock)
RangeX-X (exclusive key-range and exclusive resource lock)
RangeI-N (insert key-range and no resource lock)
Conversion key-range locks
Lock Escalation
Escalation based on SQL Server instance resource usage
Escalation based on number of locks held by a single statement
Other Types of Locks
Latches
Compile locks
Non-Lock-Related Causes of Blocking
Summary
Chapter 4: Controlling Locking
Controlling Concurrency and Locking Via the Isolation Level
Setting a Lock Timeout
Locking Hints
Sharing Locks Across Connections
Bound connections in action
Bound connection metadata
User-Defined Locks
Summary
Chapter 5: Troubleshooting Pessimistic Concurrency
Troubleshooting Locking
Detecting lock escalation
Resolving lock escalation
Controlling escalation
Troubleshooting Blocking
Detecting blocking problems
Finding the cause of blocking
Resolving blocking problems
Troubleshooting Deadlocking
Types of deadlock
Automatic deadlock detection
Finding the cause of deadlocks
Minimizing deadlocks
Summary
Chapter 6: Optimistic Concurrency
Overview of Row Versioning
How Row Versioning Works
Snapshot-based Isolation Levels
Enabling snapshot-based isolation
Working with RCSI
Working with SI
Viewing database state
Update conflicts
Summary of snapshot-based isolation levels
The Version Store
Management of the version store
Snapshot transaction metadata
Choosing a Concurrency Model
Final Recommendations
_GoBack
601
OLE_LINK3
_GoBack
_GoBack
_GoBack
_GoBack