Table of Contents
5. Concurrency Control for Transactions
Outline
5.1 A Model for Concurrency Control The Problem
System Model
How to Implement SQL
Which Operations Get Synchronized?
Assumption - Atomic Operations
Assumption - Txns communicate only via Read and Write
Transactions Can Communicate via Brain Transport
Brain Transport (cont’d)
5.2 Serializability Theory
Equivalence of Histories
Examples of Equivalence
Serializable Histories
Another Example
Serialization Graphs
The Serializability Theorem
How to Use the Serializability Theorem
5.3 Synchronization Requirements for Recoverability
Recoverability
Avoiding Cascading Aborts
Strictness
Strictness (cont’d)
5.4 Two-Phase Locking
Basic Locking Isn’t Enough
Two-Phase Locking (2PL) Protocol
PPT Slide
2PL and Recoverability
Automating Locking
2PL Preserves Transaction Handshakes
2PL Preserves Transaction Handshakes (cont’d)
Brain Transport ? One Last Time
5.5 Implementing Two-Phase Locking
Lock Manager
Lock Manager (cont’d)
Lock Manager (cont’d)
Deadlocks
Deadlock Prevention
Deadlock Detection
Detection Using Waits-For Graph
Detection Using Waits-For Graph (cont’d)
Cyclic Restart
MS SQL Server
Distributed Locking
Distributed Deadlock
Oracle Deadlock Handling
Fancier Dist’d Deadlock Detection
Locking Granularity
Multigranularity Locking (MGL)
MGL Type and Instance Graphs
MGL Compatibility Matrix
MGL Complexities
MS SQL Server
Outline
5.6 Locking Performance
Conversions in MS SQL Server
Blocking and Lock Thrashing
More on Thrashing
Avoiding Thrashing
Interesting Sidelights
How to Reduce Lock Contention
Reducing Lock Contention (cont’d)
Mathematical Model of Locking
5.7 Hot Spot Techniques
Hot Spot Techniques (cont’d)
Delaying Operations Until Commit
Locking Higher-Level Operations
Solving the Threshold Problem Another IMS Fast Path Technique
Optimistic Concurrency Control
Batching
Partitioning
5.8 Query-Update Techniques
Data Warehouse
Degrees of Isolation
Degrees of Isolation (cont’d)
ANSI SQL Isolation Levels
MS SQL Server
Multiversion Data
Multiversion Data (cont’d)
Commit List Management
Multiversion Garbage Collection
Oracle Multiversion Concurrency Control
Oracle Concurrency Control (cont’d)
5.9 Phantoms
The Phantom Phantom Problem
Avoiding Phantoms - Predicate Locks
Precision Locks
5.10 B-Trees
B-Tree Structure
Example n=3
Insertion
B-Tree Observations
Key Range Locks
5.11 Tree Locking
B-tree Locking
B-link Optimization
|