ITPub博客

首页 > 数据库 > 数据库开发技术 > db2锁等待模拟和事件监控

db2锁等待模拟和事件监控

原创 数据库开发技术 作者:dbSeeSee 时间:2016-11-20 22:46:12 0 删除 编辑

1、关于锁等待

当两个应用程序竞争资源时,第一个应用程序在资源上加锁后,
第二个应用程序企图加锁时发现希望增加的锁与资源现有的锁不兼容,就出现锁等待
第二个应用程序挂起。为捕获锁等待事件,MON_LOCKWAIT 不能设置为 NONE,
当锁等待时间超过 MON_LW_THRESH 指定的值时则事件监视器捕获该事件

 

要观察锁等待,设置这个参数,让锁一直等待着,便于分析

 

db2 update db cfg for bkdb1 using LOCKTIMEOUT -1

 

 

锁等待模拟

db2 connect to dbname模拟两个APP

 

2.1 APP1更新数据

401号APP

 

[db2inst1@aspDB01 ~]$ db2 connect to bkdb1

 

   Database Connection Information

 

 Database server        = DB2/LINUXX8664 9.7.2

 SQL authorization ID   = DB2INST1

 Local database alias   = BKDB1

 

[db2inst1@aspDB01 ~]$ db2 +c "update tab1 set name='xxx' where id=1"

DB20000I  The SQL command completed successfully.

 

未提交

 

2.2 APP2更新同一行数据

 

402号APP

 

[db2inst1@aspDB01 ~]$ db2 connect to bkdb1

 

   Database Connection Information

 

 Database server        = DB2/LINUXX8664 9.7.2

 SQL authorization ID   = DB2INST1

 Local database alias   = BKDB1

 

[db2inst1@aspDB01 ~]$ db2 +c "update tab1 set name='yyy' where id=1"

 

 

2.3 等待信息

 

[db2inst1@aspDB01 ~]$ db2 "select count(*) from lockwaitevm"

 

1          

-----------

          4

 

  1 record(s) selected.

 

 使用db2pd查看APP状态

[db2inst1@aspDB01 ~]$ db2pd -apinfo 401 -db bkdb1

 

Database Partition 0 -- Database BKDB1 -- Active -- Up 0 days 00:02:00 -- Date 11/18/2016 16:20:32

 

Application :

  Address :                0x0000000201030080

  AppHandl [nod-index] :   401      [000-00401]

  TranHdl :                9         

  Application PID :        21127     

  Application Node Name :  aspDB01                                 

  IP Address:              n/a                                     

  Connection Start Time :  (1479457188)Fri Nov 18 16:19:48 2016

  Client User ID :         db2inst1

  System Auth ID :         DB2INST1

  Coordinator EDU ID :     105       

  Coordinator Partition :  0         

  Number of Agents :       1         

  Locks timeout value :    NotSet

  Locks Escalation :       No

  Workload ID :            1         

  Workload Occurrence ID : 2         

  Trusted Context :        n/a

  Connection Trust Type :  non trusted

  Role Inherited :         n/a     

  Application Status :     UOW-Waiting             

  Application Name :       db2bp               

  Application ID :         *LOCAL.db2inst1.161118081948                                    

  ClientUserID :           n/a

  ClientWrkstnName :       n/a

  ClientApplName :         n/a

  ClientAccntng :          n/a

  CollectActData:          N  

  CollectActPartition:     C  

  SectionActuals:          N  

 

--------------------------------------------------------------------------------

[db2inst1@aspDB01 ~]$ db2pd -apinfo 402 -db bkdb1

 

Database Partition 0 -- Database BKDB1 -- Active -- Up 0 days 00:02:03 -- Date 11/18/2016 16:20:35

 

Application :

  Address :                0x0000000200FD0080

  AppHandl [nod-index] :   402      [000-00402]

  TranHdl :                10        

  Application PID :        20949     

  Application Node Name :  aspDB01                                 

  IP Address:              n/a                                     

  Connection Start Time :  (1479457194)Fri Nov 18 16:19:54 2016

  Client User ID :         db2inst1

  System Auth ID :         DB2INST1

  Coordinator EDU ID :     106       

  Coordinator Partition :  0         

  Number of Agents :       1         

  Locks timeout value :    NotSet

  Locks Escalation :       No

  Workload ID :            1         

  Workload Occurrence ID : 3         

  Trusted Context :        n/a

  Connection Trust Type :  non trusted

  Role Inherited :         n/a     

  Application Status :     Lock-wait               

  Application Name :       db2bp               

  Application ID :         *LOCAL.db2inst1.161118081954                                    

  ClientUserID :           n/a

  ClientWrkstnName :       n/a

  ClientApplName :         n/a

  ClientAccntng :          n/a

  CollectActData:          N  

  CollectActPartition:     C  

  SectionActuals:          N  

 

  List of active statements :

   *UOW-ID :          1         

    Activity ID :     1         

    Package Schema :  NULLID  

    Package Name :    SQLC2H21

    Package Version :

    Section Number :  203       

    SQL Type :        Dynamic

    Isolation :       CS

    Statement Type :  DML, Insert/Update/Delete

    Statement :       update tab1 set name='yyy'

    where id=1

 
