Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理
当多个 DB2® 用户并发地访问一个数据库时,锁等待会导致响应变慢。锁等待是临时性的,因而难以捕捉。然而,当出现锁等待情形时,需要由数据库管理员负责确定锁等待的原因。本文通过例子演示如何使用用于 DB2 for Linux®, UNIX®, and Windows® 的 db2pddb2pdcfg 实用程序完成该任务。

用于锁监视的 db2pd 选项

db2pd 是用于监视各种 DB2 数据库活动以及故障排除的实用程序。它是从 DB2 V8.2 开始随 DB2 引擎发布的一个独立的实用程序,其外观和功能类似于 Informix onstat 实用程序。db2pd 是从命令行以一种可选的交互模式执行的。该实用程序运行得非常快,因为它不需要获取任何锁,并且在引擎资源以外运行(这意味着它甚至能在一个挂起的引擎上工作)。通过快照监视还可以收集 db2pd 提供的很多监视器数据,但是 db2pd 和快照监视的输出格式却有很大不同。这使 DBA 可以选择更符合用户需求的监视替代方法。本文关注用于锁监视的 db2pd 选项。有一篇由 Sam Poon 撰写的 developerWorks 文章(参见 参考资料 小节)对 db2pd 的监视功能作了更广泛的介绍。

下面的图展示了用于锁监视的 db2pd 选项:


图 1. 用于锁监视的 db2pd 选项
db2pd 锁选项
  • TranHdl:用于指定事务句柄,以便只监视由特定事务持有的锁。
  • showlocks:这个子选项将锁名称扩展成有意义的解释。对于一个行锁,该选项显示以下信息:表空间 ID、表 ID、分区 ID、页和槽。通过使用编目视图 SYSCAT.TABLES 上的一个查询,很容易将表空间 ID 和表 ID 映射到相应的表名:

    清单 1. 将表空间 ID、表 ID 映射到表模式、表名
                                SELECT TABSCHEMA, TABNAME
                    FROM SYSCAT.TABLES
                    WHERE TBSPACEID = tbspaceid AND TABLEID = tableid
                    

  • wait:如果指定 wait 子选项,则 db2pd 只显示事务当前正在等待的锁,以及对等待情形负责的锁。这个子选项大大简化了锁等待分析,因为它将输出限制为参与锁等待情形的锁。
  • db2pd databasefile 选项不是特定于锁监视的,但是适用于(几乎)所有 db2pd 调用。database 选项将 db2pd 返回的监视器数据限制为某个数据库的监视器数据。而 file 选项则允许定义一个文件,以便将 db2pd 输出写到该文件。

锁等待分析场景

接下来,我们开始使用前面介绍的 db2pd 选项来分析一个示例锁等待情形。为此,我们创建 DB2 SAMPLE 数据库:


清单 2. 创建 SAMPLE 数据库
                        db2sampl
            

用户 A 执行事务 A,以根据每个经理的薪水为他们提供 10% 的奖金:


清单 3. 事务 A 执行的更新操作
                        UPDATE EMPLOYEE
            SET BONUS = SALARY * 0.1
            WHERE JOB = 'MANAGER'
            

当事务 A 仍然在运行(因为用户 A 还没有使用 COMMITROLLBACK 终止该事务)时,用户 B 执行事务 B,以将每个雇员的薪水提高 2%:


清单 4. 事务 B 执行的更新操作
                        UPDATE EMPLOYEE
            SET SALARY = SALARY * 0.02
            

由于事务 B 没有完成,用户 B 请求 DBA 确定问题的原因。于是,DBA 调用 db2pd,看是否存在锁等待情形:


清单 5. 检查锁等待情形
                        db2pd -db sample -locks wait showlocks
            Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:33:05
            Locks:
            Address    TranHdl    Lockname                   Type       Mode Sts Owner      Dur
            0x050A0240 6          02000600050040010000000052 Row        ..X  W   2          1
            0x050A0DB0 2          02000600050040010000000052 Row        ..X  G   2          1
            HoldCount  Att  ReleaseFlg
            0          0x00 0x40000000   TbspaceID 2  TableID 6  PartitionID 0 Page 320 Slot 5
            0          0x00 0x40000000   TbspaceID 2  TableID 6  PartitionID 0 Page 320 Slot 5
            

