 |
» |
|
|
|
|
|
5.46 How are the system catalog tables locked?
Key points
- The system catalog is a set of base tables owned by the special user HPRDBSS.
Views owned by the special users SYSTEM and CATALOG are defined on these
tables to enable the DBA and other users to access information in the system
catalog tables.
- The descriptions of isolation levels on the previous pages refer to rows in
user tables, not system catalog tables. When ALLBASE/SQL reads
rows in a system catalog table as part of normal internal processing, the
RR isolation level is always used; otherwise, the isolation level specified
in the transaction is used. When rows in a system catalog table are
explicitly read by a user transaction, the isolation level specified in the
transaction is used.
- For example, when queries are translated, ALLBASE/SQL needs to read rows in
system catalog tables as part of normal processing:
- In a preprocessed application program, queries are normally translated during
the preprocessing phase. The locks obtained during translation are released
when preprocessing completes.
- In a dynamic application program (such as ISQL), queries are dynamically
translated when the program is run. Both the locks that are required to
translate a query and the the locks that are required to execute the query
are obtained in the same transaction.
- Consider the example above:
- When the
SELECT statement is translated in ISQL, normal internal processing
includes determining whether or not a table or view named SYSTEM.TABLE
actually exists. ALLBASE/SQL determines this by performing an index
scan (using an internally defined index) over HPRDBSS.TABLE, to try to
retrieve a row for an object named SYSTEM.TABLE. If a row exists, the
object exists; otherwise an error is returned.
- During the index scan, an IS lock is obtained at the table level and an S lock
is obtained on the page that contains the row (all of the system catalog
tables are of type PUBLIC). These locks are retained until the end of the
transaction, because the RR isolation level is used.
- After the query has been translated, it is immediately executed. A serial
scan is performed over HPRDBSS.TABLE to satisfy the needs of the query. This
serial scan is not normal internal processing, it is simply a step in the
access plan for this particular
SELECT, which happens to involve a system
catalog table. Therefore, the isolation level specified in the BEGIN WORK is
used (in this case, RU), so no additional locks are acquired on HPRDBSS.TABLE.
- If the same query exists in a preprocessed application program, the IS and
S locks would be obtained and released during the preprocessing phase, and the
translated version of the query would be stored as a section in the
DBEnvironment. When the program is run, no locks would be obtained on
HPRDBSS.TABLE when the stored section is executed.
Page last updated on November 29, 1995
|
|
|
Printable version
