Adjusting the Number of Locks in Adaptive Server Enterprise

Use sp_configure to ensure that the number of locks in ASE is greater than the number of locks required by the ASE Output adapter or other applications that may query the data.

By default, the Adaptive Server is configured with 100,000 locks. System administrators can use sp_configure to change this limit. For example:

sp_configure 'number of locks', 200000

You may also need to adjust the sp_configure parameter, max memory, since each lock uses memory.

At a minimum, the number of locks needs to be greater than the number of locks required by the ASE Output adapter. Adaptive Server may also need locks for other applications which may be querying the data. The ASE Output adapter requires locks when inserting data into Adaptive Server. The number of required locks is affected by the bulk batch size parameter in the rap.ccr configuration file. This parameter tells the ASE Output adapter how many rows to include in a bulk insert before committing the transaction.

The minimum number of locks for each adapter is:

(<number of messages in unpartitioned tables> + <number of partitions for partitioned message A> + <number of partitions for partitioned message B> + ... ) * <bulk batch size>

For example, the reference application contains 14 message types. Two of these message types (stock quote and stock trade) have tables with 3 data partitions in each. The remaining 12 message types do not have partitioned tables. With a bulk batch size of 10,000, the ASE Output adapter needs a minimum of (12 + 3 + 3 ) * 10,000 = 180,000 locks. If you have multiple ASE Output adapters running against the same database, use the sum of the locks calculated for each.