The LOCKS configuration parameter and memory utilization
The LOCKS configuration parameter specifies the initial size of the lock table. The lock table holds an entry for each lock that a session uses. Each lock uses 120 bytes within a lock table. You must provide for this amount of memory when you configure shared memory.
If the number of locks needed by sessions exceeds the value set in the LOCKS configuration parameter, the database server attempts to increase the lock table by doubling its size. Each time that the lock table overflows (when the number of locks needed is greater than the current size of the lock table), the database server increases the size of the lock table, up to 99 times. Each time that the database server increases the size of the lock table, the server attempts to double its size. However, the server will limit each actual increase to no more than the maximum number of added locks shown in Maximum number of locks on 32-bit and 64-bit platforms. After the 99th time that the database server increases the lock table, the server no longer increases the size of the lock table, and an application needing a lock receives an error.
The following table shows the maximum number of locks allowed on 32-bit and 64-bit platforms
| Platform | Maximum Number of Initial Locks | Maximum Number of Dynamic Lock Table Extensions | Maximum Number of Locks Added Per Lock Table Extension | Maximum Number of Locks Allowed |
|---|---|---|---|---|
| 32-bit | 8,000,000 | 99 | 100,000 | 8,000,000 + (99 x 100,000) |
| 64-bit | 500,000,000 | 99 | 1,000,000 | 500,000,000 + (99 x 1,000,000) |
The default value for the LOCKS configuration parameter is 20,000.
To estimate a different value for the LOCKS configuration parameter, estimate the maximum number of locks that a query needs and multiply this estimate by the number of concurrent users. You can use the guidelines in the following table to estimate the number of locks that a query needs.
| Locks per Statement | Isolation Level | Table | Row | Key | TEXT or BYTE Data | CLOB or BLOB Data |
|---|---|---|---|---|---|---|
| SELECT | Dirty Read | 0 | 0 | 0 | 0 | 0 |
| SELECT | Committed Read | 1 | 0 | 0 | 0 | 0 |
| SELECT | Cursor Stability | 1 | 1 | 0 | 0 | 1 lock for the CLOB or BLOB value or (if byte-range locking is used) 1 lock for each range |
| SELECT | Indexed Repeatable Read | 1 | Number of rows that satisfy conditions | Number of rows that satisfy conditions | 0 | 1 lock for the CLOB or BLOB value or (if byte-range locking is used) 1 lock for each range |
| SELECT | Sequential Repeatable Read | 1 | 0 | 0 | 0 | 1 lock for the CLOB or BLOB value or (if byte-range locking is used) 1 lock for each range |
| INSERT | Not applicable | 1 | 1 | Number of indexes | Number of pages in TEXT or BYTE data | 1 lock for the CLOB or BLOB value |
| DELETE | Not applicable | 1 | 1 | Number of indexes | Number of pages in TEXT or BYTE data | 1 lock for the CLOB or BLOB value |
| UPDATE | Not applicable | 1 | 1 | 2 per changed key value | Number of pages in old plus new TEXT or BYTE data | 1 lock for the CLOB or BLOB value or (if byte-range locking is used) 1 lock for each range |