Database Management System Technique with Multiple Levels of Locking Mechanism
The Multiple Granularity Locking (MGL) protocol, a key component in database management systems, strikes a balance between efficiency and concurrency by allowing locks to be applied at various levels of data granularity, such as database, file, page, and record levels [2][3]. This hierarchical lock structure enables transactions to lock coarse-grained items when accessing large portions of data, thereby reducing locking overhead, while also permitting fine-grained locking when accessing smaller data subsets to maximize concurrent access.
In practice, MGL uses a tree-like hierarchy to represent data items at different granularities. Before a transaction locks a finer-grained item, such as a record, it must hold appropriate locks at all higher levels in the hierarchy, a mechanism known as "intention locking" [1][3]. This approach indicates the transaction's intent to lock items at lower levels without unnecessarily blocking concurrent transactions that lock unrelated higher-level items.
When it comes to specific locking rules, transactions must follow the 2-Phase Locking (2PL) protocol. A transaction needs to lock the root first, in any mode, as per the protocol. If a transaction reads all records in a file, it needs to lock the database and area in Intention-Shared (IS) mode, and the file in shared (S) mode. If a transaction modifies a record, it needs to lock the database, area, and file in Intention-Exclusive (IX) mode, and the record in exclusive (X) mode [2][3].
Moreover, if a transaction reads a record, it needs to lock the database, area, and file in Intention-Shared (IS) mode, and the record in shared (S) mode. To acquire exclusive (X) or Intention-Exclusive (IX) or Shared & Intention-Exclusive (SIX) locks, the parent node must be locked in Intention-Exclusive (IX) or Shared & Intention-Exclusive (SIX) mode. Similarly, to acquire shared (S) or Intention-Shared (IS) locks, the parent node must be locked in Intention-Exclusive (IX) or Shared & Intention-Exclusive (SIX) mode [2][3].
It is worth noting that the MGL protocol allows concurrent execution of transactions, but with restrictions. For instance, transaction T can execute concurrently with other transactions T, but T cannot execute concurrently with either T or T. This ensures that conflicts are minimised while maintaining a degree of concurrency [2][3].
Despite these measures, deadlocks can still occur under MGL, similar to traditional two-phase locking. However, the protocol's design allows for effective use of deadlock detection and elimination techniques [2][5].
In summary, the Multiple Granularity Locking protocol enhances performance by balancing the trade-off between lock overhead (efficiency) and the degree of concurrent transaction execution (concurrency) through a hierarchical and intention-based locking scheme [2][3]. A node can be unlocked only after all its children are unlocked, ensuring that the hierarchy constraints are preserved to maintain consistency and avoid conflicts.
Data-and-cloud-computing technology can leverage the hierarchical lock structure of the Multiple Granularity Locking (MGL) protocol to manage locking in a matrix-like data structure. For instance, if a transaction needs to lock a record at the most granular level, it would also require locks on the database, area, file, and parent nodes at Intention-Exclusive (IX) or Shared & Intention-Exclusive (SIX) modes, thus following the MGL protocol and forming a part of the overall data-and-cloud-computing matrix. Moreover, the MGL protocol's intention-based locking mechanism helps prevent unnecessary blocking, making it an effective choice for modern data- and cloud-computing applications that require high concurrency levels while maintaining data consistency.
