13 Data Concurrency and Consistency

001

This chapter explains how Oracle maintains consistent data in a multiuser database environment.
　

本章讲述 Oracle 如何在多用户数据库系统中保证数据一致性（consistent）。
　

002

This chapter contains the following topics:

Introduction to Data Concurrency and Consistency in a Multiuser Environment
How Oracle Manages Data Concurrency and Consistency
How Oracle Locks Data
Overview of Oracle Flashback Query

本章包含以下主题：

多用户环境下的数据并发访问及数据一致性简介
Oracle 如何管理数据并发访问及数据一致性
Oracle 如何锁数据
Oracle 回闪查询概述

003

Introduction to Data Concurrency and Consistency in a Multiuser Environment

13.1 多用户环境下的数据并发访问及数据一致性简介

004

In a single-user database, the user can modify data in the database without concern for other users modifying the same data at the same time. However, in a multiuser database, the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful and consistent results. Therefore, control of data concurrency and data consistency is vital in a multiuser database.

Data concurrency means that many users can access data at the same time.
Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

在只有单一用户的数据库中，用户可以任意修改数据，而无需考虑同时有其他用户正在修改相同的数据。但在一个多用户数据库中，多个并发事务中包含的语句可能会修改相同的数据。数据库中并发执行的事务最终应产生有意义且具备一致性的结果。因此在多用户数据库中，对数据并发访问（data concurrency）及数据一致性（data consistency）进行控制是两项极为重要的工作。

数据并发访问指多用户同时访问相同的数据。
数据一致性指系统中每个用户都能够取得具备一致性的数据，同时还能够看到自己或其他用户所提交的事务对数据的修改。

005

To describe consistent transaction behavior when transactions run at the same time, database researchers have defined a transaction isolation model called serializability. The serializable mode of transaction behavior tries to ensure that transactions run in such a way that they appear to be executed one at a time, or serially, rather than concurrently.
　

为了描述同时执行的多个事务如何实现数据一致性，数据库研究人员定义了被称为串行化处理（serializability）的事务隔离模型（transaction isolation model）。当所有事务都采取串行化的模式执行时，我们可以认为同一时间只有一个事务在运行（串行的），而非并发的。
　

006

While this degree of isolation between transactions is generally desirable, running many applications in this mode can seriously compromise application throughput. Complete isolation of concurrently running transactions could mean that one transaction cannot perform an insert into a table being queried by another transaction. In short, real-world considerations usually require a compromise between perfect transaction isolation and performance.
　

以串行化模式对事务进行隔离的效果很好，但在此种模式下应用程序的效率将大大降低。将并行执行的事务完全隔离意味着即便当前只存在一个对表进行查询（query）的事务，其他事务也不能再对此表进行插入（insert）操作了。总之，为了满足实际要求，我们需要在事务的隔离程度与应用的性能之间找出一个平衡点。
　

007

Oracle offers two isolation levels, providing application developers with operational modes that preserve consistency and provide high performance.
　

Oracle 支持两种事务隔离级别（isolation level），使应用程序开发者在对事务进行控制时，既能保证数据的一致性，又能获得良好的性能。
　

008

See Also:

Chapter 21, "Data Integrity" for information about data integrity, which enforces business rules associated with a database

另见：

第 21 章，“数据完整性” 了解数据完整性（data integrity）如何确保数据符合业务规则

009

Preventable Phenomena and Transaction Isolation Levels

13.1.1 需要防止的现象和事务隔离级别

010

The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput. These isolation levels are defined in terms of three phenomena that must be prevented between concurrently executing transactions.
　

ANSI/ISO SQL 标准（SQL92）定义了四种事务隔离级别（transaction isolation level），这四种隔离级别所能提供的事务处理能力各不相同。这些事务隔离级别是针对三种现象定义的，在并发事务执行时，需要阻止这三种现象中的一种或多种发生。
　

011

The three preventable phenomena are:

Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
Phantom reads (or phantoms): A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

三种需要阻止的现象（preventable phenomena）是：

脏读取（dirty read）：一个事务读取了被其他事务写入但还未提交的数据。
不可重复读取（nonrepeatable read）：一个事务再次读取其之前曾经读取过的数据时，发现数据已被其他已提交的事务修改或删除。
不存在读取（phantom read）：事务按照之前的条件重新查询时，返回的结果集中包含其他已提交事务插入的满足条件的新数据。

012

SQL92 defines four levels of isolation in terms of the phenomena a transaction running at a particular isolation level is permitted to experience. They are shown in Table 13-1:
　

SQL92 标准中定义了四个隔离级别，在各隔离级别中，允许发生上述三种需要阻止的现象中的一种或多种。详细情况见表13-1：
　

013

Table 13-1 Preventable Read Phenomena by Isolation Level
　

表13-1 各隔离级别中允许出现的需要防止的读取现象
　

014


Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read

Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible


现象	脏读取	不可重复读取	不存在读取
隔离级别	脏读取	不可重复读取	不存在读取

未提交读取（read uncommitted）	允许	允许	允许
已提交读取（read committed）	不允许	允许	允许
可重复读取（repeatable read）	不允许	不允许	允许
串行化（rerializable）	不允许	不允许	不允许

015

Oracle offers the read committed and serializable isolation levels, as well as a read-only mode that is not part of SQL92. Read committed is the default.
　

Oracle 支持三种事务隔离级别：已提交读取，串行化，以及 SQL92 中没有包含的只读模式（read-only mode）。已提交读取是 Oracle 默认使用的事务隔离级别。
　

016

See Also:

"How Oracle Manages Data Concurrency and Consistency" for a full discussion of read committed and serializable isolation levels

另见：

“Oracle 如何管理数据并发访问及数据一致性”详细了解已提交读取及串行化两种事务隔离级别

017

Overview of Locking Mechanisms

13.1.2 锁机制概述

018

In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.
　

通常，多用户数据库需要利用锁机制解决数据并发访问，数据一致性及完整性问题。锁（lock）是一种防止多个事务访问同一资源时产生破坏性的相互影响的机制。
　

019

Resources include two general types of objects:

User objects, such as tables and rows (structures and data)
System objects not visible to users, such as shared data structures in the memory and data dictionary rows

前面提到的资源（resource）大致可以分为两类：

用户对象，例如表及数据行（即数据结构及其中的数据）
对用户不可见的系统对象，例如内存中的共享数据结构（shared data structure）数据字典中的信息

020

See Also:

"How Oracle Locks Data" for more information about locks

另见：

“Oracle 如何锁数据”了解更多关于锁的信息

021

How Oracle Manages Data Concurrency and Consistency

13.2 Oracle 如何管理数据并发访问及数据一致性

022

Oracle maintains data consistency in a multiuser environment by using a multiversion consistency model and various types of locks and transactions. The following topics are discussed in this section:

Multiversion Concurrency Control
Statement-Level Read Consistency
Transaction-Level Read Consistency
Read Consistency with Real Application Clusters
Oracle Isolation Levels
Comparison of Read Committed and Serializable Isolation
Choice of Isolation Level

Oracle 利用多版本一致性模型（multiversion consistency model），各种类型的锁（lock）及事务（transaction）来管理多用户系统中的数据一致性（data consistency）。本节讨论以下主题：

多版本并发访问控制
语句级读一致性
事务级读一致性
RAC 环境下的读一致性
Oracle 事务隔离级别
已提交读取隔离与串行化隔离的区别
选择事务隔离级别

023

Multiversion Concurrency Control

13.2.1 多版本并发访问控制

024

Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). Oracle can also provide read consistency to all of the queries in a transaction (transaction-level read consistency).
　

Oracle 能够自动地实现一个查询的读一致性（read consistency），即一个查询所获得的数据来自同一时间点（single point in time）（这也被称为语句级读一致性（statement-level read consistency））。Oracle 还能令一个事务内的所有查询都具备读一致性（即事务级读一致性（transaction-level read consistency））。
　

025

Oracle uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Figure 13-1 shows how Oracle provides statement-level read consistency using data in rollback segments.
　

Oracle 利用回滚段（rollback segment）中的信息生成一个能保证一致性的数据视图。回滚段内保存了未提交或最近提交的事务中所修改数据的原值。图13-1 展示了 Oracle 如何利用回滚段实现语句级的读一致性。
　

026

Figure 13-1 Transactions and Read Consistency
　

图13-1 事务及读一致性

027

028

As a query enters the execution stage, the current system change number (SCN) is determined. In Figure 13-1, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.
　

在查询开始执行时，将记录当前的系统变化编号（system change number，SCN）。在图13-1 中，记录的系统变化编号为 10023。当查询进行扫描时，只会使用有效的（observed）数据块。如果某个数据块内的数据被修改过（即数据块的 SCN 晚于查询开始执行时记录的 SCN），Oracle 将使用回滚段中的信息重建此数据块，并以重建的数据块替代被修改的数据块供查询使用。因此，查询的结果集只包含查询开始执行时就已经提交的数据。在查询执行时，其他事务修改的数据对此查询来说是无效的，这保证了每个查询都能得到满足一致性的数据。
　

029

Statement-Level Read Consistency

13.2.2 语句级读一致性

030

Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.
　

Oracle 强制实现语句级读一致性（statement-level read consistency）。这保证了单一查询的结果集来自一个时间点——即查询开始执行的时间。因此，一个查询的结果集永远不会包含脏数据及此查询执行时其他事务提交的数据。在一个查询执行期间，只有在查询执行前提交的数据对此查询才是可见的。查询无法看到其开始执行后提交的数据。
　

031

A consistent result set is provided for every query, guaranteeing data consistency, with no action on the user's part. The SQL statements SELECT, INSERT with a subquery, UPDATE, and DELETE all query data, either explicitly or implicitly, and all return consistent data. Each of these statements uses a query to determine which data it will affect (SELECT, INSERT, UPDATE, or DELETE, respectively).
　

任何一个查询都能得到满足一致性的结果集，这保证了用户无需额外操作就能确保数据一致性。SELECT，使用子查询的 INSERT，及包含显式或隐式查询的 UPDATE 或 DELETE 语句，都能够保证数据一致性。上述语句通过一个查询（query）来得到她们所需的满足一致性的结果集（分别使用 SELECT，INSERT，UPDATE 或 DELETE 语句）。
　

032

A SELECT statement is an explicit query and can have nested queries or a join operation. An INSERT statement can use nested queries. UPDATE and DELETE statements can use WHERE clauses or subqueries to affect only some rows in a table rather than all rows.
　

SELECT 语句是一个显式地查询，且其中可以包含嵌套查询（nested query）或连接操作（join operation）。INSERT 语句中也能够使用嵌套查询。UPDATE 及 DELETE 语句能够利用 WHERE 子句或子查询进行限制，只操作数据表内的部分数据行。
　

033

Queries used in INSERT, UPDATE, and DELETE statements are guaranteed a consistent set of results. However, they do not see the changes made by the DML statement itself. In other words, the query in these operations sees data as it existed before the operation began to make changes.
　

INSERT，UPDATE，及 DELETE 语句中包含的查询能够获得一致性的结果集。这些查询无法看到其所在 DML 语句对数据的修改。换句话说，这些查询只能看到其所在 DML 语句开始之前的数据。
　

034

Note:

If a SELECT list contains a function, then the database applies statement-level read consistency at the statement level for SQL run within the PL/SQL function code, rather than at the parent SQL level. For example, a function could access a table whose data is changed and committed by another user. For each execution of the SELECT in the function, a new read consistent snapshot is established.

提示：

如果 SELECT 列表中存在 PL/SQL 函数，那么函数中包含的 SQL 语句将遵从其自身的语句级读一致性，而非其所在 SQL 的读一致性。例如，SELECT 语句中的某个函数访问的表可能会在语句执行时被其他事务修改并提交。此函数每次执行时都将建立一个新的一致性视图（snapshot）。

035

Transaction-Level Read Consistency

13.2.3 事务级读一致性

036

Oracle also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data accesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do see changes made by the transaction itself. Transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.
　

Oracle 还能够实现事务级读一致性（transaction-level read consistency）。当一个事务运行在串行化模式（serializable mode）下时，则事务内所有数据访问均反映的是事务开始时的数据状态。即事务内的所有查询对某个时间点来说具备一致性，但是运行在串行化模式下的事务能够看到事务自身对数据所作的修改。事务级的读一致性能够保证可重复读取（repeatable read）并可阻止出现不存在读取（phantom read）。
　

037

Read Consistency with Real Application Clusters

13.2.4 RAC 环境下的读一致性

038

Real Application Clusters (RAC) use a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of blocks from one instance to another. RAC does this using high speed, low latency interconnects to satisfy remote requests for data blocks.
　

RAC 系统采用缓存对缓存（cache-to-cache）的数据块传输机制（此技术被称为 Cache Fusion）在实例间传输满足读一致性（read-consistent）的数据块镜像。RAC 系统通过高速度低延迟的内部连接（interconnect）实现上述数据传输，从而满足实例之间对数据块的请求。
　

039

See Also:

Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

