Information Technology Reference
In-Depth Information
Problems
9.1 Consider a multi-granular key-range locking protocol on a four-level granule
hierarchy: the system, databases, relations (and indexes), and tuples. The units at all
the four levels are lockable. What locks are acquired by the transactions generated
by the following program fragments? All the transactions operate on a relation
r.X;V/ of database b in system s. The transactions are run at the serializable
isolation level, but at the same time as much concurrency as possible should be
allowed, while saving on the number of locks.
(a) create table r.X;V /; commit .
(b) alter table r add primary key .X /; commit .
(c) create index I on r.X/; commit .
(d) drop table r ; commit .
(e) select from r where x 1 <X and X<x 2 ;
insert into r values .y; v /; commit .
(f) select count . / from r ;
delete from r where X D x; commit .
(g) update r set V D V C 1 where X D x; commit .
(h) update r set V D V C 1 where x 1 <X and X<x 2 ; commit .
(i) delete from r where X<x; commit .
In case (c), a dense index is created. In cases (e) to (i), there exists a sparse (primary)
B-tree index to relation r on key X .
9.2 Consider the four-level granule hierarchy of the previous problem. Assume
that only the units at the two lowest levels, namely, relations and tuples, are
termed lockable, so that no locks are acquired at the two highest levels (system
and databases) of the granule hierarchy. What problems may arise? What if some
intermediate level, such as the database level, is omitted from the set of lockable
units?
9.3 Utilizing the property that an IX lock is more permissive than an X lock, we
can relax the basic (single-granular) key-range locking protocol, so that for an insert
action IŒx; v , a short-duration IX lock instead of a short-duration X lock is acquired
on the key y next to key x. Show that this relaxation works, that is, the relaxed
protocol still prevents all isolation anomalies. Symmetrically, we might think that
for a delete action DŒx; v , it would suffice to acquire only a short-duration IX lock
(instead of a short-duration X lock) on key x. However, this relaxation does not
work. Show why not.
9.4 Assume that the different-granule units of the database form an acyclic directed
graph instead of a tree-like hierarchy. Think of an object-oriented database in which
an object may be a member of more than one collection object. Also in a relational
database, the lockable units may form an acyclic graph instead of a tree when
index-specific locking is used. With index-specific locking , indexes to a relation are
lockable units.
Search WWH ::




Custom Search