Oracle编程入门经典 第12章 事务处理和并发控制

目录

12.1          什么是事务处理... 1

12.2          事务处理控制语句... 1

12.2.1       COMMIT处理... 2

12.2.2       ROLL BACK处理... 2

12.2.3       SAVEPOINT和ROLL BACK TO SAVEPOINT. 3

12.2.4       SET TRANSACTION.. 3

试验：冻结视图... 4

12.2.5       SET CONSTRAINTS. 5

12.3          事务处理的ACID属性... 7

12.3.1       原子性... 7

12.3.2       一致性... 7

试验：事务处理级别的一致性... 8

12.3.3       隔离性... 11

12.3.4       持久性... 11

12.4          并发控制... 11

12.4.1       锁定... 12

试验：引发死锁... 12

12.4.2       多版本和读取一致性... 15

考虑一个进一步的示例。... 16

12.5          小结... 17

 

 

 

 

开发者能够命名他们的PL/SQL程序块，为它们确定参数，将它们存储在数据库中，并且从任何数据库客户或者实用工具中引用或者运行它们，例如 SQL*Plus、Pro*C，甚至是JDBC。

这此听PL/SQL程序称为存储过程和函数。它们的集合称为程序包。在本章中，我们将要解释使用过程、函数和程序包的三大优势、这三种相似结构之间的区别。

 

Oracle 9i产品帮助文档：

http://docs.oracle.com/cd/B10501_01/index.htm

可根据自己需要进行查询，包含了众多的文档。

 

Sample Schemas的目录：

http://docs.oracle.com/cd/B10501_01/server.920/a96539/toc.htm

 

Sample Schemas的文档（示例模式的表及介绍）：

http://docs.oracle.com/cd/B10501_01/server.920/a96539.pdf

 

在讨论这2个特性的时候，我们将要在本章中学习如下内容：

●Oracle中的事务处理是什么

●怎样控制Oracle中的事务控制

●Oracle怎样在数据库中实现并发控制，让多个用户同时访问和修改相同的数据表

 

12.1  什么是事务处理

用于有效记录某机构感兴趣的业务活动（称为事务）的数据处理（例如销售、供货的定购或货币传输）。通常，联机事务处理 (OLTP) 系统执行大量的相对较小的事务。

12.2  事务处理控制语句

Oracle中的一个重要概念就是没有“开始事务处理”的语句。用户不能显式开始一个事务处理。事务处理会隐式地开始于第一条修改数据的语句，或者一些要求事务处理的场合。使用COMMIT或者ROLL BACK语句将会显式终止事务处理。

如上所述，事务处理具有原子性，也就是说，或者所有语句都成功执行，或者所有语句都不能成功执行。我们会在这里介绍其中一些成员：

●COMMIT

●ROLL BACK

●SAVEPOINT

●ROLL BACK TO<SAVEPOINT>

●SET TRANSACTION

●SET CONSTRAINT(S)

 

12.2.1             COMMIT处理

作为开发者，用户应该使用COMMIT或者ROLL BACK显式终止用户的事务处理，否则用户正在使用的工具/环境就将为用户选取其中一种方式。

无论事务处理的规模如何，提交都是非常快速的操作。用户可能会认为事务处理越大（换句话说，影响的数据越多），提交所耗费时间越长。事实并非如此，进化论事务处理规模如何，提交的响应时间通常都很“平缓”。这是因为提交实际上没有太多的工作去做，但是它所做的工作却至关重要。

如果能够理解这点，那么就可以避免许多开发者所采用的工作方式，去限制他们的事务处理规模，每隔若干行就进行提交，而不是当逻辑单元的工作已经执行完毕之后才进行提交。他们这样做是因为他们错误地认为他们正在降低系统资源的负载；而事实上，他们正在增加负载。如果提交一行需要耗费X个时间单元，那么提交1000行也会消耗相同的X个时间单元，而采用1000次提交1行的方式完成这项工作就需要额外运行1000*X个时间单元。如果只在必要的时候进行一次提交（当事务处理完成的时候），那么用户就不仅可以提高性能，而且可以减少对共享资源（日志文件，保护SGA内共享数据结构的锁定）的争用。