db2pd 报告 ID 为 2 的表空间中一个 ID 为 6 的表上有一个行锁存在锁等待情形。通过检查 SYSCAT.TABLES,DBA 断定表 EMPLOYEE 上的确存在锁等待。


清单 6. 确定锁等待情形所涉及的表
                        SELECT TABSCHEMA, TABNAME
            FROM SYSCAT.TABLES
            WHERE TBSPACEID = 2 AND TABLEID = 6
            TABSCHEMA                               TABNAME
            --------------------------------------------------------------------------------
            FECHNER                                 EMPLOYEE
            1 record(s) selected.
            

对于事务 2(列 TranHdl),db2pd -locks 输出的 status 列(Sts)显示一个 “G”。G 代表 “granted”,意即事务句柄为 2 的事务拥有行锁。此外,列 Mode 表明,事务 2 持有的是一个 X 锁。等待的事务(列 Sts 中显示 “W”(“wait”)的事务)是句柄为 6 的事务。该事务正在与事务 2 请求同一个行上的 X 锁。通过查看 Owner 列(显示事务 2 是锁的所有者)和比较 Lockname(对于 db2pd -locks 中的两个条目是相同的),可以看到这一点。

接下来,DBA 将事务句柄映射到应用程序。这可以使用另一个 db2pd 选项 -transactions 来完成:


清单 7. 将事务句柄映射到应用程序
                        db2pd -db sample -transactions
            Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:34:47
            Transactions:
            Address    AppHandl [nod-index] TranHdl    Locks      State   Tflag      Tflag2
            0x05141880 30       [000-00030] 2          9          WRITE   0x00000000 0x00000
            0x05144880 34       [000-00034] 6          5          WRITE   0x00000000 0x00000
            

这个 db2pd 调用的输出表明,事务 2(列 TranHdl)是由应用程序 30(列 AppHandl)执行的,而事务 6 是由应用程序 34 执行的。这两个事务都正在对数据库执行写更改(列 State = WRITE)。所以 DBA 现在知道,应用程序 30 正持有应用程序 34 所等待的锁。

要获得关于锁等待情形涉及的应用程序的更多信息,可使用 -agents 选项调用 db2pd。该选项打印代表应用程序运行的代理的信息。注意,-agents 是一个实例级选项,这意味着不需要指定一个数据库(实际上,当指定一个数据库时,db2pd 打印出一条警告,并忽略 database 选项)。


清单 8. 获得关于应用程序和相应代理的信息
                        db2pd -agents
            Database Partition 0 -- Active -- Up 3 days 08:35:42
            Agents:
            Current agents:      2
            Idle agents:         0
            Active coord agents: 2
            Active agents total: 2
            Pooled coord agents: 0
            Pooled agents total: 0
            Address    AppHandl [nod-index] AgentTid   Priority   Type     State
            0x04449BC0 34       [000-00034] 3392       0          Coord    Inst-Active
            0x04449240 30       [000-00030] 2576       0          Coord    Inst-Active
            ClientPid  Userid   ClientNm Rowsread   Rowswrtn   LkTmOt DBName
            3916       USER_B   db2bp.ex 43         43         NotSet SAMPLE
            2524       USER_A   db2bp.ex 153        14         NotSet SAMPLE
            

db2pd -agents 输出中,DBA 可以看到使用应用程序 30 和 34 的用户的 ID(列 Userid):应用程序 30 是由 USER_A 执行的,而应用程序 34 是由 USER_B 执行的。只有当每个用户都有一个单独的数据库授权 ID 时,才可能出现那样的应用程序与用户 ID 之间的映射。通常,这对于在应用服务器上运行的应用程序是不可能的,因为这些应用程序使用连接池,连接不是个人化的。

关于每个应用程序的更多信息则由 db2pd 选项 -applications 提供:


