- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain about two phase locking (2PL) protocol(DBMS)
Locking and unlocking of the database should be done in such a way that there is no inconsistency, deadlock and no starvation.
2PL locking protocol
Every transaction will lock and unlock the data item in two different phases.
Growing Phase − All the locks are issued in this phase. No locks are released, after all changes to data-items are committed and then the second phase (shrinking phase) starts.
Shrinking phase − No locks are issued in this phase, all the changes to data-items are noted (stored) and then locks are released.
The 2PL locking protocol is represented diagrammatically as follows −
In the growing phase transaction reaches a point where all the locks it may need has been acquired. This point is called LOCK POINT.
After the lock point has been reached, the transaction enters a shrinking phase.
Types
Two phase locking is of two types −
Strict two phase locking protocol
A transaction can release a shared lock after the lock point, but it cannot release any exclusive lock until the transaction commits. This protocol creates a cascade less schedule.
Cascading schedule: In this schedule one transaction is dependent on another transaction. So if one has to rollback then the other has to rollback.
Rigorous two phase locking protocol
A transaction cannot release any lock either shared or exclusive until it commits.
The 2PL protocol guarantees serializability, but cannot guarantee that deadlock will not happen.
Example
Let T1 and T2 are two transactions.
T1=A+B and T2=B+A
| T1 | T2 |
|---|---|
| Lock-X(A) | Lock-X(B) |
| Read A; | Read B; |
| Lock-X(B) | Lock-X(A) |
Here,
Lock-X(B) : Cannot execute Lock-X(B) since B is locked by T2.
Lock-X(A) : Cannot execute Lock-X(A) since A is locked by T1.
In the above situation T1 waits for B and T2 waits for A. The waiting time never ends. Both the transaction cannot proceed further at least any one releases the lock voluntarily. This situation is called deadlock.
The wait for graph is as follows −
Wait for graph: It is used in the deadlock detection method, creating a node for each transaction, creating an edge Ti to Tj, if Ti is waiting to lock an item locked by Tj. A cycle in WFG indicates a deadlock has occurred. WFG is created at regular intervals.
- Related Questions & Answers
- Explain schedules in 2PL with multiple granularities locking and schedules under tree(DBMS)
- Explain about the Time stamp ordering protocol in DBMS
- Explain about nested queries in DBMS
- Explain about concurrent transactions in DBMS
- Explain about conflict serializability in DBMS
- Explain about 2NF with an example in DBMS
- Explain about triggers and active databases in DBMS
- Explain about insert command in Structured query language in DBMS
- Explain about Create, Insert, Select command in structure query language (DBMS)?
- Explain about financial strategy.
- Explain about Transfer pricing
- What is query optimization and explain its two forms(DBMS)?
- Explain about Varargs in java?
- Explain about StringJoiner in java8?
- Explain about derivatives in finance.