Setting the Table Locking Scheme

Choose or alter a locking scheme based on required performance.

Conversions between all pages locking and data-only locking schemes can be expensive in time and I/O and require sufficient free space. Convert the locking scheme by creating copies of the tables and re-creating indexes. You must also dump the affected databases, and update statistics before changing between all pages locking and data-only locking schemes.

Conversions between data page and data row locking are quick and inexpensive, and implemented by updates to system tables. The data page and data row schemes are collectively called data-only locking.

In the Perspective Resources view, select the server on which the table resides, then click the drop-down arrow next to the server name and select Administration Console.
In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Tables, then choose one of the following:
- User Tables
- Proxy Tables
Select the table on which set the locking scheme.
Select Properties.
Click Lock Scheme.
Select the Lock Scheme.
Set the space management parameters:
- Max rows per page – limits the number of rows on a data page.
- Expected row size – sets the rows size, but can increase the amount of storage required. If your tables have many rows that are shorter than the expected row size, setting this value and reorganizing the use of table space or changing the locking scheme increases the storage space required for the table.
- Reserve page gap – leaves empty pages on extents that are allocated to the object when commands that perform extent allocation are executed. Setting Reserve page gap to a low value increases the number of empty pages and spreads the data across more extents, so the additional space required is greatest immediately after creating an index or reorganizing the use of table space.
- Fill factor – allows space on the index pages to reduce page splits. Very small fill factor values can cause the storage space required for a table or an index to be significantly greater.
(Optional) After converting from all pages locking and either of the data-only locking schemes, check table and database consistency. You must also perform a full database dump before you can back up the transaction log with dump transaction.