The LOCKS parameter specifies the initial size of the lock table. The lock table holds an entry for each lock that a session uses. If the number of locks that sessions allocate exceeds the value of LOCKS, the database server increases the lock table by doubling its size. Each time that the database server doubles the size of the lock table, it allocates no more than 100,000 locks. The database server can dynamically increase the lock table up to 15 times.
The maximum value for the LOCKS parameter is 8,000,000. The absolute maximum number of locks in the database server is 9,500,000 which is 8,000,000 plus 15 dynamic allocations of 100,000 locks each.
Each lock requires 44 bytes in the resident segment. You must provide for this amount of memory when you configure shared memory.
The default value for the LOCKS configuration parameter is 2000. For more information on when to change this default value, see Configuring and Monitoring the Number of Locks.
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
State ment |
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 |