可以看到第2号APP处于lock-wait状态
 

 Blocked/Blocking Agent Chain                                                                                                                           |

-------------------------------------------------------------|

|   401->402  

 

不过以上信息很难抓取到 ;

下面我们通过事先建好的锁事件监控器查看锁等待信息 

 

提取监控数据

3.1 刷新关闭事件监控

[db2inst1@aspDB01 ~]$ db2 flush event monitor lockwaitevm

DB20000I  The SQL command completed successfully.

 

[db2inst1@aspDB01 ~]$ db2 set event monitor lockwaitevm state 0

DB20000I  The SQL command completed successfully.

 

 3.2 格式化监控表

[db2inst1@aspDB01 ~]$ db2 "call EVMON_FORMAT_UE_TO_TABLES ( 'LOCKING', NULL, NULL, NULL, NULL, NULL, 'RECREATE_FORCE', -1, 'SELECT * FROM lockwaitevm ORDER BY event_timestamp')"

 

  Return Status = 0

 

 

3.3 查看事件

[db2inst1@aspDB01 ~]$ db2 "select substr(event_id,1,2) id,substr(XMLID,1,30) as xml_id,char(EVENT_TYPE,12) type from lock_event"

 

ID XML_ID                         TYPE        

-- ------------------------------ ------------

11 db2LockEvent_11_LOCKWAIT_2016- LOCKWAIT    

 

  1 record(s) selected.

 

lock_event中有一条记录,事件类型就是LOCKWAIT

 

3.4 查看事件参与者

 

[db2inst1@aspDB01 ~]$ db2 "select substr(XMLID,1,25) as xml_id,

 substr(PARTICIPANT_NO,1,3) as p_no,

 PARTICIPANT_TYPE as p_type,

 substr(PARTICIPANT_NO_HOLDING_LK,1,3) as p_lk,

 substr(APPLICATION_HANDLE,1,3) as p_name,

 substr(TABLE_NAME,1,1) tabname,

substr(lock_mode,1,3) lock_mode,

substr(LOCK_MODE_REQUESTED,1,3) LOCK_MODE_REQ,substr(LOCK_OBJECT_TYPE,1,3) LOCK_OBJECT_TYPE from LOCK_PARTICIPANTS"

 

XML_ID   P_NO P_TYPE  P_LK P_NAME TABNAME LOCK_MODE LOCK_MODE_REQ LOCK_OBJECT_TYPE

------------------------- ---- ---------- ---- ------ ------- --------- -------

db2LockEvent_11_LOCKWAIT_ 1    Requester  2    402    T    5   5    ROW             

db2LockEvent_11_LOCKWAIT_ 2    Owner      -    401    -    -    -     -               

 

  2 record(s) selected.


 

LOCK_PARTICIPANTS中有两条记录

 

一条记录的 PARTICIPANT_TYPE 为 Requester,应用程序句柄为402 即 APP2,LOCK_OBJECT_TYPE为ROW,表示为行锁,

LOCK_MODE_REQUESTED为5,表示请求的锁类型为排他锁(X 锁)

而行上目前加的锁 LOCK_MODE 为 5 表示排他锁(X 锁)

 

表 LOCK_PARTICIPANTS 另一条记录 PARTICIPANT_TYPE 为 Owner,应用程序句柄为401 即 APP1。这意味着 APP1 为锁的拥有者,在行上持有了 X 锁导致应用程序2锁等待

  

3.5 查看事件活动

查询事件参与者的活动:

 

[db2inst1@aspDB01 ~]$ db2 "SELECT PARTICIPANT_NO, ACTIVITY_TYPE, substr(STMT_TEXT,1,40) sql_text FROM LOCK_PARTICIPANT_ACTIVITIES"

 

PARTICIPANT_NO ACTIVITY_TYPE SQL_TEXT                                

-------------- ------------- ----------------------------------------

             1 current       update tab1 set name='yyy' where id=1   

             1 past          update tab1 set name='xxx' where id=1   

 

  2 record(s) selected.

 

 

很明显,past就是已经执行的命令 持有锁,阻塞了current当前执行的命令,导致current等待;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29023300/viewspace-2128771/,如需转载,请注明出处,否则将追究法律责任。

上一篇: db2锁监控概述
请登录后发表评论 登录
全部评论

注册时间:2013-07-04

  • 博文量
    28
  • 访问量
    96229