清单 9. 获得关于应用程序的更多信息
                        db2pd -db sample -applications
            Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:36:14
            Applications:
            Address    AppHandl [nod-index] NumAgents  CoorTid    Status
            0x04AF8080 34       [000-00024] 1          3940       Lock-wait
            0x03841960 30       [000-00020] 1          2548       UOW-Waiting
            C-AnchID C-StmtUID  L-AnchID L-StmtUID  Appid
            195      1          0        0          *LOCAL.DB2.061122195637
            0        0          60       1          *LOCAL.DB2.061122195609
            

Status 列确认了 DBA 已经知道的一些东西:应用程序 34 处在锁等待状态。但是这并不新鲜,于是 DBA 将注意力集中在列 C-AnchID/C-StmtUIDL-AnchID/L-StmtUID 上。“C” 代表当前(current),“L” 代表最近(last)的锚 ID/语句 UID。这些 ID 可用于标识应用程序最近执行的 SQL 语句和应用程序当前执行的语句。为此,可以用 -dynamic 选项调用 db2pd。该选项显示数据库动态语句缓存的内容:


清单 10. 检查动态语句缓存的内容
                        db2pd -db sample -dynamic
            Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:37:39
            Dynamic Cache:
            Current Memory Used           187188
            Total Heap Size               1271398
            Cache Overflow Flag           0
            Number of References          2
            Number of Statement Inserts   3
            Number of Statement Deletes   0
            Number of Variation Inserts   2
            Number of Statements          3
            Dynamic SQL Statements:
            Address    AnchID StmtUID    NumEnv     NumVar     NumRef     NumExe
            0x056CEBD0 60     1          1          1          1          1
            0x056CE850 180    1          0          0          0          0
            0x056CFEA0 195    1          1          1          1          1
            Text
            UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'
            SET CURRENT LOCALE LC_CTYPE = 'de_DE'
            UPDATE EMPLOYEE SET SALARY = SALARY * 0.02
            Dynamic SQL Environments:
            Address    AnchID StmtUID    EnvID Iso QOpt Blk
            0x056CECD0 60     1          1     CS  5    B
            0x056D30A0 195    1          1     CS  5    B
            Dynamic SQL Variations:
            Address    AnchID StmtUID    EnvID VarID      NumRef     Typ
            0x056CEEB0 60     1          1     1          1          4
            0x056D3220 195    1          1     1          1          4
            Lockname
            010000000100000001003C0056
            01000000010000000100C30056
            

-applications 输出与 -dynamic 输出之间的映射很简单:

应用程序 34(处于锁等待状态)当前正在执行当前锚 ID 195 和当前语句 ID 1 所标识的 SQL 语句。在 db2pd -dynamic 输出的 Dynamic SQL Statements 部分中,那些 ID 可以映射到以下 SQL 语句:


清单 11. 应用程序 34 执行的 SQL 语句
                        UPDATE EMPLOYEE SET SALARY = SALARY * 0.02
            

持有锁的应用程序 30 最近执行的 SQL 语句是最近锚 ID 60 和最近语句 ID 1 所标识的 SQL 语句。那些 ID 可以映射到以下 SQL 语句:


清单 12. 应用程序 30 执行的 SQL 语句
                        UPDATE EMPLOYEE SET BONUS = SALARY * 0.1 WHERE JOB = 'MANAGER'
            

注意,db2pd -dynamic 输出包含另一个通常难以发现的有趣信息:Dynamic SQL Environments 部分的列 Iso 中显示了被执行的动态 SQL 语句的隔离级别(UR = Uncommitted Read,CS = Cursor Stability,RS = Read Stability,RR = Repeatable Read)。

我们来总结一下 DBA 就用户 B 的应用程序被挂起的原因有什么发现:

  • 挂起是由表 EMPLOYEE 上一个独占式的行锁导致的。
  • 持有锁的事务属于用户 A 执行的一个应用程序。而用户 B 的事务正在等待那个锁。
  • 两条有冲突的语句都是表 EMPLOYEE 上的 UPDATE 语句。

