ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 179
Concurrency Control through Locks and Isolation Levels
Details of Locking
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.
Types of Locks
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