另见：

Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide

040

Oracle Isolation Levels

13.2.5 Oracle 事务隔离级别

041

Oracle provides these transaction isolation levels.
　

Oracle 支持以下三种事务隔离级别（transaction isolation level）。
　

042


Isolation Level	Description

Read committed	This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.
Serializable	Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.
Read-only	Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.


隔离级别	描述

已提交读取	Oracle 默认使用的事务隔离级别。事务内执行的查询只能看到查询执行前（而非事务开始前）就已经提交的数据。Oracle 的查询永远不会读取脏数据（未提交的数据）。 Oracle 不会阻止一个事务修改另一事务中的查询正在访问的数据，因此在一个事务内的两个查询的执行间歇期间，数据有可能被其他事务修改。举例来说，如果一个事务内同一查询执行两次，可能会遇到不可重复读取（nonrepeatable read）或不存在读取（phantom）的现象。
串行化	串行化隔离的事务只能看到事务执行前就已经提交的数据，以及事务内 INSERT，UPDATE，及 DELETE 语句对数据的修改。串行化隔离的事务不会出现不可重复读取或不存在读取的现象。
只读模式	只读事务只能看到事务执行前就已经提交的数据，且事务中不能执行 INSERT，UPDATE，及 DELETE 语句。

043

Set the Isolation Level

13.2.5.1 设置隔离级别

044

Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:
　

应用程序的设计开发者及数据库管理员可以依据应用程序的需求及系统负载（workload）而为不同的事务选择不同的隔离级别（isolation level）。用户可以在事务开始时使用以下语句设定事务的隔离级别：
　

045

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION READ ONLY;

046

To save the networking and processing cost of beginning each transaction with a SET TRANSACTION statement, you can use the ALTER SESSION statement to set the transaction isolation level for all subsequent transactions:
　

如果在每个事务开始时都使用 SET TRANSACTION 语句，将加重网络及处理器的负担。用户可以使用 ALTER SESSION 语句改变一个会话所有内事务的默认隔离级别：
　

047

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;

ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

048

See Also:

Oracle Database SQL Reference for detailed information on any of these SQL statements

另见：

Oracle Database SQL Reference 了解关于上述 SQL 语句的详细信息

049

Read Committed Isolation

13.2.5.2 已提交读取隔离

050

The default isolation level for Oracle is read committed. This degree of isolation is appropriate for environments where few transactions are likely to conflict. Oracle causes each query to run with respect to its own materialized view time, thereby permitting nonrepeatable reads and phantoms for multiple executions of a query, but providing higher potential throughput. Read committed isolation is the appropriate level of isolation for environments where few transactions are likely to conflict.
　

Oracle 默认使用的隔离级别（isolation level）是已提交读取（read committed）隔离。这种程度的隔离适合在事务发生冲突的可能性较小的系统中使用。在这种隔离级别下，Oracle 能够保证事务内每个查询在执行期间都拥有一个唯一的数据视图，因此事务可能出现不可重复读取（nonrepeatable read）或不存在读取（phantom）的现象，但此时系统的数据处理能力较高。因此已提交读取隔离（read committed isolation）适合在事务发生冲突的可能性较小的系统中使用。
　

051

Serializable Isolation

13.2.5.3 串行化隔离

052

Serializable isolation is suitable for environments:

With large databases and short transactions that update only a few rows
Where the chance that two concurrent transactions will modify the same rows is relatively low
Where relatively long-running transactions are primarily read only

符合以下特性的系统适合采用串行化隔离（serializable isolation）：

数据量大，但事务短小，只会更新较少数据行的数据库
两个并发事务修改相同数据的概率较小
运行时间相对较长的事务只执行只读操作

053

Serializable isolation permits concurrent transactions to make only those database changes they could have made if the transactions had been scheduled to run one after another. Specifically, Oracle permits a serializable transaction to modify a data row only if it can determine that prior changes to the row were made by transactions that had committed when the serializable transaction began.
　

在串行化隔离下，并发事务对数据库进行修改时只能顺序执行。具体来说，在串行化隔离下，Oracle 在允许一个采用串行化隔离的事务修改某些数据行时，需要判断在此事务开始执行之前，其他所有事务对这些数据行的修改已经被提交。
　

054

To make this determination efficiently, Oracle uses control information stored in the data block that indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE.
　

为了实现上述判断，Oracle 在数据块（data block）内存储了相关的控制信息，用于记录此块内数据行中所包含的数据是已提交或未提交的。即数据块内记录了近期对本数据块内数据行进行了修改的所有事务及事务的状态。在一个数据块内能够保留多少这样的记录是由 CREATE TABLE 或 ALTER TABLE 语句中的 INITRANS 参数设定的。
　

055

Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a too recent transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.
　

有些情况下，Oracle 无法获得足够的历史信息来判断某个数据行是否被一个事务修改过。当大量事务在短时间内并发地修改同一数据块就会出现以上情况。用户可以为可能被多个事务同时更新相同数据块的表设置较大的 INITRANS 值，以便避免上述情况。设置了较大的 INITRANS 值后，Oracle 就能为每个数据块分配足够的空间来记录访问此数据块的事务的信息。
　

056

Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:
　

当一个串行化事务试图更新或删除数据，而这些数据在此事务开始后被其他事务修改并进行了提交，Oracle 将报错：
　

057

ORA-08177: Cannot serialize access for this transaction

ORA-08177: 无法进行串行化访问

058

When a serializable transaction fails with the Cannot serialize access error, the application can take any of several actions:

Commit the work executed to that point
Execute additional (but different) statements (perhaps after rolling back to a savepoint established earlier in the transaction)
Undo the entire transaction

当一个串行化事务因为无法进行串行化访问（Cannot serialize access）错误而失败时，应用程序可以选择以下几种处理方式：

将错误发生之前的操作提交
执行其他操作（执行前可以回滚到事务内的某个保存点）
撤销整个事务

059

Figure 13-2 shows an example of an application that rolls back and retries the transaction after it fails with the Cannot serialize access error:
　

图13-2 显示了一个事务遇到无法进行串行化访问后，程序进行回滚并尝试重新执行此事务的例子：
　

060

Figure 13-2 Serializable Transaction Failure
　

图13-2 串行化事务失败
　

061

062

Figure 13-2 shows a serializable transaction consisting of a SELECT query that is repeated, followed by an UPDATE query. The second SELECT query sees the same data as the first, even though between the two SELECT queries someone else has changed the data. The UPDATE query fails if attempting to update a row changed and committed by another transaction since this transaction began. The "Cannot Serialize Access" error causes the transaction to roll back to the beginning and retry.

图13-2 显示了一个串行化事务，其中首先执行了两个相同的 SELECT 语句，接着执行了一个 UPDATE 语句。即便在两个 SELECT 执行之间有其他事务修改了相关数据，这两个 SELECT 也能够返回相同的结果。当 UPDATE 语句更新数据时，所更新的数据在此事务开始后被其他事务修改并提交过，Oracle 将报错 Cannot Serialize Access。这个错误将导致事务回滚并尝试重新执行。

063

Comparison of Read Committed and Serializable Isolation

13.2.6 已提交读取隔离与串行化隔离的区别

064

Oracle gives the application developer a choice of two transaction isolation levels with different characteristics. Both the read committed and serializable isolation levels provide a high degree of consistency and concurrency. Both levels provide the contention-reducing benefits of Oracle's read consistency multiversion concurrency control model and exclusive row-level locking implementation and are designed for real-world application deployment.
　

Oracle 为应用程序开发者提供了两种特性相异的事务隔离级别。已提交读取（read committed）隔离和串行化（serializable）隔离都能实现高度的数据一致性及并发访问能力。这两种隔离级别都能够利用 Oracle 的读一致性多版本并发访问控制模型及独有的行级锁（row-level locking）技术，从而减少并发事务间的竞争。应用程序开发者可以使用这两种隔离级别开发符合现实要求的应用系统。
　

065

Transaction Set Consistency

13.2.6.1 事务集数据一致性

066

A useful way to view the read committed and serializable isolation levels in Oracle is to consider the following scenario: Assume you have a collection of database tables (or any set of data), a particular sequence of reads of rows in those tables, and the set of transactions committed at any particular time. An operation (a query or a transaction) is transaction set consistent if all its reads return data written by the same set of committed transactions. An operation is not transaction set consistent if some reads reflect the changes of one set of transactions and other reads reflect changes made by other transactions. An operation that is not transaction set consistent in effect sees the database in a state that reflects no single set of committed transactions.
　

我们可以参考以下场景来研究 Oracle 中的两种隔离级别：假设现有一组数据库表（或称为一组数据集），一系列读取表数据的查询，以及一组在任意时间提交的事务。如果一个数据库操作（一个查询或一个事务）中所有读取返回的数据是由同一组已提交事务写入的，我们就称此操作满足事务集数据一致性（transaction set consistent）。相反，当一个数据库操作内的不同读取反映了不同事务集对数据的修改，此操作就不满足事务集数据一致性。换句话说，一个不满足事务集数据一致性的操作所看到的数据库的状态是由不同的已提交事务集决定的。
　

067

Oracle provides transactions executing in read committed mode with transaction set consistency for each statement. Serializable mode provides transaction set consistency for each transaction.
　

在已提交读取（read committed）隔离模式下，Oracle 能保证每个语句的事务集数据一致性。而在串行化（serializable）隔离模式下，Oracle 能保证每个事务的事务集数据一致性。
　

068

Table 13-2 summarizes key differences between read committed and serializable transactions in Oracle.
　

表13-2 总结了 Oracle 中已提交读取事务和串行化事务的关键区别。
　

069

Table 13-2 Read Committed and Serializable Transactions
　

表13-2 已提交读取事务和串行化事务
　

070


	Read Committed	Serializable

Dirty write	Not possible	Not possible
Dirty read	Not possible	Not possible
Nonrepeatable read	Possible	Not possible
Phantoms	Possible	Not possible
Compliant with ANSI/ISO SQL 92	Yes	Yes
Read materialized view time	Statement	Transaction
Transaction set consistency	Statement level	Transaction level
Row-level locking	Yes	Yes
Readers block writers	No	No
Writers block readers	No	No
Different-row writers block writers	No	No
Same-row writers block writers	Yes	Yes
Waits for blocking transaction	Yes	Yes
Subject to cannot serialize access	No	Yes
Error after blocking transaction terminates	No	No
Error after blocking transaction commits	No	Yes


	已提交读取	串行化

脏写入（dirty write）	不可能	不可能
脏读取（dirty read）	不可能	不可能
不可重复读取（nonrepeatable read）	可能	不可能
不存在读取（phantom）	可能	不可能
与 ANSI/ISO SQL 92 标准兼容	是	是
唯一的数据视图的使用范围	语句	事务
事务集数据一致性	语句级	事务级
行级锁	是	是
读操作（reader）阻塞写操作（writer）	否	否
写操作阻塞读操作	否	否
针对不同数据行的写操作是否相互阻塞	否	否
针对相同数据行的写操作是否相互阻塞	是	是
等待导致阻塞的事务（blocking transaction）	是	是
会出现无法进行串行化访问（Cannot serialize access）错误	否	是
在导致阻塞的事务结束后将发生错误	否	否
在导致阻塞的事务提交后将发生错误	否	是

[参考 051-062，尤其是 056，关于串行化隔离的特点；而与阻塞相关的条目可以参考 13.2.6.2 关于行级锁的介绍。]
　

071

Row-Level Locking

13.2.6.2 行级锁

072

Both read committed and serializable transactions use row-level locking, and both will wait if they try to change a row updated by an uncommitted concurrent transaction. The second transaction that tries to update a given row waits for the other transaction to commit or undo and release its lock. If that other transaction rolls back, the waiting transaction, regardless of its isolation mode, can proceed to change the previously locked row as if the other transaction had not existed.
　

在已提交读取隔离模式（read committed）及串行化隔离模式（serializable）下执行的事务都采用行级锁（row-level locking）技术，他们在更新被未提交的并发事务修改的数据行时都会发生等待--等待未提交的并发事务提交或撤销，并释放锁。如果未提交的并发事务进行了回滚，那么无论发生等待的事务运行在何种隔离模式下，都能修改之前被锁住的数据行，如同未提交的并发事务不存在一样。
　

073

However, if the other blocking transaction commits and releases its locks, a read committed transaction proceeds with its intended update. A serializable transaction, however, fails with the error Cannot serialize access error, because the other transaction has committed a change that was made since the serializable transaction began.
　

当导致阻塞的事务（blocking transaction）[前文中提到的未提交的并发事务]提交并释放了锁后，运行在已提交读写模式下的等待事务就能够继续执行其中的更新操作。而运行在串行化模式下的等待事务将出现无法进行串行化访问（Cannot serialize access）错误，因为阻塞事务在串行化等待事务开始后更新了后者所存取的数据。
　

074

Referential Integrity

13.2.6.3 引用完整性

075