有了这些信息,DBA 可以开始采取一些必要的步骤来解决锁等待状况,例如建议用户 A 终止事务,或者强制关闭用户 A 的应用程序。此外,可以采取措施避免将来出现那样的状况,例如配置 DB2 控制器(governor),使之自动终止运行时间过长的事务。

在这个示例场景中,db2pd 被连续执行数次,每次使用一个单独的选项。现实中不会出现这样的情况。相反,db2pd 只被调用一次,调用时同时使用前面介绍的所有选项:


清单 13. 分析锁等待情形所需的带有所有选项的单个 db2pd 调用
                        db2pd -db sample -locks wait showlocks -transactions -agents -applications -dynamic
            -file db2pd.out -repeat 15 40
            

产生的输出由针对每个选项的输出组成,各部分输出之间的顺序与各选项在 db2pd 调用中的顺序一致。而且,请注意 db2pd 调用最后的 2 个附加选项:

  • -file 表明 db2pd 输出应该被写到一个文件。在示例调用中,输出被写到文件 db2pd.out 中。
  • -repeat 表明 db2pd 应该每隔 15 秒执行一次,共执行 40 次(即每隔 15 秒执行一次,共执行 10 分钟)。每次执行的输出被附加到 -file 选项指定的文件后面。

-file-repeat 选项对于在一段时间内监视数据库活动比较有用。对于锁等待分析,这两个选项可以帮助捕捉只存在一小段时间的锁等待情形。例如,如果数据库参数 LOCKWAIT 被设置为 20 秒,一个等待锁的事务在过了 20 秒的等待时间后被回滚。为了捕捉那样的锁等待情形,db2pd 的时间间隔必须设置为比 20 秒更短的时间间隔,例如例子中的 15 秒。

捕捉罕见的锁超时

有时候,锁等待情形会导致锁超时,而锁超时又会导致事务被回滚。锁等待导致锁超时所需的时间段由数据库配置参数 LOCKTIMEOUT 指定。锁超时分析最大的问题是,不知道下一次的锁超时何时发生。为了捕捉死锁,可以创建一个死锁事件监视器。每当出现死锁时,这个死锁事件监视器便写一个条目。但是,对于锁超时就没有类似的事件监视器。所以到 DB2 9® 为止,捕捉锁超时的惟一方法还是连续的 db2pd 或快照监视(对于 db2pd,和前面解释的一样,-file-repeat 选项可用于连续的锁监视)。

DB2 9 包含了一种新的机制,用于在数据库出现故障或发生事件时收集监视器数据:db2cos 脚本。为了捕捉锁超时事件,可以配置数据库,使之每当出现锁超时时启动 db2cos 脚本。在 db2cos 脚本中,和前面讨论的一样,可以以相同的选项调用 db2pd。我们来看一个示例场景,该场景演示了如何用 db2cos 脚本捕捉锁超时。

对于这个场景,假设 DBA 将数据库锁超时值设为 10 秒:


清单 14. 更新锁超时设置
                        UPDATE DB CFG FOR SAMPLE USING LOCKTIMEOUT 10
            

为了每当出现锁超时时启动 db2cos 脚本,DBA 调用 db2pdcfg 实用程序,如下所示:


清单 15. 使用 db2pdcfg 配置 db2cos 脚本的调用
                        db2pdcfg -catch locktimeout count=1
            

-catch 选项指定应该自动导致调用 db2cos 脚本的故障或事件。对于锁超时事件,可以指定字符串 locktimeout。或者,可以指定与锁超时相应的 SQL 错误码和原因码:


清单 16. 用于捕捉锁超时的另一种 db2pdcfg 调用
                        db2pdcfg -catch 911,68 count=1
            

除了一些字符串值和 SQL 代码之外,db2pdcfg 还接受内部 DB2 错误码。所以,用这种方式可以捕捉很多数据库故障和事件。锁超时事件只是使用 db2pdcfgdb2cos 的一个例子。

如果 count 子选项的值为 1,则表明当出现锁超时事件时应该执行 db2cos 脚本。

