Oracle 11gR2 概念 第9章 数据并发性和一致性

Previous Next
View PDF 9 Data Concurrency and Consistency
Previous Next
View PDF 第9章数据并发性和一致性
This chapter explains how Oracle Database maintains consistent data in a
multiuser database environment.
本章介绍了在多用户数据库环境中，Oracle数据库如何维护一致的数据。

This chapter contains the following sections: 本章包含以下各节：
∙Introduction to Data Concurrency and Consistency o Multiversion Read Consistency
o Locking Mechanisms
o ANSI/ISO Transaction Isolation Levels
∙Overview of Oracle Database Transaction Isolation Levels o Read Committed Isolation Level
o Serializable Isolation Level
o Read-Only Isolation Level
∙Overview of the Oracle Database Locking Mechanism o Summary of Locking Behavior
o Use of Locks
o Lock Modes
o Lock Conversion and Escalation
o Lock Duration
o Locks and Deadlocks
∙Overview of Automatic Locks
o DML Locks
o DDL Locks
o System Locks
∙Overview of Manual Data Locks
∙Overview of User-Defined Locks ∙数据并发和一致性介绍
o多版本读一致性
o锁定机制
o ANSI/ISO 事务隔离级别∙Oracle 数据库事务隔离级别概述o读提交隔离级别
o可串行化隔离级别
o只读隔离级别
∙数据库锁定机制概述
o锁定行为总结
o使用锁
o锁模式
o锁转换和锁升级
o锁持续时间
o锁和死锁
∙自动锁的概述
o DML锁
o DDL锁
o系统锁
∙手动数据锁概述
∙用户定义的锁的概述
Introduction to Data Concurrency and Consistency 数据并发和一致性介绍
In a single-user database, a user can modify data without concern for other users modifying the same data at the same time. However, in a multiuser database, statements within multiple simultaneous transactions 在单用户的数据库中，用户可以修改数据，而不用担心其他用户在同一时间修改相同的数据。

但是，在一个多用户的数据库中，多个事务内的语句可以同时更新相同的数据。

同时执行的多个事务必须产生有意义且一致的结果。

can update the same data. Transactions executing simultaneously must
produce meaningful and consistent results. Therefore, a multiuser
database must provide the following:
因此，多用户数据库必须提供以下功能：
∙Data concurrency, which ensures that users can access data at
the same time
∙数据并发性，确保多个用户可以同时访问数据
∙Data consistency, which ensures that each user sees a consistent view of the data, including visible changes made by the user's own transactions and committed transactions of other users ∙数据一致性，确保每个用户看到数据的一致的视图，包括可以看到用户自己的事务所做的更改，和其他用户已提交的事务所做的更改。

To describe consistent transaction behavior when transactions run concurrently, database researchers have defined a transaction isolation
model called serializability. A serializable transaction operates in an environment that makes it appear as if no other users were modifying
data in the database. 为描述当多个事务同时运行时的事务一致性行为，数据库研究人员定义了一种称为可串行性的事务隔离模型。

可串行化事务在一种使其看起来好像没有其他用户正在修改数据库中的数据的环境中运作。

While this degree of isolation between transactions is generally desirable,
running many applications in serializable mode can seriously compromise application throughput. Complete isolation of concurrently running
transactions could mean that one transaction cannot perform an insertion into a table being queried by another transaction. In short, real-world considerations usually require a compromise between perfect transaction isolation and performance. 虽然事务之间的这种隔离度好像不错，但在可序列化模式下运行许多应用程序可能会严重影响应用程序吞吐量。

对并发运行事务的完全隔离可能意味着一个事务无法在某个正在被另一个事务查询的表上执行插入操作。

简而言之，现实的考虑通常需要在完美的事务隔离性和性能之间的一个折衷。

Oracle Database maintains data consistency by using a multiversion consistency model and various types of locks and transactions. In this way, the database can present a view of data to multiple concurrent users, with each view consistent to a point in time. Because different versions of data blocks can exist simultaneously, transactions can read the version of data committed at the point in time required by a query and return results that are consistent to a single point in time. Oracle 数据库通过使用多版本一致性模型和各种类型的锁和事务，来维护数据的一致性。

通过这种方式，数据库可以向多个并发用户呈现一个数据的视图，每个视图都在某个时间点上是一致的。

因为不同版本的数据块可以同时存在，事务可以读取在某个查询请求时间点上的已提交版本数据，并返回符合一个单一时间点的结果。

