Appendix E Locks Held on the System Catalog by SQL Statements

This appendix shows the locks that you can obtain on the tables in the system catalog. This appendix may be useful in determining the source of repeated locking problems. For more information, refer to the chapter "Concurrency Control Through Locks and Isolation Levels" in theALLBASE/SQL Reference Manual.

If an SQL statement listed inTable E-2 “Locks Held on the System Catalog by SQL Statements”(which appears later in this chapter) is embedded in a procedure or a preprocessed application and a section is stored for the statement, system catalog pages will also be locked as follows:

As an example, consider an UPDATE statement embedded in an application program. When you preprocess the application, a page in the SYSTEM.SECTION table is locked exclusively as the new section is added. When you run the application, the EXECUTE statement implicitly runs as the stored section executes. EXECUTE obtains share locks on pages in SYSTEM.SECTION. If the section becomes invalid and then you run the application again, the PREPARE statement implicitly runs, obtaining an exclusive lock on pages in SYSTEM.SECTION; then the EXECUTE statement implicitly runs as the stored section executes.

Some of the system catalog views overlap with each other, because they are based on the same underlying table. The following groups of system views overlap in such a way that a lock on one member of the group effectively is a lock on all the members of the group:

Table E-1 “Mapping the System Views to the Base System Tables” lists the base tables from which the system catalog views are derived.

Locks on the system catalog are held to the end of the transaction, no matter what isolation level is used, to ensure the integrity of database objects while they are being used.

Whenever the HPRDBSS.SECTION table is locked, similar locks are placed on a STOREDSECT.DBEFileSetName table.

Some statements by their nature incorporate one or more other SQL statements. When a statement from the following list includes other SQL statements, locks may also be obtained for each statement incorporated:

The following group of statements used only within procedures do not obtain locks:

	NOTE: The information in this appendix is general in nature, and it shows worst case locking for a particular SQL statement. Not all locks are necessarily requested in all instances.