Oracle LOCK TABLE语句

电玩女神 2023-10-07 13:17 20阅读 0赞

在Oracle中，LOCK TABLE语句可以用来锁定表、表分区或表子分区。本教程将和大家一起学习LOCK TABLE语句的详细用法。

### LOCK TABLE语法 ###

LOCK TABLE语句的语法是：

LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

**参数**

*  tables：用逗号分隔的表格列表。
 *  lock\_mode ：它是以下值之一：

<table> 
 <thead> 
  <tr> 
   <th>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;lock_mode</th> 
   <th>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>ROW SHARE</code></td> 
   <td>允许同时访问表，但阻止用户锁定整个表以进行独占访问。</td> 
  </tr> 
  <tr> 
   <td><code>ROW EXCLUSIVE</code></td> 
   <td>允许对表进行并发访问，但阻止用户以独占访问方式锁定整个表并以共享方式锁定表。</td> 
  </tr> 
  <tr> 
   <td><code>SHARE UPDATE</code></td> 
   <td>允许同时访问表，但阻止用户锁定整个表以进行独占访问。</td> 
  </tr> 
  <tr> 
   <td><code>SHARE</code></td> 
   <td>允许并发查询，但用户无法更新锁定的表。</td> 
  </tr> 
  <tr> 
   <td><code>SHARE ROW EXCLUSIVE</code></td> 
   <td>用户可以查看表中的记录，但是无法更新表或锁定<code>SHARE</code>表中的表。</td> 
  </tr> 
  <tr> 
   <td><code>EXCLUSIVE</code></td> 
   <td>允许查询锁定的表格，但不能进行其他活动。</td> 
  </tr> 
 </tbody> 
</table>

*  WAIT：它指定数据库将等待(达到指定整数的特定秒数)以获取DML锁定。
 *  NOWAIT：它指定数据库不应该等待释放锁。

### LOCK TABLE示例 ###

下面是一个如何在Oracle中使用LOCK TABLE语句的例子：

LOCK TABLE suppliers IN SHARE MODE NOWAIT;

这个例子会锁定suppliers表在共享模式，而不是等待锁定被释放。

## LOCK TABLE ##

Purpose

Use the `LOCK` `TABLE` statement to lock one or more tables, table partitions, or table 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:

["Referring to Objects in Remote Databases"][Referring to Objects in Remote Databases] for information on specifying database links

lockmode Clause

Specify one of the following modes:

ROW SHARE  `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 Database.

ROW EXCLUSIVE  `ROW` `EXCLUSIVE` is the same as `ROW` `SHARE`, but it also prohibits locking in `SHARE` mode. `ROW` `EXCLUSIVE` locks are automatically obtained when updating, inserting, or deleting.

SHARE UPDATE  See `ROW` `SHARE`.

SHARE `SHARE` permits concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVE  `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 from updating rows.

EXCLUSIVE  `EXCLUSIVE` permits queries on the locked table but prohibits any other activity on it.

NOWAIT

Specify `NOWAIT` if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.

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

Examples

Locking a Table: Example 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;

[Oracle Database Concepts]: https://docs.oracle.com/cd/B19306_01/server.102/b14220/toc.htm
[COMMIT]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4010.htm#i2060233
[ROLLBACK]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9021.htm#i2104635
[SAVEPOINT]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10001.htm#BABFIJGC
[Description of lock_table.gif follows]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9kb2NzLm9yYWNsZS5jb20vY2QvQjE5MzA2XzAxL3NlcnZlci4xMDIvYjE0MjAwL2ltZy9sb2NrX3RhYmxlLmdpZg
[Description of the illustration lock_table.gif]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/img_text/lock_table.htm
[Referring to Objects in Remote Databases]: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements009.htm#i27761