Locks for Database Objects

Use

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.

Types of Locks

Name

Objects that Can Be Locked

Shared Lock (SHARE LOCK)

Tables, rows, database catalog

Exclusive Lock (EXCLUSIVE LOCK)

Tables, rows, database catalog

Optimistic Locks

Rows

Activities

Requesting Locks

Locks can be requested as follows:

?     Locks for rows or tables can be requested/released implicitly by the database system, or explicitly requested/released by a user (see the SQL Reference Manual, LOCK Statement and LOCK Option (lock_option)).

?     Locks in the database catalog are always requested and released implicitly by the database system.

Through the isolation level, you define when the database system sets which type of lock. By doing so, you define the degree of parallelism of transactions and the phenomena that can occur. You can specify the isolation level when you open up a database session. 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. All changing SQL statements (such as INSERT, UPDATE, DELETE) always request an exclusive lock.

To explicitly request a lock for a transaction, you can use the LOCK statement.

To lock individual rows of a table, you can use the LOCK option within an SQL statement. This is possible at every isolation level. You can use the LOCK option to temporarily override the isolation level for individual SQL statements.

If too many row locks are requested in a table by a transaction, then the database system tries to set a table lock on it. You configure the limit from which the database system tries to turn row locks into a table lock using the MAXLOCKS general database parameter.

Releasing Locks

Type of Lock

Release

Requested explicitly by the user with the LOCK statement

The database system releases the lock after the end of the transaction.

Exceptions: If a COMMIT or ROLLBACK statement contains a LOCK statement, the database system does not release the locks at the end of the transaction.

Implicitly set shared lock

Depends on the isolation level

Implicitly set exclusive lock

Exclusive lock for changed rows

Exclusive locks that were requested by a changing SQL statement

Table locks

The database system only releases the lock after the end of the entire transaction.

Exclusive lock for rows that have not yet been changed

Shared locks for rows

You can release these locks with an UNLOCK statement.

See the SQL Reference Manual, UNLOCK Statement (unlock_statement).

With the special database parameter REQUEST_TIMEOUT you set the maximum amount of time that can elapse before the database system releases a lock (Timeout).

Displaying Lock Information

You can display information about locks as follows:

Database Manager GUI, Displaying Database Activities

?     In SAP systems, use the CCMS; see:

Database Administration in CCMS: MaxDB, SQL Locks

Database Administration in CCMS: SAP liveCache Technology, SQL Locks

The LOCKS system table contains information about locks; LOCK_WAITS contains information about the lock requests.

See also:

Transactions

Overview of the Database System