Because Oracle does not use read locks in either read-consistent or serializable transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level cannot assume that the data they read will remain unchanged during the execution of the transaction even though such changes are not visible to the transaction. Database inconsistencies can result unless such application-level consistency checks are coded with this in mind, even when using serializable transactions.
　

无论在已提交读取隔离模式（read committed）还是串行化隔离模式（serializable）中，Oracle 都不会使用读取锁，即某一个事务读取的数据可能会被其他事务更新。在应用级（application level）进行数据库一致性检查的事务无法确定在其执行期间所读取的数据是否同时已被修改，因为所有修改对此事务来说是透明的。如果应用程序的代码逻辑需要进行应用级的一致性检查，即便采用串行化事务（serializable transaction），也不能避免数据不一致的问题。
　

076

See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information about referential integrity and serializable transactions

另见：

Oracle Database Application Developer's Guide - Fundamentals 了解关于引用完整性和串行化事务的详细信息

077

Note:

You can use both read committed and serializable transaction isolation levels with Real Application Clusters.

Note:

在 RAC 环境中，用户也可以选择已提交读取或串行化两种事务隔离级别。

078

Distributed Transactions

13.2.6.4 分布式事务

079

In a distributed database environment, a given transaction updates data in multiple physical databases protected by two-phase commit to ensure all nodes or none commit. In such an environment, all servers, whether Oracle or non-Oracle, that participate in a serializable transaction are required to support serializable isolation mode.
　

在分布式数据库系统中，一个更新多个物理数据库中数据的事务将采用两步提交机制（two-phase commit）以保证事务在所有节点上全部提交或全部回滚。在分布式数据库系统中，一个采用串行化隔离（serializable）的事务所涉及的所有 Oracle 及非 Oracle 数据库都必须支持串行化隔离模式。
　

080

If a serializable transaction tries to update data in a database managed by a server that does not support serializable transactions, the transaction receives an error. The transaction can undo and retry only when the remote server does support serializable transactions.
　

当一个串行化事务试图在不支持串行化隔离模式的数据库中更新数据时，此事务将发生错误。只有远程数据库支持串行化隔离模式时，事务发生错误后才能撤销并重试。
　

081

In contrast, read committed transactions can perform distributed transactions with servers that do not support serializable transactions.
　

与串行化隔离模式相反，采用已提交读取隔离模式（read committed）的事务在不支持串行化隔离模式的数据库中执行分布式事务。
　

082

See Also:

Oracle Database Administrator's Guide

另见：

Oracle Database Administrator's Guide

083

Choice of Isolation Level

13.2.7 选择事务隔离级别

084

Application designers and developers should choose an isolation level based on application performance and consistency needs as well as application coding requirements.
　

应用程序的设计、开发者应当根据应用程序的性能要求，数据一致性要求，以及应用程序的编码需求来决定选择何种隔离级别（isolation level）。
　

085

For environments with many concurrent users rapidly submitting transactions, designers must assess transaction performance requirements in terms of the expected transaction arrival rate and response time demands. Frequently, for high-performance environments, the choice of isolation levels involves a trade-off between consistency and concurrency.
　

对于存在大量并发用户快速地提交事务的系统来说，应用程序设计者应该从事务处理量及响应时间的角度评估事务处理的性能。通常来说，为一个对性能要求高的系统选择事务隔离级别时，需要在数据一致性及数据并发处理间进行平衡。
　

086

Application logic that checks database consistency must take into account the fact that reads do not block writes in either mode.
　

在应用层对数据库一致性进行检查的应用逻辑必须注意，在两种隔离模式下读操作都不会阻塞写操作。
　

087

Oracle isolation modes provide high levels of consistency, concurrency, and performance through the combination of row-level locking and Oracle's multiversion concurrency control system. Readers and writers do not block one another in Oracle. Therefore, while queries still see consistent data, both read committed and serializable isolation provide a high level of concurrency for high performance, without the need for reading uncommitted data.
　

在 Oracle 行级锁（row-level locking）及 Oracle 多版本并发访问控制系统（multiversion concurrency control system）的基础上，Oracle 的两种事务隔离模式能够实现高一致性，高并发性及高性能。在 Oracle 系统中，读写操作不会相互阻塞。因此，已提交读取（read committed）和串行化（serializable）两种隔离模式都能提供高度的并发访问能力，从而保证系统的高性能，同时查询无需读取未提交的数据。
　

088

Read Committed Isolation

13.2.7.1 已提交读取隔离

089

For many applications, read committed is the most appropriate isolation level. Read committed isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results due to phantoms and non-repeatable reads for some transactions.
　

对于大多数应用来说，已提交读取隔离（read committed）是最适合的事务隔离级别。已提交读取隔离能够最大限度地保证数据并发性，但在某些事务中可能会出现不可重复读取（non-repeatable read）或不存在读取（phantom），因此略微增加了出现数据不一致性的风险。
　

090

Many high-performance environments with high transaction arrival rates require more throughput and faster response times than can be achieved with serializable isolation. Other environments that supports users with a very low transaction arrival rate also face very low risk of incorrect results due to phantoms and nonrepeatable reads. Read committed isolation is suitable for both of these environments.
　

在对性能要求较高的系统中，为了应对较高的事务到来率（transaction arrival rate），系统需要提供更大的事务吞吐量和更快的响应速度，此时采用串行化隔离可能难以实现。还有一类系统，其事务到来率较低，出现不可重复读取或不存在读取的风险也较低。以上两种系统均适合采用已提交读取隔离。
　

091

Oracle read committed isolation provides transaction set consistency for every query. That is, every query sees data in a consistent state. Therefore, read committed isolation will suffice for many applications that might require a higher degree of isolation if run on other database management systems that do not use multiversion concurrency control.
　

Oracle 的已提交读取隔离能够确保所有查询的事务集数据一致性（transaction set consistent）。即查询获得的数据是处于一致性状态下的。因此在 Oracle 中已提交读取隔离能够满足大多数应用的要求。而在没有多版本并发访问控制的数据库管理系统中，开发者可能需要采用更高程度的隔离方式。
　

092

Read committed isolation mode does not require application logic to trap the Cannot serialize access error and loop back to restart a transaction. In most applications, few transactions have a functional need to issue the same query twice, so for many applications protection against phantoms and non-repeatable reads is not important. Therefore many developers choose read committed to avoid the need to write such error checking and retry code in each transaction.
　

在已提交读取隔离模式下，开发者不需要在应用逻辑中捕获无法进行串行化访问（Cannot serialize access）错误，也无需回滚并重新执行事务。在大多数应用程序中，几乎不会有在一个事务中执行同一查询多次的情况，因此在这些应用程序中，为防止出现不可重复读取或不存在读取而采取的保护措施并不重要。如果开发者选择已提交读取隔离，就能够省略在每个事务中加入错误检查及事务重做的代码。
　

093

Serializable Isolation

13.2.7.2 串行化隔离

094

Oracle's serializable isolation is suitable for environments where there is a relatively low chance that two concurrent transactions will modify the same rows and the long-running transactions are primarily read only. It is most suitable for environments with large databases and short transactions that update only a few rows.
　

Oracle 的串行化隔离（serializable isolation）适合于具备以下特点的系统：出现修改相同数据的事务的几率较小，且长时间执行的事务以只读操作为主。最适合采用串行化隔离的系统是大型数据库，且其中主要运行更新少量数据的短小事务。
　

095

Serializable isolation mode provides somewhat more consistency by protecting against phantoms and nonrepeatable reads and can be important where a read/write transaction runs a query more than once.
　

串行化隔离能够提供更好的数据一致性，她能阻止不可重复读取（nonrepeatable read）或不存在读取（phantom）的现象。当一个读或写事务中需要运行同一查询多次时，串行化隔离的作用更加明显。
　

096

Unlike other implementations of serializable isolation, which lock blocks for read as well as write, Oracle provides nonblocking queries and the fine granularity of row-level locking, both of which reduce read/write contention. For applications that experience mostly read/write contention, Oracle serializable isolation can provide significantly more throughput than other systems. Therefore, some applications might be suitable for serializable isolation on Oracle but not on other systems.
　

某些数据库管理系统在实现串行化隔离时，无论读写操作都要对整个数据块加锁。而 Oracle 则采用了无阻塞查询（nonblocking query）及低粒度的行级锁技术（row-level locking），减少了读写操作间的竞争。对于存在较多读写竞争的应用，Oracle 的串行化隔离与其他数据库管理系统相比能够大大地提高事务处理能力。因此，某些应用在 Oracle 中可以采用串行化隔离，而在其他数据库管理系统则未必可行。
　

097

All queries in an Oracle serializable transaction see the database as of a single point in time, so this isolation level is suitable where multiple consistent queries must be issued in a read/write transaction. A report-writing application that generates summary data and stores it in the database might use serializable mode because it provides the consistency that a READ ONLY transaction provides, but also allows INSERT, UPDATE, and DELETE.
　

运行在串行化隔离模式下的事务中的所有查询所获得的数据都来自同一时间点（single point in time），因此这种隔离级别适合于需要执行多个满足一致性的查询的事务。例如，汇总数据并将结果写入数据库的报表应用可以采用串行化隔离，因为串行化事务所提供的数据一致性与 READ ONLY 事务相同，但其中还可以执行 INSERT，UPDATE，和 DELETE 操作。
　

098

Note:

Transactions containing DML statements with subqueries should use serializable isolation to guarantee consistent read.

提示：

如果事务中存在使用了子查询的 DML 语句，应该使用串行化隔离来保证一致性的读取。

099

Coding serializable transactions requires extra work by the application developer to check for the Cannot serialize access error and to undo and retry the transaction. Similar extra coding is needed in other database management systems to manage deadlocks. For adherence to corporate standards or for applications that are run on multiple database management systems, it may be necessary to design transactions for serializable mode. Transactions that check for serializability failures and retry can be used with Oracle read committed mode, which does not generate serializability errors.
　

在实现串行化隔离事务时，应用开发者必须编码来捕获无法进行串行化访问（Cannot serialize access）错误，之后回滚并重做事务。在其他数据库管理系统中需要类似的代码来处理死锁（deadlock）。有时为了遵从已有系统的标准，或者当应用可能运行在多种数据库管理系统上时，应按照串行化隔离的要求来设计事务处理代码。具备检查并处理串行化错误功能的事务也可以运行在 Oracle 的已提交读取（read committed）隔离模式下，因为此种隔离模式下不会产生串行化错误。
　

100

Serializable mode is probably not the best choice in an environment with relatively long transactions that must update the same rows accessed by a high volume of short update transactions. Because a longer running transaction is unlikely to be the first to modify a given row, it will repeatedly need to roll back, wasting work. Note that a conventional read-locking, pessimistic implementation of serializable mode would not be suitable for this environment either, because long-running transactions—even read transactions—would block the progress of short update transactions and vice versa.
　

如果系统中存在长时间运行的写事务，且其所操作的数据同时还会被大量的小事务更新，则此类系统不应采用串行化模式。因为长事务所需更新的数据可能会被其他事务抢先更新，则长事务可能需要重复地回滚，浪费系统资源。需要注意的是，其他数据库管理系统所实现的串行化隔离（使用读取锁（read-locking））同样不适合上述情况，因为长事务（即便是只执行读取操作的事务）会和短小的写事务相互阻塞。
　

101

Application developers should take into account the cost of rolling back and retrying transactions when using serializable mode. As with read-locking systems, where deadlocks occur frequently, use of serializable mode requires rolling back the work done by terminated transactions and retrying them. In a high contention environment, this activity can use significant resources.
　

应用开发者在采用串行化隔离时应考虑回滚及重做事务所带来的开销。而在采用读取锁的数据库管理系统中，死锁会频繁出现，在这样的系统中采用串行化隔离，必须令因死锁而终止的事务回滚并重做。如果一个系统对数据访问的竞争较激烈，那么处理串行化错误将消耗大量资源。
　

102

In most environments, a transaction that restarts after receiving the Cannot serialize access error is unlikely to encounter a second conflict with another transaction. For this reason, it can help to run those statements most likely to contend with other transactions as early as possible in a serializable transaction. However, there is no guarantee that the transaction will complete successfully, so the application should be coded to limit the number of retries.
　

在大多数系统中，一个事务发生无法进行串行化访问（Cannot serialize access）错误后重做时再次与其他事务冲突的几率较小。基于上述原因，采用串行化隔离时，容易与其他事务产生竞争的语句应该在事务开始后尽早执行。但是我们始终无法保证事务能够成功执行，因此在应用程序中应该限制重做的次数。
　

103

Although Oracle serializable mode is compatible with SQL92 and offers many benefits compared with read-locking implementations, it does not provide semantics identical to such systems. Application designers must take into account the fact that reads in Oracle do not block writes as they do in other systems. Transactions that check for database consistency at the application level can require coding techniques such as the use of SELECT FOR UPDATE. This issue should be considered when applications using serializable mode are ported to Oracle from other environments.
　

