(Aktu Btech) Database Management System Important Unit-5 Concurrency Control Techniques

Aktu’s Quantum Notes for Database Management System can help you excel in your B.Tech courses. Master important topics and excel in examinations with these vital, repeatable questions and notes. Unit-5 Concurrency Control Techniques

Dudes 🤔.. You want more useful details regarding this subject. Please keep in mind this as well.

Important Questions For Database Management System:
*Quantum               *B.tech-Syllabus
*Circulars                *B.tech AKTU RESULT
* Btech 3rd Year    * Aktu Solved Question Paper

Q1. What do you understand by lock compatibility? Explain with example.

Ans.

• 1. Lock compatibility determines whether locks can be acquired on a data item by multiple transactions at the same time.  
• 2. Suppose a transaction T_i requests a lock of mode m₁ on a data item Q on which another transaction T_j currently holds a lock of mode m₂.
• 3.  If mode m₂ is compatible with mode m₁ the request is immediately granted, otherwise rejected.  
• 4. The lock compatibility can be represented by a matrix called the compatibility matrix. 
• 5. The term “YES” indicates that the request can be granted and “NO” indicates that the request cannot be granted. 

Q2. How is locking implemented? How are requests to lock and unlock a data item handled?

Ans. Implementation of locking: 

• 1. The locking or unlocking of data items is implemented by a subsystem of the database system known as the lock manager. 
• 2. It receives the lock requests from transactions and replies them with a lock grant message or rollback message (in case of deadlock). 
• 3. In response to an unlock request, the lock manager only replies with an acknowledgement. In addition, it may result in lock grant messages to other waiting transactions.

The lock manager handles the requests by the transaction to lock and unlock a data item in the following way:

1. Lock request: 

• a. When a first request to lock a data item arrives, the lock manager creates a new linked list to record the lock request for the data item. 
• b. It immediately grants the lock request of the transaction.
• c. If the linked list for the data item already exists, it includes the request at the end of the linked list. 
• d The lock request will be granted only if the lock request is compatible with all the existing locks and no other transaction is waiting for acquiring lock on this data item otherwise, the transaction has to wait. 

2. Unlock request: 

• a. When an unlock request for the data items arrives, the lock manager deletes the record corresponding to that transaction from the linked list for the data item. 
• b. It then checks whether other waiting requests on that data item can be granted.
• c. If the request can be granted, it is granted by the lock manager, and the next record, if any, is processed. 
• d. If a transaction aborts, the lock manager deletes all waiting lock requests by the transaction.
• e. In addition, the lock manager releases all locks acquired by the transaction and updates the records in the lock table. 

Q3. Explain two phase locking protocol with suitable example.

Ans.

• 1. Two-phase locking is a procedure in which a transaction is said to follow the two-phase locking protocol if all locking operations precede the first unlock operation in the transaction. 
• 2. In 2PL, each transaction lock and unlock the data item in two phases : 
  • a. Growing phase: In the growing phase, the transaction acquires locks on the desired data items. 
  • b. Shrinking phase: In the shrinking phase, the transaction releases the locks acquired by the data items. 
• 3. According to 2PL, the transaction cannot acquire a new lock, after it has unlocked any of its existing locked items. 
• 4. Given below, the two transactions T₁ and T₂ that do not follow the two-phase locking protocol. 

• 5. This is because the write-lock (X) operation follows the unlock (Y) operation in T₁, and similarly the write-lock (Y) operation follows the unlock (X) operation in T₂. 
• 6. If we enforce two-phase locking, the transaction can be rewritten as:

• 7. It can be proved that, if every transaction in a schedule follows the two-phase locking protocol, the schedule is guaranteed to be serializable, obviating the need to test for serializability of schedules any more.

Q4. What do you mean by multiple granularities ? How it is implemented in transaction system ? 

Ans. Multiple granularity: 

• 1. Multiple granularity can be defined as hierarchically breaking up the database into blocks which can be locked. 
• 2. It maintains the track of what to lock and how to lock.
• 3. It makes easy to decide either to lock a data item or to unlock a data item. 

Implementation: 

• 1. Multiple granularity is implemented in transaction system by defining multiple levels of granularity by allowing data items to be of various sizes and defining a hierarchy of data granularity where the small granularities are nested within larger ones. 
• 2. In the tree, a non leaf node represents the data associated with its descendents. 
• 3. Each node is an independent data item. 
• 4. The highest level represents the entire database. 
• 5. Each node in the tree can be locked individually using shared or exclusive mode locks.
• 6. If a node is locked in an intention mode, explicit locking is being done at lower level of the tree (that is, at a finer granularity). 
• 7. Intention locks are put on all the ancestors of a node before that node is locked explicitly.
• 8. While traversing the tree, the transaction locks the various nodes in an intention mode. This hierarchy can be represented graphically as a tree.

• 9. When a transaction locks a node, it also has implicitly locked all the descendents of that node in the same mode.

Q5. What is granularity locking ? How does granularity of data item affect the performance of concurrency control ? What factors affect the selection of granularity size of data item? 

Ans. Granularity locking: 

• 1. Granularity locking is a concept of locking the data item on the basis of size of data item. 
• 2. It is based on the hierarchy of data where small granularities are nested within larger one. The lock may be granted at any level from bottom to top. 

Effect of granularity of data item over the performance of concurrency control: 

• 1. The larger the data item size is, the lower the degree of concurrency permitted. For example, if the data item size is disk block, a transaction T that need to lock a record B must lock the whole disk block X that contains B. If the other transactions want to lock record C which resides in same lock then it is forced to wait. 
• 2. If the data item size is small then the number of items in the database increases. Because every item is associated with a lock, the system will have a larger number of active locks to be handled by the lock manager. 
• 3. More lock and unlock operations will be performed which cause higher overhead.

Factors affecting the selection of granularity size of data items: 

• 1. It depends on the types of transaction involved. 
• 2. If a typical transaction accesses a small number of records, it is advantageous to have the data item granularity be one record. 
• 3. If a transaction typically accesses many records in the same tile, it may be better to have block or file granularity so that the transaction will consider all those records as one (or a few) data items.

Q6. Explain the recovery with concurrent transactions. 

Ans. Recovery from concurrent transaction can be done in the following four ways: 

• 1. Interaction with concurrency control: 
  • a. In this scheme, the recovery scheme depends greatly on the concurrency control scheme that is used. 
  • b. So to rollback a failed transaction, we must undo the updates performed by the transaction.  
• 2. Transaction rollback: 
  • a. In this scheme we rollback a failed transaction by using the log. 
  • b. The system scans the log backward, for every log record found in the log the system restores the data item.
• 3. Checkpoints: 
  • a. In this scheme we used checkpoints to reduce the number of log records that the system must scan when it recovers from a crash. 
  • b. In a concurrent transaction processing system, we require that the checkpoint log record be of the form <checkpoint L>, where L is a list of transactions active at the time of the checkpoint.
• 4. Restart recovery: 
  • a. When the system recovers from a crash, it constructs two lists. 
  • b. The undo-list consists of transactions to be undone, and the redolist consists of transaction to be redone.
  • c. The system constructs the two lists as follows: Initially, they are both empty. The system scans the log backward, examining each record, until it finds the first <checkpoint> record.

Database Management System Btech Quantum PDF, Syllabus, Important Questions

Database Management System Quantum PDF | AKTU Quantum PDF:

AKTU Important Links | Btech Syllabus

Important Links-Btech (AKTU)