See Also: 另见：
Chapter 5, "Data Integrity" and Chapter 10, "Transactions"第 5 章，"数据完整性"和第 10 章，"事务" Multiversion Read Consistency 多版本读一致性
In Oracle Database, multiversioning is the ability to simultaneously materialize multiple versions of data. Oracle Database maintains multiversion read consistency, which means that database queries have the following characteristics: 在Oracle数据库中，多版本即同时实现数据的多个版本的能力。

Oracle 数据库维护多版本读取一致性，这意味着数据库查询具有以下特征：
∙Read-consistent queries ∙读一致查询
The data returned by a query is committed and consistent with
respect to a single point in time.
查询所返回的数据已提交的，且关于某个单一时间点一致。

Important: 重要：
Oracle Database never permits dirty reads, which occur when a transaction reads uncommitted data in another transaction. Oracle 数据库绝不允许脏读。

当一个事务读取了另一个事务中未提交的数据时，会发生脏读。

To illustrate the problem with dirty reads, suppose one transaction updates a column value without committing. A second transaction reads the updated and dirty (uncommitted) value. The first session rolls back the transaction so that the column has its old value, but the second transaction proceeds using the updated value, corrupting the database. Dirty reads compromise data integrity, violate foreign keys, and ignore unique constraints. 为说明脏读的问题，假设一个事务更新某列的值，但不提交。

第二个事务读取此已更新的脏（未提交）值。

第一个会话回滚了事务，使该列仍具有其旧值，但第二个事务继续使用更新的值，这会损坏数据库。

脏读会破坏数据的完整性、破坏外键、和忽略唯一约束。

∙Nonblocking queries ∙非阻塞查询
Readers and writers of data do not block one another (see "Summary of Locking Behavior"). 数据的读取者和写入者不会相互阻塞（请参见"锁定行为总结"）。

Statement-Level Read Consistency 语句级读取一致性
Oracle Database always enforces statement-level read consistency,
which guarantees that data returned by a single query is committed and consistent with respect to a single point in time. The point in time to
which a single SQL statement is consistent depends on the transaction
isolation level and the nature of the query: Oracle 数据库始终强制执行语句级读取一致性，保证单个查询所返回的数据是已提交的、且关于某个单一时间点一致。

单个 SQL 语句所一致的时间点取决于事务的隔离级别和查询的性质：
∙In the read committed isolation level, this point is the time at which the statement was opened. For example, if a SELECT statement opens at SCN 1000, then this statement is consistent to SCN 1000. ∙在读提交隔离级别，该时间点是语句打开的时间。

例如，如果一个SELECT 语句在SCN 1000时打开，则此语句一致于SCN 1000。

∙In a serializable or read-only transaction this point is time the transaction began. For example, if a transaction begins at SCN
1000, and if multiple SELECT statements occur in this transaction, then each statement is consistent to SCN 1000. ∙在可串行化或只读事务隔离级别，该时间点为事务开始的时间。

例如，如果一个事务开始于SCN 1000，且在该事务中有多个 SELECT 语句发生，则每个语句都一致于SCN 1000。

∙In a Flashback Query operation (SELECT ... AS OF), the SELECT statement explicitly specifies the point in time. For
example, you can query a table as it appeared last Thursday at 2 p.m. ∙在闪回查询操作（SELECT ... AS OF）中，SELECT 语句显式指定时间点。

例如，你可以查询某个表在上星期四下午 2 时的数据
See Also: 另见：
Oracle Database Advanced Application Developer's Guide to learn about
Flashback Query
《Oracle 数据库高级应用开发指南》了解闪回查询Transaction-Level Read Consistency 事务级读取一致性
Oracle Database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began. Oracle 数据库还可以为一个事务中的所有查询提供读取一致性，这称为事务级读取一致性。

在这种情况下，事务中的每个语句都看到来自同一时间点（即该事务开始的时间）的数据。

Queries made by a serializable transaction see changes made by the transaction itself. For example, a transaction that updates employees and then queries employees will see the updates. Transaction-level read consistency produces repeatable reads and does not expose a query
to phantom reads. 在一个可序列化事务中的多个查询，能看到事务本身所做的更改。

例如，某个事务更新了employees表，然后其后续查询将看到对employees所做的更新。

事务级读取一致性产生可重复的读取，且不会产生幻读读。

