The isolation level of queries and transactions and the workload type determine how many concurrent locks the database server needs. For example, a DSS application that accesses tables in Dirty Read isolation level uses locks sparingly. On the other hand, an OLTP application with many users who alter tables and work in Cursor Stability isolation level might require a very large number of concurrent locks.
Set the LOCKS configuration parameter to specify the initial number of locks that can be used at any one time. Each lock requires 44 bytes in the resident segment of shared memory. Multiply the number of locks by this amount to estimate of the amount of shared memory required for locks.
If the database server needs more locks, it adds 100,000 additional locks and writes a message to the event log. Locks can be increased as many as 32 times before the database server reports that it is out of locks. If you frequently see the dynamic-lock- allocation message, increase the value of the LOCKS parameter. The maximum you can specify is 16,000,000 locks.
Set LOCKS to the number of locks that a transaction or query usually needs, multiplied by the number of concurrent users. To estimate the number of locks that a query needs, use the guidelines in the following table.
| Locks per SQL statement | Isolation Level | Table | Row | Key | Simple Large Object |
|---|---|---|---|---|---|
| SELECT | Dirty Read | 0 | 0 | 0 | 0 |
| Committed Read | 1 | 0 | 0 | 0 | |
| Cursor Stability | 1 or value specified in ISOLATION_LOCKS | 1 or value specified in ISOLATION_LOCKS | 0 | 0 | |
| Indexed Repeatable Read | 1 | Number of rows satisfying conditions | Number of rows satisfying conditions | 0 | |
| Sequential Repeatable Read | 1 | 0 | 0 | 0 | |
| INSERT | For locks, relevant only for SELECT statements. | 1 | 1 | Number of indexes | Number of pages in simple large objects |
| DELETE | For locks, relevant only for SELECT statements. | 1 | 1 | Number of indexes | Number of pages in simple large objects |
| UPDATE | For locks, relevant only for SELECT statements. | 1 | 1 | 2 per changed key value | Number of pages in old plus new simple large objects |