Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
Use the LOCK
TABLE
statement to lock one or more tables (or table partitions or subpartitions) 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.
Some forms of locks can be placed on the same table at the same time. Other locks allow only one lock for a table.
A locked table remains locked until you either commit your transaction or roll it back, either 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.
See Also:
|
The table or view must be in your own schema or you must have the LOCK
ANY
TABLE
system privilege, or you must have any object privilege on the table or view.
lock_table::=
Specify the schema containing the table or view. If you omit schema
, then Oracle assumes the table or view is in your own schema.
Specify the name of the table to be locked.
If you specify view
, then Oracle locks the view's base tables.
If you specify PARTITION
(partition
) or SUBPARTITION
(subpartition
), then Oracle first acquires an implicit lock on the table. The table lock is the same as the lock you specify for partition
or subpartition
, with two exceptions:
SHARE
lock for the subpartition, then Oracle acquires an implicit ROW
SHARE
lock on the table.EXCLUSIVE
lock for the subpartition, then Oracle acquires an implicit ROW
EXCLUSIVE
lock on the table.If you specify PARTITION
and table
is composite-partitioned, then Oracle acquires locks on all the subpartitions of partition
.
If table
is part of a hierarchy, then it must be the root of the hierarchy.
Specify a database link to a remote Oracle database where the table or view is located. You can lock tables and views on a remote database only if you are using Oracle's distributed functionality. All tables locked by a LOCK
TABLE
statement must be on the same database.
If you omit dblink
, then Oracle assumes the table or view is on the local database.
See Also:
"Referring to Objects in Remote Databases" for information on specifying database links |
Specify one of the following modes:
ROW
SHARE
permits concurrent access to the locked table, but prohibits users from locking the entire table for exclusive access. ROW
SHARE
is synonymous with SHARE
UPDATE
, which is included for compatibility with earlier versions of Oracle.
ROW
EXCLUSIVE
is the same as ROW
SHARE
, but also prohibits locking in SHARE
mode. ROW
EXCLUSIVE
locks are automatically obtained when updating, inserting, or deleting.
See ROW
SHARE
.
SHARE
permits concurrent queries but prohibits updates to the locked table.
SHARE
ROW
EXCLUSIVE
is 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.
EXCLUSIVE
permits queries on the locked table but prohibits any other activity on it.
Specify NOWAIT
if you want Oracle to return control to you immediately if the specified table (or specified partition or subpartition) is already locked by another user. In this case, Oracle returns a message indicating that the table, partition, or subpartition is already locked by another user.
If you omit this clause, then Oracle waits until the table is available, locks it, and returns control to you.
The following statement locks the employees
table in exclusive mode, but does not wait if another user already has locked the table:
LOCK TABLE employees IN EXCLUSIVE MODE NOWAIT;
The following statement locks the remote employees
table that is accessible through the database link remote
:
LOCK TABLE employees@remote IN SHARE MODE;