LOCK TABLE Statement
Manually lock one or more tables.
Syntax:
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 available.
Several tables can be locked with a single command - separate with commas
Eg LOCK TABLE table1, table2, table3 IN ROW EXCLUSIVE MODE;
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 NOWAIT - will place a ROW EXCLUSIVE 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
Related Commands:
COMMIT
DELETE
INSERT
ROLLBACK
SELECT
UPDATE
DBMS_LOCK
Related Views:
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
Tags: Statement