Read Consistency and Undo Segments 读取一致性及撤消
To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this goal through undo data. 为管理多版本的读取一致性模型，当表同时被查询和更新时，数据库必须创建一组读取一致的数据。

Oracle 数据库通过使用撤销数据实现了这一目标。

Whenever a user modifies data, Oracle Database creates undo entries,
which it writes to undo segments ("Undo Segments"). The undo segments
contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data,
all at different points in time, can exist in the database. The database can
use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries. 每当用户修改了数据，Oracle数据库会创建撤销条目，并写入到撤销段 ("撤销段"）。

撤销段包含由未提交事务或最近提交的事务所更改的数据的旧值。

因此，同一数据在各个不同时间点上的多个版本，都可以存在于数据库中。

数据库可以使用在不同时间点的数据快照，来提供数据读取一致视图，并实现非阻塞查询。

Read consistency is guaranteed in single-instance and Oracle Real Application Clusters (Oracle RAC) environments. Oracle RAC uses a cache-to-cache block transfer mechanism known as Cache Fusion to transfer
read-consistent images of data blocks from one database instance to
another. 读取一致性在单实例和 Oracle 真正应用集群（Oracle RAC）环境中都可以得到保证。

Oracle RAC 使用一种称为缓存融合的“缓存到缓存”的数据块传输机制，将一个数据库实例中的数据块读取一致映像传送到另一个实例中。

See Also: 另见：
∙"Internal LOBs" to learn about read consistency mechanisms for
LOBs
∙"内部 LOBs"了解 LOB的读取一致性机制
∙Oracle Database 2 Day + Real Application Clusters Guide to learn
about Cache Fusion
∙《Oracle 数据库 2 日 + 实际应用集群指南》了解缓存融合
Read Consistency: Example 读一致性：示例
Figure 9-1 shows a query that uses undo data to provide statement-level read consistency in the read committed isolation level. 图 9-1 显示了一个查询，在已提交读隔离级别使用撤销数据以提供语句级的读取一致性。

Figure 9-1 Read Consistency in the Read Committed Isolation
Level
图 9-1 在已提交读隔离级别的读取一致性
Description of "Figure 9-1 Read Consistency in the Read Committed Isolation Level"Description of "Figure 9-1 Read Consistency in the Read Committed Isolation Level"
As the database retrieves data blocks on behalf of a query, the database ensures that the data in each block reflects the contents of the block when the query began. The database rolls back changes to the block as needed to reconstruct the block to the point in time the query started processing. 当数据库为某个查询检索数据块时，数据库确保每个块中的数据反映了该查询开始时的内容。

数据库根据需要回滚对数据块所做的更改，以将块重建到查询处理开始的状态。

The database uses a mechanism called an SCN to guarantee the order of transactions. As the SELECT statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9-1, this SCN is 10023. Each query in the transaction must return committed data with respect to SCN 10023. 数据库使用一种称为SCN的机制，来保证事务的顺序。

当SELECT 语句进入执行阶段时，数据库会确定查询开始执行时所记录的SCN。

在图 9-1中，该SCN为10023。

在事务中的每个查询必须返回在SCN 10023时的已提交数据。

In Figure 9-1, blocks with SCNs after 10023 indicate changed data, as shown by the two blocks with SCN 10024. The SELECT statement requires a version of the block that is consistent with committed changes. The database copies current data blocks to a new buffer and applies undo data to reconstruct previous versions of the blocks. These reconstructed data blocks are called consistent read (CR) clones. 在图 9-1中，其SCN大于10023的块具有已更改数据，如图中的两个具有SCN 10024的块所示。

SELECT 语句需要一个与已提交更改块一致的版本。

该数据库将当前数据块复制到新的缓冲区，并应用撤消数据，以重新构造块的早期版本。

这些重建的数据块被称为一致读取 (CR) 克隆。

In Figure 9-1, the database creates two CR clones: one block consistent to
SCN 10006 and the other block consistent to SCN 10021. The database returns the reconstructed data for the query. In this way, Oracle Database
prevents dirty reads. 在图 9-1中，数据库创建了两个 CR 克隆：一个块与SCN 10006一致，而另一个块与SCN 10021 一致。

数据库为查询返回重建的数据。

通过这种方式，数据库可以防止脏读。

See Also: 另见：
"Database Buffer Cache" and "System Change Numbers (SCNs)""数据库缓冲区缓存"和"系统更改号 (SCNs) " Read Consistency and Transaction Tables 读取一致性和事务表
The database uses a transaction table, also called an interested transaction list (ITL), to determine if a transaction was uncommitted when the database began modifying the block. The block header of every segment block contains a transaction table. 数据库使用一个称为感兴趣事务列表(ITL)的事务表，来确定当数据库开始修改块时是否某个事务还未提交。

每个段块的块头包含一个事务表。

Entries in the transaction table describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. The transaction table points to the undo segment, which provides information about the timing of changes made to the database. 事务表中的条目描述了哪些事务有被锁定的行，以及块中的哪些行包含提交和未提交的更改。

事务表指向撤销段，提供对数据库所做的更改的时间相关信息。

In a sense, the block header contains a recent history of transactions that affected each row in the block. The INITRANS parameter of the CREATE TABLE and ALTER TABLE statements controls the amount of transaction history that is kept. 在某种意义上，块头包含影响块中每个行的事务的最近历史记录。

CREATE TABLE和ALTER TABLE语句的INITRANS参数，控制被保留的交易历史记录条数。

See Also: 另见：
Oracle Database SQL Language Reference to learn about the INITRANS
parameter
《Oracle 数据库 SQL 语言参考》了解INITRANS参数Locking Mechanisms 锁定机制
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. 通常，多用户数据库使用某种形式的数据锁定，来解决与数据并发性、一致性、和完整性相关的问题。

锁是防止访问同一资源的事务之间的破坏性相互作用的机制。

See Also: 另见：
"Overview of the Oracle Database Locking Mechanism""数据库锁定机制概述" ANSI/ISO Transaction Isolation Levels ANSI/ISO 事务隔离级别
The SQL standard, which has been adopted by both ANSI and ISO/IEC, defines four levels of transaction isolation. These levels have differing degrees of impact on transaction processing throughput. 已由 ANSI 和 ISO/IEC 采纳的SQL 标准，定义了四个事务隔离级别。

这些级别对事务处理吞吐量有不同程度的影响。

These isolation levels are defined in terms of phenomena that must be prevented between concurrently executing transactions. The 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. For example, a user queries a row and then later queries the same row, only to discover that the data has changed. 一个事务重新读取之前曾经读取过的数据，发现另一个已提交的事务已修改或删除了该数据。

例如，用户查询某行，然后稍后又查询相同的行，却发现数据已更改。

∙Phantom reads ∙幻像读
A transaction reruns 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. 一个事务重新运行满足某搜索条件的查询，并返回一个行集，发现另一个已提交的事务已插入了满足搜索条件的其他行。

For example, a transaction queries the number of employees. Five minutes later it performs the same query, but now the number has increased by one because another user inserted a record for a new
hire. More data satisfies the query criteria than before, but unlike in 例如，一个事务查询雇员数目。

五分钟后它执行相同的查询，但现在人数却增加了一个，这是因为另一个用户为一名新员工插入了一条记录。

满足查询条件的数据比之前更多了，但与不可重复读不同，之前读取的数据不会变化。

a fuzzy read the previously read data is unchanged.
The SQL standard defines four levels of isolation in terms of the phenomena that a transaction running at a particular isolation level is permitted to experience. Table 9-1 shows the levels. 根据运行在某个特定的隔离级别的事务所允许发生的现象，SQL 标准定义了四个隔离级别。

表 9-1 显示了这些级别。

Table 9-1 Preventable Read Phenomena by Isolation Level 表 9-1 在不同隔离级别下的可预防的读现象
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 隔离级别脏读不可重复读幻像读
未提交读可能可能可能已提交读不可能可能可能可重复读不可能不可能可能可串行化不可能不可能不可能
Oracle Database offers the read committed (default) and serializable isolation levels. Also, the database offers a read-only mode. Oracle 数据库提供了已提交读（默认值）和可串行化隔离级别。

另外，数据库也可以提供一种只读模式。

See Also: 另见：
∙"Overview of Oracle Database Transaction Isolation Levels" to learn about read committed, serializable, and read-only isolation levels ∙Oracle Database SQL Language Reference for a discussion of Oracle Database conformance to SQL standards ∙"Oracle 数据库事务隔离级别概述"了解已提交读、可串行化、和只读隔离级别
∙《Oracle 数据库 SQL 语言参考》关于Oracle数据库与SQL 标准的一致性的讨论
Overview of Oracle Database Transaction Isolation
Levels
Oracle 数据库事务隔离级别概述
Table 9-1 summarizes the ANSI standard for transaction isolation levels.
The standard is defined in terms of the phenomena that are either permitted or prevented for each isolation level. Oracle Database provides
the transaction isolation levels: 表 9-1 总结了事务隔离级别的ANSI 标准。

该标准定义了在各个隔离级别所允许或必须防止的现象。

Oracle 数据库提供如下事务隔离级别：
∙Read Committed Isolation Level ∙Serializable Isolation Level
∙Read-Only Isolation Level ∙已提交读隔离级别∙可串行化隔离级别∙只读隔离级别
See Also: 另见：
∙Oracle Database Advanced Application Developer's Guide to learn
more about transaction isolation levels
∙《Oracle 数据库高级应用开发指南》了解事务隔离级别的更多信息
∙Oracle Database SQL Language Reference and Oracle Database PL/SQL Language Reference to learn about SET TRANSACTION ISOLATION LEVEL ∙《Oracle 数据库 SQL 语言参考》和《Oracle 数据库 PL/SQL 语言参考》了解SET TRANSACTION ISOLATION LEVEL
Read Committed Isolation Level 读提交隔离级别
In the read committed isolation level, which is the default, every
query executed by a transaction sees only data committed before the query—not the transaction—began. This level of isolation is appropriate
for database environments in which few transactions are likely to conflict. 在（默认的）已提交读隔离级别中，事务中执行的每个查询，仅看到在查询开始之前提交的数据——而不是事务开始之前提交的数据。

这一隔离级别适合于几乎不可能发生事务冲突的数据库环境。

A query in a read committed transaction avoids reading data that commits while the query is in progress. For example, if a query is halfway through a scan of a million-row table, and if a different transaction commits an update to row 950,000, then the query does not see this change when it reads row 950,000. However, because the database does not prevent other transactions from modifying data read by a query, other transactions may change data between query executions. Thus, a transaction that runs the same query twice may experience fuzzy reads and phantoms. 已提交读事务中的查询可以避免读取在查询过程中所提交的数据。

例如，如果一个查询正扫描到一个百万行表的中间，而另一个不同的事务对第950000行提交了一个更新，但当查询读到第950000行时，它并不能看见这个变化。

但是，因为数据库不会阻止其它事务修改一个查询所读取的数据，其他事务可能会在查询执行期间更改数据。

因此，两次运行相同查询的事务可能会遇到模糊读取和幻像读取现象。

Read Consistency in the Read Committed Isolation Level 在已提交读隔离级别中的读取一致性
A consistent result set is provided for every query, guaranteeing data
consistency, with no action by the user. An implicit query, such as a query implied by a WHERE clause in an UPDATE statement, is guaranteed a consistent set of results. However, each statement in an implicit query
does not see the changes made by the DML statement itself, but sees the data as it existed before changes were made. 为每个查询提供一个一致的结果集，其目的是为了保证数据一致性，而无需用户采取任何行动。

对于隐含的查询（如在一个UPDATE 语句中的 WHERE 子句），也同样可以保证其一致的结果集。

但是，在隐式查询中的每个语句不会看到 DML 语句本身所做的更改，只能看到更改之前所存在的数据。

If a SELECT list contains a PL/SQL 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 函数，则数据库在该PL/SQL 函数代码内运行的SQL所在语句级别（而不是在父SQL级别）上，应用语句级别读取一致性。

例如，一个函数可能会访问某个表，其数据被另一个用户更改并提交。

在SELECT语句中的每次函数运行，都会建立一个新的读一致性快照。

See Also: 另见：
"Subqueries and Implicit Queries""子查询和隐式查询" Conflicting Writes in Read Committed Transactions 在读提交事务中的写入冲突
In a read committed transaction, a conflicting write occurs when the transaction attempts to change a row updated by an uncommitted concurrent transaction, sometimes called a blocking transaction. The 在一个读已提交事务中，当事务尝试更改由另一个未提交并发事务（有时称为阻塞事务）所更新的行时，会发生写入冲突。

读提交事务将等待阻塞事务结束并释放其行锁。

有两个选项如下所示：
read committed transaction waits for the blocking transaction to end and release its row lock. The options are as follows:
∙If the blocking transaction rolls back, then the waiting transaction proceeds to change the previously locked row as if the other
transaction never existed. ∙如果阻塞事务回滚，正在等待的事务将继续并更改之前被锁定的行，就像另一个事务从未存在一样。

∙If the blocking transaction commits and releases its locks, then the waiting transaction proceeds with its intended update to the newly changed row. ∙如果阻塞事务提交并释放了锁，则正在等待的事务将对这个刚被更新的行继续其预定更新。

Table 9-2 shows how transaction 1, which can be either serializable or read committed, interacts with read committed transaction 2. Table 9-2 shows a classic situation known as a lost update (see "Use of Locks"). The update made by transaction 1 is not in the table even though
transaction 1 committed it. Devising a strategy to handle lost updates is an important part of application development. 表 9-2 显示了一个可能是可串行化的或已提交读的事务 1，如何与另一个已提交读的事务 2 进行交互。

表 9-2 显示了一个称为丢失更新的典型情况（请参阅"使用锁定"）。

事务 1 所作的更新不能在表中反映出来，即使事务1 已经提交它。

制定一项策略以处理更新丢失是应用程序开发的一个重要部分。

Table 9-2 Conflicting Writes and Lost Updates in a READ
COMMITTED Transaction
表 9-2 在一个已提交读事务中的写入冲突和丢失更新Session 1 Session 2 Explanation 解释
SQL> SELECT last_name, salary FROM employees WHERE
last_name IN
('Banda','Greene','Hintz'); LAST_NAME SALARY
------------- ----------
Banda 6200 Greene 950 Session 1 queries the salaries for
Banda, Greene, and Hintz. No
employee named Hintz is found.
会话1查询Banda、Greene、和
Hintz的薪金。

找不到名为 Hintz的任
何雇员。

SQL> UPDATE employees SET salary = 7000 WHERE last_name = 'Banda';
Session 1 begins a transaction by
updating the Banda salary. The
default isolation level for transaction
1 is READ COMMITTED.
会话1开始一个事务1，更新Banda
的薪金。

事务 1 的默认隔离级别是已
提交读。

SQL> SET TRANSACTION ISOLATION
LEVEL READ COMMITTED;
Session 2 begins transaction 2 and
sets the isolation level explicitly to
READ COMMITTED.
会话2开始一个事务2，并将隔离级
别显式设置为已提交读。

SQL> SELECT last_name, salary FROM employees WHERE last_name IN('Banda','Greene','Hintz'); LAST_NAME SALARY
------------- ---------- Banda 6200 Greene 9500 Transaction 2 queries the salaries for
Banda, Greene, and Hintz. Oracle
Database uses read consistency to
show the salary for Banda before the
uncommitted update made by
transaction 1.
事务 2查询Banda、Greene和Hintz
的薪金。

Oracle 数据库使用读取一致
性，显示事务 1 做出未提交更新前
Banda的薪金。

SQL> UPDATE employees
SET salary =9900
WHERE last_name = 'Greene'; Transaction 2 updates the salary for
Greene successfully because
transaction 1 locked only the Banda
row (see "Row Locks (TX)").
事务 2 成功更新Greene的薪金，因
为事务 1 只锁定了Banda所在的行
（请参阅"行锁 (TX)"）。

SQL> INSERT INTO employees (employee_id, last_name, email,hire_date, job_id) VALUES (210,'Hintz', 'JHINTZ', SYSDATE,'SH_CLERK');
Transaction 1 inserts a row for
employee Hintz, but does not
commit.
事务 1 为雇员Hintz插入一行，但不
提交。

SQL> SELECT last_name, salary
FROM employees WHERE last_name
IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 6200
Greene 9900
Transaction 2 queries the salaries for
employees Banda, Greene, and
Hintz.
Transaction 2 sees its own update to
the salary for Greene. Transaction 2
does not see the uncommitted
update to the salary for Banda or the
insertion for Hintz made by
transaction 1.
事务 2 查询Banda、Greene和Hintz
的薪金。

事务 2 看到它自己对Greene薪金的
更新。

但事务 2 看不到由事务 1对
Banda薪金所做的未提交更新，或为
Hintz插入的新行。

SQL> UPDATE employees SET
salary =6300
WHERE last_name = 'Banda';
-- prompt does not return
Transaction 2 attempts to update
the row for Banda, which is currently
locked by transaction 1, creating a
co flicting write. Transaction 2 waits
until transaction 1 ends.
事务2试图更新当前被事务1 锁定的
Banda行，这会产生一个写入冲突。

事务 2 必须等待，直到事务1 结束。

SQL> COMMIT; Transaction 1 commits its work, 事务 1 提交其工作，以结束事务。

ending the transaction.
1 row updated. SQL> The lock on the Banda row is now
released, so transaction 2 proceeds
with its update to the salary for
Banda.
现在Banda行上的锁释放了，所以事
务2 得以继续，并完成对Banda薪金
的更新。

SQL> SELECT last_name, salary FROM employees WHERE last_name IN('Banda','Greene','Hintz'); LAST_NAME SALARY
------------- ---------- Banda 6300 Greene 9900
Hintz Transaction 2 queries the salaries for
employees Banda, Greene, and
Hintz. The Hintz insert committed by
transaction 1 is now visible to
transaction 2. Transaction 2 sees its
own update to the Banda salary.
事务 2 查询雇员Banda、Greene和
Hintz的薪酬。

现在事务 1已提交了
所插入的Hintz行，并能被事务 2看
到。

事务 2 也看到自己对Banda薪金
的更新。

COMMIT; Transaction 2 commits its work,
ending the transaction.
事务 2提交其工作，以结束事务。

SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY
------------- ---------- Banda 6300 Greene 9900
Hintz Session 1 queries the rows for
Banda, Greene, and Hintz. The
salary for Banda is 6300, which is
the update made by transaction 2.
The update of Banda's salary to
7000 made by transaction 1 is now
"lost."
会话 1 查询Banda、Greene、Hintz
的行。

Banda的薪金是 6300，这是
事务 2 所作的更新。

事务 1对Banda
的薪金改至 7000的更新现在"丢失"
了。

Serializable Isolation Level 可串行化隔离级别
In the serialization isolation level, a transaction sees only changes
committed at the time the transaction—not the query—began and changes made by the transaction itself. A serializable transaction operates in an environment that makes it appear as if no other users were
modifying data in the database. 在可串行化隔离级别，事务只看到自事务开始以来（而不是自查询以来）该事务本身所提交的更改。

可串行化事务的运行环境，使其看起来好像没有其他用户在修改数据库中的数据。

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 ∙较长时间运行的事务主要为只读事务
In serializable isolation, the read consistency normally obtained at the statement level extends to the entire transaction. Any row read by the transaction is assured to be the same when reread. Any query is
guaranteed to return the same results for the duration of the transaction, so changes made by other transactions are not visible to the query regardless of how long it has been running. Serializable transactions do not experience dirty reads, fuzzy reads, or phantom reads. 在可串行化隔离级别，在语句级别所获得的读取一致性通常延伸到整个事务范围。

当重新读取在同一事务中之前读取的任何行时，保证结果相同。

可以保证任何查询在该事务的持续期间返回相同的结果，因此其他事务所做的更改是不可见的，无论该查询已运行了多长时间。

可串行化事务不会遇到脏读、模糊读取、或幻读。

Oracle Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began. The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began: Oracle 数据库允许可串行化事务修改行，只要当可序列化事务开始时，由其它事务对行所做更改已提交。

当一个串行化事务试图更新或删除某数据，而该数据在串行化事务开始后被一个不同的事务更改并提交，则数据库将生成一个错误：
ORA-08177: Cannot serialize access for this transaction ORA-08177: Cannot serialize access for this transaction
When a serializable transaction fails with the ORA-08177 error, an application can take several actions, including the following: 当可序列化事务失败，产生ORA-08177 错误时，应用程序可以采取行动，包括以下几种：
∙Commit the work executed to that point ∙将所执行的工作提交到该点
∙Execute additional (but different) statements, perhaps after rolling back to a savepoint established earlier in the transaction ∙也许要先回滚到事务中之前建立的某保存点，然后执行一些其他额外的（不同）语句
∙Roll back the entire transaction ∙回滚整个事务
Table 9-3 shows how a serializable transaction interacts with other transactions. If the serializable transaction does not try to change a row committed by another transaction after the serializable transaction began, then a serialized access problem is avoided. 表 9-3 显示了一个可串行化事务与其它事务之间的交互。

如果可串行化事务不会尝试更改由另一个事务在该可序列化事务开始后所提交的行，就可以避免串行化访问问题。

Table 9-3 Read Consistency and Serialized Access Problems in 表 9-3 可串行化事务中的读取一致性和串行化访问问题。