尽管 Oracle 的串行化隔离模式与 SQL92 兼容，而且与采用读取锁的实现方式相比具备很多优点，但是 Oracle 串行化隔离所包含的语义与其他数据库管理系统不完全相同。应用开发者必须注意，与其他数据库管理系统不同，在 Oracle 中读操作不会阻塞写操作。在应用级（application level）对数据库一致性进项检查的事务需要使用 SELECT FOR UPDATE 之类的技巧才能避免错误。当从其他数据库管理系统向 Oracle 迁移应用时，尤其要注意上述问题。
　

104

Quiesce Database

13.2.7.3 静默数据库

105

You can put the system into quiesced state. The system is in quiesced state if there are no active sessions, other than SYS and SYSTEM. An active session is defined as a session that is currently inside a transaction, a query, a fetch or a PL/SQL procedure, or a session that is currently holding any shared resources (for example, enqueues--enqueues are shared memory structures that serialize access to database resources and are associated with a session or transaction). Database administrators are the only users who can proceed when the system is in quiesced state.
　

管理员可以将数据库置于静默状态（quiesced state）。静默状态是指数据库中只存在 SYS 和 SYSTEM 用户建立的活动会话（active session）。活动会话的定义是，其中正在执行事务（transaction），查询（query），数据提取（fetch），或 PL/SQL 过程的会话，或者是当前拥有某种共享资源（例如，队列（enqueue）--队列是一个共享的内存结构，她总是和事务或会话相关，用于串行化地访问数据库资源）的会话。当数据库处于静默状态后，只有数据库管理员才能继续操作数据库。
　

106

Database administrators can perform certain actions in the quiesced state that cannot be safely done when the system is not quiesced. These actions include:

Actions that might fail if there are concurrent user transactions or queries. For example, changing the schema of a database table will fail if a concurrent transaction is accessing the same table.
Actions whose intermediate effect could be detrimental to concurrent user transactions or queries. For example, suppose there is a big table T and a PL/SQL package that operates on it. You can split table T into two tables T1 and T2, and change the PL/SQL package to make it refer to the new tables T1 and T2, instead of the old table T.

When the database is in quiesced state, you can do the following:

CREATE TABLE T1 AS SELECT ... FROM T;
CREATE TABLE T2 AS SELECT ... FROM T;
DROP TABLE T;

You can then drop the old PL/SQL package and re-create it.

数据库管理员在静默状态下可以执行某些特殊的操作，而这类操作如果在非静默状态下执行可能存在不安全因素。这样的特殊操作包括：

如存在并发的用户事务或查询可能会出错的操作。例如，改变数据库表的所属方案时，如果有并发事务正在访问此表将会出错。
操作结果对并发的用户事务或查询有损害的操作。例如，假设现有一个大表 T，以及一个对此大表进行操作的 PL/SQL 包。在静默状态下，管理员可以将 T 分割为 T1 和 T2，并改写 PL/SQL 包，使其引用新表 T1 和 T2 来代替原有的 T 表。

当数据库处于静默状态时，管理员可以执行以下操作：

CREATE TABLE T1 AS SELECT ... FROM T;
CREATE TABLE T2 AS SELECT ... FROM T;
DROP TABLE T;

之后再移除旧 PL/SQL 包并重建。

107

For systems that must operate continuously, the ability to perform such actions without shutting down the database is critical.
　

对于必须持续运行的系统，无需关闭数据库就能执行某些特殊操作的功能十分必要。
　

108

The Database Resource Manager blocks all actions that were initiated by a user other than SYS or SYSTEM while the system is quiesced. Such actions are allowed to proceed when the system goes back to normal (unquiesced) state. Users do not get any additional error messages from the quiesced state.
　

当数据库静默后，数据库资源管理器（Database Resource Manager）将阻止 SYS 及 SYSTEM 之外的用户所提交的操作。当数据库恢复正常（非静默）状态后，用户操作可以继续进行。用户不会因为数据库进入了静默状态而得到额外的错误信息。
　

109

How a Database Is Quiesced

13.2.7.3.1 数据库如何进入静默模式

110

The database administrator uses the ALTER SYSTEM QUIESCE RESTRICTED statement to quiesce the database. Only users SYS and SYSTEM can issue the ALTER SYSTEM QUIESCE RESTRICTED statement. For all instances with the database open, issuing this statement has the following effect:

Oracle instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than SYS and SYSTEM) from becoming active. No user other than SYS and SYSTEM can start a new transaction, a new query, a new fetch, or a new PL/SQL operation.
Oracle waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or terminate). Oracle also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. If a query is carried out by multiple successive OCI fetches, Oracle does not wait for all fetches to finish. It waits for the current fetch to finish and then blocks the next fetch. Oracle also waits for all sessions (other than those of SYS or SYSTEM) that hold any shared resources (such as enqueues) to release those resources. After all these operations finish, Oracle places the database into quiesced state and finishes executing the QUIESCE RESTRICTED statement.
If an instance is running in shared server mode, Oracle instructs the Database Resource Manager to block logins (other than SYS or SYSTEM) on that instance. If an instance is running in non-shared-server mode, Oracle does not impose any restrictions on user logins in that instance.

数据库管理员使用 ALTER SYSTEM QUIESCE RESTRICTED 语句将数据库置为静默状态。只有 SYS 和 SYSTEM 能够提交 ALTER SYSTEM QUIESCE RESTRICTED 语句。当提交了上述语句后，将对数据库的所有实例产生以下影响：

Oracle 通过数据库资源管理器（Database Resource Manager）控制所有实例，阻止所有非活动（inactive）的会话（SYS 和 SYSTEM 的会话除外）恢复为活动（active）状态。SYS 及 SYSTEM 之外的用户不能执行新的事务（transaction），查询（query），数据提取操作（fetch）及 PL/SQL 过程。
Oracle 将等待所有实例中由 SYS 及 SYSTEM 之外的用户所提交的事务结束（提交或终止）。Oracle 还会等待所有实例中由 SYS 及 SYSTEM 之外的用户执行且不在事务内的查询（query），数据提取（fetch），PL/SQL 过程结束。如果一个查询是由多个连续的 OCI 数据提取操作执行的，Oracle 不会等待所有的数据提取全部结束。Oracle 只会等待当前的数据提取结束并阻塞之后的数据提取。Oracle 还会等待所有拥有共享资源的会话（SYS 及 SYSTEM 用户的资源除外）释放资源。上面提到的所有等待都结束之后，Oracle 可以将数据库置为静默状态，并结束 QUIESCE RESTRICTED 语句。
如果数据库的某个实例是在共享服务模式（shared server mode）下运行的，Oracle 通过数据库资源管理器（Database Resource Manager）阻止用户（SYS 及 SYSTEM 用户除外）登录到此实例。如果实例运行在专用服务模式（dedicated server mode）下，Oracle 不会对此实例的用户登录进行限制。

111

During the quiesced state, you cannot change the Resource Manager plan in any instance.
　

在数据库处于静默状态期间，管理员不能修改任何实例的资源管理器计划（Resource Manager plan）。
　

112

The ALTER SYSTEM UNQUIESCE statement puts all running instances back into normal mode, so that all blocked actions can proceed. An administrator can determine which sessions are blocking a quiesce from completing by querying the v$blocking_quiesce view.
　

管理员可以使用 ALTER SYSTEM UNQUIESCE 语句将所有实例恢复为正常状态，各实例中所有被阻塞的操作都得以继续执行。在将数据库置为静默状态的过程中，管理员可以通过 V$BLOCKING_QUIESCE 视图来查询系统中哪些会话正在阻碍数据库进入静默状态。
　

113

See Also:

Oracle Database SQL Reference

Oracle Database Administrator's Guide

另见：

Oracle Database SQL Reference

Oracle Database Administrator's Guide

114

How Oracle Locks Data

13.3 Oracle 如何锁数据

115

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource—either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows.
　

锁（lock）是防止访问相同资源（例如表或数据行等用户对象，或内存中的共享数据结构及数据字典等对用户不可见的系统对象）的事务产生破坏性交互的机制。
　

116

In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automatically uses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.
　

在任何情况下，Oracle 都能够自动地获得执行 SQL 语句所必须的所有锁，无需用户干预。Oracle 会尽可能地减少锁产生的影响，从而最大程度地保证数据的并发访问能力，并确保数据一致性及错误恢复。同时，Oracle 也支持用户手工加锁的操作。
　

117

See Also:

"Types of Locks"

另见：

“锁的类型”

118

Transactions and Data Concurrency

13.3.1 事务及数据并发访问

119

Oracle provides data concurrency and integrity between transactions using its locking mechanisms. Because the locking mechanisms of Oracle are tied closely to transaction control, application designers need only define transactions properly, and Oracle automatically manages locking.
　

Oracle 利用其锁机制（locking mechanism）来实现事务间的数据并发访问及数据一致性。由于 Oracle 的锁机制是与事务控制紧密相关的，因此应用开发者只需正确地定义事务，Oracle 就能自动地对所需的锁进行管理。
　

120

Keep in mind that Oracle locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements so that database users never need to lock any resource explicitly. Oracle's default locking mechanisms lock data at the lowest level of restrictiveness to guarantee data integrity while allowing the highest degree of data concurrency.
　

Oracle 的锁机制是由 Oracle 自动实现的，完全无需用户干预。任何 SQL 语句执行时 Oracle 都隐式地对 SQL 所需的锁进行管理，因此用户无需显式地对资源加锁。Oracle 默认采用的锁机制能尽可能地减小对数据访问的限制，在保证数据一致性的同时实现高度的数据并发性。
　

121

See Also:

"Explicit (Manual) Data Locking"

另见：

“显式（手工）数据加锁”

122

Modes of Locking

13.3.1.1 锁的模式

123

Oracle uses two modes of locking in a multiuser database:

Exclusive lock mode prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.
Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

在多用户的数据库系统中，Oracle 使用两种模式的锁：

排他锁（exclusive lock）模式：能够阻止共享被加锁的资源。对数据进行修改时必须获得此种模式的锁。第一个排他地对资源加锁的事物是唯一可以对此资源进行修改的事物，直至排他锁被释放。
共享锁（share lock）模式：依据操作类型有条件地允许共享被加锁的资源。对数据进行读取的多个用户可共享此数据，这些用户可以对资源加以共享锁，防止其他用户并发地修改此资源（对数据进行修改的用户需要排他锁）。多个事务可以对相同的资源加共享锁。

124

Lock Duration

13.3.1.2 锁的持续时间

125

All locks acquired by statements within a transaction are held for the duration of the transaction, preventing destructive interference including dirty reads, lost updates, and destructive DDL operations from concurrent transactions. The changes made by the SQL statements of one transaction become visible only to other transactions that start after the first transaction is committed.
　

事务内各语句获得的锁在事务执行期内有效，以防止事务间破坏性的相互干扰，例如：脏读取（dirty read），无效地更新（lost update），以及其他并发事务中具有破坏性的 DDL 操作。如果某个事务中的 SQL 语句对数据进行了修改，只有在此事务提交后开始的事务才能看到前者修改的结果。
　

126

Oracle releases all locks acquired by the statements within a transaction when you either commit or undo the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
　

当用户提交（commit）或撤销（undo）一个事务后，Oracle 将释放此事务内各个 SQL 语句获得的锁。当用户在事务内回滚到某个保存点（savepoint）后，Oracle 也会释放此保存点后获得的锁。只有当前没有等待被锁资源的事务才能获得可用资源的锁。等待事务不会对可用资源加锁而是继续等待，直至拥有其所等待资源的事务完成提交或回滚。
　

127

Data Lock Conversion Versus Lock Escalation

13.3.1.3 数据锁转换及锁升级

128

A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock conversion is required or performed.
　

事务拥有在此事务内被插入（insert），更新（update），删除（delete）的数据行的排他行级锁（exclusive row lock）。对于数据行来说，排他行级锁已经是限制程度最高的锁，因此无需再进行锁转换（lock conversion）。
　

129

Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as appropriate. For example, assume that a transaction uses a SELECT statement with the FOR UPDATE clause to lock rows of a table. As a result, it acquires the exclusive row locks and a row share table lock for the table. If the transaction later updates one or more of the locked rows, the row share table lock is automatically converted to a row exclusive table lock.
　

对于数据表，Oracle 能够自动地将限制程度较低的锁转化为限制程度更高的锁。例如，某个事务内使用了 SELECT ... FOR UPDATE 语句对数据行加锁。此时，事务获得了相关数据行上的排他行级锁，以及相关数据表上的行共享表级锁（row share table lock）。如果此事务将对某些数据行进行更新，那么之前获得的行共享表级锁将自动地转换为行排他表级锁（row exclusive table lock）。
　

130

Lock escalation occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). For example, if a single user locks many rows in a table, some databases automatically escalate the user's row locks to a single table. The number of locks is reduced, but the restrictiveness of what is being locked is increased.
　

锁升级（lock escalation）是指处于同一粒度级别（例如，数据行级）上的锁被数据库升级为更高粒度级别（例如，表）上的锁。例如，某个用户已经对某个表内的多行数据加锁，某些数据库管理系统会将用户的这些行级锁升级为一个单一的表级锁。此时系统内锁的数量减少了，对被锁资源的限制程度增加了。
　

