In which scenario is a "High Granularity Lock" preferred in a database system?When transactions frequently access a small number of rows within a table.When transactions require access to large portions of the database, such as entire tables or indexes.When the overhead of lock management is negligible compared to transaction execution time.When the database system prioritizes read operations over write operations.

What does the term "lock granularity" refer to in the context of database locking?The size of the lock timeout intervalThe level of detail at which locks are applied (e.g., at the row or table level)The number of transactions waiting for a lockThe duration for which a lock is held

What is the primary purpose of the "No-Wait" locking strategy in database systems?To ensure that transactions wait indefinitely for locks to ensure data integrity.To allow transactions to abort immediately if the required locks cannot be acquired instantly.To prioritize lock requests based on the duration a transaction has been waiting.To escalate locks from shared to exclusive mode more rapidly.

n a database system employing both pessimistic and optimistic concurrency control mechanisms, which of the following scenarios would typically be handled more efficiently by optimistic concurrency control?A high volume of transactions where most of them update a common set of data.Transactions that are mostly read-only and seldom update the database.Transactions in a banking system where account balances are frequently updated.

Which technique is commonly used in database systems to handle "phantom reads"?Using shared locks for all read operations.Applying range locks to cover the search criteria.Escalating row-level locks to table-level locks.Implementing timestamp-based version control

2 Concurrency Control TechniquesThese rules can be enforced by the lock manager module of the DBMS. Between thelock_item(X) and unlock_item(X) operations in transaction T, T is said to hold thelock on item X. At most one transaction can hold the lock on a particular item.Thus no two transactions can access the same item concurrently.Shared/Exclusive (or Read/Write) Locks. The preceding binary lockingscheme is too restrictive for database items because at most, one transaction canhold a lock on a given item. We should allow several transactions to access the sameitem X if they all access X for reading purposes only. This is because read operationson the same item by different transactions are not conflicting (see Section 21.4.1).However, if a transaction is to write an item X, it must have exclusive access to X. Forthis purpose, a different type of lock called a multiple-mode lock is used. In thisscheme—called shared/exclusive or read/write locks—there are three lockingoperations: read_lock(X), write_lock(X), and unlock(X). A lock associated with anitem X, LOCK(X), now has three possible states: read-locked, write-locked, orunlocked. A read-locked item is also called share-locked because other transactionsare allowed to read the item, whereas a write-locked item is called exclusive-lockedbecause a single transaction exclusively holds the lock on the item.One method for implementing the preceding operations on a read/write lock is tokeep track of the number of transactions that hold a shared (read) lock on an itemin the lock table. Each record in the lock table will have four fields: <Data_item_name,LOCK, No_of_reads, Locking_transaction(s)>. Again, to save space, the system needs tomaintain lock records only for locked items in the lock table. The value (state) ofLOCK is either read-locked or write-locked, suitably coded (if we assume no recordsare kept in the lock table for unlocked items). If LOCK(X)=write-locked, the value oflocking_transaction(s) is a single transaction that holds the exclusive (write) lockon X. If LOCK(X)=read-locked, the value of locking transaction(s) is a list of one ormore transactions that hold the shared (read) lock on X. The three operationsread_lock(X), write_lock(X), and unlock(X) are described in Figure 22.2. 2 As before,each of the three locking operations should be considered indivisible; no interleav-ing should be allowed once one of the operations is started until either the opera-tion terminates by granting the lock or the transaction is placed in a waiting queuefor the item.When we use the shared/exclusive locking scheme, the system must enforce the fol-lowing rules:1. A transaction T must issue the operation read_lock(X) or write_lock(X) beforeany read_item(X) operation is performed in T.2. A transaction T must issue the operation write_lock(X) before anywrite_item(X) operation is performed in T.2These algorithms do not allow upgrading or downgrading of locks, as described later in this section. Thereader can extend the algorithms to allow these additional operations.read_lock(X):B: if LOCK(X) = “unlocked”then begin LOCK(X) ← “read-locked”;no_of_reads(X) ← 1endelse if LOCK(X) = “read-locked”then no_of_reads(X) ← no_of_reads(X) + 1else beginwait (until LOCK(X) = “unlocked”and the lock manager wakes up the transaction);go to Bend;write_lock(X):B: if LOCK(X) = “unlocked”then LOCK(X) ← “write-locked”else beginwait (until LOCK(X) = “unlocked”and the lock manager wakes up the transaction);go to Bend;unlock (X):if LOCK(X) = “write-locked”then begin LOCK(X) ← “unlocked”;wakeup one of the waiting transactions, if anyendelse it LOCK(X) = “read-locked”then beginno_of_reads(X) ← no_of_reads(X) −1;if no_of_reads(X) = 0then begin LOCK(X) = “unlocked”;wakeup one of the waiting transactions, if anyendend;22.1 Two-Phase Locking Techniques for Concurrency Control 781Figure 22.2Locking and unlockingoperations for two-mode (read-write orshared-exclusive)locks.3. A transaction T must issue the operation unlock(X) after all read_item(X) andwrite_item(X) operations are completed in T.34. A transaction T will not issue a read_lock(X) operation if it already holds aread (shared) lock or a write (exclusive) lock on item X. This rule may berelaxed, as we discuss shortly.3This rule may be relaxed to allow a transaction to unlock an item, then lock it again later.782 Chapter 22 Concurrency Control Techniques5. A transaction T will not issue a write_lock(X) operation if it already holds aread (shared) lock or write (exclusive) lock on item X. This rule may also berelaxed, as we discuss shortly.6. A transaction T will not issue an unlock(X) operation unless it already holdsa read (shared) lock or a write (exclusive) lock on item X