理解 DB2 通用数据库中的锁定

泰山神

介绍

我们在进行客户支持时遇到最多的话题之一就是锁定。“为什么 DB2 锁住了这个表、行或者对象？” “这个锁会阻塞多长时间及为什么？” “为什么出现了死锁？” “我的锁请求在等待什么？”更仔细地分析一些常见的锁定示例可以说明 DB2 锁定策略背后的原则。

下面是关于 DB2 如何访问和锁定行的一个常见误解。当两位用户插入同一个表，然后第一位用户试图选择他刚插入的行时，就会出现问题。他会得到一个锁等待。这是预料之外的 —— 或者是用户在等待他自己的锁，而这是不可能的，或者是他在等待不相关的插入，而这没有意义。不管是什么原因，这个锁等待是一个意外，用户不知道如何发现到底发生了什么事情。

让我们看看在这种情况下如何分析锁定。

问题

假定在名为 LOCKTEST 的数据库中有以下两个表：

CREATE TABLE MAIN_TABLE(
        MAIN_JOIN_COLUMN VARCHAR(10) NOT NULL ,
        MAIN_DATA_COLUMN VARCHAR(20) NOT NULL )
       IN USERSPACE1 ;
ALTER TABLE MAIN_TABLE
   ADD CONSTRAINT MAINPKEY PRIMARY KEY
      (MAIN_JOIN_COLUMN,
       MAIN_DATA_COLUMN);
CREATE TABLE CHILD_TABLE  (
        CHILD_JOIN_COLUMN VARCHAR(10) NOT NULL ,
        CHILD_DATA_COLUMN VARCHAR(20) NOT NULL )
       IN USERSPACE1 ;
ALTER TABLE CHILD_TABLE
   ADD CONSTRAINT CHILDPKEY PRIMARY KEY
      (CHILD_JOIN_COLUMN);


并且用以下数据初始化这些表：

MAIN_TABLE:
MAIN_JOIN_COLUMN   MAIN_DATA_COLUMN
------------------ --------------------
1                  some existing data 1
2                  some existing data 2
3                  some existing data 3
4                  some existing data 4
5                  some existing data 5
CHILD_TABLE:
CHILD_JOIN_COLUMN CHILD_DATA_COLUMN
----------------- --------------------
1                 ONE
2                 TWO
3                 THREE
4                 FOUR
5                 FIVE


下面的一系列操作生成了一个好象是意外的锁等待：

   1. 在一个会话中，不提交：
      insert into main_table values（'1'，'deadlock 1'）
   2. 在第二个会话中，不提交：
      insert into main_table values（'1'，'deadlock 2'）
   3. 在第一个会话中，使用下列 select 语句：
      select main_table.main_join_column, child_table.child_data_column
      from main_table, child_table
      where main_table.main_join_column = child_table.child_join_column
      and main_table.main_data_column = 'deadlock 1'

这个 select 不会完成，它看来是在等待一个锁。因此一定是等待第二个会话的插入提交。但是并没有选择那一行。到底发生了什么？

由于在 main_table 的两列上有一个主键，并且选择在第一列上进行联接，并有一个针对第二列的谓词，您可能会认为 DB2 会从 main_table 中提取满足条件的一行，因而不会被第二个会话中的锁所阻塞。但是 DB2 并没有这样工作。

答案

为了理解发生的情况，让我们分析这个查询和存取计划（access plan）。要找出发生锁等待时持有哪个锁，我们使用 db2pd 实用程序。为了阅读方便，对下面的 db2pd 输出进行了修改，删除除了锁定的行以外的所有行。

/home/lorysa $db2pd -db locktest -locks show detail
Locks:
Address    TranHdl    Lockname                   Type       Mode Sts
Owner      Dur HldCnt     Att Rlse
0x402C07E0 3          000200020000000A0000000052 Row        ..X  G   
3          1   0          8   0x40 TbspaceID 2 TableID 2 RecordID 0xA
0x402C02E0 2          000200020000000A0000000052 Row        .NS  W   
3          1   0          0   0x0  TbspaceID 2 TableID 2 RecordID 0xA
0x402C03A8 2          00020002000000090000000052 Row        ..X  G   
2          1   0          8   0x40 TbspaceID 2 TableID 2 RecordID 0x9


可以看到 DB2 持有 tbspaceid 2 表空间中 tableid 2 表上的锁。现在找出这是哪个表：

/home/lorysa $db2 "select substr(tabschema,1,9) as tabschema,
         substr(tabname,1,12) as tabname, tableid, tbspaceid
         from syscat.tables
         where tbspaceid = 2 and tableid = 2"
