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

A comma-delimited list of tables.

lock_mode

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.

WAIT

It specifies that the database will wait (up to a certain number of seconds as specified by integer) to acquire a DML lock.

NOWAIT

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.