6.7 监控和解决锁定争用

当某个会话请求一行或一个对象上的锁定，但是由于其他会话已经获取了该行或对象上的排他锁而无法获得锁定时，这个会话将被挂起。这种现象称为锁定争用。在所有会话都排队等待获取锁定时，锁定争用会导致数据库的性能急剧恶化。某些作为正常活动结果的锁定争用可能无法避免，例如不同用户需要访问相同数据的应用。然而在许多情况下，锁定争用是由程序和系统设计导致的。

Oracle数据库提供了若干用于检测锁定争用的实用程序，并且有可能解决突发的锁定争用问题。“死锁(deadlock)”是锁定争用的一种特殊情况，并且通常由数据库本身自动解决。

6.7.1 锁定争用的原因

在各种业务中，经常会出现多个用户同时需要对相同记录进行写访问的情况。如果这个因素会限制系统的性能，那么唯一的解决方案是通过重新设计业务流程来开发一个更有效的业务模型。然而，除了业务数据处理过程中必需的某些锁定之外，应用程序设计中还会存在一些导致锁定问题更严重的错误。

运行时间很长的事务会导致某些问题。用户更新某行但是未提交变更就是一种显著的情况。用户可能在没有结束事务时就出去就餐。此时，如果用户使用诸如SQL*Plus之类的工具访问数据库，那么DBA无法阻止这种情况的发生。不过，使用编写得当的软件应当不会发生这种情况。这样的软件应当注意正好在更新操作之前施加锁定，在操作结束后(使用COMMIT或ROLLBACK命令)立即解除这个锁定。

编写得不太好的批处理过程，如果编写为长事务，也会带来问题。考虑财务软件中的名目账项总账。在会计术语中，从逻辑上看，某个财务软件的名目账项总账不能为八、九月份各半月的账目之和，因此月末结转就是一个业务事务，这个事务可能会影响数千个表中的几百万条记录，并且需要花费几个小时才能完成。如果回滚例程编写为一个用COMMIT结尾的事务，几百万条记录就要锁定好几个小时——但在会计术语中，这是应该的。优秀的程序设计应分组更新记录，定期执行，来避免这个问题，程序员还必须模拟事务的读取一致性，处理过程中途失败的情形。如果它是一个事务，就没有问题，数据库会回滚。如果它涉及许多小事务，它们就必须管理名目账项总账是八、九月份各半月的账目之和的情形。这些考虑不应出问题。程序员应记住长事务对系统有效性的影响，并据此设计系统。

第三方用户进程产品可能会带来过高的锁定级别。例如，某些应用程序开发工具经常会执行SELECT...FOR UPDATE命令，从而避免重新查询数据以及检查变更的必要性。其他某些产品无法进行记录层次上的锁定。此时，如果用户希望更新一行，那么这些工具会锁定一组行(大约数十条，甚至数百条)。如果应用程序使用诸如这些产品之类的工具进行编写，那么Oracle数据库会简单地执行被告知的操作，这将在业务进行期间带来许多不必要的锁定。如果察觉到这些软件应用了过多不必要的锁定，就可以查看其是否具有修改这种行为的配置选项。

最后，确保程序员了解数据库的功能。一个常见的问题是可重复的读取。考虑下面的例子：

        SQL> select * from regions;
         REGION_ID REGION_NAME
        ---------- -------------------------
                    1 Europe
                    2 Americas
                    3 Asia
                    4 Middle East and Africa
        SQL> select count(*) from regions;
          COUNT(*)
        ----------
                  5

这怎么可能？第一个查询(详细报表)显示了4行，但第二个查询(汇总报告)显示了5行。问题是在第一个查询期间，另一个会话插入并提交了第5行。解决该问题的一种方式是运行报表时锁定表，使其他会话挂起。更专业的方式是使用SET TRANSACTION READ ONLY语句。这将确保(不施加任何锁)会话不会在任何表上看到任何DML(无论提交与否)，直到用COMMIT或ROLLBACK中断只读事务为止。该机制基于撤消段的使用。

6.7.2 检测锁定争用

可以通过多种视图，来了解数据库中的锁定情况，Database Express为锁定的监控提供了一个图形界面。锁定争用是由许多用户同时访问相同数据而导致的。设计不当的软件会使这种问题更加严重，但是大体上锁定争用是正常数据库活动的一个部分。因此，DBA不可能完全解决锁定争用问题，而只能标识问题，并提醒系统和应用程序设计人员，在设计数据结构与程序时牢记锁定争用的影响。

锁定争用可以在V$SESSION视图中查看。这个视图给每个当前登录的会话显示了一行。唯一标识符是列的会话标识符(SID)。如果一个会话被另一个会话阻塞，阻塞会话的SID就会显示在列BLOCKING_SESSION上。图6-7显示的查询把V$SESSION加进来，使用了SID和BLOCKING_SESSION列。唯一列出的会话是被阻塞的会话，所有其他会话的BLOCKING_SESSION都是NULL，因此没有包含进来。

在图6-7中，用户MILLER阻塞了另外两个会话JW和KING。用户SCOTT阻塞了用户SYSTEM。为了在Database Express中标识锁定争用，请单击Performance标签，单击Performance Hub链接，再单击Current ADDM Findings标签。这会显示当前检测到的问题，如果单击Impact栏，就会显示细节。图6-8显示了SID=256的会话阻塞另外两个会话(SID=15和SID=251)的情形，且按行锁定排队。还可以理解队列：会话SID=15被SID为251和256的会话阻塞，所以关闭256(这是推荐1)不足以释放它。但是，关闭会话256会释放会话251。这样251就可以完成其工作，接着释放会话15。所有会话都标识一个3方会话ID，它由实例号(总是1，因为这不是群集数据库)、SID和SERIAL#组成。

