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