锁定问题

    讨论Oracle使用的各种类型的锁之前，先了解一些锁定问题会很有好处，其中很多问题都是因为应用设计不当，没有正确地使用（或者根本没有使用）数据库锁定机制产生的。 

6.2.1  丢失更新

丢失更新 （ lost update ）是一个经典的数据库问题。实际上，所有多用户计算机环境都存在这个问题。简单地说，出现下面的情况时（按以下所列的顺序），就会发生丢失更新：

(1) 会话 Session1 中的一个事务获取（查询）一行数据，放入本地内存，并显示给一个最终用户 User1 。

(2) 会话 Session2 中的另一个事务也获取这一行，但是将数据显示给另一个最终用户 User2 。

(3) User1 使用应用修改了这一行，让应用更新数据库并提交。会话 Session1 的事务现在已经 执行。

(4) User2 也修改这一行，让应用更新数据库并提交。会话 Session2 的事务现在已经执行。

这个过程称为“丢失更新”，因为第 (3) 步所做的所有修改都会丢失。例如，请考虑一个员工更新屏幕，这里允许用户修改地址、工作电话号码等信息。应用本身非常简单：只有一个很小的搜索屏幕要生成一个员工列表，然后可以搜索各位员工的详细信息。这应该只是小菜一碟。所以，编写应用程序时没有考虑锁定，只是简单的 SELECT 和 UPDATE 命令。

然后最终用户（ User1 ）转向详细信息屏幕，在屏幕上修改一个地址，单击 Save （保存）按钮，得到提示信息称更新成功。还不错，但是等到 User1 第二天要发出一个税表时，再来检查记录，会发现所列的还是原先的地址。到底出了什么问题？很遗憾，发生这种情况太容易了。在这种情况下， User1 查询记录后，紧接着另一位最终用户（ User2 ）也查询了同一条记录；也就是说，在 User1 读取数据之后，但在她修改数据之前， User2 也读取了这个数据。然后，在 User2 查询数据之后， User1 执行了更新，接到成功信息，甚至还可能再次查询看看是否已经修改。不过，接下来 User2 更新了工作电话号码字段，并单击 Save （保存）按钮，完全不知道他已经用旧数据重写（覆盖）了 User1 对地址字段的修改！之所以会造成 这种情况 ，这是因为应用开发人员编写的程序是这样的：更新一个特定的字段时，该记录的所有字段都会“刷新”（只是因为更新所有列更容易，这样就不用先得出哪些列已经修改，并且只更新那些修改过的列）。

可以注意到，要想发生这种情况， User1 和 User2 甚至不用同时处理记录。他们只要在大致同一时间处理这个记录就会造成丢失更新。

我发现，如果 GUI 程序员在数据库方面的培训很少（或者没有），编写数据库应用程序时就时常会冒出这个数据库问题。这些程序员了解了如何使用 SELECT 、 INSERT 、 UPDATE 和 DELETE 等语句后，就着手开始编写应用程序。如果开发出来的应用程序有上述表现，就会让用户完全失去对它的信心，特别是这种现象只是随机地、零星地出现，而且在受控环境中完全不可再生（这就导致开发人员误以为是用户的错误）。

许多工具可以保护你避免这种情况，如 Oracle Forms 和 HTML DB ，这些工具能确保：从查询记录的那个时刻开始，这个记录没有改变，而且对它执行任何修改时都会将其锁定，但是其他程序做不到这一点（如手写的 Visual Basic 或 Java 程序）。为了保护你不丢失更新，这些工具在后台做了哪些工作呢？或者说开发人员必须自己做哪些工作呢？实际上就是要使用某种锁定策略，共有两种锁定策略：悲观锁定或乐观锁定。

6.2.2  悲观锁定

用户在屏幕上修改值之前，这个锁定方法就要起作用。例如，用户一旦有意对他选择的某个特定行（屏幕上可见）执行更新，如单击屏幕上的一个按钮，就会放上一个行锁。