那么，为什么无论事务处理的规模如何，提交的响应时间都相当平缓呢？这是因为在数据库中进行提交之前，我们已经完成了所有实际工作，我们已经修改了数据库中的数据，完成了99.9%的工作。

当提交的时候，我们还要执行三个任务：

●为我们的事务处理生成 SCN（系统改变编号）。这是Oracle的内部时钟，可以称为数据库时间。SCN不是传统意义上的时钟，因为它不是随着时间失衡而递进。相反，它是在事务处理提交的时候递进，由Oracle在内部使用，以对事务处理排序。

●将所有剩余的已经缓冲的重做日志表项写入磁盘，并且将SCN记录到在重做日志文件中。这要由LGWR执行。

●释放我们的会话（以及所有正在等等我们所占有锁定的用户）所占有的所有锁定。

LGWR不会一直缓冲完成的所有工作，而是会随着操作的进行，在后台不断清理重做日志缓冲的内容，这非常类似于在PC机上运行的磁盘缓冲软件。它可以避免在清理所有的用户重做日志时，提交操作出现长时间等待现象。LGWR会在以下情况执行清理工作：

●每隔3秒

●当SGA中的日志缓冲超过了1/3的空间，或者包含了1MB或者更多的已缓冲数据

●进行任何事务处理提交

所以，即使我们长时间运行事务处理，也会有部分它所产生的已缓冲重做日志在提交之前写入了磁盘。

 

12.2.2             ROLL BACK处理

当我们进行回滚的时候，我们还有一些任务需要执行：

●撤销所有已经执行的改变。这要通过读取我们生成的UNDO数据，有效地反转我们的操作来完成。如果我们插入了一行，那么回滚就要删除它。如果我们更新了一行，回滚就要将其更新到原来的样子。如果我们删除了一行，就要重新插入它。

●释放我们的会话（以及正在等等我们已经锁定的行的用户）占用的所有锁定。

 

12.2.3             SAVEPOINT和ROLL BACK TO SAVEPOINT

SAVEPOINT可以让用户在事务处理中建立标记点。用户可以在单独的事务处理中拥有多个保存点。当使用ROLL BACK TO <SAVEPOINT NAME>的时候，它就可以让用户有选择地回滚更大的事务处理中的一组语句。

保存点是委有用的事务处理特性，它们可以让用户将单独的大规模事务处理分割成一系列较小的部分。拟执行的所有语句仍然是较大的事务处理的组成部分，用户可以将特定的语句组织到一起，将它们作为单独的语句进行回滚。

为了做到这一点，用户需要在开始函数的时候使用如下的SQL语句：

Savepoint function_name;

 

这里的function_name是用户的函数名称。如果用户在处理期间遇到错误，用户就只需使用：

Roll back to function_name;

 

 

12.2.4             SET TRANSACTION

这个语句可以使您设置事务处理的各种属性，例如它的隔离层（参考以下的隔离层次列表），它是只读还是可以进行读写，以及是否要使用特定的回滚段。

SET TRANSACTION语句必须是事务处理中使用的第一个语句。这就是说，必须在任何INSERT、UPDATE或者DELETE语句，以及任何其它可以开始事务处理的语句之前使用它。SET TRANSACTION的作用域只是当前的事务处理。

SET TRANSACTION语句可以让用户：

●规定事务处理隔离层次。

●规定为用户事务处理所使用的特定回滚段。

●命名用户事务处理。

 

我们将要在这里使用的重要SET TRANSACTION语句包括：

●SET TRANSACTION READ ONLY

●SET TRANSACTION READ WRITE

●SET TRANSACTION ISOLACTION LEVEL SERIALIZABLE

●SET TRANSACTION ISOLACTION LEVEL READ COMMITTED

注意：

SET TRANSACTION语句事实上都是互斥的。例如，如果您选择了READ ONLY，那么就不能为它选择READ WRITE、SERIALIZABLE或者READ COMMITTED。

 

1. READ ONLY

