Configuring Locks 
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.
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