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 Table 1. 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

Table 1. Maximum number of locks 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
Important: During the execution of the SQL statement DROP DATABASE, the database server acquires and holds a lock on each table in the database until the entire DROP operation completes. Make sure that the value for LOCKS is large enough to accommodate the largest number of tables in a database.

Copyright© 2019 HCL Technologies Limited