Transcript Managing Hierarchies of Database Elements

Locking Scheduler
&
Managing Hierarchies of Database Elements
Prepared by: Mudra Patel (113)
Pradhyuman Raol(114)
Scheduler

The order in which the individual steps of
different transactions occur is regulated
by the scheduler.

The general process of assuring that
transactions preserve consistency when
executing simultaneously is called
concurrency control.
Role of a Scheduler
Architecture of a Locking Scheduler

The transactions themselves do not request
locks, or cannot be relied upon to do so. It is
the job of the scheduler to insert lock actions
into the stream of reads, writes and other
actions that access data.

Transactions do not locks. Rather the
scheduler releases the locks when the
transaction manager tells it that the
transaction will commit or abort.
Lock Table
Lock Table

The lock table is a relation that
associates database elements with locking
information about that element.

The table is implemented with a hash
table using database elements as a hash
key.
Size of Lock Table

The size of the table is proportional to the
number of locked elements only and not
to the entire size of the database since any
element that is not locked does not appear
in the table.
Structure of Lock Table Entries
Group Mode

The group mode is a summary of the most
stringent conditions that a transaction
requesting a new lock on an element
faces. Rather than comparing the lock
request with every lock held by another
transaction on the same element, we can
simplify the grant/deny decision by
comparing the request with only the group
mode.
Handling Lock Requests
Suppose transaction T requests a lock on
A.
 If there is no lock-table entry for A, then
surely there are no locks on A, so the
entry is created and the request is granted.
 If the lock-table entry for A exists then we
use it to guide the decision about the lock
request.
Handling Unlocks
If the value of waiting is ‘Yes’ then we
need to grant one or more locks from the
list of requested locks. The different
approaches for this are:
 First-come-first-served
 Priority to shared locks
 Priority to upgrading

Managing Hierarchies of Database Elements

It Focus on two problems that come up when there id tree
structure to our data.
1.
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.
2.
Another is data that is itself organized in a tree. A major example
would be B-tree index.
Locks With Multiple Granularity
“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
Example: Bank database

Small granularity locks: Larger concurrency can achieved.

Large granularity locks: Some times saves from unserializable
behavior.
Warning locks

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
Warning locks: Intention to
Exclusive(IX) locks.
Exclusive(X) locks.
shared(IS) and Intention to
Warning Protocols

These are the rules to be followed while putting locks on
different elements.
1. To place an ordinary S or X lock on any element. we must
begin at the root 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
lock.
Compatibility Matrix
IS
IX
S
X
IS
YES
YES
YES
NO
IX
YES
YES
NO
NO
S
YES
NO
YES
NO
X
NO
NO
NO
NO
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.
Warning Protocols
Consider the relation:
M o v i e ( t i t l e , year, length, studioName)
Transaction1 (T1):
SELECT *
FROM Movie
WHERE title = 'King Kong';
Transaction2(T2):
UPDATE Movie
SET year = 1939
WHERE title = 'Gone With the Wind';
Phantoms and Handling Insertions

When ever some transaction inserts sub elements to the
node being locked then there may be problem like
serializability issues.
Lets have transaction 3 (T3) to be executed:
SELECT SUM(length)
FROM Movie
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
solved.
References
BOOK: DATABASE SYSTEM THE COMPLETE BOOK
THANK YOU!