命令SET TRANSACTION READ ONLY将会做2件事，它会确保您无法执行修改数据的DML操作，例如INSERT、UPDATE或者DELETE。如下所示：

SQL> set transaction read only;

事务处理集。

SQL> update emp set ename=lower(ename);
update emp set ename=lower(ename)
       *
ERROR 位于第 1 行:
ORA-01456: 不可以在 READ ONLY 事务处理中执行插入/删除/更新操作

 

 

其效果很明显。而READ ONLY事务处理的另一个副作用则更为微妙。通过将事务处理设置为READ ONLY，我们就可以有效地将我们的数据库视图冻结到某个时间点。我的意思是，无论数据库中的其它会话如何工作，数据库在我们的面前都会是使用SET TRANSACTION语句时候样子。这有什么用处呢？我们假定现在是一个繁忙的工作日的下午1点。用户需要运行一个报表。这个报表将要执行几十条查询，从许多数据源中获取数据。所有这些数据都会相互关联，为了使其有意义，它们必须保持一致。换句话说，用户需要每个查询都看到“1点钟”的数据库。如果每个查询看不同时间点的数据库，那么我们报告中的结果就没有意义。

为了确保用户数据一致，用户应该锁定报表查询所需的所有表，防止其它用户更新它们。作为一种替代的方法，用户可以使用SET TRANSACTION READ ONLY语句，冻结“1点钟”的用户数据库视图。这可以得到两全其美的结果。

试验：冻结视图

(1)       为了运行这个示例，用户需要在SQL*Plus中打开3个会话。建立一个表。

SQL> create table t as select object_id from all_objects where rownum<=2000;
表已创建。

 

(2)       用以观察READ ONLY和READ WRITE事务处理区别。

时间	会话1	会话2	会话3	注释
T1	set transaction read only;
T2	select count(*) from t;	select count(*) from t;		2个事务处理都可以看到2000行
T3			delete from t where rownum<=500;	从T中删除500行，但是没有提交
T4	select count(*) from t;	select count(*) from t;		由于多版本的作用，所以这2个会话都会看到2000行
T5			commit;	让500个已删除的行永久删除
T6	select count(*) from t;	select count(*) from t;		会话1仍然会看到2000行，会话2现在将要看到1500行！到提交或者回滚为止，会话1将会一直看到200行
T7			insert into t select * from t;	对T的规模进行加倍，使其达到3000个记录
T8			commit;	使得改变可见
T9	select count(*) from t;	select count(*) from t;		会话1会继续看到2000行。会话2现在可以看到所有的3000行
T10	commit;
T11	select count(*) from t;			会话1现在也可以看到3000行。

 

1. READ WRITE

第二个命令SET TRANSACTION READ WRITE不会经常使用，因为它是默认设置。很少有必要使用这个命令，在这里包含它只是出于完整性的考虑。

 

1. ISOLATION LEVEL SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE与READ ONLY有一些类似。当使用了这个命令之后，无论是否出现改变，数据库都会为您进行“冻结”。就如同我们在READ ONLY事务处理中看到的那样，您可以完全隔离其它事务处理的影响。

1. ISOLATION LEVEL READ COMITTED

这个命令大体相当于将事务处理设置为READ WRITE，由于它是设置隔离层次时Oracle的默认操作模式，所以很少使用。如果您在会话的前面使用ALTER SESSION命令，将用户会话的事务处理的默认隔离层次从READ COMMITTED改变为SERIABLIZABLE，那么就可以会用到这个命令。使用ISOLATION LEVEL READ COMMITTED命令可以重置默认值。

12.2.5             SET CONSTRAINTS

在Oracle中，约束可以在DML语句执行后立即生效，也可以延迟到事务处理提交的时候才生效。SET CONSTRAINT语句可以让您在事务处理中设置延迟约束的强制模式。可以使用如下语法延迟单独的约束：

set constraint constraint_name defferred

 

或者，您也可以使用如下语法延迟所有约束：

set constraints all defferred

 

在这些命令中，您可以使用关键字IMMEDIATE代替DEFERRED，将他们的强制模式改回立即模式。为了看到实际的命令，我们可以使用一个简单的示例：