悲观锁定（ pessimistic locking ）仅用于 有状态 （ stateful ）或 有连接 （ connected ）环境，也就是说，你的应用与数据库有一条连续的连接，而且至少在事务生存期中只有你一个人使用这条连接。这是 20 世纪 90 年代中期客户 / 服务器应用中的一种流行做法。每个应用都得到数据库的一条直接连接，这条连接只能由该应用实例使用。这种采用有状态方式的连接方法已经不太常见了（不过并没有完全消失），特别是随着 20 世纪 90 年代中后期应用服务器的出现，有状态连接更是少见。

假设你在使用一条有状态连接，应用可以查询数据而不做任何锁定：

最后，用户选择他想更新的一行。在这个例子中，假设用户选择更新 MILLER 行。在这个时间点上（即用户还没有在屏幕上做任何修改，但是行已经从数据库中读出一段时间了），应用会绑定用户选择的值，从而查询数据库，并确保数据尚未修改。在 SQL*Plus 中，为了模拟应用可能执行的绑定调用，可以发出以下命令：

下面，除了简单地查询值并验证数据尚未修改外，我们要使用 FOR UPDATE NOWAIT 锁定这一行。应用要执行以下查询：

根据屏幕上输入的数据，应用将提供绑定变量的值（在这里就是 7934 、 MILLER 和 1300 ），然后重新从数据库查询这一行，这一次会锁定这一行，不允许其他会话更新；因此，这种方法称为 悲观锁定 （ pessimistic locking ）。在试图更新之前我们就把行锁住了，因为我们很悲观，对于这一行能不能保持未改变很是怀疑。

所有表都 应该 有一个主键（前面的 SELECT 最多会获取一个记录，因为它包括主键 EMPNO ），而且主键应该是不可变的（不应更新主键），从这句话可以得出三个结论：

q  如果底层数据没有改变，就会再次得到 MILLER 行，而且这一行会被锁定，不允许其他会话更新（但是允许其他会话读）。

q  如果另一个用户正在更新这一行，我们就会得到一个 ORA-00054 ： resource busy （ ORA-00054 ： 资源忙）错误。相应地，必须等待更新这一行的用户执行工作。

q  在选择数据和指定有意更新之间，如果有人已经修改了这一行，我们就会得到 0 行。这说明，屏幕上的数据是过时的。为了避免前面所述的丢失更新情况，应用需要 重新查询 （ requery ），并在允许在最终用户修改之前锁定数据。有了悲观锁定， User2 试图更新电话号码字段时，应用现在会识别出地址字段已经修改，所以会重新查询数据。因此， User2 不会用这个字段的旧数据覆盖 User1 的修改。

一旦成功地锁定了这一行，应用就会绑定新值，发出更新命令后，提交所做的修改：

现在可以非常安全地修改这一行。我们不可能覆盖其他人所做的修改，因为已经验证了在最初读出数据之后以及对数据锁定之前数据没有改变。

6.2.3  乐观锁定

第二种方法称为 乐观锁定 （ optimistic locking ），即把所有锁定都延迟到即将执行更新之前才做。换句话说，我们会修改屏幕上的信息而不要锁。我们很乐观，认为数据不会被其他用户修改；因此，会等到最后一刻才去看我们的想法对不对。

这种锁定方法在所有环境下都行得通，但是采用这种方法的话，执行更新的用户“失败”的可能性会加大。这说明，这个用户要更新他的数据行时，发现数据已经修改过，所以他必须从头再来。

可以在应用中同时保留旧值和新值，然后在更新数据时使用如下的更新语句，这是乐观锁定的一种流行实现：

在此，我们乐观地认为数据没有修改。在这种情况下，如果更新语句更新了 一 行，那我们很幸运；这说明，在读数据和提交更新之间，数据没有改变。但是如果更新了 零 行，我们就会失败；另外一个人已经修改了数据，现在我们必须确定应用中下一步要做什么。是让最终用户查询这一行现在的新值，然后再重新开始事务呢（这可能会让用户很受打击，因为这一行有可能又被修改了）？还是应该根据业务规则解决更新冲突，试图合并两个更新的值（这需要大量的代码）？

