ALLBASE/SQL Reference Manual (36216-90216)

186 Chapter5
Concurrency Control through Locks and Isolation Levels
What Determines Lock Types
A simplified summary of locks obtained on PUBLICROW tables appears in Table 5-4. Hash
and TID scans are omitted.
NOTE ALLBASE/SQL locks system catalog pages at the RR isolation level when
they are accessed or modified on behalf of an SQL statement. Refer to the
appendix “Locks Held on the System Catalog by SQL Statements” in the
ALLBASE/SQL Database Administration Guide for a list of locks acquired
for each SQL statement.
Neighbor Locking
Neighbor locking is a way indexes are maintained. More than one object is locked within a
Publicrow. SQLMon is the best tool to get the kind of locks held on SQL objects.
During an index scan, “weak” (IS, IX) locks are placed on index and data pages. A tuple
(page) lock will be placed on the qualifying tuple(s). In order to insure RR (Repeatable
Read), an additional tuple (page) lock is placed on the data tuple corresponding to the
higher key next to the qualifying key. During a RR/CS/RC index scan, the qualifying data
tuple are locked in S. During inserts and deletes, the higher key's tuple is locked in X for
uniqueness and to insure RR for readers. Of course, the updated tuple is locked in X also.
During an update where the key is updated, we end up with two higher key locks because
Table 5-4. Locks Obtained on PUBLICROW Tables with Different Isolation
Levels
Isolation Level
and Scan Type
Read Operations
(SELECT, FETCH)
Read for Update
a
a. Opening a cursor that was declared FOR UPDATE (RR and CS), or using REFETCH (RC
and RU).
Write Operations
(UPDATE, INSERT,
DELETE)
Table Page Row Table Page Row Table Page Row
RR Sequential S - - SIX - - SIX IX X
RR Index IS IS S IX IX SIX IX IX
X
b
b. Next higher key’s data row is locked for an insert or delete, and the next two higher
key's data rows are locked for an update.
CS Sequential IS
IS
c
c. Lock released at the end of the next read.
Sc IX IXc SIXc IX IX X
CS Index IS ISc Sc IX IXc SIXc IX IX Xb
RC Sequential IS
IS
d
d. Lock released at the end of the current read.
Sd IX IXd SIX IX IX X
RC Index IS ISd Sd IX IX SIX IX IX Xb
RU Sequential None None None IX IX SIX IX IX X
RU Index None None None IX IX SIX IX IX Xb