SQL> drop table t;

表已丢弃。

SQL> create table t
  2  (x int,
  3  constraint x_greater_than_zero check(x>0)
  4   deferrable initially immediate
  5  )
  6  /

表已创建。

SQL> insert into t values(-1);
insert into t values(-1)
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SCOTT.X_GREATER_THAN_ZERO)

 

不错，我们无法向X中插入值-1.现在，我们使用SET CONSTRAINT命令延迟约束的脸证，如下所示：

SQL> set constraint x_greater_than_zero deferred;
约束条件已设置。

SQL> insert into t values(-1);
已创建 1 行。

 

然而，我们无法在数据库中提交这条语句：

SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已重算
ORA-02290: 违反检查约束条件 (SCOTT.X_GREATER_THAN_ZERO)

 

有2种方法使用这个命令，或者规定一组约束名称，或者使用关键词ALL。例如，为了延迟约束X_GREATER_THAN_ZERO，我们可以使用：

set constraint x_greater_than_zero deferred;

 

我们也可以很容易地使用如下命令：

set constraints all deferred;

 

这里的区别是第2个版本使用了ALL，它会影响我们会话中的所有延迟约束，而不只是我们感兴趣的约束。而且，为了明确使用用户约束，也可以使用如下语句：

set constraint <constraint_name> immediate;

 

例如，我们可以在以上的救命中使用如下语句：

SQL> set constraint x_greater_than_zero deferred;

约束条件已设置。

SQL> insert into t values(-1);

已创建 1 行。

SQL> set constraint x_greater_than_zero immediate;
SET constraint x_greater_than_zero immediate
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SCOTT.X_GREATER_THAN_ZERO)


SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已重算
ORA-02290: 违反检查约束条件 (SCOTT.X_GREATER_THAN_ZERO)

 

Oracle会回滚我们的事务处理。通过SET CONSTRAINT <constraint_name> IMMEDIATE命令，我们就能够发现我们已经违反了一些约束，而我们的事务处理仍然有效。这个约束将会处理延迟模式，它不会立即检查。

12.3  事务处理的ACID属性

ACID是替代如下内容的首字母缩写：

●原子性（Atomicity）——事务处理要么全部进行，要么不进行。

●一致性（Consistency）——事务处理要将数据库从一种状态变成另一种状态。

●隔离性（Isolation）——在事务处理提交之前，事务处理的效果不能由系统中其它事务处理看到。

●持久性（Durability）——一旦提交了事务处理，它就永久生效。

 

12.3.1             原子性

在Oracle中，事务处理具有原子性。换句话说，或者提交所有的工作，或者什么工作都不提交。

12.3.2             一致性

这是非常重要的事务处理特性，任何事务处理都会将数据库从一种逻辑上的一致状态转变为另一种逻辑上的一致状态。这就是说，在事务处理开始之前，数据库的所有数据都会满足您已经施加给数据库的业务规则（约束）。

我们所使用的表和触发器如下所示：

SQL> drop table t;

表已丢弃。

SQL> create table t
  2  ( x int,
  3   constraint t_pk primary key(x)
  4  )
  5  /

表已创建。

SQL> create trigger t_trigger
  2  after update on T for each row
  3  begin
  4   dbms_output.put_line('Updated x='||:old.x||' to x='||:new.x);
  5  end;
  6  /

触发器已创建

 

现在，我们要向T中插入一些行：

SQL> insert into t values(1);

已创建 1 行。

SQL> insert into t values(2);

已创建 1 行。

 

这就结束了这个示例的设置。现在，物体 尝试对表T进行更新，将所有行都设置为数值2。

SQL> set serverout on
SQL> begin
  2   update t set x=2;
  3  end;
  4  /
Updated x=1 to x=2
Updated x=2 to x=2
begin
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.T_PK)
ORA-06512: 在line 2

 

现在，如果我们使用如下所示的成功语句：

SQL> begin
  2   update t set x=x+1;
  3  end;
  4  /
Updated x=1 to x=2
Updated x=2 to x=3

PL/SQL 过程已成功完成。

 