131

Oracle never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Imagine the situation where the system is trying to escalate locks on behalf of transaction T1 but cannot because of the locks held by transaction T2. A deadlock is created if transaction T2 also requires lock escalation of the same data before it can proceed.
　

Oracle 数据库中不存在锁升级。锁升级将显著地增加发生死锁（deadlock）的可能性。例如，数据库试图将事务 T1 中对某资源的锁升级，同时事务 T2 也拥有此资源的锁。如果此时事务 T2 也需要将锁升级则将产生死锁。
　

132

See Also:

"Table Locks (TM)"

另见：

“表级锁（TM）”

133

Deadlocks

13.3.2 死锁

134

A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Figure 13-3 is a hypothetical illustration of two transactions in a deadlock.
　

当两个（或多个）用户互相等待被对方加锁的资源时就会发生死锁（deadlock）。死锁将导致相关的事务停止执行。图 13-3 演示了产生死锁的两个事务。
　

135

In Figure 13-3, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds without being terminated. However, each tries next to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.
　

如图 13-3 所示，在时间点 A，两个事务均获得了更新操作所需数据行上的锁，此时两事务均正常，能够继续执行。接下来，两个事务均要更新当前被对方加锁的数据。因此，在时间点 B 将发生死锁，因为此时两个事务都不能获得继续执行或终止所需的资源。无论两个事务等待多久，相互冲突的锁都无法被释放，所以此种情况被称为死锁。
　

136

Figure 13-3 Two Transactions in a Deadlock
　

图 13-3 产生死锁的两个事务

137

138

Deadlock Detection

13.3.2.1 检测死锁

139

Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes statement-level rollback. The statement rolled back is the one belonging to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.
　

Oracle 能自动地检测死锁的情况，回滚造成死锁的某个语句，以便释放冲突的行级锁，从而解决死锁问题。Oracle 将向执行了语句级回滚的事务返回一个错误信息。哪个事务检测出了死锁，哪个事务中的语句就将被回滚。通常，Oracle 通知某一事务后，此事务需要显示地执行回滚操作，但事务可以在等待之后重试被回滚的语句。
　

140

Note:

In distributed transactions, local deadlocks are detected by analyzing wait data, and global deadlocks are detected by a time out. Once detected, nondistributed and distributed deadlocks are handled by the database and application in the same way.

提示：

对于分布式事务，Oracle 通过分析被等待的数据来检测本地死锁（local deadlock），而全局死锁（global deadlock）则是根据超时（time out）来检测的。无论分布式或非分布式死锁，在数据库及应用程序中的处理方法都是相同的。

141

Deadlocks most often occur when transactions explicitly override the default locking of Oracle. Because Oracle itself does no lock escalation and does not use read locks for queries, but does use row-level locking (rather than page-level locking), deadlocks occur infrequently in Oracle.
　

通常，用户显式指定的锁方式覆盖（override）了 Oracle 默认的锁方式时更容易发生死锁。由于 Oracle 中不存在锁升级（lock escalation），也不会为查询使用读取锁（read lock），而只使用行级锁（row-level lock）（不是分页级锁（page-level lock）），因此在 Oracle 中死锁的现象较少见。
　

142

See Also:

"Explicit (Manual) Data Locking" for more information about manually acquiring locks

另见：

“显式（手工）数据加锁” 了解关于手工获得锁的详细信息

143

Avoid Deadlocks

13.3.2.2 避免死锁

144

Multitable deadlocks can usually be avoided if transactions accessing the same tables lock those tables in the same order, either through implicit or explicit locks. For example, all application developers might follow the rule that when both a master and detail table are updated, the master table is locked first and then the detail table. If such rules are properly designed and then followed in all applications, deadlocks are very unlikely to occur.
　

如果两个事务需要访问相同的一组表，那么在两个事务按相同的顺序对这组表加锁（显式或隐式地加锁）通常能避免多表死锁（multitable deadlock）。例如，如果系统中的一个主表及一个明细表都需要更新时，开发者应该遵从一定的规则，如先对主表加锁，再对明细表加锁。如果能够仔细设计类似的规则并严格执行，出现死锁的可能性是很低的。
　

145

When you know you will require a sequence of locks for one transaction, consider acquiring the most exclusive (least compatible) lock first.
　

如果开发者预先知道需要在同一事务内对一系列资源加锁，那么应考虑首先对排他性最高的资源加锁。
　

146

Types of Locks

13.3.3 锁的类型

147

Oracle automatically uses different types of locks to control concurrent access to data and to prevent destructive interaction between users. Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from doing something also requiring exclusive access to the same resource. The lock is released automatically when some event occurs so that the transaction no longer requires the resource.
　

Oracle 能够自动地选择不同类型的锁对数据并发访问进行控制，防止用户间破坏性的交互操作。Oracle 将自动地为事务进行锁管理，防止其他事务对需要排他访问的资源执行操作。当事务不再需要加锁的资源并触发某个事件后，锁能够被自动地释放。
　

148

Throughout its operation, Oracle automatically acquires different types of locks at different levels of restrictiveness depending on the resource being locked and the operation being performed.
　

在事务执行期间，Oracle 能够根据加锁的资源及需要执行的操作自动地决定锁的类型（types of lock）及对资源的限制级别（level of restrictiveness）。
　

149

Oracle locks fall into one of three general categories.
　

Oracle 的锁可以分为三大类。
　

150


Lock	Description

DML locks (data locks)	DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows.
DDL locks (dictionary locks)	DDL locks protect the structure of schema objects—for example, the definitions of tables and views.
Internal locks and latches	Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.


锁	描述

DML 锁（数据锁）	DML 的作用是保护数据。例如，表级锁（table lock）对整个表加锁，行级锁（row lock）则对选定的数据行加锁。
DDL 锁（数据字典锁）	DDL 锁的作用是保护方案对象的结构。例如，表及视图的定义。
内部锁（internal lock）及闩锁（latch）	内部锁及闩锁用于保护数据库的内部结构，例如，数据文件。内部锁及闩锁的管理完全由 Oracle 自动完成。

151

The following sections discuss DML locks, DDL locks, and internal locks.
　

以下各节将讨论 DML 锁，DDL 锁，闩锁及内部锁。
　

152

DML Locks

13.3.4 DML 锁

153

The purpose of a DML lock (data lock) is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. DML statements automatically acquire both table-level locks and row-level locks.
　

DML 锁（数据锁）的作用是保证被多用户并发访问的数据的一致性。DML 锁能够防止存在冲突的并发 DML 及 DDL 操作间出现破坏性的相互干扰。DML 语句能够自动地获得所需的表级锁（table-level lock）与行级锁（row-level lock）。
　

154

Note:

The acronym in parentheses after each type of lock or lock mode is the abbreviation used in the Locks Monitor of Enterprise Manager. Enterprise Manager might display TM for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

提示：

在后文中，每种锁及锁模式之后括号内的首字母缩写词，对应企业管理器（Enterprise Manager）锁监视器（Locks Monitor）中所使用的缩写。在企业管理器中，表级锁可能只会被显示为 TM，而不会显示表级锁的模式（如 RS 或 SRX）。

155

Row Locks (TX)

13.3.4.1 行级锁（TX）

156

Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.
　

行级锁（row-level lock）的作用是防止两个事务同时修改相同的数据行。当一个事务需要修改一行数据时，就需对此行数据加锁。
　

157

There is no limit to the number of row locks held by a statement or transaction, and Oracle does not escalate locks from the row level to a coarser granularity. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.
　

Oracle 对语句或事务所能获得的行级锁的数量没有限制，Oracle 也不会讲行级锁的粒度升级（lock escalation）。行级锁是粒度最精细的锁，因此行级锁能够提供最好的数据并发访问能力及数据处理能力。
　

158

The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:

Readers of data do not wait for writers of the same data rows.
Writers of data do not wait for readers of the same data rows unless SELECT ... FOR UPDATE is used, which specifically requests a lock for the reader.
Writers only wait for other writers if they attempt to update the same rows at the same time.

Oracle 同时支持多版本并发访问控制（multiversion concurrency control）及行级锁技术（row-level locking），因此用户只有在访问相同数据行时才会出现竞争，具体来说：

读取操作无需等待对相同数据行的写入操作。
写入操作无需等待对相同数据行的读取操作，除非读取操作使用了 SELECT ... FOR UPDATE 语句，此读取语句需要对数据加锁。
写入操作只需等待并发地且针对相同数据行的其他写入操作。

159

Note:

Readers of data may have to wait for writers of the same data blocks in some very special cases of pending distributed transactions.

提示：

读取操作可能会等待对相同数据块（data block）的写入操作，这种情况只会在出现挂起的分布式事务（pending distributed transaction）时偶尔出现。

160

A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
　

在执行下列语句时，事务需要获得被修改的每一数据行的排他行级锁（exclusive row lock）：INSERT，UPDATE，DELETE，及使用了 FOR UPDATE 子句的 SELECT 语句。
　

161

A modified row is always locked exclusively so that other transactions cannot modify the row until the transaction holding the lock is committed or rolled back. However, if the transaction dies due to instance failure, block-level recovery makes a row available before the entire transaction is recovered. Row locks are always acquired automatically by Oracle as a result of the statements listed previously.
　

在事务被提交或回滚前，此事务拥有在其内部被修改的所有数据行的排他锁，其他事务不能对这些数据行进行修改操作。但是，如果事务由于实例故障而终止，在整个事务被恢复前，数据块级的恢复将使数据块内数据行上的锁释放。执行前面提到的 4 种 SQL 语句时，Oracle 能自动地对行级锁进行管理。
　

162

If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
　

当事务获得了某些数据行上的行级锁时，此事务同时获得了数据行所属表上的表级锁（table lock）。表级锁能够防止系统中并发地执行有冲突的 DDL 操作，避免当前事务中的数据操作被并发地 DDL 操作影响。
　

163

See Also:

"DDL Locks"

另见：

“DDL 锁”

164

Table Locks (TM)

13.3.4.2 表级锁（TM）

165

Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation. When a DDL or DML statement is on a table, a table lock is acquired. Table locks do not affect concurrency of DML operations. For partitioned tables, table locks can be acquired at both the table and the subpartition level.
　

表级锁（table-level lock）的作用是对并发的 DDL 操作进行访问控制，例如防止在 DML 语句执行期间相关的表被移除。当用户对表执行 DDL 或 DML 操作时，将获取一个此表的表级锁。表级锁不会影响其他并发的 DML 操作。对于分区表来说，表级锁既可以针对整个表，也可以只针对某个分区。
　

166

A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. Any table lock prevents the acquisition of an exclusive DDL lock on the same table and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it.
　

当用户执行以下 DML 语句对表进行修改：INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE，或执行 LOCK TABLE 语句时，事务将获取一个表级锁。这些 DML 语句获取表级锁的目的有两个：首先保证自身对表的访问不受其他事务 DML 语句的干扰，其次阻止其他事务中和自身有冲突的 DDL 操作执行。任何类型的表级锁都将阻止对此表的排他 DDL 锁（exclusive DDL lock），从而阻止了必须具备排他 DDL 锁才能执行的 DDL 操作。例如，当一个未提交的事务拥有某个表上的锁时，此表就无法被修改定义或被移除。
　

167

A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held.
　

表级锁具有以下几种模式：行共享（row share，RS），行排他（row exclusive，RX），共享（share，S），共享行排他（share row exclusive，SRX），及排他（exclusive，X）。各种模式的表级锁具有的限制级别决定了其是否能与其他表级锁共处于同一数据表上。
　

168

Table 13-3 shows the table lock modes that statements acquire and operations that those locks permit and prohibit.
　

表 13-3 显示了各种语句所获得的表级锁的模式，以及此模式下被允许或禁止的操作。
　

169

Table 13-3 Summary of Table Locks
　

表 13-3 表级锁模式总结

170


		Lock Modes Permitted?
SQL Statement	Mode of Table Lock	RS	RX	S	SRX	X

SELECT...FROM table...	none	Y	Y	Y	Y	Y
INSERT INTO table ...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table ...	RX	Y*	Y*	N	N	N
SELECT ... FROM table FOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	N
LOCK TABLE table IN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE table IN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE table IN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE table IN EXCLUSIVE MODE	X	N	N	N	N	N


		否允许锁操作？
SQL 语句	表级锁模式	RS	RX	S	SRX	X

SELECT...FROM table...	无	Y	Y	Y	Y	Y
INSERT INTO table ...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table ...	RX	Y*	Y*	N	N	N
SELECT ... FROM table FOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	N
LOCK TABLE table IN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE table IN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE table IN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE table IN EXCLUSIVE MODE	X	N	N	N	N	N

171

RS: row share

RX: row exclusive

S: share

SRX: share row exclusive

X: exclusive

*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

RS: 行共享

RX: 行排他

S: 共享

