Editing Multiple granularity locking

{{short description|In a database, locking objects that contain other objects}}
{{More citations needed|date=January 2021}}

In [[computer science]], '''multiple granularity locking''' ('''MGL''') is a [[Lock (computer science)|locking]] method used in [[database management system]]s (DBMS) and [[relational database]]s.

In multiple granularity locking, locks are set on [[Object (computer science)|objects]] that contain other objects. MGL exploits the hierarchical nature of the ''contains'' relationship. For example, a [[database]] may have files, which contain pages, which contain records. This can be thought of as a [[tree (data structure)|tree]] of objects, where each [[Node (computer science)|node]] contains its children. A lock on this structure (such as a shared or exclusive lock) locks the targeted node as well as all of its descendants.<ref>{{cite journal |author1=Jim Gray |author2=Raymond A. Lorie |author3=G.&nbsp;R. Putzolu |author4=Iriving L. Traiger |title=Granularity of locks and degrees of consistency in a shared data base |journal=IBM Technical Report |date=1976 |publisher=IBM|citeseerx=10.1.1.92.8248 |author1-link=Jim Gray (computer scientist) }}</ref>

Multiple granularity locking is usually used with non-strict [[two-phase locking]] to guarantee [[serializability]].

==Lock modes==
In addition to shared ('''S''') locks and exclusive ('''X''') locks from other locking schemes, like strict two-phase locking, MGL also uses intentional "locks", which do not directly lock a node, but instead denote the existence, or intent to add, a lock of the specified type lower in the node hierarchy.  Intentional locks include "intention shared" ('''IS'''), "intention exclusive" ('''IX'''), and the combined "shared and intention exclusive"  ('''SIX''') locks. '''IS''' locks conflict with '''X''' locks, while '''IX''' locks conflict with '''S''' and '''X''' locks. The null lock ('''NL''') is compatible with everything.

To lock a node in '''S''' (or '''X'''), MGL has the transaction lock on all of its ancestors with '''IS''' (or '''IX'''), so if a transaction locks a node in '''S''' (or '''X'''), no other transaction can access its ancestors in '''X''' (or '''S''' and '''X'''). This protocol is shown in the following table:

{| class="wikitable"
| '''To Get''' || '''Must have on all ancestors'''
|- 
| IS or S || IS or IX
|-
| IX, SIX or X || IX or SIX
|}

Determining what level of granularity to use for locking is done by locking the finest level possible (i.e., at the lowest leaf), and then escalating these locks to higher levels in the file hierarchy to cover more records or file elements as needed in a process known as "lock escalation". MGL locking modes are compatible with each other as defined in the following matrix.
{| class="wikitable"
|-
! Mode || NL || IS || IX || S || SIX || X
|-
! NL
| {{Yes}} || {{Yes}} || {{Yes}} || {{Yes}} || {{Yes}} || {{Yes}}
|-
! IS
| {{Yes}} || {{Yes}} || {{Yes}} || {{Yes}} || {{Yes}} || {{No}}
|-
! IX
| {{Yes}} || {{Yes}} || {{Yes}} || {{No}} || {{No}} || {{No}}
|-
! S
| {{Yes}} || {{Yes}} || {{No}} || {{Yes}} || {{No}} || {{No}}
|-
! SIX
| {{Yes}} || {{Yes}} || {{No}} || {{No}} || {{No}} || {{No}}
|-
! X
| {{Yes}} || {{No}} || {{No}} || {{No}} || {{No}} || {{No}}
|}

Following the locking protocol and the compatibility matrix, if one [[Database transaction|transaction]] holds a node in S mode, no other transactions can have locked any ancestor in X mode.

==See also==
* [[Atomicity (programming)]]
* [[Concurrency control]]
* [[Lock (computer science)]]

==References==
{{Reflist}}

{{DEFAULTSORT:Multiple Granularity Locking}}
[[Category:Concurrency control]]