数据库在逻辑上保持了一致（它满足了所有的业务规则），所以语句将会成功。

试验：事务处理级别的一致性

(1)       我们要建立2个表PARENT和CHILD。CHILD表具有PARENT表上的外键，这个外键要定义为可延迟。

SQL> create table parent(pk int,
  2   constraint parent_pk primary key(pk));

表已创建。

SQL> create table child(fk,
  2   constraint child_fk foreign key(fk)
  3   references parent deferrable);

表已创建。

 

(2)       现在，我们使用一些数据生成这些表。

SQL> insert into parent values(1);

已创建 1 行。

SQL> insert into child values(1);

已创建 1 行。

 

(3)       现在，我们尝试更新PARENT表，改变它的主键值。

SQL> update parent set pk=2;
update parent set pk=2
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (SCOTT.CHILD_FK) - 已找到子记录日志

 

(4)       为了解决这个问题，我们只需告诉Oracle我们将要延迟约束CHILD_FK，也就是说，我们不想它在语句层次进行检查。

SQL> set constraints child_fk deferred;
约束条件已设置。

 

(5)       现在，我们可以正确地更新PARENT表

SQL> update parent set pk=2;
已更新 1 行。

SQL> select * from parent;
        PK
----------
         2

SQL> select * from child;
        FK
----------
         1
SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已重算
ORA-02292: 违反完整约束条件 (SCOTT.CHILD_FK) - 已找到子记录日志

 

(6)       我们再次进行尝试，看看如何让Oracle验证我们数据的逻辑一致性，而不进行回滚。我们首先会再次设置约束DEFERRED，然后再次更新父表，重做Oracle刚才撤销的工作。

SQL> set constraints child_fk deferred;
约束条件已设置。

SQL> update parent set pk=2;
已更新 1 行。

SQL> select * from parent;
        PK
----------
         2

SQL> select * from child;
        FK
----------
         1

 

(7)       我们现在将CHILD_FK约束设置为IMMEDIATE。这将导致Oracle立即验证我们业务规则的一致性。

SQL> set constraints child_fk immediate;
SET constraints child_fk immediate
*
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (SCOTT.CHILD_FK) - 未找到父项关键字

 

(8)       现在，我们可以更新CHILD记录，再次检查约束并且提交

SQL> update child set fk=2;
已更新 1 行。

SQL> set constraints child_fk immediate;
约束条件已设置。

SQL> commit;
提交完成。
SQL> select * from parent;
        PK
----------
         2

SQL> select * from child;
        FK
----------
         2

 

这将会导致一致性数据满足我们的约束。

12.3.3             隔离性

在给定用户隔离层次的情况下，使用相同的输入，采用相同的方式执行的相同的工作可能会导致不同的答案，这些隔离层次采用指定层次上许可（或者不符合规定）的三种“读取”方式进行定义。它们是：

●脏读取（Dirty read）——这种读取方式的含义同它听起来一样糟糕。您可以读取没有提交的“脏”数据。

●非可重复读取（Non-repeatable read）——这种方式意味着，如果用户在T1时刻读取了一行，在T2时刻再次读取一行，那么这个行就可能发生改变。例如，它可能已经被删除或者更新。

●影像读取（Phantom read）——这种方式意味着如果用户在T1时刻执行了一个查询，在T2时刻再次执行它，就可能会有影响结果的附加行加入到数据库中。在这种情况下，这种读取方式与非可重复读取有所不同，您已经读取的数据不会发生变化，而是会有比以前更多的满足查询条件的数据。

SQL92采用了这三种“读取”方式，并且基于它们的存在与否建立了4种隔离层次，见表13-2，它们是：

隔离层次	脏读取	非重复读取	影像读取
非提交读取（Read Uncommitted）	允许	允许	允许
提交读取（Read committed）	禁止	允许	允许
可重复读取（Repeatable Read）	禁止	禁止	允许
串行读取（Serializable）	禁止	禁止	禁止

 

12.3.4             持久性

持久性是数据库提供的最重要的特性之一。它可以确保一旦事务处理提交之后，它的改变就会永久生效。它们不会由于系统故障或者错误而“消失”。

 