SRX: 共享行排他

X: 排他

*Y，不与其他事务的行级锁冲突时才允许。否则将产生等待。

172

The following sections explain each mode of table lock, from least restrictive to most restrictive. They also describe the actions that cause the transaction to acquire a table lock in that mode and which actions are permitted and prohibited in other transactions by a lock in that mode.
　

以下各节将解释各种模式的表级锁，介绍的顺序按各模式的限制程度由低到高排列。文中还将讲述导致事务获得不同模式表级锁的数据库操作，以及不同模式表级锁对其他事务中操作的影响。
　

173

See Also:

"Explicit (Manual) Data Locking"

另见：

“显式（手工）对数据加锁”

174

Row Share Table Locks (RS)

13.3.4.2.1 行共享表级锁（RS）

175

A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is run:
　

行共享表级锁（row share table lock）（也称为 subshare table lock，SS）表明拥有此锁的事务已经锁定了表内的某些数据行，并有意对数据行进行更新操作。当执行以下 SQL 语句时将获得表上的行共享表级锁：
　

176

SELECT ... FROM table ... FOR UPDATE OF ... ;

LOCK TABLE table IN ROW SHARE MODE;

177

A row share table lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.
　

行共享模式的表级锁的限制程度最低，因而能够保证表的最大的并发访问能力。
　

178

Permitted Operations: A row share table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row exclusive table locks for the same table.
　

允许的操作：某个事务拥有了某个表的行共享表级锁后，其他事务依然可以并发地对相同数据表执行查询，插入，更新，删除操作，或对表内数据行加锁的操作。也就是说，其他事务同时也能获得相同表上的行共享，行排他，共享，及共享行排他模式的表级锁。
　

179

Prohibited Operations: A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only the following statement:
　

禁止的操作：某个事务拥有了某个表的行共享表级锁后，只会禁止其他事务通过以下语句排他对相同表进行写操作：
　

180

LOCK TABLE table IN EXCLUSIVE MODE;

181

Row Exclusive Table Locks (RX)

13.3.4.2.2 行排他表级锁（RX）

182

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:
　

行排他表级锁（row exclusive table lock）（也被称为 subexclusive table lock, SX）通常表明拥有此锁的事务已经对表内的某些数据行进行了更新操作。当事务使用以下语句修改数据表时将自动地获得行排他表级锁：
　

183

INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

184

A row exclusive table lock is slightly more restrictive than a row share table lock.
　

行排他表级锁比行共享表级锁（row share table lock）的限制程度略高。
　

185

Permitted Operations: A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.
　

允许的操作：某个事务拥有了某个表的行排他表级锁后，其他事务依然可以并发地对相同数据表执行查询，插入，更新，删除操作，或对表内数据行加锁的操作。即行排他表级锁允许其他多个事务同时获得相同表上的行共享表级锁或行排他表级锁。
　

186

Prohibited Operations: A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:
　

禁止的操作：某个事务拥有了某个表的行排他表级锁后，将禁止其他事务手工地对表加锁进行排他地读写操作。因此其他语句不能使用以下语句并发地对相同表加锁：
　

187

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

188

Share Table Locks (S)

13.3.4.2.3 共享表级锁（S）

189

A share table lock is acquired automatically for the table specified in the following statement:
　

使用以下语句能够获得表上的共享表级锁（share table lock）：
　

190

LOCK TABLE table IN SHARE MODE;

191

Permitted Operations: A share table lock held by a transaction allows other transactions only to query the table, to lock specific rows with SELECT ... FOR UPDATE, or to run LOCK TABLE ... IN SHARE MODE statements successfully. No updates are allowed by other transactions. Multiple transactions can hold share table locks for the same table concurrently. In this case, no transaction can update the table (even if a transaction holds row locks as the result of a SELECT statement with the FOR UPDATE clause). Therefore, a transaction that has a share table lock can update the table only if no other transactions also have a share table lock on the same table.
　

允许的操作：某个事务拥有了某个表的共享表级锁后，其他事务可以查询表，可以使用 SELECT ... FOR UPDATE 语句锁定选中的数据行，也能够成功执行 LOCK TABLE ... IN SHARE MODE 语句。但其他事务不能对表进行更新操作。多个事务可以并发地获得同一个表上的共享表级锁，在此种情况下任何事务都不能对表进行更新（即便事务通过 SELECT ... FOR UPDATE 语句获得了行级锁也不行）。因此，拥有共享表级锁的事务只有在此表上没有其他事务的共享表级锁时，才能对表进行更新操作。
　

192

Prohibited Operations: A share table lock held by a transaction prevents other transactions from modifying the same table and from executing the following statements:
　

禁止的操作：某个事务拥有了某个表的共享表级锁后，将禁止其他事务修改此表，同时禁止其他事务执行以下语句：
　

193

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

194

Share Row Exclusive Table Locks (SRX)

13.3.4.2.4 共享行排他表级锁（SRX）

195

A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows:
　

共享行排他表级锁（share row exclusive table lock）（也称为 share-subexclusive table lock，SSX）与共享表级锁（share table lock）相比限制更为严格。用户可以使用以下语句获得共享行排他表级锁：
　

196

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

197

Permitted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR UPDATE clause, but not to update the table.
　

允许的操作：同一时间只有一个事物能够获得表的共享行排他表级锁。某个事务拥有了某个表的共享行排他表级锁后，其他事务可以查询表，可以使用 SELECT ... FOR UPDATE 语句锁定选中的数据行，但不能对表进行更新操作。
　

198

Prohibited Operations: A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements:
　

禁止的操作：拥有共享行排他表级锁的事务将阻止其他事务获取行排他表级锁（row exclusive table lock）来修改数据。共享行排他表级锁还能阻止其他事务在相同表上获取共享表级锁（share table lock），共享行排他表级锁（share row exclusive table lock），及排他表级锁（exclusive table lock）。概括来说，其他事务不能执行以下语句。
　

199

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

200

Exclusive Table Locks (X)

13.3.4.2.5 排他表级锁（X）

201

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
　

排他表级锁（exclusive table lock）是限制程度最高的表级锁，她能使获得此锁的事务排他地对表进行写操作。排他表级锁可以使用以下语句获得：
　

202

LOCK TABLE table IN EXCLUSIVE MODE;

203

Permitted Operations: Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
　

允许的操作：同一时间只有一个事务能获得表上的排他表级锁。一个事务获得排他表级锁后，其他事务只能对表进行查询操作。
　

204

Prohibited Operations: An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.
　

禁止的操作：一个事务获得排他表级锁后，将禁止其他事务对表执行任何 DML 操作，其他事务也无法获取表上任何类型的锁。
　

205

DML Locks Automatically Acquired for DML Statements

13.3.4.3 执行 DML 语句时自动获得的 DML 锁

206

The previous sections explained the different types of data locks, the modes in which they can be held, when they can be obtained, when they are obtained, and what they prohibit. The following sections summarize how Oracle automatically locks data on behalf of different DML operations.
　

前两小节讲述了不同类型的 DML 锁，锁的不同模式，何时需要获得锁，何时锁可以被获得，锁禁止何种操作。本节将讲述执行不同的 DML 语句时，Oracle 如何自动地对数据加锁。
　

207

Table 13-4 summarizes the information in the following sections.
　

表 13-4 是对本小节的一个总结。
　

208

Table 13-4 Locks Obtained By DML Statements
　

表 13-4 DML 语句获取的锁

209


DML Statement	Row Locks?	Mode of Table Lock

SELECT ... FROM table
INSERT INTO table ...	X	RX
UPDATE table ...	X	RX
DELETE FROM table ...	X	RX
SELECT ... FROM table ... FOR UPDATE OF ...	X	RS
LOCK TABLE table IN ...
ROW SHARE MODE		RS
ROW EXCLUSIVE MODE		RX
SHARE MODE		S
SHARE EXCLUSIVE MODE		SRX
EXCLUSIVE MODE		X


DML 语句	是否存在行级锁	表级锁的模式

SELECT ... FROM table
INSERT INTO table ...	X	RX
UPDATE table ...	X	RX
DELETE FROM table ...	X	RX
SELECT ... FROM table ... FOR UPDATE OF ...	X	RS
LOCK TABLE table IN ...
ROW SHARE MODE		RS
ROW EXCLUSIVE MODE		RX
SHARE MODE		S
SHARE EXCLUSIVE MODE		SRX
EXCLUSIVE MODE		X

210

X: exclusive

RX: row exclusive

RS: row share

S: share

SRX: share row exclusive

RS: 行共享

RX: 行排他

S: 共享

SRX: 共享行排他

X: 排他

211

Default Locking for Queries

13.3.4.3.1 查询操作默认获取的锁

212

Queries are the SQL statements least likely to interfere with other SQL statements because they only read data. INSERT, UPDATE, and DELETE statements can have implicit queries as part of the statement. Queries include the following kinds of statements:
　

执行查询（query）的 SQL 语句不易与其他 SQL 语句冲突，因为查询只需读取数据。除了 SELECT 之外，INSERT，UPDATE，及 DELETE 语句中也可能包含隐式的查询。因此，以下语句都属于查询操作：
　

213

SELECT

INSERT ... SELECT ... ;

UPDATE ... ;

DELETE ... ;

214

They do not include the following statement:
　

但是以下语句不属于查询操作：
　

215

SELECT ... FOR UPDATE OF ... ;

216

The following characteristics are true of all queries that do not use the FOR UPDATE clause:

A query acquires no data locks. Therefore, other transactions can query and update a table being queried, including the specific rows being queried. Because queries lacking FOR UPDATE clauses do not acquire any data locks to block other operations, such queries are often referred to in Oracle as nonblocking queries.
A query does not have to wait for any data locks to be released; it can always proceed. (Queries may have to wait for data locks in some very specific cases of pending distributed transactions.)

查询操作具备以下特性：

查询无需获取数据锁。因此当某事务查询数据表时，其他事务可以并发地查询、更新同一个表，包括此表中正在被查询的数据行。没有使用 FOR UPDATE 子句的 SELECT 语句无需获取任何数据锁，因此也不会阻塞任何操作，此类查询在 Oracle 中被称为非阻塞查询（nonblocking query）。
执行查询也不受数据锁的限制。（在某些特殊情况下，查询需要等待挂起的分布式事务所拥有的数据锁）

217

Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE

13.3.4.3.2 INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE 语句默认获取的锁

218

The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:

The transaction that contains a DML statement acquires exclusive row locks on the rows modified by the statement. Other transactions cannot update or delete the locked rows until the locking transaction either commits or rolls back.
The transaction that contains a DML statement does not need to acquire row locks on any rows selected by a subquery or an implicit query, such as a query in a WHERE clause. A subquery or implicit query in a DML statement is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.
A query in a transaction can see the changes made by previous DML statements in the same transaction, but cannot see the changes of other transactions begun after its own transaction.
In addition to the necessary exclusive row locks, a transaction that contains a DML statement acquires at least a row exclusive table lock on the table that contains the affected rows. If the containing transaction already holds a share, share row exclusive, or exclusive table lock for that table, the row exclusive table lock is not acquired. If the containing transaction already holds a row share table lock, Oracle automatically converts this lock to a row exclusive table lock.

INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE 语句默认获取的锁有以下特点：

包含 DML 语句的事务需要获得被其修改的数据行上的排他行级锁（exclusive row lock）。在拥有锁的事务提交或回滚前，其他事务不能更新或删除被加锁的数据行。
事务无需获取 DML 语句内的子查询（subquery）或隐式查询（implicit query）（例如 WHERE 子句内的查询）所选择的行上的行级锁。DML 内的子查询或隐式查询获得的数据相对查询开始的时间点满足一致性，这些查询不会看到 DML 语句自身对数据的影响。
事务内的查询能够看到本事务内之前执行的 DML 语句对数据的修改，但无法看到本事务开始后执行的其他事务对数据的修改。
事务内的 DML 语句除了需要获得必要的排他行级锁（exclusive row lock）外，至少还需获得包含被修改数据行的表上的行排他表级锁（row exclusive table lock）。如果事务已经获得了相关表上的共享表级锁（share），共享行排他表级锁（share row exclusive），或排他表级锁（exclusive），那么就无需获取行排他表级锁了。如果事务已经获得了相关表上的行共享表级锁（row share table lock），Oracle 将自动地将此锁转换为行排他表级锁。

219

DDL Locks

13.3.5 DDL 锁

220

A data dictionary lock (DDL) protects the definition of a schema object while that object is acted upon or referred to by an ongoing DDL operation. Recall that a DDL statement implicitly commits its transaction. For example, assume that a user creates a procedure. On behalf of the user's single-statement transaction, Oracle automatically acquires DDL locks for all schema objects referenced in the procedure definition. The DDL locks prevent objects referenced in the procedure from being altered or dropped before the procedure compilation is complete.
　

