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:
create table
drop table
create index
drop index
create view
drop view
create procedure
drop procedure
create trigger
drop trigger
create default
drop default
create rule
drop rule
create function
drop function
create functional index
drop functional index
create computed column
drop computed column
select into
alter table (all versions)
create schema
reorg rebuild
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.