12.4  并发控制

开发多用户、数据库驱动的应用 的主要挑战之一就是要最大化并发访问（多个用户同时访问数据），而且与此同时，还要确保每个用户都能在一致的方式下读取和修改数据。能够提供这些功能的锁定和并发控制是所有数据库的关键特性。

12.4.1             锁定

锁定(lock)是用来控制共享资源并发访问的机制。要注意，我们使用术语“共享资源”，而没有使用“数据库行”或者“数据库表”。Oracle确实可以在行级别上锁定表数据，但是它还可以在许多不同的层次上使用锁定，提供对各种资源的并发访问。

大多数情况下，锁对于作为开发者的用户来讲是透明的。当更新数据行的时候，我们不必对其进行锁定，Oracle会为我们完成这些工作。有些时候显式锁定是必须的，但是大多数时候，锁定会自行管理。

在单用户的数据库中，锁根本没有必要。因为根据定义，在这种情况下只有一个用户会修改信息。然而，当多用户访问或者修改数据或者数据结构的时候，具有可以防止并发访问修改相同信息的机制就分外重要。这就是锁定的价值。

1. 死锁

当2个用户战胜了2者都希望使用的资源时就会出现死锁。

Oracle处理死锁的方式非常简单。当检测出死锁的时候（它们就会立刻被检测出来），Oracle就会选择一个会话作为“牺牲者”。

试验：引发死锁

(1)       我们要建立2个所需要的表，如下所示：

SQL> create table a as select 1 x from dual;
表已创建。

SQL> create table b as select 1 x from dual;
表已创建。

 

(2)       现在，打开SQL*Plus会话，并且更新表A。

SQL> update a set x=x+1;

已更新 1 行。

 

(3)       新打开一个SQL*Plus会话，更新B表。

SQL> update b set x=x+1;

已更新 1 行。

 

(4)       在相同的会话，再一次更新a表

SQL> update a set x=x+1;

 

会发生死锁。

1. 锁定升级

可以在许多层次上进行锁定。用户可以在一行上拥有锁定，或者实际上也可以在一个表上拥有锁定。一些数据库（尽管没有Oracle）还可以在数据库层次上锁定数据。在一些数据库中，锁定是稀缺的资源，拥有许多锁定可以负面地影响系统的性能。在这些数据库中，你可以发现为了保存这些资源，用户的100个行级别的锁定会转换为一个表级别的锁定。这个过程就称为锁定升级（lock escalation）。它使得系统降低了用户锁定的粒度。

锁定升级不是数据库想要的属性。事实上，数据库支持升级锁定暗示着它的锁定机制存在固有的系统开销，管理上百个锁定是需要处理的重要工作。在Oracle中，拥有一个锁定或者上百万个锁定的系统开销是相同的——没有区别。

Oracle会使用锁定转换（lock conversion）或者提升（promotion）。它将会在尽可能级别上获取锁定，并且将锁定转换到更具限制性的级别上。例如，如果使用FOR UPDATE子句从表中选取一行，那么就会应用2个锁定。一个锁定会放置在您所选择的行上。这个锁定是的，它将会阻止其它的用户锁定指定行。另一个锁定要放置在表上，它是一个ROW SHARE TABLE锁。这个锁将会阻止其它会话获取表上的排它锁。

1. 遗失更新

阻塞（Blocking）会在一个会话拥有另一个会话正在请求的资源上的锁定时出现。正在进行请示的会话一直阻塞，直到占用资源的会话翻译锁定资源为止。例如，USER1、USER2同时查询，1分钟后，USER1修改了USER3的地址，USER2的会话仍然为1分钟前的数据，修改了USER3的电话号码，此时，USER2的旧数据替换了USER1修改的数据。

1. 悲观锁定

悲观锁定（Pessimistic locking）听起来不好，但是相信我，它实际并非如此。在使用悲观锁定的时候，您就是在表明“我相信，某人很有可能会改变我正在读取（并且最终要更新）的相同数据，因此，在我们花费时间，改变数据之前，我要锁定数据库中的行，防止其它会话更新它”。

