Many database maintenance
statements (CREATE, DROP, ALTER, and so on) lock
system catalog resources. To maximize concurrency and improve
performance, restructuring and creation should take place in
single-user mode during off hours when the DBEnvironment is not
usually being accessed.
Transactions that update the system catalog should be kept as short
as possible. Commands that create, grant, add, drop, revoke, or
update statistics write to the system catalog, which
is locked exclusive at the page level for updates.
For example, to improve concurrency, you can divide a
transaction that creates and loads a table into
two transactions. The first transaction creates the table and
then commits work, thus freeing system catalog pages. The second
transaction loads the table and then commits work.
A chart that shows which SQL statements
hold which locks on various system tables is in the appendix, "Locks Held on the System Catalog by SQL Statements."
Keep in mind that lock granularity can be set to table, page, or row
level. See the "Changing System Table Lock Types" section in this
SECT for more details.
If you think you are having locking problems with the
system catalog, refer to that appendix as you review your
transactions.
