By default, Adaptive Server is configured with 5000 locks. System administrators can use sp_configure to change this limit. For example:
sp_configure "number of locks", 25000
You may also need to adjust the sp_configure parameter total memory, since each lock uses memory.
The number of locks required by a query can vary widely, depending on the locking scheme and on the number of concurrent and parallel processes and the types of actions performed by the transactions. Configuring the correct number for your system is a matter of experience and familiarity with the system.
You can start with 20 locks for each active concurrent connection, plus 20 locks for each worker process. Consider increasing the number of locks if:
You change tables to use datarows locking
Queries run at isolation level 2 or 3, or use serializable or holdlock
You enable parallel query processing, especially for isolation level 2 or 3 queries
You perform many multirow updates
You increase lock promotion thresholds
