The locking technique uses a lock that guarantees the exclusive use of data of transactions. The lock is used to restrict different transaction which is executing at the same time.
2-phase locking technique
It is the same as the Locking technique explained above but it has two phases to act which are:
- Growing phase: It is a phase where a transaction acquires the lock but does not release it.
- Shrinking phase: It is a phase where a transaction releases the lock but does not acquire it.
The 2-phase locking technique is further divided into 3 categories
- Strict 2 phase locking
- Rigorous 2 phase locking
- Conservative 2 phase locking
Strict 2 phase locking
This locking technique satisfies the basic 2 phase locking but holds all the exclusive locks until the transaction is committed or aborted during the execution.
Rigorous 2 phase locking
This locking technique satisfies the basic 2 phase locking but holds all the exclusive locks and shared locks until the transaction is committed or aborted during the execution.
Conservative 2 phase locking
It is a locking technique that satisfies the basic 2 phase locking but it holds all the required locks at the beginning of transaction execution.
Rigorous two-phase locking is stricter than any of the available categories but it is very easy to implement. It ensures Cascadeless and strict recovery.
Deadlock in 2-phase locking technique
T1 | T2 |
Exclusive_lock(X) | |
Exclusive_lock(Y) | |
Exclusive_lock(Y) // wait till T2 release Y | |
Exclusive_lock(X) // wait till T1 release Y |
Let T1 and T2 be two transactions executing simultaneously. Transaction T1 takes an exclusive lock on a data value (Let’s say X) from the database and transaction T2 takes an exclusive lock on another data value (Let’s say Y) from the database and both of them start executing the transaction further. After some time the transaction T1 asks for another exclusive lock on data value Y, which leads T1 to wait until T2 releases the exclusive lock on Y as it is currently holding Y. After some time, transaction T2 asks for an exclusive lock on data value X which is currently held by transaction T1, so T2 also suffers from waiting till X is released from T1.
Now the situation is T1 is holding X and waiting for Y to complete its transaction, and T2 is holding Y and waiting for X to get released. Until T1 gets Y and completes the transaction, it will not release X. Similarly, unless T2 gets X and completes the transaction, it will not release Y. Now both the transactions are holding data values that are needed by each other to complete the transaction which leads both of them to wait infinitely for their resources which blocks both transactions to execute further (none of the transactions will abort in this process). This situation is called deadlock state which is possible in the two-phase locking technique.
Similar Reads
-
Aggregate function in SQL with Examples
In a Database Management System (DBMS), an aggregate function is a special tool that performs calculations on a set of… -
SQL and Parts of SQL language
SQL is a popular programming language, which is used to manage the relational databases and to perform various operations on… -
Views in Relational Algebra
In SQL, virtual table are considered as views. Like Tables, views also contain rows and columns. We can select the… -
Syntax of following SQL commands: sysdate, to date(), dual table ,to_number, substr() and initcap()
1. SYSDATE Purpose: SYSDATE is a function that returns the current date and time of the database server. Syntax SELECT… -
What is Super key, Candidate key and Primary key?
1. Super Key A super key is an attribute or a set of attributes within a table that uniquely identifies… -
What is Cursor? Difference between Implicit and Explicit Cursor
A Cursor is essentially a temporary work area created in the system memory that comes into play during the execution… -
What is Relational Algebra?
Relational algebra is a formal language used to query and manipulate data stored in relational database systems. It provides a… -
What do you mean by Relational Model? Explain with Example
The relational model is a type of model, which represents how the data are stored in a relational database. In… -
Functions of DBA in DBMS
The role of a Database Administrator (DBA) is to manage and maintain the performance, security, and integrity of a database.… -
What is Data Abstraction and Data Redundancy in DBMS
In the world of data management, two terms often come up: data abstraction and data redundancy. Both play crucial roles…