To promote the greatest concurrency, ALLBASE/SQL supports
a variety of granularities and lock types. Granularity is
the size of the object locked. Lock type is
the severity of locking provided. Compatibility refers
to the ability of different transactions to hold locks at the same
time on the same object.
The use of different granularities of locking promotes a high
level of concurrency. There are three levels of granularity in ALLBASE/SQL:
Row (tuple) level
Page level
Table level
Although some system operations use row level locking internally,
system operations acquire page locks by default. User-created tables
can be locked at the row, page, or table level, depending on the
table type. B-tree and constraint indexes are locked with weak locks
at the page level for update operations and are not locked at all
on reads. Table, page, and row level locking are illustrated in
Figure 5-3 Page Versus Table Level Locking
and Figure 5-4 Row Versus Page Level Locking.
Figure 5-3 portrays a query
that accesses two pages of a table.
Figure 5-3 Page Versus Table Level Locking
With page level locking, pages containing data scanned for
the query are locked. All other pages can be locked by other transactions.
With table level locking, the same query locks the table as a whole,
whether or not the individual pages are being used for a query.
This means that when a table has an exclusive lock on it, no other
transaction can obtain any locks on the table or any data page in
it until the transaction holding the page lock terminates.
Title not available also portrays
a query that accesses two pages of a table.
Figure 5-4 Row Versus Page Level Locking
With row level locking, only the rows containing data scanned
for the query are locked. All other rows can be locked by other
transactions. With page level locking, the same query locks an entire
page, even if the page contains row(s) not used by the query.
Table size can affect concurrency at the page level. For example,
if a small table occupies only one page, then the effect of a page
level lock is the same as locking the entire table. In the case
of small tables where frequent access is needed by multiple transactions,
row level locking can provide the best concurrency. After issuing
an UPDATE STATISTICS statement on a table, you can query the
SYSTEM.TABLE view to determine how many pages it occupies.
Table level locking serializes access to the table, that is,
forces transactions with incompatible locks to operate on a table
one at a time. This reduces deadlocks by keeping other users from
accessing the table until the transaction is committed or otherwise terminated.
A small table limits concurrency by its very nature since the probability
is high that many users will want to access the limited number of
pages or rows. By locking a small table at the table level, you
can improve performance by reducing the work of retrying deadlocked
transactions. On larger tables, the price of table level locking
is higher, since the naturally higher concurrency of the large table
is sacrificed to serialization.
Page level locking improves concurrency by allowing multiple
users to access different pages in the same table concurrently.
Row level locking maximizes concurrency by allowing multiple users
to access different rows in the same table at the same time, even on
the same page.
Because ALLBASE/SQL uses a buffer system in accessing data
from database files, keep in mind that the system can actually acquire
several page or row locks, one at a time, before the data is exposed
to the user. In effect, the user's transaction obtains and releases locks
on sets of pages or rows at a time as it moves through a
query result. This is because data from many pages and rows can
be required to fill the 12K tuple buffer.
Locks in ALLBASE/SQL can be classified into the following
five types, listed from the lowest to the highest level of severity:
Intention Share (IS): Indicates an intention to read data
at a lower level of granularity. An IS lock on a PUBLIC
table indicates an intention to read a page. An IS lock on a
PUBLICROW table together with an IS lock on a page indicates
an intention to read a row on that page. When a need to read data at
a lower level is established, ALLBASE/SQL internally requests an IS
lock at the higher level. For example, after an IS table lock has
been granted on a PUBLIC table, requests are made for S
locks on particular pages. In the case of a PUBLICROW table,
after IS locks have been granted on both table and page, requests are
made for S locks on particular rows.
Intention Exclusive (IX): Indicates an intention to update
or modify data at a lower level of granularity. An IX lock on a
PUBLIC table indicates an intention to modify data on a
page. An IX lock on a PUBLICROW table together with an IX
lock on a page indicates an intention to modify a row on that page.
When a need to write data at a lower level is established,
ALLBASE/SQL internally requests an IX lock at the higher level. For
example, after an IX table lock has been granted on a PUBLIC
table, requests are made for X locks on particular pages. In the case
of a PUBLICROW table, after IX locks have been granted on
both table and page, requests are made for X locks on particular
rows.
Share (S): Permits reading by other transactions.
Share and Intention Exclusive (SIX): Indicates a share lock
at the current level and an intention to update or modify data at a
lower level of granularity. SIX locks are placed on both tables,
pages, and rows. When the need to write data at the page or row level
is established, and there is also a need to be able to read every
page in the table without its being modified by any other
transaction, then ALLBASE/SQL internally requests a SIX lock on the
table. After an SIX lock has been granted on a PUBLIC table, no
additional locks are acquired when a page is read, but an X page lock
is acquired when a page is written. After an SIX lock has been
granted on a PUBLICROW table, no additional locks are
acquired when a row is read, but an IX page lock and an X row lock
are acquired when a row is written.
Exclusive (X): Prevents any access by other users. An
exclusive lock is required whenever data is inserted, deleted, or
updated. Because no other user can read this data before the
transaction completes, the integrity of the database is not
endangered if the changes have to be rolled back, either at the
user's request or on recovery after a system failure.
Some of these locks are intention locks. Intention
locks are obtained at a higher level of granularity whenever a lock
is obtained at a lower level. For example, when you obtain a share
lock (S) on a page, the table is normally locked with an intention
share lock (IS). This is done so that other transactions can quickly
tell that a table is being read by someone without the need to determine
which specific pages are being read. Suppose another transaction
wishes to lock the table in exclusive mode. The IS lock on the table
would prevent the other transaction from locking the table in exclusive
mode. Without the use of higher granularity locks, ALLBASE/SQL would
have to search all page or row locks to determine whether the exclusive
lock request could be granted.
Figure 5-5 Locks at Different Granularities
shows the use of an intention lock at the table level and share locks on the
page level. The example assumes that an index is being used for data access.
Figure 5-5 Locks at Different Granularities
Table 5-1 "Lock Compatibility Matrix"
shows the compatibility of different lock types. A Y (yes) at the intersection
of a row and column in the table indicates that two locks are compatible at the
same level of granularity; a blank space indicates that they are not
compatible.
Table 5-1 Lock Compatibility Matrix
IS
IX
S
SIX
X
IS
Y
Y
Y
Y
IX
Y
Y
S
Y
Y
SIX
Y
X
When two lock requests are compatible, both transactions are
allowed to access the table, page, or row concurrently, and the
lock on this data object is promoted to or left at the lock mode
of higher severity. For example, if transaction 2 wishes to update
a page that is already being read by transaction 1, transaction
2 requests an IX lock on the table and an X lock on the page. Transaction
1 has an IS lock on the table, which is compatible with the requested
IX, so the lock on the table is promoted to IX. Then, transaction
2 obtains the X lock on the page it needs to update only if transaction
1 is not already reading that same page. Note that S and X locks
on the same page are not compatible.
When locks are not compatible, the second access request must
wait until the lock acquired by the first access request is released.
Intention exclusive locks are called weak locks when
there is no other lock at a finer level of granularity on the object
being locked. This is the case for index pages, which are locked IX
when concurrent transactions are updating different rows on the
same page. Weak locks, also known as sublocks or concurrent locks,
are used to prevent the deletion of an index page by another concurrent
transaction. ALLBASE/SQL uses strong locks (exclusive locks) on
index pages only for splitting, deleting, or compressing index pages.