数据字典锁（data dictionary lock，DDL）的作用是在执行 DDL 操作时对被修改的方案对象或其引用对象的定义进行保护。管理员及开发者应该意识到 DDL 语句将会隐式地提交一个事务。例如，用户创建一个存储过程时，相当于执行一个只包含一条 SQL 语句的事务，Oracle 会自动获取过程定义中所引用的所有方案对象的 DDL 锁。DDL 锁能够防止编译期间过程所引用的对象被其他事务修改或移除。
　

221

Oracle acquires a dictionary lock automatically on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. Only individual schema objects that are modified or referenced are locked during DDL operations. The whole data dictionary is never locked.
　

当 DDL 事务需要时 Oracle 将自动地为其获取数据字典锁。用户不能显示地获取 DDL 锁。只有在 DDL 操作中被修改或引用的对象才会被加锁，整个数据字典不会被加锁。
　

222

DDL locks fall into three categories: exclusive DDL locks, share DDL locks, and breakable parse locks.
　

DDL 锁可以分为三类：排他 DDL 锁（exclusive DDL lock），共享 DDL 锁（share DDL lock），及可解除的解析锁（breakable parse lock）。
　

223

Exclusive DDL Locks

13.3.5.1 排他 DDL 锁

224

Most DDL operations, except for those listed in the section "Share DDL Locks", require exclusive DDL locks for a resource to prevent destructive interference with other DDL operations that might modify or reference the same schema object. For example, a DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa.
　

除了在“共享 DDL 锁”一节列出的操作外，大部分 DDL 操作都需要获得相关资源上的排他 DDL 锁（exclusive DDL lock），从而防止其他可能修改或引用相同方案对象的 DDL 操作带来的破坏性的冲突。例如，当 ALTER TABLE 操作为表添加数据列时，不允许 DROP TABLE 操作将此表移除，反之亦然。
　

225

During the acquisition of an exclusive DDL lock, if another DDL lock is already held on the schema object by another operation, the acquisition waits until the older DDL lock is released and then proceeds.
　

在获取 DDL 锁的过程中，如果其他操作已经获取了相关方案对象上的 DDL 锁，则获取 DDL 锁的操作只有在已有的 DDL 锁被释放后能继续执行。
　

226

DDL operations also acquire DML locks (data locks) on the schema object to be modified.
　

DDL 操作还需要获取被修改方案对象上的 DML 锁（数据锁）。
　

227

Share DDL Locks

13.3.5.2 共享 DDL 锁

228

Some DDL operations require share DDL locks for a resource to prevent destructive interference with conflicting DDL operations, but allow data concurrency for similar DDL operations. For example, when a CREATE PROCEDURE statement is run, the containing transaction acquires share DDL locks for all referenced tables. Other transactions can concurrently create procedures that reference the same tables and therefore acquire concurrent share DDL locks on the same tables, but no transaction can acquire an exclusive DDL lock on any referenced table. No transaction can alter or drop a referenced table. As a result, a transaction that holds a share DDL lock is guaranteed that the definition of the referenced schema object will remain constant for the duration of the transaction.
　

某些 DDL 操作需要获取相关资源上的共享 DDL 锁（share DDL lock）以防止与之冲突的 DDL 操作造成破坏性的干扰，但与之类似的 DDL 操作可以并发地访问数据，不受共享 DDL 锁的限制。例如，执行 CREATE PROCEDURE 语句时，事务将获取所有引用对象上的共享 DDL 锁。此时，其他事务可以并发地获取相同表上的共享 DDL 锁并创建引用了相同表的过程。但任何事务都无法获取被引用表上的排他 DDL 锁（exclusive DDL lock），即任何事务都无法对表进行修改或移除操作。因此获得了共享 DDL 锁的事务能够保证在其执行期间，所有引用对象的定义不会被修改。
　

229

A share DDL lock is acquired on a schema object for DDL statements that include the following statements: AUDIT, NOAUDIT, COMMENT, CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER, CREATE SYNONYM, and CREATE TABLE (when the CLUSTER parameter is not included).
　

执行以下 DDL 语句时，需要获取引用对象上的共享 DDL 锁：AUDIT，NOAUDIT，COMMENT，CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER，CREATE SYNONYM，及 CREATE TABLE（没有使用 CLUSTER 参数时）。
　

230

Breakable Parse Locks

13.3.5.3 可解除的解析锁

231

A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.
　

位于共享池（shared pool）内的 SQL 语句（或 PL/SQL 程序结构）拥有其引用的所有方案对象上的解析锁（parse lock）。解析锁的作用是，当共享 SQL 区（shared SQL area）所引用的对象被修改或移除后，此共享 SQL 区能够被置为无效。解析锁不会禁止任何 DDL 操作，当出现与解析锁冲突的 DDL 操作时，解析锁将被解除，因此也称之为可解除的解析锁。
　

232

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.
　

解析锁是在 SQL 语句执行的解析阶段（parse phase）获得的，在共享 SQL 区被清除出共享池（shared pool）前一直保持。
　

233

See Also:

Chapter 6, "Dependencies Among Schema Objects"

另见：

第 6 章，“方案对象间的依赖性”

234

Duration of DDL Locks

13.3.5.4 DDL 锁的持续时间

235

The duration of a DDL lock depends on its type. Exclusive and share DDL locks last for the duration of DDL statement execution and automatic commit. A parse lock persists as long as the associated SQL statement remains in the shared pool.
　

DDL 锁的持续时间取决于其类型。共享 DDL 锁（share DDL lock）及排他 DDL 锁（exclusive DDL lock）在 DDL 语句执行期间一直存在，在 DDL 语句自动提交后释放。而解析锁一直存在，直至相关的共享 SQL 区从共享池中被清除。
　

236

DDL Locks and Clusters

13.3.5.5 DDL 锁与簇

237

A DDL operation on a cluster acquires exclusive DDL locks on the cluster and on all tables and materialized views in the cluster. A DDL operation on a table or materialized view in a cluster acquires a share lock on the cluster, in addition to a share or exclusive DDL lock on the table or materialized view. The share DDL lock on the cluster prevents another operation from dropping the cluster while the first operation proceeds.
　

对簇（cluster）执行的 DDL 操作需要获取簇及簇内所有表及物化视图上的排他 DDL 锁（exclusive DDL lock）。对簇内表及物化视图的 DDL 操作需要获取簇上的共享 DDL 锁（share DDL lock），以及表或物化视图上的共享 DDL 锁或排他 DDL 锁。簇上的共享 DDL 锁能够防止操作期间其他 DDL 操作将簇移除。
　

238

Latches and Internal Locks

13.3.6 闩锁及内部锁

239

Latches and internal locks protect internal database and memory structures. Both are inaccessible to users, because users have no need to control over their occurrence or duration. The following section helps to interpret the Enterprise Manager LOCKS and LATCHES monitors.
　

闩锁（latche）及内部锁（internal lock）的作用是保护数据库及其内存结构。这两种锁对用户均不可见，因为用户无需对数据库内部资源的使用进行管理。以下各节有助于用户理解企业管理器（Enterprise Manager）的 LOCKS 及 LATCHES 监视器。
　

240

Latches

13.3.6.1 闩锁

241

Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA). For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.
　

闩锁（latche）是一种简单的底层串行化机制，用于保护 SGA 内的共享数据结构。例如，用于记录当前正在访问数据库的用户的列表，或用于记录位于数据库缓存（buffer cache）内的数据块的数据结构，都可通过闩锁进行保护。当服务进程（background process）或后台进程（server process）需要操作或查询此类数据结构时，就需要获取一个闩锁，但其加锁时间极短。栓锁的实现与操作系统有关，例如进程是否需要等待栓锁以及等待多长时间等。
　

242

Internal Locks

13.3.6.2 内部锁

243

Internal locks are higher-level, more complex mechanisms than latches and serve a variety of purposes.
　

与栓锁（latch）相比，内部锁（internal lock）是一种更高层且更复杂的锁机制，她具有多种用途。
　

244

Dictionary Cache Locks

13.3.6.2.1 数据字典缓存锁

245

These locks are of very short duration and are held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.
　

当用户更新或使用时数据字典缓存内的条目（entry）时，需要获取条目上的数据字典缓存锁（dictionary cache lock），此类锁的持续时间极短。此类锁的作用是确保正在被解析的语句不会看到不一致的对象定义。
　

246

Dictionary cache locks can be shared or exclusive. Shared locks are released when the parse is complete. Exclusive locks are released when the DDL operation is complete.
　

数据字典缓存锁可以为共享或排他的。当语句解析结束时共享锁将被释放，而当 DDL 操作结束时排它锁将被释放。
　

247

File and Log Management Locks

13.3.6.2.2 文件及重做日志管理锁

248

These locks protect various files. For example, one lock protects the control file so that only one process at a time can change it. Another lock coordinates the use and archiving of the redo log files. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode. Because file and log locks indicate the status of files, these locks are necessarily held for a long time.
　

此类内部锁（internal lock）用于保护各种文件。例如，保护控制文件（control file）的锁，确保同一时间只有一个进程能够对其进行修改。还有协调重做日志文件（redo log file）使用与归档的锁。以及数据文件（datafile）锁，实现多实例在共享模式下挂载数据库，或一个实例在排他模式下挂载数据库。由于文件及重做日志锁反映的是物理文件的状态，因此此类锁的持续时间较长。
　

249

Tablespace and Rollback Segment Locks

13.3.6.2.3 表空间及回滚段锁

250

These locks protect tablespaces and rollback segments. For example, all instances accessing a database must agree on whether a tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.
　

此类锁用于保护表空间及回滚段（rollback segment）。例如，一个表空间处于联机（online）还是脱机（offline）状态对访问同一数据库的所有实例应该是一致的。回滚段上的锁保证同一时间只有一个实例能够对其执行写操作。
　

251

Explicit (Manual) Data Locking

13.3.7 显式（手工）对数据加锁

252

Oracle always performs locking automatically to ensure data concurrency, data integrity, and statement-level read consistency. However, you can override the Oracle default locking mechanisms. Overriding the default locking is useful in situations such as these:

Applications require transaction-level read consistency or repeatable reads. In other words, queries in them must produce consistent data for the duration of the transaction, not reflecting changes by other transactions. You can achieve transaction-level read consistency by using explicit locking, read-only transactions, serializable transactions, or by overriding default locking.
Applications require that a transaction have exclusive access to a resource so that the transaction does not have to wait for other transactions to complete.

Oracle 能够自动地管理锁，从而确保数据库的并发访问，数据完整性，及语句级的读一致性。但是，用户可以覆盖（override）Oracle 的默认锁机制。以下情况可以采用手工锁覆盖默认的锁：

应用需要事务级的读一致性（read consistency）（即可重复读取（repeatable read））。换句话说，在整个事务内所有查询都要返回一致的结果，不受其他事务的影响。用户可以显示地对数据加锁，使用只读事务（read-only transaction）或串行化事务（serializable transaction），或覆盖默认的锁，从而实现事务级的读一致性。
应用要求事务必须排他地访问某种资源，而不应等待其他事务。

253

Oracle's automatic locking can be overridden at the transaction level or the session level.
　

用户可以在事务级或语句级覆盖 Oracle 默认采用的锁。
　

254

At the transaction level, transactions that include the following SQL statements override Oracle's default locking:

The SET TRANSACTION ISOLATION LEVEL statement
The LOCK TABLE statement (which locks either a table or, when used with views, the underlying base tables)
The SELECT ... FOR UPDATE statement

在事务级，包含以下 SQL 语句的事务将覆盖 Oracle 的默认锁：

SET TRANSACTION ISOLATION LEVEL 语句
LOCK TABLE 语句（可以对表加锁，对视图使用时，实际将对底层的表加锁）
SELECT ... FOR UPDATE 语句

255

Locks acquired by these statements are released after the transaction commits or rolls back.
　

上述语句获得的锁将在事务提交或回滚后释放。
　

256

At the session level, a session can set the required transaction isolation level with the ALTER SESSION statement.
　

在会话级（session level），一个会话可以通过 ALTER SESSION 语句更改此会话内所有事务的隔离级别（isolation level）。
　

257

Note:

If Oracle's default locking is overridden at any level, the database administrator or application developer should ensure that the overriding locking procedures operate correctly. The locking procedures must satisfy the following criteria: data integrity is guaranteed, data concurrency is acceptable, and deadlocks are not possible or are appropriately handled.

提示：

无论在哪个级别覆盖 Oracle 的默认锁，数据库管理员或应用开发者都必须保证手工锁能够正确地执行。手工锁操作必须满足以下条件：保证数据完整性，提供足够的数据并发能力，不会发生死锁，或能够正确处理死锁。

258

See Also:

Oracle Database SQL Reference for detailed descriptions of the SQL statements LOCK TABLE and SELECT ... FOR UPDATE

另见：

Oracle Database SQL Reference 了解 LOCK TABLE 及 SELECT ... FOR UPDATE 语句的详细描述

259

Oracle Lock Management Services

13.3.8 Oracle 锁管理服务

260

With Oracle Lock Management services, an application developer can include statements in PL/SQL blocks that:

