Setting the Table Locking Scheme

Choose or alter a locking scheme based on required performance.

Conversions between allpages 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 allpages 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 left pane of the Administration Console, expand ASE Servers > Schema Objects > Tables, then choose one of the following:
- User Tables
- Proxy Tables
Click the Name field of the table, then click the drop-down arrow and select Properties.
In the left pane, click Lock Scheme.
On the Lock Scheme screen:
1. Select the lock scheme.
2. Set the maximum rows per page, to limit the number of rows on a data page.
3. Set the rows size. This 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.
4. Set the reserve page gap to leaves empty pages on extents that are allocated to the object when commands that perform extent allocation are executed. Setting the 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.
5. Set the fill factor to allow space on the index pages to reduce page splits. Very small fillfactor 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 a dump transaction.