实际上，前面的 UPDATE 能避免丢失更新，但是确实有可能被阻塞，在等待另一个会话执行对这一行的 UPDATE 时，它会挂起。如果所有应用（会话）都使用乐观锁定，那么使用直接的 UPDATE 一般没什么问题，因为执行更新并提交时，行只会被锁定很短的时间。不过，如果某些应用使用了悲观锁定，它会在一段相对较长的时间内持有行上的锁，你可能就会考虑使用 SELECT FOR UPDATE NOWAIT ，以此来验证行是否未被修改，并在即将 UPDATE 之前锁定来避免被另一个会话阻塞。

实现乐观并发控制的方法有很多种。我们已经讨论了这样的一种方法，即应用本身会存储行的所有“前”（ before ）映像。在后几节中，我们将介绍另外三种方法，分别是：

q  使用一个特殊的列，这个列由一个数据库触发器或应用程序代码维护，可以告诉我们记录的“版本”

q  使用一个校验和或散列值，这是使用原来的数据计算得出的

q  使用新增的 Oracle 10g 特性 ORA_ROWSCN 。

1. 使用版本列的乐观锁定

这是一个简单的实现，如果你想保护数据库表不出现丢失更新问题，应对每个要保护的表增加一列。这一列一般是 NUMBER 或 DATE/TIMESTAMP 列，通常通过表上的一个行触发器来维护。每次修改行时，这个触发器要负责递增 NUMBER 列中的值，或者更新 DATE/TIMESTAMP 列。

如果应用要实现乐观并发控制，只需要保存这个附加列的值，而不需要保存其他列的所有“前”映像。应用只需验证请求更新那一刻，数据库中这一列的值与最初读出的值是否匹配。如果两个值相等，就说明这一行未被更新过。

下面使用 SCOTT.DEPT 表的一个副本来看看乐观锁定的实现。我们可以使用以下数据定义语言（ Data Definition Language ， DDL ）来创建这个表：

然后向这个表 INSERT （插入） DEPT 数据的一个副本：

以上代码会重建 DEPT 表，但是将有一个附加的 LAST_MOD 列，这个列使用 TIMESTAMP WITH TIME ZONE 数据类型（ Oracle9i 及以上版本中才有这个数据类型）。我们将这个列定义为 NOT NULL ，以保证这个列必须填有数据，其默认值是当前的系统时间。

这个 TIMESTAMP 数据类型在 Oracle 中精度最高，通常可以精确到微秒（百万分之一秒）。如果应用要考虑到用户的思考时间，这种 TIMESTAMP 级的精度实在是绰绰有余，而且数据库获取一行后，人看到这一行，然后修改，再向数据库发回更新，一般不太可能在不到 1 秒钟的片刻时间内执行整个过程。两个人在同样短的时间内（不到 1 秒钟）读取和修改同一行的几率实在太小了。

接下来，需要一种方法来维护这个值。我们有两种选择：可以由应用维护这一列，更新记录时将 LAST_MOD 列的值设置为 SYSTIMESTAMP ；也可以由触发器 / 存储过程来维护。如果让应用维护 LAST_MOD ，这比基于触发器的方法表现更好，因为触发器会代表 Oracle 对修改增加额外的处理。不过这并不是说：无论什么情况，你都要 依赖 所有应用在表中经过修改的所有位置上一致地维护 LAST_MOD 。所以，如果要由各个应用负责维护这个字段，就需要一致地验证 LAST_MOD 列未被修改，并把 LAST_MOD 列设置为当前的 SYSTIMESTAMP 。例如，如果应用查询 DEPTNO=10 这一行：

目前我们看到的是：

