LOCK TABLE Statement
Manually lock one or more tables.
LOCK TABLE [schema.] table [options] IN lockmode MODE [NOWAIT] LOCK TABLE [schema.] view [options] IN lockmode MODE [NOWAIT] options: PARTITION partition SUBPARTITION subpartition @dblink lockmodes: EXCLUSIVE SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE ROW SHARE* | SHARE UPDATE*
If NOWAIT is omitted Oracle will wait until the table is
Default Locking Behaviour
A pure SELECT will not lock any rows.
INSERT, UPDATE or DELETE's will place a ROW EXCLUSIVE lock.
SELECT...FROM...FOR UPDATE Will place a ROW SHARE lock.
Multiple Locks on the same rows with LOCK TABLE
Even when a row is locked you can always perform a SELECT (because SELECT does not lock any rows) in addition to this, each type of lock will allow additional locks to be granted as follows.
ROW SHARE = Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks to be granted to the locked rows.
ROW EXCLUSIVE = Allow ROW EXCLUSIVE or ROW SHARE locks to be granted to the locked rows.
SHARE ROW EXCLUSIVE = Allow ROW SHARE locks to be granted to the locked rows.
SHARE = Allow ROW SHARE or SHARE locks to be granted to the locked rows.
EXCLUSIVE = Allow SELECT queries only
Although it is valid to place more than one lock on a row, UPDATES and DELETE's may still cause a wait if a conflicting row lock is held by another transaction.
* = Oracle 6 option included for compatibility
DBA_ALL_TABLES ALL_ALL_TABLES USER_ALL_TABLES DBA_TABLES ALL_TABLES USER_TABLES TAB DICTIONARY DICT_COLUMNS V$LOCK V$_LOCK V$LOCKED_OBJECT V$LOCKS_WITH_COLLISIONS V$LOCK_ACTIVITY V$LOCK_ELEMENT