Configuring Locks for Database Objects 
Multiple transactions can access the same database object, such as a table, at the same time. To isolate the transactions each 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 and DELETE statements, always request an exclusive lock. |
Tables, rows, database catalog |
Optimistic lock |
- |
Keeps track of changes to a database object 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 in what situations the database system uses which lock type. This determines the degree to which transactions can run in parallel and the phenomena that may occur. Depending on the isolation level, the database system then requests the necessary locks as the SQL statements are being processed.
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.
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 number of locks that the system can set), the database system attempts to set table locks instead, until the number of locks drops below 20% of the maximum number of locks. This process is called lock escalation.
You can configure lock escalation using the special 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