6.7.3 解决锁定争用

大多数情况下，这种阻塞持续的时间非常短。阻塞会话会执行其事务，接着，被阻塞的会话就可以工作了。不过在紧急情况下，DBA可以通过终止过长时间拥有过多锁定的会话来解决锁定争用问题。强制终止某个会话时，这个会话拥有的任何锁定都会在回滚其作用的事务的同时被释放。此时，被锁定的会话将被释放并继续执行。

使用ALTER SYSTEM KILL SESSION命令可以终止一个会话。该命令用会话的SID和SERIAL#指定要终止的会话。SID在任意时刻都是唯一的，但用户登录和注销时，会重用SID。SID和SERIAL#确保其在实例的整个生命周期都是唯一的，所以在终止会话时必须指定它们两个。

        ALTER SYSTEM KILL SESSION ' sid , serial# ';

6.7.4 死锁：一种特殊情况

我们可能遇到如下场景：两个会话相互阻塞，这两个会话都被挂起，每个会话都在等待另一个会话释放其锁定。这种场景称为“死锁(deadlock)”。死锁是由不当的设计所导致的，数据库自身能够自动解决死锁问题。与死锁相关的信息会写入警报日志，并详细记录至某个跟踪文件。作为日常的一部分监视工作，我们将找出所发生的死锁现象，并将发生死锁的情况告知开发人员。

如果出现某个死锁，那么将挂起两个会话，但这种情况只是暂时的。其中一个会话将在几秒钟内监测到这个死锁，并且会回滚导致死锁问题的语句。上述操作将释放另一个会话，同时返回消息“ORA-00060 Deadlock detected”。编程人员必须在异常处理子句中捕获这条消息，从而采取适当的动作。

需要强调的是，死锁是一种程序设计错误。产生死锁的原因是代码试图执行在逻辑上不可行的操作。编写合理的代码不仅始终能够按照不会导致死锁的顺序请求锁定，而且还可以测试在请求锁定之前是否存在矛盾的锁定。

练习6-8 描述锁定机制和数据并发管理

本练习的第一部分，首先使用SQL*Plus 制造问题，然后检测和解决问题。第二部分学习如何处理死锁。

下面是演示锁定争用的步骤：

(1) 使用SQL*Plus，以用户SYSTEM的身份在三个会话中连接到数据库。

(2) 在第一个会话上，创建一个表。

        create table lockdemo as select * from all_users;

(3) 在第二个会话上，更新一行。

        update lockdemo set user_id=99 where username='SYS';

(4) 在第三个会话上，执行与步骤2相同的命令。会话会挂起。

(5) 在第一个会话上，运行如下查询，以确定哪个会话被阻塞：

        select username, sid, blocking_session from v$session
        where blocking_session is not null;

然后运行如下查询，来检索阻塞会话的必要细节：

        select username, sid, serial# from v$session
        where sid=&blocking_session;

出现提示时，输入阻塞会话的SID。

(6) 在第一个会话上，关闭阻塞会话。

        alter system kill session '&blocking_sid, &serial'

出现提示时，输入阻塞会话的SID和SERIAL #。

(7) 整理。

A．在第二个会话上，尝试运行任何SQL语句。收到消息“ORA-00028: your session has been killed。”

B．在第三个会话上，更新将成功完成。用COMMIT或ROLLBACK终止事务。

以下步骤演示了死锁：

(1) 在第一个会话上，更新一行。

        update lockdemo set user_id=99 where username='SYS';

(2) 在第三个会话上，更新第二行。

        update lockdemo set user_id=99 where username='SYSTEM';

(3) 在第一个会话上，尝试更新第二行。

        update lockdemo set user_id=99 where username='SYSTEM';

这个会话将挂起，因为该行已锁定。

(4) 在第三个会话上更新第一行，建立死锁。

        update lockdemo set user_id=99 where username='SYS';

这个会话将挂起，但在三秒内，第一个会话会被释放，并显示信息“ORA-00060: deadlock requested while waiting for resource” 。

(5) 从警报日志和跟踪文件中检索死锁的信息。

A．从操作系统提示行上，打开数据库的跟踪目录中的警告日志。该位置可以用如下查询确定：

        select value from v$diag_info where name='Diag Trace';

该文件是alert_SID.ora。文件中的最后一个条目如下(Windows例子)：

        Sat Jan 10 22:32:54 2015
        ORA-00060: Deadlock detected. More info in file
    D:\APP\ORACLE\diag\rdbms\coda\coda\trace\coda_ora_8364.trc.

B．用编辑器打开跟踪文件。在文件的顶部是如下关键信息：

        *** 2015-01-10 22:32:54.817
        DEADLOCK DETECTED ( ORA-00060 )

       [Transaction Deadlock]
        The following deadlock is not an ORACLE error. It is a
        deadlock due to user error in the design of an application
        or from issuing incorrect ad-hoc SQL.

请注意，此消息将责任推给开发人员。在文件的后面，会找到参与会话的SID和它们执行的、导致死锁的语句。

(6) 整理。在第一个会话上，执行一个ROLLBACK命令，回滚成功的更新。这将释放第三个会话，在这里也可以执行ROLLBACK。删除LOCKDEMO表。