为了完成这项工作，我们要使用类似如下语句的查询：

select *
from table
where column1 =  : old_column1
    and column2 =  : old.column2
    and...
    and primary_key =  : old_primary_key
    for update nowait;

 

所以我们将会从这个语句中得到三种结果：

●我们将要获取我们的行，并且对这个行进行锁定，防止被其它会话更新（但是阻止读取）。

●我们将会得到ORA-00054 Resource Busy错误。其它的人已经锁定了这个行，我们必须要等待它。

●由于某人已经对行进行了改变，所以我们将会返回0行。

由于我们要在进行更新之前对行进行锁定，所以这称为悲观锁定（pessimistic locking）。我们对行维持不被改动并不乐观（因此命名为悲观）。用户应用中的活动流程应该如下所示：

●不进行锁定查询数据

SQL> select empno,ename,sal from emp where deptno=10;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300

 

●允许终端用户“查询”数据。

SQL> select empno,ename,sal
  2  from emp
  3  where empno=7934
  4  and ename='MILLER'
  5  and sal=1300
  6  for update nowait
  7  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1300

 

●假如我们锁定了数据行，我们的应用就可以使用更新，提交改变

SQL> update emp
  2  set ename='miller'
  3  where empno=7934;
已更新 1 行。

SQL> commit;
提交完成。

 

在Oracle中，悲观锁定可以运行良好。它可以让用户相信他们正在屏幕上修改的数据当前由他们所“拥有”。如果用户要离开，或者一段时间没有使用记录，用户就要让应用释放锁定，或者也可以在数据库中使用资源描述文件设定空闲会话超时。这样就可以避免用户锁定数据行，然后回家过夜的问题。

1. 乐观锁定

第二种方法称为乐观锁定（optimistic locking），这经会在应用中同时保存旧值和新值。

我们首先作为用户SCOTT登录，并且打开2个SQL*Plus会话，我们要作为2个独立的用户（USER1和USER2）。然后，2个用户都要使用以下的SELECT语句。

USER1

SQL> select * from dept where deptno=10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

 

USER1更新

SQL> update dept
  2  set loc='BOSTON'
  3  where deptno=10;
已更新 1 行。

 

USER2更新

SQL> update dept
  2  set loc='ALBANY'
  3  where deptno=10;
已更新 1 行。

 

DEPTNO=10的LOC=’BOSTON’的记录不再存在，所以这里更新了0行。我们乐观地认为更新可以完成。

1. 悲观锁定和乐观锁定

悲观锁定最大的优势在于终端用户可以在他们花费时间进行修改之前，就能够发现他们的改变不能够进行。而在乐观锁定的情况中，终端用户将会花费大量的时间进行修改。

12.4.2             多版本和读取一致性

Oracle使用了多版本读取一致性并发模型。从根本讲，Oracle通过这个机制可以提供：

●读取一致性查询（Read-consistent queries）：能够产生结果与相应时间点一致的查询。

●非阻塞查询（Non-blocking queries）：查询不会被数据定入器阻塞。

SQL> drop table t;

表已丢弃。

SQL> create table t as select * from all_users;

表已创建。

SQL> set serverout on
SQL> declare
  2   cursor c1 is select username from t;
  3   l_username varchar2(30);
  4  begin
  5   open c1;
  6   delete from t;
  7   commit;
  8   loop 
  9    fetch c1 into l_username;
 10    exit when c1%notfound;
 11    dbms_output.put_line(l_username);
 12   end loop;
 13   close c1;
 14  end;
 15  /
SYS
SYSTEM
OUTLN
DBSNMP
WMSYS
ORDSYS
ORDPLUGINS
MDSYS
……

PL/SQL 过程已成功完成。

 

虽然从表中删除所有数据，我们甚至可以提交删除工作。这些行都不见了，我们也会这样认为。事实上，它们还可以通过游标获得。实际上，通过OPEN命令返回给我们的结果集在打开它的时候就已经预先确定了。我们在获取数据之前没有办法知道答案是什么，但是从游标的角度来看，结果是不变的。并不是Oracle在我们打开游标的时候，将所有以上数据复制到了其它的位置；事实上是删除操作作为我们保留了数据，将它们放置到了UNDO表空间或者回滚段中。

