ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 183
Concurrency Control through Locks and Isolation Levels
What Determines Lock Types
PUBLICROW and PUBLIC tables allow concurrent users to access the table for both reads and
writes but they increase the chances of deadlock, because concurrent transactions can be
waiting for each other to release locks. PUBLICROW tables obtain locks at the row level,
which affords more concurrency than with PUBLIC tables, at the possible cost of obtaining
more locks. PUBLICREAD tables allow only one transaction to write to a table, or they allow
multiple transactions to read the table; no readers can access the table while any writing
is going on. PRIVATE tables allow only one transaction to read from or write to a table at a
time.
If the locking structure of a table does not allow a transaction to access the table, the
transaction must wait. In a typical example, if one transaction is reading a PUBLICREAD
table, and a second transaction executes a statement to update that table, the second
transaction waits until the first transaction executes a COMMIT WORK or ROLLBACK WORK
statement.
The implicit locking structure of a table can be changed by using the ALTER TABLE
statement.
Use of the LOCK TABLE Statement
The LOCK TABLE statement is another determinant of lock types. With this statement,
ALLBASE/SQL explicitly locks a table as a whole, making most page or row locking
unnecessary. You can lock tables in SHARE mode, EXCLUSIVE mode, or in SHARE UPDATE
mode. With SHARE locking (S locks), other transactions may read pages in the table you
have locked but not update them. With EXCLUSIVE locking (X locks), no other transaction
may access the locked table until your transaction commits. With share update locking
(SIX locks), other transactions may read pages that are not being updated. However, no
other transaction can obtain an exclusive lock until your transaction ends with a COMMIT
WORK or ROLLBACK WORK statement.
You can upgrade the implicit locking mode of a table to a more severe level by using the
LOCK TABLE statement. Thus, you can lock a PUBLIC, PUBLICROW,orPUBLICREAD table in
EXCLUSIVE mode. However, you cannot downgrade the implicit locking mode. If you
attempt to lock a PRIVATE table in SHARE mode, the LOCK TABLE statement has no effect.
Use the LOCK TABLE statement to reduce the following:
• The overhead of obtaining and maintaining locks
• The potential for deadlock
Choice of a Scan Type
Another factor that determines the kind of locking in a data access transaction is the type
of scan used to process a query. There are four types of scan:
• Serial scan
• Index scan
• Hash scan
• TID scan
A sequential scan (also known as a serial scan) is one in which ALLBASE/SQL begins at