再使用下面的更新语句来修改信息。最后一行执行了一个非常重要的检查，以确保时间戳没有改变，并使用内置函数 TO_TIMESTAMP_TZ ( TZ 是 TimeZone 的缩写，即时区）将以上 select （选择）得到的串转换为适当的数据类型。另外，如果发现行已经更新，以下更新语句中的第 3 行会把 LAST_MOD 列更新为当前时间：

可以看到，这里更新了一行，也就是我们关心的那一行。在此按主键（ DEPTNO ）更新了这一行，并验证从最初读取记录到执行更新这段时间， LAST_MOD 列未被其他会话修改。如果我们想尝试再更新这个记录，仍然使用同样的逻辑，不过没有获取新的 LAST_MOD 值，就会观察到以下情况：

注意到这一次报告称“ 0 rows updated ”（更新了 0 行），因为关于 LAST_MOD 的谓词条件不能满足。尽管 DEPTNO 10 还存在，但是想要执行更新的那个时刻的 LAST_MOD 值与查询行时的时间戳值不再匹配。所以，应用知道，既然未能修改行，就说明数据库中的数据已经（被别人）改变，现在它必须得出下一步要对此做什么。

不能总是依赖各个应用来维护这个字段，原因是多方面的。例如，这样会增加应用程序代码，而且只要是表中需要修改的地方，都必须重复这些代码，并正确地实现。在一个大型应用中，这样的地方可能很多。另外，将来开发的每个应用也必须遵循这些规则。应用程序代码中很可能会“遗漏”某一处，未能适当地使用这个字段。因此，如果应用程序代码本身不负责维护这个 LAST_MOD 字段，我相信应用也不应负责检查这个 LAST_MOD 字段（如果它确实能执行检查，当然也能执行更新！）。所以在这种情况下，我建议把更新逻辑封装到一个存储过程中，而不要让应用直接更新表。如果无法相信应用能维护这个字段的值，那么也无法相信它能正确地检查这个字段。存储过程可以取以上更新中使用的绑定变量作为输入，执行同样的更新。当检测到更新了 0 行时，存储过程会向客户返回一个异常，让客户知道更新实际上失败了。

还有一种实现是使用一个触发器来维护这个 LAST_MOD 字段，但是对于这么简单的工作，我建议还是避免使用触发器，而让 DML 来负责。触发器会引入大量开销，而且在这种情况下没有必要使用它们。

2. 使用校验和的乐观锁定

这与前面的版本列方法很相似，不过在此要使用基数据本身来计算一个“虚拟的”版本列。为了帮助解释有关校验和或散列函数的目标和概念，以下引用了 Oracle 10g PL/SQL Supplied Packages Guide 中的一段话（尽管现在还没有介绍如何使用 Oracle 提供的任何一个包！）：

单向散列函数取一个变长输入串（即数据），并把它转换为一个定长的输出串（通常更小），这个输出称为散列值（hash value ）。散列值充当输入数据的一个惟一标识符（就像指纹一样）。可以使用散列值来验证数据是否被修改。

需要注意，单向散列函数只能在一个方向上应用。从输入数据计算散列值很容易，但是要生成能散列为某个特定值的数据却很难。

散列值或校验和并非真正惟一。只能说，通过适当地设计，能使出现冲突的可能性相当小，也就是说，两个随机的串有相同校验和或散列值的可能性极小，足以忽略不计。

与使用版本列的做法一样，我们可以采用同样的方法使用这些散列值或校验和，只需把从数据库读出数据时得到的散列或校验和值与修改数据前得到的散列或校验和值进行比较。在我们读出数据之后，但是在修改数据之前，如果有人在这段时间内修改了这一行的值，散列值或校验和值往往会大不相同。

有很多方法来计算散列或校验和。这里列出其中的 3 种方法，分别在以下 3 个小节中介绍。所有这些方法都利用了 Oracle 提供的数据库包：

q  OWA_OPT_LOCK.CHECKSUM ： 这个方法在 Oracle8i 8.1.5 及以上版本中提供。给定一个串，其中一个函数会返回一个 16 位的校验和。给定 ROWID 时，另一个函数会计算该行的 16 位校验和，而且同时将这一行锁定。出现冲突的可能性是 65 536 分之一（ 65 536 个串中有一个冲突，这是假警报的最大几率）。

q  DBMS_OBFUSCATION_TOOLKIT.MD5 ： 这个方法在 Oracle8i 8.1.7 及以上版本中提供。它会计算一个 128 位的消息摘要。冲突的可能性是 3.4028E+38 分之一（非常小）。

q  DBMS_CRYPTO.HASH ： 这个方法在 Oracle 10gRelease 1 及以上版本中提供。它能计算一个 SHA-1 （安全散列算法 1 ， Secure Hash Algorithm 1 ）或 MD4/MD5 消息摘要。建议你使用 SHA-1 算法。

注意    很多编程语言中都提供了一些散列和校验和函数，所以还可以使用数据库之外的散列和校验和函数。

下面的例子显示了如何使用 Oracle 10g 中的 DBMS_CRYPTO 内置包来计算这些散列 / 校验和。这个技术也适用于以上所列的另外两个包；逻辑上差别不大，但是调用的 API 可能不同。

下面在某个应用中查询并显示部门 10 的信息。查询信息之后，紧接着我们使用 DBMS_CRYPTO 包计算散列。这是应用中要保留的“版本”信息：

可以看到，散列值就是一个很大的 16 进制位串。 DBMS_CRYPTO 的返回值是一个 RAW 变量，显示时，它会隐式地转换为 HEX 。这个值会在更新前使用。为了执行更新，需要在数据库中获取这一行，并按其现在的样子 锁定 ，然后计算所获取的行的散列值，将这个新散列值与从数据库读出数据时计算的散列值进行比较。上述逻辑表示如下（当然，在实际中，可能使用绑定变量而不是散列值直接量）：

更新后 ，重新查询数据，并再次计算散列值，此时可以看到散列值大不相同。如果有人抢在我们前面先修改了这一行，我们的散列值比较就不会成功：

这个例子显示了如何利用散列或校验和来实现乐观锁定。要记住，计算散列或校验和是一个 CPU 密集型操作（相当占用 CPU ），其计算代价很昂贵。如果系统上 CPU 是稀有资源，在这种系统上就必须充分考虑到这一点。不过，如果从“网络友好性”角度看，这种方法会比较好，因为只需在网络上传输相当小的散列值，而不是行的完整的前映像和后映像（以便逐列地进行比较），所以消耗的资源会少得多。下面最后一个例子会使用一个新的 Oracle 10g 函数 ORA_ROWSCN ，它不仅很小（类似于散列），而且计算时不是 CPU 密集的（不会过多占用 CPU ）。

3. 使用 ORA_ROWSCN 的乐观锁定

从 Oracle 10gRelease 1 开始，你还可以使用内置的 ORA_ROWSCN 函数。它的工作与前面所述的版本列技术很相似，但是可以由 Oracle 自动执行，而不需要在表中增加额外的列，也不需要额外的更新 / 维护代码来更新这个值。

ORA_ROWSCN 建立在内部 Oracle 系统时钟（ SCN ）基础上。在 Oracle 中，每次提交时， SCN 都会推进（其他情况也可能导致 SCN 推进，要注意， SCN 只会推进，绝对不会后退）。这个概念与前面在获取数据时得到 ORA_ROWSCN 的方法是一样的，更新数据时要验证 SCN 未修改过。之所以我会强调这一点（而不是草草带过），原因是除非你创建表时支持在行级维护 ORA_ROWSCN ，否则 Oracle 会在块级维护。也就是说，默认情况下，一个块上的多行会共享相同的 ORA_ROWSCN 值。如果更新一个块上的某一行，而且这个块上还有另外 50 行，那么这些行的 ORA_ROWSCN 也会推进。这往往会导致许多假警报，你认为某一行已经修改，但实际上它并没有改动。因此，需要注意这一点，并了解如何改变这种行为。

我们想查看这种行为，然后进行修改，为此还要使用前面的小表 DEPT ：

现在可以观察到每一行分别在哪个块上（在这种情况下，可以假设它们都在同一个文件中，所以如果块号相同，就说明它们在同一个块上）。我使用的块大小是 8 KB ，一行的宽度大约 3 550 字节，所以我预料这个例子中每块上有两行：

不错，我们观察的结果也是这样，每块有两行。所以，下面来更新块 20972 上 DEPTNO = 10 的那一行：

接下来观察到， ORA_ROWSCN 的结果在块级维护。我们只修改了一行，也只提交了这一行的修改，但是块 20972 上两行的 ORA_ROWSCN 值都推进了：

如果有人读取 DEPTNO=20 这一 行，看起来这一行已经修改了，但实际上并非如此。块 20973 上的行是“安全”的，我们没有修改这些行，所以它们没有推进。不过，如果更新其中任何一行，两行都将推进。所以现在的问题是：如何修改这种默认行为。遗憾的是，我们必须启用 ROWDEPENDENCIES 再重新创建这个段。

Oracle9i 为数据库增加了行依赖性跟踪，可以支持推进复制，以便更好地并行传播修改。在 Oracle 10g 之前，这个特性只能在复制环境中使用；但是从 Oracle 10g 开始，还可以利用这个特性用 ORA_ROWSCN 来实现一种有效的乐观锁定技术。它会为每行增加 6 字节的开销（所以与自己增加版本列的方法（即 DIY 版本列方法）相比，并不会节省空间），而实际上，也正是因为这个原因，所以需要重新创建表，而不只是简单地 ALTER TABLE ：必须修改物理块结构来适应这个特性。

下面重新建立我们的表，启用 ROWDEPENDENCIES 。可以使用 DBMS_REDEFINITION 中（ Oracle 提供的另一个包）的在线重建功能来执行，但是对于一个这么小的任务，我们还是从头开始更好一些：

又回到前面：两个块上有 4 行，它们都有相同的 ORA_ROWSCN 值。现在，更新 DEPTNO=10 的那一行时：

查询 DEPT 表时应该能观察到以下结果：

此时，只有 DEPTNO = 10 这一 行的 ORA_ROWSCN 改变，这正是我们所希望的。现在可以依靠 ORA_ROWSCN 来为我们检测行级修改了。

将 SCN 转换为墙上时钟时间

使用透明的 ORA_ROWSCN 列还有一个好处：可以把 SCN 转换为近似的墙上时钟时间（有 +/–3 秒的偏差），从而发现行最后一次修改发生在什么时间。例如，可以执行以下查询：

在此可以看到，在表的最初创建和更新 DEPTNO = 10 行之间，我等了大约 3 分钟。不过，从 SCN 到墙上时钟时间的这种转换有一些限制：数据库的正常运行时间只有 5 天左右。例如，如果查看一个“旧”表，查找其中最旧的 ORA_ROWSCN （注意，在此我作为 SCOTT 登录；没有使用前面的新表）：

如果我试图把这个 SCN 转换为一个时间戳，可能看到以下结果（取决于 DEPT 表有多旧！）：

所以从长远看不能依赖这种转换。

6.2.4  乐观锁定还是悲观锁定？

那么哪种方法最好呢？根据我的经验，悲观锁定在 Oracle 中工作得非常好（但是在其他数据库中可能不是这样），而且与乐观锁定相比，悲观锁定有很多优点。不过，它需要与数据库有一条有状态的连接，如客户 / 服务器连接，因为无法跨连接持有锁。正是因为这一点，在当前的许多情况下，悲观锁定不太现实。过去，客户 / 服务器应用可能只有数十个或数百个用户，对于这些应用，悲观锁定是我的不二选择。不过，如今对大多数应用来说，我都建议采用乐观并发控制。要在整个事务期间保持连接，这个代价太大了，一般无法承受。

在这些可用的方法中，我使用哪一种呢？我喜欢使用版本列方法，并增加一个时间戳列（而不只是一个 NUMBER ）。从长远看，这样能为我提供一个额外的信息：“这一行最后一次更新发生在什么时间？”所以意义更大。而且与散列或校验和方法相比，计算的代价不那么昂贵，在处理 LONG 、 LONG RAW 、 CLOB 、 BLOB 和其他非常大的列时，散列或校验和方法可能会遇到一些问题，而版本列方法则没有这些问题。

如果必须向一个表增加乐观并发控制，而此时还在利用悲观锁定机制使用这个表（例如，客户 / 服务器应用都在访问这个表，而且还在通过 Web 访问），我则倾向于选择 ORA_ROWSCN 方法。这是因为，在现有的遗留应用中，可能不希望出现一个新列，或者即使我们另外增加一步把这个额外的列隐藏起来，为了维护这个列，可能需要一个必要的触发器，而这个触发器的开销非常大，这是我们无法承受的。 ORA_ROWSCN 技术没有干扰性，而且在这个方面是轻量级的（当然，这是指我们执行表的重建之后）。

散列 / 校验和方法在数据库独立性方面很不错，特别是如果我们在数据库之外计算散列或校验和，则更是如此。不过，如果在中间层而不是在数据库中执行计算，从 CPU 使用和网络传输方面来看，就会带来更大的资源使用开销。