DB2 10.5 for Linux, UNIX, and Windows

locktimeout - Lock timeout configuration parameter

This parameter specifies the number of seconds that an application will wait to obtain a lock, helping avoid global deadlocks for applications.

Configuration type

Database

Parameter type

Configurable

Default [range]

-1 [-1; 0 - 32 767 ]

Unit of measure

Seconds

If you set this parameter to 0, locks are not waited for. In this situation, if no lock is available at the time of the request, the application immediately receives a -911.

If you set this parameter to -1, lock timeout detection is turned off. In this situation a lock will be waited for (if one is not available at the time of the request) until either of the following events occur:

The lock is granted
A deadlock occurs.

Note: The value you specify for this configuration parameter is not used to control lock timeouts for event monitor target tables. Event monitors use a separate, non-configurable setting that will cause locks on event monitor tables to time out.

Recommendation: In a transaction processing (OLTP) environment, you can use an initial starting value of 30 seconds. In a query-only environment you could start with a higher value. In both cases, you should use benchmarking techniques to tune this parameter.

The value should be set to quickly detect waits that are occurring because of an abnormal situation, such as a transaction that is stalled (possibly as a result of a user leaving their workstation). You should set it high enough so valid lock requests do not time out because of peak workloads, during which time, there is more waiting for locks.

You can use the database system monitor to help you track the number of times an application (connection) experienced a lock timeout or that a database detected a timeout situation for all applications that were connected.

High values of the lock_timeout (number of lock timeouts) monitor element can be caused by:

Too low a value for this configuration parameter.
An application (transaction) that is holding locks for an extended period. You can use the database system monitor to further investigate these applications.
A concurrency problem, that could be caused by lock escalations (from row-level to a table-level lock).