ALLBASE/SQL Reference Manual (36216-90216)

180 Chapter5
Concurrency Control through Locks and Isolation Levels
Details of Locking
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. 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
Lock Compatibility
Table 5-1. 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.
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
Table 5-1. Lock Compatibility Matrix
IS IX S SIX X
IS YYYY
IX YY
S YY
SIX Y
X