LOCK TABLE statement

Use the LOCK TABLE statement to control access to a table by other processes.

Syntax


Element	Description	Restrictions	Syntax
owner	Owner of synonym or table	Must be the owner of the specified object	Owner name
synonym	Synonym for the table to be locked	Synonym and the table to which it points must exist	Identifier
table	Table to be locked	See first paragraph of Usage.	Identifier

Usage

This statement is an extension to the ANSI/ISO standard for SQL.

You can use LOCK TABLE to lock a table if either of the following is true:

You are the owner of the table.
You have Select privilege on the table or on a column in the table, either from a direct grant or from a grant to PUBLIC or to your current role.

The LOCK TABLE statement fails if the table is already locked in EXCLUSIVE mode by another process, or if you request an EXCLUSIVE lock while another user has locked the same table in SHARE mode.

The SHARE keyword locks a table in shared mode. Shared mode gives other processes read access to the table but denies write access. Other processes cannot update or delete data if a table is locked in shared mode.

The EXCLUSIVE keyword locks a table in exclusive mode. This mode denies other processes both read and write access to the table. Exclusive-mode locking automatically occurs during the following statements:

ALTER FRAGMENT
ALTER INDEX
ALTER TABLE
CREATE INDEX
DROP INDEX
RENAME COLUMN
RENAME TABLE
START VIOLATIONS TABLE
STOP VIOLATIONS TABLE
TRUNCATE

The ONLINE keyword in some DDL operations

During certain ALTER FRAGMENT, DROP INDEX, and CREATE INDEX operations, including the ONLINE keyword can reduce the risk of run time errors when a concurrent session attempts to access the same table. For more information about the locking behavior of DDL statements that support the ONLINE keyword option, see these topics:

LOCK TABLE statement behavior on secondary servers

You can set an exclusive lock on a table from an updatable secondary server in a high-availability cluster. For exclusive mode locks requested from a secondary server, sessions can read the table but not update it. This behavior is similar to shared access mode on a secondary server; that is, when one session has an exclusive lock on a given table, no other session can obtain a shared or exclusive lock on that table.

On read-only secondary servers, the session issuing the LOCK TABLE statement does not lock the table and the database server does not return an error to the client.

Shared mode locks in a cluster behave the same as for a standalone server. After a LOCK TABLE statement runs successfully, users can read the table but cannot modify it until the lock is released.