Chapter 7 - Concurrency Control for Transactions Notes | EduRev

: Chapter 7 - Concurrency Control for Transactions Notes | EduRev

 Page 1


2/8/2012 
1 
7. Concurrency Control 
for Transactions 
Part Two 
 CSEP 545 Transaction Processing 
Philip A. Bernstein 
Sameh Elnikety 
 
Copyright ©2012 Philip A. Bernstein 
Page 2


2/8/2012 
1 
7. Concurrency Control 
for Transactions 
Part Two 
 CSEP 545 Transaction Processing 
Philip A. Bernstein 
Sameh Elnikety 
 
Copyright ©2012 Philip A. Bernstein 
2/8/2012 
2 
Outline 
1. A Model for Concurrency Control 
2. Serializability Theory 
3. Synchronization Requirements for Recoverability 
4. Two-Phase Locking 
5. Implementing Two-Phase Locking 
6. Locking Performance 
7. Multigranularity Locking (revisited) 
8. Hot Spot Techniques 
9. Query-Update Techniques 
10. Phantoms 
11. B-Trees 
12. Tree locking 
? 
? 
? 
? 
? 
Page 3


2/8/2012 
1 
7. Concurrency Control 
for Transactions 
Part Two 
 CSEP 545 Transaction Processing 
Philip A. Bernstein 
Sameh Elnikety 
 
Copyright ©2012 Philip A. Bernstein 
2/8/2012 
2 
Outline 
1. A Model for Concurrency Control 
2. Serializability Theory 
3. Synchronization Requirements for Recoverability 
4. Two-Phase Locking 
5. Implementing Two-Phase Locking 
6. Locking Performance 
7. Multigranularity Locking (revisited) 
8. Hot Spot Techniques 
9. Query-Update Techniques 
10. Phantoms 
11. B-Trees 
12. Tree locking 
? 
? 
? 
? 
? 
2/8/2012 
3 
8.6 Locking Performance 
• Deadlocks are rare 
– Up to 1% - 2% of transactions deadlock. 
• One exception: lock conversions 
– r-lock a record and later upgrade to w-lock 
– e.g., T
i
 = read(x) … write(x) 
– If two txns do this concurrently, they’ll deadlock  
(both get an r-lock on x before either gets a w-lock). 
– To avoid lock conversion deadlocks, get a w-lock first 
and down-grade to an r-lock if you don’t need to write. 
– Use SQL Update statement or explicit program hints. 
Page 4


2/8/2012 
1 
7. Concurrency Control 
for Transactions 
Part Two 
 CSEP 545 Transaction Processing 
Philip A. Bernstein 
Sameh Elnikety 
 
Copyright ©2012 Philip A. Bernstein 
2/8/2012 
2 
Outline 
1. A Model for Concurrency Control 
2. Serializability Theory 
3. Synchronization Requirements for Recoverability 
4. Two-Phase Locking 
5. Implementing Two-Phase Locking 
6. Locking Performance 
7. Multigranularity Locking (revisited) 
8. Hot Spot Techniques 
9. Query-Update Techniques 
10. Phantoms 
11. B-Trees 
12. Tree locking 
? 
? 
? 
? 
? 
2/8/2012 
3 
8.6 Locking Performance 
• Deadlocks are rare 
– Up to 1% - 2% of transactions deadlock. 
• One exception: lock conversions 
– r-lock a record and later upgrade to w-lock 
– e.g., T
i
 = read(x) … write(x) 
– If two txns do this concurrently, they’ll deadlock  
(both get an r-lock on x before either gets a w-lock). 
– To avoid lock conversion deadlocks, get a w-lock first 
and down-grade to an r-lock if you don’t need to write. 
– Use SQL Update statement or explicit program hints. 
2/8/2012 
4 
Conversions in MS SQL Server 
• Update-lock prevents lock conversion deadlock.  
– Conflicts with other update and write locks, but not 
with read locks.  
– Since at most one transaction can have an update 
lock, it can’t lead to a lock conversion deadlock. 
– Only on pages and rows (not tables). 
• You get an update lock by using the UPDLOCK 
hint in the FROM clause 
Select Foo.A 
From Foo (UPDLOCK) 
Where Foo.B = 7 
Page 5


2/8/2012 
1 
7. Concurrency Control 
for Transactions 
Part Two 
 CSEP 545 Transaction Processing 
Philip A. Bernstein 
Sameh Elnikety 
 
Copyright ©2012 Philip A. Bernstein 
2/8/2012 
2 
Outline 
1. A Model for Concurrency Control 
2. Serializability Theory 
3. Synchronization Requirements for Recoverability 
4. Two-Phase Locking 
5. Implementing Two-Phase Locking 
6. Locking Performance 
7. Multigranularity Locking (revisited) 
8. Hot Spot Techniques 
9. Query-Update Techniques 
10. Phantoms 
11. B-Trees 
12. Tree locking 
? 
? 
? 
? 
? 
2/8/2012 
3 
8.6 Locking Performance 
• Deadlocks are rare 
– Up to 1% - 2% of transactions deadlock. 
• One exception: lock conversions 
– r-lock a record and later upgrade to w-lock 
– e.g., T
i
 = read(x) … write(x) 
– If two txns do this concurrently, they’ll deadlock  
(both get an r-lock on x before either gets a w-lock). 
– To avoid lock conversion deadlocks, get a w-lock first 
and down-grade to an r-lock if you don’t need to write. 
– Use SQL Update statement or explicit program hints. 
2/8/2012 
4 
Conversions in MS SQL Server 
• Update-lock prevents lock conversion deadlock.  
– Conflicts with other update and write locks, but not 
with read locks.  
– Since at most one transaction can have an update 
lock, it can’t lead to a lock conversion deadlock. 
– Only on pages and rows (not tables). 
• You get an update lock by using the UPDLOCK 
hint in the FROM clause 
Select Foo.A 
From Foo (UPDLOCK) 
Where Foo.B = 7 
2/8/2012 
5 
Blocking and Lock Thrashing 
Throughput 
Low 
High 
# of Active Txns 
Low High 
• The locking performance problem is too much delay 
due to blocking. 
– Little delay until locks are saturated. 
– Then major delay, due to the locking bottleneck. 
– Thrashing - the point where throughput decreases with 
increasing load. 
thrashing 
Read More
Offer running on EduRev: Apply code STAYHOME200 to get INR 200 off on our premium plan EduRev Infinity!