Procedure documentationConfiguring Locks Locate this document in the navigation structure

 

Multiple transactions can access the same database object, such as a table, at the same time. To isolate the transactions from one another, the database system sets locks for database objects.

Lock Types

Name

System Name

Description

Objects that Can Be Locked

Shared lock

SHARE LOCK

Other transactions retain read access to the database object and can continue to set shared locks for this database object.

Tables, rows, database catalog

Exclusive lock

EXCLUSIVE LOCK

Other transactions have neither read nor write access to the database object.

All SQL statements that change data, such as INSERT, UPDATE andDELETE statements, always request an exclusive lock.

Tables, rows, database catalog

Optimistic lock

-

Other transactions can continue to set shared, exclusive or optimistic locks for the database object.

Rows

More information: Concepts of the Database System, Locks

Procedure

Configuring the Isolation Level

The isolation level defines when the database system uses which lock type. This determines the degree to which transactions can run in parallel and the phenomena that may occur.

You can specify the isolation level when you open a database session or during a database session for individual SQL statements. If you do not specify an isolation level, the database system uses the default value. Depending on the isolation level, the database system then requests the necessary locks as the SQL statements are being processed.

More information:

Concepts of the Database Systems, Isolation Level

Database Studio,

SQL Reference Manual,

Loader,

Configuring the Maximum Number of Locks

With the general database parameter MaxSQLLocks you configure the maximum allowable number of locks.

More information: Database Administration, General Database Parameters

Configuring Lock Escalation

If a transaction requests too many row locks (more than 50% of the maximum allowable number of locks), the database system attempts to set table locks instead, until the number of locks drops below 20% of the maximum allowable number of locks (lock escalation).

You can configure lock escalation using the database parameter RowLocksPerTransactionThreshold.

More information: Database Administration, Special Database Parameters

Setting Timeouts for Locks

With the special database parameter RequestTimeout you configure the maximum amount of time that can elapse before the system releases a lock (timeout).

More information: Database Administration, Special Database Parameters