Downloaded 574 times
JS
Uploaded byJanki Shah
PPTX, PDF20,704 views
Concurrency Control in Database Management System
The document discusses concurrency control in database management systems, focusing on lock-based protocols and strategies for handling deadlocks. It explains the mechanisms for obtaining and releasing locks, the two-phase locking protocol, and the risks of deadlock and starvation. Strategies for deadlock handling, including prevention, avoidance, and detection, are also outlined, highlighting specific rules like wait-die and wound-wait to manage resource allocation.
Recommended
More Related Content
What's hot
Similar to Concurrency Control in Database Management System
More from Janki Shah
![ANPARA THERMAL POWER STATION[1] sangam.pdf](/image.pl?url=https%3a%2f%2fcdn.slidesharecdn.com%2fss_thumbnails%2fanparathermalpowerstation1sangam-251121115219-9261cde4-thumbnail.jpg%3fwidth%3d640%26amp%3bheight%3d640%26amp%3bfit%3dbounds&f=jpg&w=240)
In this document
Powered by AI
Introduction to Database Management System focusing on Concurrency Control, explaining its role and contents covered in the presentation.
Defining Concurrency Control as a technique for protecting data during concurrent access by multiple users.
Discusses the Lock Based Protocol, types of locks (Exclusive, Shared), compatibility, and examples demonstrating lock requests.
Identifies pitfalls such as deadlocks and starvation in Lock Based Protocols, providing examples and solutions for improvement.
Details of the Two Phase Locking Protocol ensuring conflict-serializability schedules, along with conditions regarding locks.
Defines deadlock in process management and outlines the four necessary conditions for a deadlock to occur.
Explains strategies for dealing with deadlocks including prevention, avoidance, detection, and recovery methods.
Concurrency Control in Database Management System
- 1.Subject : DatabaseManagement SystemTopic : Concurrency Control
- 2.CONTENTS• What isConcurrency Control?• Lock Based Protocol• Two Phase Locking Protocol• Deadlock
- 3.What is ConcurrencyControl?The technique is used to protect data when multiple usersare accessing same data concurrently (same time) iscalled concurrency control
- 5.Lock Based Protocol•Lock is a mechanism to control concurrent access to dataitem• Data items can be locked in two modes:1) Exclusive (X) Mode :- Data item can be both read as well aswritten. X-lock is requested using lock-X instruction2) Shared (S) Mode :- Data item can only be read. S-lock isrequested using lock-S instruction• Lock requests are made to concurrency-control manager• Transaction can proceed only after request is granted
- 6.Lock Based Protocol•Lock-compatibility Matrix :S XS TRUE FALSEX FALSE FALSE• A transaction may be granted a lock on an item if therequested lock is compatible with locks already held on theitem by other transaction• Any number of transactions can hold shared locks on anitem, But if any transaction holds an exclusive on the itemno other transaction may hold any lock on the item
- 7.Lock Based Protocol•If a lock cannot be granted, the requesting transaction ismade to wait till all incompatible locks held by othertransaction have been released. The lock is then granted.• Example:T1: lock-S(A); // Grant-S(A,T1)read (A);unlock(A);lock-S(B); // Grant-S(B,T1)read (B);unlock(B);display(A+B)
- 8.Pitfalls of LockBased Protocol• Locking as above is not sufficient to guarantee serializability -if A and B get updated in-between the read of A and B, thedisplayed sum would be wrong.• A locking protocol is a set of rules followed by all transactionswhile requesting and releasing locks. Locking protocols restrictthe set of possible schedules.
- 9.Pitfalls of LockBased Protocol• Neither T3 nor T4 can make progress — executing lock-S(B)causes T4 to wait for T3 to release its lock on B, while executinglock-X(A) causes T3 to wait for T4 to release its lock on A.• Such a situation is called a deadlock.– To handle a deadlock one of T3 or T4 must be rolled backand its locks released.
- 10.Pitfalls of LockBased Protocol• Starvation is also possible if concurrency control manager isbadly designed. For example:– A transaction may be waiting for an X-lock on an item, whilea sequence of other transactions request and are granted anS-lock on the same item.– The same transaction is repeatedly rolled back due todeadlocks.• Concurrency control manager can be designed to preventstarvation.
- 12.Two Phase LockingProtocol• This is a protocol which ensures conflict-serializable schedules.• Phase 1: Growing Phase– transaction may obtain locks– transaction may not release locks• Phase 2: Shrinking Phase– transaction may release locks– transaction may not obtain locks• The protocol assures serializability. It can be proved that thetransactions can be serialized in the order of their lock points(i.e. the point where a transaction acquired its final lock).
- 13.Two Phase LockingProtocol• Two-phase locking does not ensure freedom from deadlocks• Cascading roll-back is possible under two-phase locking. Toavoid this, follow a modified protocol called strict two-phaselocking. Here a transaction must hold all its exclusive locks tillit commits/aborts.• Rigorous two-phase locking is even stricter: here all locks areheld till commit/abort. In this protocol transactions can beserialized in the order in which they commit.
- 15.Deadlock•A set ofprocesses is deadlocked if each process in the set iswaiting for an event that only another process in the set cancause.Ti Tj
- 16.Four Conditions forDeadlockA. Mutual exclusion condition– each resource assigned to 1 process or is availableB. Hold and wait condition– process holding resources can request additionalC. No preemption condition– previously granted resources cannot forcibly taken awayD. Circular wait condition– must be a circular chain of 2 or more processes– each is waiting for resource held by next member of the chain
- 17.Strategies of DeadlockHandling•Deadlock prevention.Prevents deadlocks by restraining requests made to ensurethat at least one of the four deadlock conditions cannot occur.•Deadlock avoidance.Dynamically grants a resource to a process if the resultingstate is safe. A state is safe if there is at least one executionsequence that allows all processes to run to completion.•Deadlock detection and recovery.That Allows deadlocks to form; then finds and breaks them.
- 18.Deadlock AvoidanceWAIT-DIE Rule:If Ti requests a lock on a data item which is already lockedby Tj , then Ti is permitted to wait if ts (Ti ) < ts (Tj ). Ifts (Ti ) >ts (Tj ) , then Ti is aborted and restarted with the same timestamp.– if ts (Ti ) <ts (Tj ) then Ti waits else Ti dies– non-preemptive: Ti never preempts Tj– prefers younger transactions
- 19.Deadlock AvoidanceWOUND-WAIT Rule:If Ti requests a lock on a data item which is already lockedby Tj , then Ti is permitted to wait iff ts(Ti)>ts(Tj). Ifts(Ti)<ts(Tj), then Tj is aborted and the lock is granted to Ti.– if ts(Ti)<ts(Tj) then Tj is wounded else Ti waits– preemptive: Ti preempts Tj if it is younger– prefers older transactions
- 20.Deadlock Detection• Fordeadlock detection, the system must provide– An algorithm that examines the state of the system to detect whether adeadlock has occurred– And an algorithm to recover from the deadlock• A detection-and-recovery scheme requires various kinds ofoverhead– Run-time costs of maintaining necessary information and executing thedetection algorithm