Commands that take intent locks

Versions of Adaptive Server earlier than 15.0.2 used table locks to achive system catalog synchronization. Adaptive Server version 15.0.2 and later uses intent locks for table-level synchronization and row locks for row-level synchronization. Earlier releases of Adaptive Server locked the entire system catalog while performing operations on the object, so a single lock request was made. However, Adaptive Server version 15.0.2 and later requests locks for all applicable rows while performing operations on the object if there are multiple rows corresponding to an object in a system catalog.

This change means that Adaptive Server version 15.0.2 and later requests more locks to perform the same operation than earlier releases, and increases the number of lock resources the system needs. Consequently, you may need to change the number of locks configuration option after you upgrade Adaptive Server.

These commands take intent locks in Adaptive Server version 15.0.2 later when they update a system table:

If two or more of these commands simultaneously access or update the same system table, their intent locks do not conflict with each other so they do not block on the system table.

The sp_fixindex and sp_spaceusage system procedures provide information about the row-locked catalogs.