Information Technology Reference
In-Depth Information
1,000 different items are sold per day on the average, the table will contain about
.6
300/
200
1000
D
360 million tuples at the end of the year 2015.
A typical bulk-insert action on the
sales
table inserts new sales tuples represent-
ing the sales amounts of items sold at a single store during the previous day, such
as
insert into
sales
select
from
sales-2015-09-25-S123
order by
sales-date
,
store-id
,
item-id
,
where the relation (or view)
sales-2015-09-25-S123
contains the sales data of store
S123 from September 25, 2015 (about 1,000 tuples).
A typical bulk-delete action undoes a committed bulk insert of incorrect data, or
periodically deletes some of the oldest data from the table, such as the data for the
first quarter of 2010:
delete from
sales
where
sales-date
< '2010-04-01'
(about .300=4/
200
1000
D
15 million tuples).
The above actions insert or delete a set of tuples belonging to a subrange of the
key space of the primary key of the table, where the component attributes of the key
appear in the order
sales-date
,
store-id
,
item-id
.
t
Given that typical actions on the fact table are key-range actions on the primary-
index key of the table, it is tempting to try to define a locking protocol that uses
a single X lock to lock the entire key range to be inserted or deleted and a single
S lock to lock the entire key range to be read. A simple solution would be to resort to
predicate locking
(see Sect.
6.8
), which in this case means that the lock table would
store predicates that specify a key range of the primary key, and checking two locks
of incompatible modes for compatibility would mean testing the intersection of two
ranges for emptiness.
However, as explained earlier, in efficient lock management, the lock names are
short (4-byte) hash values computed from the key or identifier of the data item to
be locked, the lock table is organized as a hash table, and the compatibility of two
locks of incompatible modes is tested by comparing two lock names for inequality.
We wish to define a locking protocol that satisfies all these properties. Moreover,
we wish to maintain the natural requirement that locks once granted to a transaction
never need be changed due to a structure modification (such as a page split or merge)
that affects the locked part of the database while the transaction is still active. This
rules out using locks whose names are derived from dynamic physical structures,
such as page-ids of B-tree pages (possibly used to name a lock on the key range
currently covered by the page).
Our solution is to allow for the database administrator to define a hierarchy of
lockable
partition fragments
of different granularities for a relation by specifying
logical range partitions
on the relation. The specification would need a simple
extension of the
SQL
create table
and
alter table
statements.