TABSCHEMA TABNAME      TABLEID TBSPACEID
--------- ------------ ------- ---------
LORYSA    MAIN_TABLE         2         2
  1 record(s) selected.


上面的 db2pd 输出提供了被锁定的行的记录标识（RID）。值0xA实际上表示0x0000000A，RID 是由一个三字符页号（这里是0）和一个单字符 slot 标识（这里是0xA，即 10）组成的四字符字段。它告诉我们所关注的这一行是在表的第 0 页的 slot 10 中。每个数据页有最多 255 个 “slot”，它包含给定行在页中的偏移量。RID 通常描述为（页号；slot 数），即十进制记数的（0；10）、二进制记数的（0；A）。RID 惟一地标识了表中的一行。

输出表明等待的是 db2pd 输出中的行（0；A）上的锁，因为提供锁请求状态的 ‘Sts’ 列显示W，表示等待。其他锁的状态为G，表示授予（granted），因此它们被持有。

因此，总结有关的锁：

    * 具有 TranHdl 2 的代理： X 锁对于主表行（0；9）为 GRANTED（由于未提交的插入）
    * 具有 TranHdl 3 的代理： X 锁对于主表行（0；A）为 GRANTED（由于未提交的插入）
    * 具有 TranHdl 2 的代理： NS 锁对于主表行（0：A）为 WAITING（由于选择）

运行 select 的代理等待的行是值为('1', 'deadlock 2')的行，而不是它刚插入的行。可以推断由于插入的行上有 X 锁 —— 新插入的行独占性地锁定，直到插入被提交。同时，一个代理不能等待它自己，如果一个代理对于它已经拥有独占（X）锁的行请求一个共享（NS）锁，那么这个请求会被授予，因为已经拥有了一个具有足够或者更高模式的锁。因此问题是：如果选择具有值deadlock 1的行，为什么会等待新插入的、值为deadlock 2的行？要理解其中的原因，必须分析 select 的存取计划。

select main_table.main_join_column, child_table.child_data_column
from main_table, child_table
where main_table.main_join_column = child_table.child_join_column
  and main_table.main_data_column = 'deadlock 1'


存取计划基本上就是 DB2 访问满足查询的数据的路线图。可以用 DB2 的explain工具程序生成存取计划。用下面的命令创建包含explain结果的表：

db2 -tvf EXPLAIN.DDL

在实例主目录的 sqllib 目录中的EXPLAIN.DDL文件包含创建 explain 表所需要的 DDL 语句。然后可以用下面的命令解释语句：

db2 explain all forselect statement

db2exfmt工具就会生成存取计划。

关于explain和db2exfmt的细节的更多信息，请参阅DB2 Command Reference和DB2 SQL Reference，第 1 卷。

Total Cost:       43.712
   Query Degree:     1

              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
              3.44
             HSJOIN
             (   2)
             43.712
                2
          /-----+-----\
       86              3.44
     TBSCAN           TBSCAN
     (   3)           (   4)
     20.7463          22.2771
        1                1
       |                |
       86               86
TABLE: LORYSA    TABLE: LORYSA
   CHILD_TABLE     MAIN_TABLE


啊哈。在缺少当前统计的情况下，优化器选择对参与联接的两个表进行表扫描。因此要找到满足 select 语句中谓词的行，这个优化器必须将主表中的所有行上锁、读取它们的值并与谓词中提供的值进行比较。表扫描总是读取（并上锁）表中所有行。在 explain（db2exfmt）输出中，可以看到对计划中第 4 号表进行扫描的 sargable 谓词：

Predicates:
      ----------
      2) Sargable Predicate
         Relational Operator:       Equal (=)
         Subquery Input Required:   No
         Filter Factor:          0.04
         Predicate Text:
         --------------
         (Q2.MAIN_DATA_COLUMN = 'deadlock 1')


因此主表中所有 7 行都被读取（先上锁），data_column 中的值与deadlock 1比较，匹配的行参与联接。

现在让我们对两个表进行 runstats 以了解它对计划的影响。情况在这里有了有意思的变化。仍然会有锁等待，但是稍有不同：

在这里，发生锁等待时，被持有的锁是：

/home/lorysa $db2pd -db locktest -locks show detail
Locks:
Address    TranHdl    Lockname                   Type       Mode Sts
Owner      Dur HldCnt     Att Rlse
0x402C07E0 3          000200020000000A0000000052 Row        ..X  G   
3          1   0          8   0x40 TbspaceID 2 TableID 2 RecordID 0xA
0x402C02E0 2          000200020000000A0000000052 Row        .NS  W   
3          1   0          0   0x0  TbspaceID 2 TableID 2 RecordID 0xA
0x402C06A0 2          00020003000000040000000052 Row        .NS  G   
2          1   0          0   0x0  TbspaceID 2 TableID 3 RecordID 0x4
0x402C03A8 2          00020002000000090000000052 Row        ..X  G   
2          1   0          8   0x40 TbspaceID 2 TableID 2 RecordID 0x9