db2pdcfg 通过以下输出确认错误捕捉的设置:


清单 17. db2pdcfg 对错误捕捉设置的确认
                        Error Catch #1
            Sqlcode:        0
            ReasonCode:     0
            ZRC:            -2146435004
            ECF:            0
            Component ID:   0
            LockName:       Not Set
            LockType:       Not Set
            Current Count:  0
            Max Count:      1
            Bitmap:         0x4A1
            Action:         Error code catch flag enabled
            Action:         Execute sqllib/db2cos callout script
            Action:         Produce stack trace in db2diag.log
            

db2diag.log 报告中也包括错误捕捉设置。可以使用 db2diag 实用程序(用于检查 db2diag.log 内容的一个有用的实用程序)过滤 db2diag.log 文件,而不必在一个文本编辑器中打开它:


清单 18. 在 db2diag.log 中确认错误捕捉设置
                        db2diag -g funcname:=pdErrorCatch
            2006-12-18-13.37.25.177000+060 I727480H285        LEVEL: Event
            PID     : 4648                 TID  : 3948        PROC : db2syscs.exe
            INSTANCE: DB2                  NODE : 000
            FUNCTION: DB2 UDB, RAS/PD component, pdErrorCatch, probe:30
            START   : Error catch set for ZRC -2146435004
            

ZRC -2146435004 是用于锁超时的 DB2 内部错误码。可以通过下面的 db2diag 调用查看这些错误码:


清单 19. 使用 db2diag 查看 DB2 内部错误码的含义
                        db2diag -rc -2146435004
            

通过使用 db2pdcfg,数据库引擎现在被配置为每当出现锁超时时调用 db2cos 脚本。db2cos 脚本收集判别锁超时原因所需的所有监视器信息。为此,DBA 必须修改 db2cos 脚本,以便用已知的选项调用 db2pd。可以在下面的子目录中找到 db2cos 脚本:

  • Windows:DB2 install directory\BIN\db2cos.bat,例如 C:\Program Files\IBM\SQLLIB\BIN\db2cos.bat
  • UNIX/Linux:Instance owner home/sqllib/bin/db2cos

在 Microsoft Windows® 上,默认的 db2cos.bat 脚本看上去如下所示:


清单 20. Windows 上默认 db2cos.bat 的内容
                        setlocal
            :iterargs
            if %0. == . goto iterdone
            if /i %0. == INSTANCE. set INSTANCE=%1
            if /i %0. == DATABASE. set DATABASE=%1
            if /i %0. == TIMESTAMP. set TIMESTAMP=%1
            if /i %0. == APPID. set APPID=%1
            if /i %0. == PID. set PID=%1
            if /i %0. == TID. set TID=%1
            if /i %0. == DBPART. set DBPART=%1
            if /i %0. == PROBE. set PROBE=%1
            if /i %0. == FUNCTION. set FUNCTION=%1
            if /i %0. == REASON. set REASON=%1
            if /i %0. == DESCRIPTION. set DESCRIPTION=%1
            if /i %0. == DiAGPATH. set DIAGPATH=%1
            shift
            goto iterargs
            :iterdone
            if %DATABASE%. == . goto no_database
            db2pd -db %DATABASE% -inst >> %DIAGPATH%\db2cos%PID%%TID%.%DBPART%
            goto exit
            :no_database
            db2pd -inst >> %DIAGPATH%\db2cos%PID%%TID%.%DBPART%
            :exit
            

对于数据库级的事件或故障,默认的 db2cos 脚本用 -db-inst 选项调用 db2pd。DBA 用一个 db2pd 调用替换相应的行,该调用收集锁超时分析所需的监视器数据:


清单 21. 更改 db2cos 脚本,以收集用于锁超时分析的数据
                        if %DATABASE%. == . goto no_database
            db2pd -db %DATABASE% -locks wait -transactions -agents -applications -dynamic
            >> %DIAGPATH%\db2cos%PID%%TID%.%DBPART%
            goto exit
            

