Oracle Plsql Lock Table Statement
Oracle / PLSQL: LOCK TABLE Statement
This Oracle tutorial explains how to use the Oracle LOCK TABLE statement with syntax and examples.
Description
The LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.
Syntax
The syntax for the LOCK TABLE statement is:
LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];Parameters or Arguments
- tables
- lock_mode
- WAIT
- NOWAIT
A comma-delimited list of tables.
It is one of the following values:
| lock_mode | Explanation |
|---|---|
| ROW SHARE | Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access. |
| ROW EXCLUSIVE | Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode. |
| SHARE UPDATE | Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access. |
| SHARE | Allows concurrent queries but users are prevented from updating the locked table. |
| SHARE ROW EXCLUSIVE | Users can view records in table, but are prevented from updating the table or from locking the table in SHARE mode. |
| EXCLUSIVE | Allows queries on the locked table, but no other activities. |
It specifies that the database will wait (up to a certain number of seconds as specified by integer) to acquire a DML lock.
It specifies that the database should not wait for a lock to be released.
Example
Let's look at an example of how to use the LOCK TABLE statement in Oracle.
For example:
LOCK TABLE suppliers IN SHARE MODE NOWAIT;This example would lock the suppliers table in SHARE MODE and not wait for a lock to be released.
