Configuring and managing lock usage
The LOCKS configuration parameter specifies the initial size of the internal lock table. If the database server increases the size of the lock table, you should increase the size of the LOCKS configuration parameter.
For information about how to determine an initial value for the LOCKS configuration parameter, see The LOCKS configuration parameter and memory utilization.
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.
Maximum number of locks allowed on 32-bit and 64-bit platforms
The following table shows the maximum number of allowed locks.
| 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) |
View messages concerning increases to the size of the lock table
Every time the database server increases the size of the lock table, the server places a message in the message log file. You should monitor the message log file periodically and increase the size of the LOCKS configuration parameter if you see that the database server has increased the size of the lock table.
Monitor out-of-locks errors
To monitor the number of times that applications receive the out-of-locks error, view the ovlock field in the output of onstat -p. You can also see similar information from the sysprofile table in the sysmaster database. The following rows contain the relevant statistics.
| Row | Description |
|---|---|
| ovlock | Number of times that sessions attempted to exceed the maximum number of locks |
| lockreqs | Number of times that sessions requested a lock |
| lockwts | Number of times that sessions waited for a lock |
Examine how applications use locks
If the database server is using an unusually large number of locks, you can examine how individual applications are using locks, as follows:
- Monitor sessions with onstat -u to see if a particular user is using an especially high number of locks (a high value in the locks column).
- If a particular user uses a large number of locks, examine the SQL statements in the application to determine whether you should lock the table or use individual row or page locks.
A table lock is more efficient than individual row locks, but it reduces concurrency.
One way to reduce the number of locks placed on a table is to alter a table to use page locks instead of row locks. However, page locks reduce overall concurrency for the table, which can affect performance.
You can also reduce the number of locks placed on a table by locking the table in exclusive mode.