现在,db2cos 脚本已准备好,DBA 可以坐等下一次锁超时事件的发生。

假设像之前描述的那样,用户 A 与 B 之间发生相同的锁情形。但是,这一次设置了 LOCKTIMEOUT,因此过了 10 秒(LOCKTIMEOUT = 10)之后用户 B 的事务被回滚。用户 B 通知 DBA 回滚自己的事务,并且收到 SQL 错误消息 -911 和原因码 68(SQL code -911 / reason code 68 = locktimeout)。于是,DBA 检查通过自动调用 db2cos 脚本收集到的监视器数据。

首先,DBA 用锁超时内部错误码调用 db2diag,以确定锁超时发生的确切时间:


清单 22. 在 db2diag.log 中检查锁超时事件的时间点
                        db2diag -g data:=-2146435004
            2006-12-18-14.27.24.656000+060 I6857H409          LEVEL: Event
            PID     : 2968                 TID  : 2932        PROC : db2syscs.exe
            INSTANCE: DB2                  NODE : 000         DB   : SAMPLE
            APPHDL  : 0-21                 APPID: *LOCAL.DB2.061226132544
            AUTHID  : FECHNER
            FUNCTION: DB2 UDB, lock manager, sqlplnfd, probe:999
            DATA #1 : <preformatted>
            Caught rc -2146435004.  Dumping stack trace.
            

db2diag.log 条目显示,在 2006-12-18-14.27.24.656000 时发生了一次锁超时。由于 db2cos 脚本将它的输出写到 %DIAGPATH% 中的 db2cos%PID%%TID%.%DBPART% 文件中,DBA 有望在实例的诊断路径中找到一个 db2cos29682932.0 文件:

  • %DIAGPATH% = instance's diagnostic path = on Windows by default C:\Program Files\IBM\SQLLIB\DB2
  • %PID% = process ID = 2968(如 db2diag.log 条目中所示)
  • %TID% = thread ID = 2932(也显示在 db2diag.log 条目中)
  • %DBPART% = database partition = 0(在一个非分区数据库环境中)

那个文件的内容很像本文第一部分中逐步考察的那个 db2pd 监视器输出,DBA 可以通过它来识别锁超时的原因。

捕捉到锁超时后,DBA 可以通过 -catch clear 选项调用 db2pdcfg 来禁用 db2cos 脚本:


清单 23. 再次使用 db2pdcfg 清除错误捕捉设置
                        db2pdcfg -catch clear
            All error catch flag settings cleared.
            


结束语

本文演示了如何使用 db2pd 实用程序进行锁等待监视。本文借助一个示例场景展示了 DBA 如何通过检查不同 db2pd 选项的输出来识别并发问题的原因。从 DB2 9 开始,可以将 db2pd 与新的 db2cos 脚本一起使用,以便在出现锁超时事件时捕捉它们。您学习了如何配置出现锁超时事件时 db2cos 脚本的自动调用。本文还介绍了 db2diag 实用程序 —— 这是用于检查 db2diag.log 内容的一个有用的工具。



参考资料

学习

获得产品和技术
  • 下载免费试用版的 DB2 Enterprise 9

  • 现在可以免费使用 DB2。您可以下载 DB2 Express-C,这是为社区提供的免费版本的 DB2 Express Edition,它具有与 DB2 Express Edtion 相同的核心数据特性,并为构建和部署应用程序提供了坚实的基础。

  • 下载 IBM 试用软件,并获取来自 DB2®、Lotus®、Rational®、Tivoli® 和 WebSphere® 的应用程序开发工具和中间件产品。

讨论


关于作者

 

Dirk Fechner 在 IBM Software Group 中担任 IT 服务专家。他的专长领域是分布式平台上 DB2 UDB 的管理和应用程序开发。他拥有 5 年 DB2 UDB 方面的经验,也是一名 IBM 认证的高级 DBA 和 IBM 认证的应用程序开发人员。目前,他在 DaimlerChrysler 为管理员、开发人员和最终用户就各种 DB2 主题提供支持,包括管理任务、应用程序开发以及问题判别


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理