Oracle7 Server SQL Reference

LOCK TABLE

Purpose

To lock one or more tables in a specified mode. This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation.

Prerequisites

The table or view must be in your own schema or you must have LOCK ANY TABLE system privilege or you must have any object privilege on the table or view.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view or you must have READUP system privilege.

Syntax

Keywords and Parameters

schema

is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.

table view

is the name of the table to be locked. If you specify view, Oracle7 locks the view's base tables.

dblink

is a database link to a remote Oracle7 database where the table or view is located. For information on specifying database links, see the section, "Referring to Objects in Remote Databases," . You can only lock tables and views on a remote database if you are using Oracle7 with the distributed option. All tables locked by a LOCK TABLE statement must be on the same database.

If you omit dblink, Oracle7 assumes the table or view is on the local database.

lockmode

is one of the following:

ROW SHARE

ROW EXCLUSIVE

SHARE UPDATE

SHARE ROW EXCLUSIVE

EXCLUSIVE

NOWAIT

specifies that Oracle7 returns control to you immediately if the specified table is already locked by another user. In this case, Oracle7 returns a message indicating that the table is already locked by another user.

If you omit this clause, Oracle7 waits until the table is available, locks it, and returns control to you.

Usage Notes

Exclusive locks allow queries on the locked table but prohibit any other activity on it.

Share locks allow concurrent queries but prohibit updates to the locked table.

Row Share locks allow concurrent access to the locked table. They prohibit users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE.

Row Exclusive locks are the same as ROW SHARE locks, but also prohibit locking in SHARE mode. Row Exclusive locks are automatically obtained when updating, inserting, or deleting.

Share Row Exclusive locks are used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or updating rows.

Share Update locks are synonymous with ROW SHARE and included for compatibility with earlier versions of the Oracle7 RDBMS.

Some forms of locks can be placed on the same table at the same time, other locks only allow one lock per table. For example, multiple users can place SHARE locks on the same table at the same time, but only one user can place an EXCLUSIVE lock on a table at a time. For a complete description of the interaction of lock modes, see the "Data Concurrency" chapter of Oracle7 Server Concepts.

When you lock a table, you choose how other users can access it. A locked table remains locked until you either commit your transaction or roll it back entirely or to a savepoint before you locked the table.

A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers.

Example I

The following statement locks the EMP table in exclusive mode, but does not wait if another user already has locked the table:

LOCK TABLE emp

	IN EXCLUSIVE MODE

	NOWAIT

Example II

The following statement locks the remote ACCOUNTS table that is accessible through the database link BOSTON:

LOCK TABLE accounts@boston