LOCK TABLE Statement

最新推荐文章于 2021-09-09 13:45:15 发布

tianyacao007

最新推荐文章于 2021-09-09 13:45:15 发布

阅读量936

点赞数 2

分类专栏：数据库文章标签： table delete dictionary insert schema locking

数据库专栏收录该内容

18 篇文章 0 订阅

订阅专栏

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

e.g. 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: