Adaptive Server version 11.9.2 provides two new locking schemes to improve the concurrency and performance of Adaptive Server:
Datapages locking
Datarows locking, also known as row-level locking
Together, these new locking schemes are referred to as data-only locking.
The pre-11.9.2 locking scheme continues to be supported; it is called allpages locking, and it is the default locking scheme when you first install or upgrade to version 11.9.2. A System Administrator can specify any locking scheme as the server-wide default.
Users can specify a locking scheme for a newly created table, using the create table command, and can change the locking scheme for an existing table to any other locking scheme using the alter table command.
Some of the changes made to support these new locking schemes include:
Additional types of locks
Changes to table and index structures for tables using the new locking schemes
New configuration parameters and changes to existing configuration parameters
Additions and changes to Transact-SQL command syntax, including:
Additions to create table and alter table to allow specifying the locking scheme
A new reorg command to manage space in tables that use the new locking schemes
Changes to select into syntax to allow specifying the locking scheme on the created table
Changes to system procedures to allow reporting on and configuring of new functionality
The number of locks available to all processes on the
server is limited by the configuration parameter number
of locks. Changing to data-only locking affects the number
of locks required during query processing.