Request a lock of a specific type
Give the lock a unique name recognizable in another procedure in the same or in another instance
Change the lock type
Release the lock

应用开发者利用 Oracle 锁管理服务（Lock Management service）可以在 PL/SQL 块中使用以下语句：

请求特定类型的锁
赋给锁一个唯一的名称，使实例（PL/SQL 块所在实例或其他实例）中的其他过程能够对锁进行识别
改变锁的类型
释放锁

261

Because a reserved user lock is the same as an Oracle lock, it has all the Oracle lock functionality including deadlock detection. User locks never conflict with Oracle locks, because they are identified with the prefix UL.
　

由于用户锁（user lock）与 Oracle 锁相同，因此用户锁具备 Oracle 锁的全部功能，包括死锁检测。用户锁不会和 Oracle 锁冲突，因为用户锁的名称使用前缀 UL。
　

262

The Oracle Lock Management services are available through procedures in the DBMS_LOCK package.
　

用户可以通过 DBMS_LOCK 包使用 Oracle 的锁管理服务。
　

263

See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information about Oracle Lock Management services

Oracle Database PL/SQL Packages and Types Reference for information about DBMS_LOCK

另见：

Oracle Database Application Developer's Guide - Fundamentals 了解更多关于 Oracle 锁管理服务的信息

Oracle Database PL/SQL Packages and Types Reference 了解关于 DBMS_LOCK 包的信息

264

Overview of Oracle Flashback Query

13.4 Oracle 回闪查询概述

265

Oracle Flashback Query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system change number (SCN).
　

用户可以使用 Oracle 回闪查询（Flashback Query）功能查看或修复历史数据（historical data）。用户可以指定一个时间或系统变化编号（system change number，SCN），使查询的结果能够反映当时的数据库状态。
　

266

Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.
　

回闪查询借助 Oracle 的多版本读一致性（multiversion read-consistency）功能，利用撤销信息（undo）恢复所需的数据。Oracle 10g 数据库能够自动的调节决定撤销信息保存周期（undo retention period）的参数。撤销信息保存周期指旧的撤销信息（即已提交事务的撤销信息）必须经过多长时间才能被覆盖。数据库能够自动地收集撤销信息的使用情况，并依据统计结果及撤销表空间（undo tablespace）的大小确定撤销信息保存周期。
　

267

Using Flashback Query, you can query the database as it existed this morning, yesterday, or last week. The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.
　

利用回闪查询，用户可以查看数据库今天早晨，昨天甚至上星期的状态。回闪查询的速度取决于查询的数据量及需要从撤销信息中恢复的数据量。
　

268

You can query the history of a given row or a transaction. Using undo data stored in the database, you can view all versions of a row and revert to a previous version of that row. Flashback Transaction Query history lets you examine changes to the database at the transaction level.
　

利用回闪查询，用户可以查看某一行的历史信息，或在事务级上查看数据库的历史信息。通过数据库中存储的撤销信息，用户可以查看某一数据行的各个历史版本，并进行任意的恢复。用户还可以使用回闪事务查询（Flashback Transaction Query）在事务级查看数据库变化的历史情况。
　

269

You can audit the rows of a table and get information about the transactions that changed the rows and the times when it was changed. With the transaction ID, you can do transaction mining through LogMiner to get complete information about the transaction.
　

用户能够对数据行进行监控，了解哪些事务对数据进行了修改，以及修改是何时发生的。用户可以在 LogMiner 中通过事务 ID（transaction ID）获得与一个事务相关的全部信息。
　

270

See Also:

Oracle Database Administrator's Guide for more information on the automatic tuning of undo retention and on LogMiner

"Automatic Undo Retention"

另见：

Oracle Database Administrator's Guide 了解撤销信息保存周期的自动调整及 LogMiner

“自动撤销信息保存周期管理”

271

You set the date and time you want to view. Then, any SQL query you run operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.
　

用户可以设置一个需要查看的日期与时间。之后运行的 SQL 查询将反映指定日期时间时的数据库状态。如果用户具备足够权限，无需通过数据库管理员就能够利用保存的撤销信息修正现有数据中的错误。
　

272

With the AS OF SQL clause, you can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot get the same default snapshot.
　

用户在查询中可以使用 AS OF 子句为每个表指定不同时间的快照（snapshot）。将快照作用于表的操作被称为表修饰（table decoration）。如果用户没有使用特定的快照对表进行修饰，Oracle 将使用默认的快照。所有没指定快照的表将使用相同时间点上的默认快照。
　

273

For example, suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.
　

例如，用户需要查询出过去一小时中所有新建的用户帐户。用户可以对同一张表采用不同的快照（使用 AS OF 子句）进行修饰，再对这张表的两个实例做集合运算即可。
　

274

DML and DDL operations can use table decoration to choose snapshots within subqueries. Operations such as INSERT TABLE AS SELECT and CREATE TABLE AS SELECT can be used with table decoration in the subqueries to repair tables from which rows have been mistakenly deleted. Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a cursor and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with.
　

DML 及 DDL 操作可以在其子查询中使用表修饰来选择快照。在子查询中使用了表修饰的 INSERT TABLE AS SELECT 及 CREATE TABLE AS SELECT 操作可以用于恢复误删除的数据。表修饰语句可以使用任何类型的表达式：绑定变量（bind variable），常量，字符串，及日期函数（date operation）均可。用户可以在打开游标（cursor）时动态地绑定快照来进行表修饰。
　

275

See Also:

"Overview of Oracle Flashback Features" for an overview of all Oracle Flashback features

Oracle Database SQL Reference for information on the AS OF clause

另见：

“Overview of Oracle Flashback Features” 了解 Oracle 回闪特性的概述

Oracle Database SQL Reference 了解如何使用 AS OF 子句

276

Flashback Query Benefits

13.4.1 回闪查询的优势

277

This section lists some of the benefits of using Flashback Query.

Application Transparency

Packaged applications, like report generation tools that only do queries, can run in Flashback Query mode by using logon triggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be satisfied are guaranteed to hold good, because there is a consistent version of the database as of the Flashback Query time.
Application Performance

If an application requires recovery actions, it can do so by saving SCNs and flashing back to those SCNs. This is a lot easier and faster than saving data sets and restoring them later, which would be required if the application were to do explicit versioning. Using Flashback Query, there are no costs for logging that would be incurred by explicit versioning.
Online Operation

Flashback Query is an online operation. Concurrent DMLs and queries from other sessions are allowed while an object is queried inside Flashback Query.The speed of these operations is unaffected. Moreover, different sessions can flash back to different Flashback times or SCNs on the same object concurrently. The speed of the Flashback Query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes.
Easy Manageability

There is no additional management on the part of the user, except setting the appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past versions are constructed automatically, as needed.

本节列出了回闪查询（Flashback Query）功能的一些优势。

对应用透明

像报表生成工具之类只对数据库进行查询的应用程序，可以通过登录触发器（logon trigger）进入回闪查询模式。应用程序代码无需修改，即回闪查询对应用程序是透明的。在回闪查询模式下，应用程序所需的数据约束都能被满足，因为回闪查询反映的是某一时间点上具备一致性的数据库状态。
提高应用性能

如果应用程序需要执行恢复的操作，她只需记录下 SCN，之后就能够回闪到此 SCN。这与先转存再恢复相比更易行也更快捷，因为后者需要在应用程序中显式地进行版本管理工作。使用回闪查询后，系统中就不会出现显式版本管理工作带来的重做日志开销。
可以联机操作

回闪查询是一种联机操作。当一个对象被回闪查询使用时，其他会话中的 DML 或查询操作可以并发地使用此对象，且这些并发操作的速度不会受到回闪查询的影响。此外，不同会话内的回闪查询操作能够并发地看到同一对象在不同时间或 SCN 时的状态。回闪查询的速度取决于其所需的撤销信息的数量，而撤销信息的数量决定了回闪查询能够回溯的时长。
易于管理

用户只需具备足够权限，并设定适当的撤销信息保存周期（undo retention period），而无需进行其他的管理工作。系统中也不会生成额外的重做日志，因为数据的历史状态能够被 Oracle 自动地生成。

278

Note:

Flashback Query does not undo anything. It is only a query mechanism. You can take the output from a Flashback Query and perform an undo yourself in many circumstances.

Flashback Query does not tell you what changed. LogMiner does that.

Flashback Query can undo changes and can be very efficient if you know the rows that need to be moved back in time. You can use it to move a full table back in time, but this is very expensive if the table is large since it involves a full table copy.

Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables.

LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.

提示：

回闪查询不会将现有数据撤销到从前的状态，她只是一种查询机制。但是用户可以利用回闪查询的输出手工地执行撤销操作。

用户不应通过回闪查询来了解数据库中进行了哪些修改操作，而应使用 LogMiner。

如果用户能清楚地了解哪些数据行需要被恢复，那么使用回闪查询效率较高。用户也可以利用回闪查询对整个表进行恢复，但是当表数据量很大时此类操作的开销也很大。

回闪查询对 DDL 操作无效，例如对列进行修改，移除或清除表。

LogMiner 的用途是获得对数据库修改的历史记录。但 LogMiner 是以操作（delta）的形式（插入，更新，删除）来反映修改的历史记录的，而不能直接将修改前后的数据展现给用户。对于某些应用来说，LogMiner 的结果使用起来不够方便。

279

Some Uses of Flashback Query

13.4.2 回闪查询的用途

280

This section lists some ways to use Flashback Query.

Self-Service Repair

Perhaps you accidentally deleted some important rows from a table and wanted to recover the deleted rows. To do the repair, you can move backward in time and see the missing rows and re-insert the deleted row into the current table.
E-mail or Voice Mail Applications

You might have deleted mail in the past. Using Flashback Query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box.
Account Balances

You can view account prior account balances as of a certain day in the month.
Packaged Applications

Packaged applications (like report generation tools) can make use of Flashback Query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN.

本节列举了回闪查询（Flashback Query）的一些使用方法。

数据修复

假设用户删除了表中的重要数据需要进行恢复。用户可以回闪查询数据表中过去某个时间点上的数据，并将被删除的数据重新插入到表中。
电子邮件或语音邮件应用

用户可能会删除邮件。使用回闪查询，用户可以找到被删除的邮件并重新插入到收件箱中。
帐户结算（account balance）

用户可以查询某一日期的帐户结算情况。
供预制的应用程序（packaged application）使用

预制的应用程序（例如报表生成工具）可以利用回闪查询功能而无需修改程序代码。在回闪查询模式下，应用程序所需的数据约束都能被满足，因为回闪查询反映的是某一时间点上满足一致性的数据库状态。

281

In addition, Flashback Query could be used after examination of audit information to see the before-image of the data. In DSS environments, it could be used for extraction of data as of a consistent point in time from OLTP systems.
　

此外，回闪查询可以用来对比不同时间点上的监控（audit）数据。在 DSS 系统中，用户可以使用回闪查询来抽取 OLTP 系统中某一时间点的满足一致性的数据。
　

282

See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information about using Oracle Flashback Query

Oracle Database PL/SQL Packages and Types Reference for a description of the DBMS_FLASHBACK package

Oracle Database Administrator's Guide for information about undo tablespaces and setting retention period

另见：

Oracle Database Application Developer's Guide - Fundamentals 了解更多关于如何使用回闪查询的信息

Oracle Database PL/SQL Packages and Types Reference 查询关于 DBMS_FLASHBACK 包的描述

Oracle Database Administrator's Guide 了解关于撤销表空间的信息及如何设置撤销信息保存周期

翻译不确定的词汇（格式：黄色背景）　

[280] Packaged Applications

翻译不确定的Oracle/数据库词汇（格式：黄色背景）

[009] Preventable Phenomena
[038] Cache Fusion
[066] transaction set consistent
[070] Dirty write
[070] Read materialized view time
[070] Readers block writers
[070] Writers block readers
[070] Different-row writers block writers
[070] Same-row writers block writers
[074] Referential Integrity
[090] transaction arrival rates
[100] read-locking
[125] lost updates
[128] lock conversion
[129] row share table lock
[129] row exclusive table lock
[141] override
[150] latches
[167] row share
[167] row exclusive
[167] share
[167] share row exclusive
[167] exclusive
[175] subshare table lock
[182] subexclusive table lock
[195] share-subexclusive table lock
[231] breakable parse lock
[261] reserved user lock
[272] table decoration
[278] delta

翻译不确定的句子（格式：黄色背景）

[080] The transaction can undo and retry only when the remote server does support serializable transactions.
[100] Note that a conventional read-locking, pessimistic implementation of serializable mode would not be suitable for this environment either
[139] The statement rolled back is the one belonging to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.
[220] Recall that a DDL statement implicitly commits its transaction.
[241] The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.
[274] Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on.
[281] In addition, Flashback Query could be used after examination of audit information to see the before-image of the data.

注释性的文字（格式：[绿色]）

[070]
[073]

未完成的链接

[008] Data Integrity
[276] Overview of Oracle Flashback Features

translator: zw1840@hotmail.com