Locking Scheduler & Managing Hierarchies of Database Elements. Prepared by: Pradhyuman Raol (114). Managing Hierarchies of Database Elements. It Focus on two problems that come up when there id tree structure to our data.
Managing Hierarchies of Database Elements
Prepared by: PradhyumanRaol (114)
It Focus on two problems that come up when there id tree
structure to our data.
Tree Structure : Hierarchy of lockable elements. And How to allow locks on both large elements, like Relations and elements in it such as blocks and tuples of relation, or individual.
Another is data that is itself organized in a tree. A major example would be B-tree index.
“Database Elements” : It is sometime noticeably the various elements which can be used for locking.
Eg: Tuples, Pages or Blocks, Relations etc.
Granularity locks and Types : While putting locks actually when we decide which database element is to be used for locking makes it separates in two types.
Types of granularity locks:
1) Large grained
2) Small grained
The solution to the problem of managing locks at different granularities involves a new kind of lock called a “Warning.“
It is helpful in hierarchical or nested structure .
It involves both “ordinary” locks and “warning” locks.
Ordinary locks: Shared(S) and Exclusive(X) locks.
Warning locks: Intention to shared(IS) and Intention to
These are the rules to be followed while putting locks on
1. To place an ordinary S or X lock on any element. we must
begin at theroot of the hierarchy.
2. If we are at the element that we want to lock, we need look
no further. We request lock there only
3. If the element is down in hierarchy then place warning lock
on that node respective of shared and exclusive locks and then
Move on to appropriate child and then try steps 2 or 3 and until
you go to desired node and then request shared or exclusive
IS column: Conflicts only on X lock.
IX column: Conflicts on S and X locks.
S column: Conflicts on X and IX locks.
X column: Conflicts every locks.
Consider the relation:
M o v i e ( t i t l e , year, length, studioName)
WHERE title = 'King Kong';
SET year = 1939
WHERE title = 'Gone With the Wind';
When transaction creates new sub elements of a lockable element, there are some opportunities to go wrong. The problem is that we lock only the existing items; there is no easy way to lock database elements that do not exist but might later be inserted.
Lets have transaction 3 (T3) to be executed:
WHERE studioName = ‘Disney’
But at the same time the transaction t4 inserts the new movie of
‘Disney’ studio. Then what happens if t3 gets executed and t4
afterwards that sum will be incorrect.
But solution could be we could treat the insert or delete transaction like
writing operation with exclusive locks at that time this problem gets
BOOK: DATABASE SYSTEM THE COMPLETE BOOK