像前面一样总结：

    * 代理 2: X 锁对主表行 ID（0；9）为 GRANTED（由于未提交的插入）
    * 代理 3: X 锁对主表行 ID（0；A）为 GRANTED（由于未提交的插入）
    * 代理 2: NS 锁对子表行 ID（0.4）为 GRANTED（由于 select）
    * 代理 2: NS 锁对于主表行（0；A）为 WAITING（由于 select）

这里的存取计划是：

Total Cost:       17.0258
   Query Degree:     1
               Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                 1
              NLJOIN
              (   2)
              17.0258
                 1
          /------+------\
        1                  1
     IXSCAN             FETCH
     (   3)             (   4)
     2.05312            14.9727
        0                  1
       |               /---+---\
        5            1            5
INDEX: LORYSA    IXSCAN   TABLE: LORYSA
MAINPKEY         (   5)     CHILD_TABLE
                  1.64992
                     0
                    |
                     5
              INDEX: LORYSA
              CHILDPKEY


在这里我们使用了两个索引，并且有同样的谓词，这一次针对计划中编号为 3 的索引扫描：

Predicates:
      ----------
      2) Sargable Predicate
         Relational Operator:       Equal (=)
         Subquery Input Required:   No
         Filter Factor:          0.2
         Predicate Text:
         --------------
         (Q2.MAIN_DATA_COLUMN = 'deadlock 1')


注意完全相同的谓词。这是键。这是一个 sargable 谓词，而不是一个索引 sargable 谓词（即，它不能作为索引扫描的开始/停止键）。这是因为它针对索引的第二列。

索引 sarg（即开始/停止键）会限制由索引扫描返回的行数。这意味着：在值 X 处开始扫描，在值 Y 处停止，只返回值在 X 与 Y 之间的键。没有索引 sarg 时，索引扫描将使用索引访问数据，但是会返回索引中的所有键。

为了展示这一点，将它与计划中第 5 号索引扫描上使用的谓词相比较，它针对子表中惟一的索引列：

Predicates:
      ----------
      3) Start Key Predicate
         Relational Operator:       Equal (=)
         Subquery Input Required:   No
         Filter Factor:          0.2   
         Predicate Text:
         --------------
         (Q2.MAIN_JOIN_COLUMN = Q1.CHILD_JOIN_COLUMN)
      3) Stop Key Predicate
         Relational Operator:       Equal (=)
         Subquery Input Required:   No
         Filter Factor:          0.2
         Predicate Text:
         --------------
         (Q2.MAIN_JOIN_COLUMN = Q1.CHILD_JOIN_COLUMN)

因为主表上的谓词不能用于索引，所以我们必须读取表中所有行，使用索引，然后在将满足条件的行传给联接之前使用谓词。与以前一样，在判断谓词之前必须锁住这些行。

避免锁等待的方法

我们知道了所发生的事情，可以采取以下步骤改进并发性：

   1. 交换主表的主键中列的顺序（所以先是 MAIN_DATA_COLUMN，然后是 MAIN_JOIN_COLUMN）。这使谓词可以用于索引。
   2. 检查 DB2_EVALUNCOMMITTED 注册表变量。这个设置使 DB2 不必事先在 CS 或者 RS 隔离级别锁住一行才判断 sargable 谓词，这样在我们确定这一行满足谓词之前，它不会锁住。不过，访问未锁定的数据可能会有副作用（如，这个注册表设置改变了隔离级别），不是每个人都能接受这个副作用的，因此在使用这个功能之前对它加以了解是很重要的。
   3. 检查 DB2_SKIPINSERTED 注册表变量。这个变量控制未提交的插入在 CS 或者 RS 隔离级别下是否可以被游标忽略。启用这个变量会使未提交的插入被当成它们完全没被插入一样处理。同样，这种行为也许可以被接受，也许不能接受，因此了解它的隐含后果很重要。

      结束语

      在完成这个例子的过程中，您看到了不同的工具（db2pd、SQL、explain、db2exfmt）是如何帮助揭示真实情况的。这些工具所收集的信息使我们可以理解为什么锁会被持有，这又可帮助我们确定避免不必要的锁定的策略和技术。有了这些信息，任何需要了解锁定的人都可以将这里使用的技术和原则应用到自己的场景中，并得出类似的分析和建议。
转自：http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0511bond/