Was this helpful?
Set Lockmode Option
The set lockmode option allows you to specify a number of different types and levels of locks. Valid values for set lockmode parameters are listed in the following table:
Lockmode
Description
level
Specifies locking level as follows:
 
page
Specifies locking at the level of the data page (subject to escalation criteria as described below).
 
table
Specifies table-level locking.
 
session
Specifies the current default for your session.
 
system
Specifies that the DBMS Server starts with page level locking, unless it estimates that more than maxlocks pages are locked, in which case, table level locking is used.
readlock
Specifies lock mode for tables being read but not updated. You can specify any of the following readlock modes:
 
nolock
Specifies no locking when reading data.
 
shared
Specifies the default mode of locking when reading data.
 
exclusive
Specifies exclusive locking when reading data (useful in "retrieve-for-update" processing within a multi-query transaction).
 
session
Specifies the session default readlock.
 
system
Specifies the system readlock default.
maxlocks
Specifies the number of locks at which locking escalates from page level to table level. The number of locks available to you is dependent upon your system configuration. The following are valid values for maxlocks:
 
n
Specifies the number of page locks to allow before escalating to table level locking. The default is 10; n must be greater than 0.
 
session
Specifies the current maxlocks default for your session.
 
system
Specifies the system default for maxlocks. If you specify page level locking and the number of locks granted during a query exceeds the system-wide lock limit or the operating system's locking resources are depleted, locking escalates to table level. This escalation occurs automatically and is independent of the user.
timeout
Specifies the amount of time to wait for a lock. If the DBMS Server cannot grant the lock request within the specified time, the query that requested the lock aborts. Valid values for timeout are:
 
n
Specifies the number of seconds to wait for a lock; to specify an indefinite wait, set timeout to 0.
 
session
Specifies the session default.
 
system
Specifies the system timeout default.
 
nowait
Specifies that when a lock request is made that cannot be granted without incurring a wait, control is immediately returned to the application that issued the request.
The system defaults for each of the parameters are listed in the following table:
Parameter
Default
level
Dynamically determined by the DBMS Server
readlock
Shared
maxlocks
50
timeout
0 (no timeout)
At the beginning of a session, the system defaults are in effect. If you override them with other values using the set lockmode statement, you can revert back to the system defaults by specifying system, or the session defaults by specifying session.
The set lockmode statement cannot be issued within a transaction, except for the following statement:
set lockmode ... with timeout=<n|session|system|nowait>
Last modified date: 11/09/2022