LOCKMODE
The SET LOCKMODE statement changes the system defaults for locking that are in effect for a session (as defined for the DBMS in CBF). These defaults can be changed for any tables and indexes accessed during the session.
Use the SET LOCKMODE statement to optimize performance, alter the level of concurrency, or set locking back to either session or system level.
The SET LOCKMODE statement cannot be issued within a transaction, except for the statement:
SET LOCKMODE … WITH TIMEOUT=n|SESSION|SYSTEM|NOWAIT.
The following SET LOCKMODE parameters control locking for a session:
LEVEL
Specifies the level at which locks will be taken. It must be one of the following:
ROW
Takes row-level locks.
If row-level locking is specified and the number of locks granted during a query exceeds the system lock limit or the number of locks defined for the transaction, locking escalates to table level. This escalation occurs automatically and is independent of the user.
PAGE
(Default) Takes page-level locks.
If page-level locking is specified and the number of locks granted during a query exceeds the system lock limit or the number of locks defined for the transaction, locking escalates to table level. This escalation occurs automatically and is independent of the user.
TABLE
Takes table-level locks.
SESSION
Takes locks according to the default in effect for the session.
SYSTEM
Starts with page-level locking. If the optimizer estimates that more than maxlocks pages are referenced, escalates to table level locking.
MVCC
Uses multiversion concurrency control, which does not use page or read row locks. For more information, see the chapter on MVCC in the Database Administrator Guide.
READLOCK
Specifies the type of locking that applies when accessing a table to read the rows. It does not apply when accessing the table for INSERT, UPDATE, or DELETE or a table that is the object of an INSERT ... INTO SELECT ... FROM or CREATE TABLE ... AS SELECT. Any of the following modes can be specified:
NOLOCK
Takes no locks when reading data.
SHARED
Takes shared locks when reading data; this is the default mode of locking when reading data.
EXCLUSIVE
Takes exclusive locks when reading data; useful in “select-for-update” processing within a multi-statement transaction.
SESSION
Takes locks according to the current readlock default for your session.
SYSTEM
Takes locks according to the readlock default, which is shared.
MAXLOCKS
Specifies the maximum number of logical locks held by a transaction before locking escalates to a table lock. The number of locks available depends on your system configuration. The following escalation factors can be specified:
n
Specifies the number of logical locks to allow before escalating to table level locking. n must be an integer greater than 0.
SESSION
Specifies the current MAXLOCKS default for your session.
SYSTEM
Specifies the MAXLOCKS default, which is 50.
TIMEOUT
Specifies how long, in seconds, a lock request can remain pending. If the DBMS Server cannot grant the lock request within the specified time, the request aborts. Valid settings are:
n
Specifies the number of seconds to wait. n must be an integer between 0 and 2,147,483,647. If 0 is specified, the DBMS Server waits indefinitely for the lock.
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.
SESSION
Specifies the current timeout default for the session.
SYSTEM
Specifies the default, which is no timeout.
Last modified date: 04/26/2024