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.

You can use the isolation level to configure the locking behavior of the database system. More information: Isolation Level

Locks: Types

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` and`DELETE` 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

Shared Lock (SHARE LOCK)

When a transaction sets a shared lock on a database object, then other transactions can continue to gain read access to the database object, and other shared locks can be set for this database object. However, you cannot set an exclusive lock, or change the database object.

Shared locks can be set for the database catalog, for a table, or for a table row.

A shared lock is released implicitly at the end of the transaction by the database system, or it is released explicitly by an UNLOCK statement executed by the user.

Exclusive Lock (EXCLUSIVE LOCK)

When a transaction sets an exclusive lock on a database object, then the database object in question is available only to this transaction, and can only be changed by this transaction. Other transactions that check for exclusive locks or that want to set exclusive or shared locks conflict with the existing exclusive lock for this transaction (lock collision). They are permitted neither shared nor exclusive access to the locked object.

Exclusive locks can be set for the database catalog, for tables, or for table rows.

An exclusive lock is released by the database system at the end of the transaction in question. It can also be released with the UNLOCK statement by the respective user. However, this is only possible when the database object has not been changed. If the database object has been changed with an SQL statement (for example, using the INSERT, UPDATE, or DELETE statement), the exclusive lock remains in place until the end of the transaction.

Optimistic Lock

A transaction can set an optimistic lock for a table row (data record). The database system informs the transaction of the current version number of the table row.

Each time a table row is changed, the database system increases the version number. By comparing the original and current version numbers, database applications can determine whether the data read while setting the lock is still current or must be read again.

To get an optimistic lock, you have to request it explicitly with the LOCK statement. If an exclusive lock already exists for a database object, you cannot set an optimistic lock (lock collision). If an optimistic lock exists for a database object, other users can continue to set exclusive, shared, or additional optimistic locks.

Before a transaction can change a table row for which it has set an optimistic lock, the database system checks whether the table row has been changed by another user since the optimistic lock was set.

If the table row has not been changed, the database system first converts the optimistic lock into an exclusive lock, and makes the changes.
If another transaction has changed the table row in the meantime, the database system rejects the change and releases the optimistic lock.

An optimistic lock is only useful if one of the isolation levels 0, 1, 10, or 15 has been configured.

Setting Locks

Locks for rows or tables can be requested/released implicitly by the database system, or explicitly requested/released by a user.

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

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.

More information: SQL Reference Manual, LOCK Statement and LOCK Option (lock_option)

Releasing Locks

When Are Locks Released?
Type of Lock	Release
Locks are released explicitly by the user by means of a `LOCK` statement.	The database system releases the lock after the end of the transaction. Exceptions: If a `COMMIT` statement or a `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 lock	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.

To explicitly release a lock, you use the UNLOCK statement.

More information: SQL Reference Manual, UNLOCK Statement (unlock_statement)

More Information

Transactions

Database Administration, Analyzing Database Performance

Database Studio, Configuring Locks