考虑一个进一步的示例。

建立一个ACCOUNTS的表。可以使用如下方式建立它：

SQL> create table accounts
  2  (
  3   account_id number,
  4   account_type varchar2(20),
  5   balance number
  6  );

表已创建。

 

精确快速地报告BALANCE总数。用户需要报告时间以及所有账户余额总和：

SQL> select current_timestamp,sum(balance) total_balance from accounts;

 

注意：

CURRENT_TIMESTAMP是Oracle 9i的内建列，它将会返回当前的日期和时间。在较早的Oracle版本中，用户需要使用SYSDATE。

然而，如果当我们进行处理的时候，这个数据库表上要应用成百上千个事务处理，那么问题就会稍微复杂一些。人们会从他们的储蓄账号向支票账号划转资金，他们还要进行取款、存款（我们可能是一个非常繁忙的银行）等。

我们假定ACCOUNTS表如表13-3所示：

表13-3 ACCOUNTS表示例

ACCOUNT_ID	ACCOUNT_TYPE	BALANCE
1234	储蓄	100
5678	支票	4371
2542	储蓄	6232
7653	储蓄	234
…<上百万行>
1234	支票	100

我们的示例将要有2个结局，一个结局将要展示当查询检测到锁定灵气的时候会出现什么情况，另一个结局展示当查询检测到数据已经发生改变，它不能够看到它们的时候会出现什么情况。

表13-4 查询遇到锁定数据

时间	事件	注释
T1	我们在会话1中开始查询W（现在有150）	它要开始对表进行读取，由于ACCOUNTS表非常大，所以这 需要花几分钟的时间。这个查询已经读取了“第一行”的ACCOUNT_ID为1234储蓄账号，但是还没有到达支票账号
T2	账号1234的所有者在ATM上开始事务处理
T3	账号1234的所有者选取TRANSFERFUNDS，并且选取从他们的支票账号向储蓄账号划转50美金（150-50）	数据库中的数据进行了更新，所以支票账号现在具有50美金，而储蓄账号具有150美金。工作还没有提交（但是已经存储了UNDO信息）
T4	我们查询最终到达ACCOUNT_ID为1234的支票账户行	这时发生什么呢？在其它大多数流行数据库中，答案是“查询将要等待”。而Oracle中不会这样
T5	由于检测到数据已经被T3时刻执行的工作所锁定，所以我们的查询将要接受到UNDO信息（数据“以前的”映像），并且使用T1时刻的数据映像	Oracle将要讲到锁定，它不会等待。我们查询将要在支票账号读取到100美金
T6	我们的报告生成
T7	ATM提交并且完成

本将操作的有意思的部分发生在以上时间链T5时刻

表13-5 查询遇到已经改变的数据

时间	事件	注释
T4	ATM会话进行提交，完成转账	资金发生转移，另外的会话现在可以看到在ACCOUNT_ID为1234的储蓄账号中有150美金，支票账号中有50美金
T5	我们查询最终到达ACCOUNT_ID为1234的支票账户行	它不再锁定，没有人正在更新它
T6	由于检测到在T1时刻之后数据已经进行了修改，我们的查询将会接收到UNDO信息，并且使用T1时刻的数据映像	我的查询将要再次为支票账号读取100美金
T6	我们的报告生成

简单来说，Oracle除了删除，在更新、增加中，能够把UNDO表空间或者回滚段中的数据读取出来，来为客户展示它数据的一致性。

12.5  小结

在本章中，我们首先了事务处理的构成。了解了事务处理控制语句，以及怎样和在什么时候使用它们。

当讨论并发控制的时候，我们讨论了2个重要而又复杂的主题，它们是锁定和多版本读取一致性。了解了死锁、跟踪文件分析死锁、数据库中避免阻塞等待的不同模式、悲观锁定和乐观锁定。

 

文章根据自己理解浓缩，仅供参考。

摘自：《Oracle编程入门经典》 清华大学出版社 http://www.tup.com.cn