DB2和Oracle的并发控制（锁）比较四

文章作者 100test 发表时间 2007:09:25 13:12:17
来源 100Test.Com百考试题网

db2 get snapshot for database on dbname |grep -i locks（UNIX,LINUX平台）

Locks held currently = 7 Lock waits = 75 Time database waited on locks (ms) = 82302438 Lock list memory in use (Bytes) = 20016 Deadlocks detected = 0 Lock escalations = 8 Exclusive lock escalations = 8 Agents currently waiting on locks = 0 Lock Timeouts = 20

db2 get snapshot for database on dbname |find /i "locks"(NT平台)
db2 get snapshot for locks for applications agentid 45(注：45为应用程序句柄)

Application handle = 45 Application ID = *LOCAL.db2dev.030815021827 Sequence number = 0001 Application name = tp Authorization ID = SAPR3 Application status = UOW Waiting Status change time = Application code page = 819 Locks held = 7 Total wait time (ms) = 0 List Of Locks Lock Object Name = 1130185838 Node number lock is held at = 0 Object Type = Key Value Tablespace Name = PSAPBTABD Table Schema = SAPR3 Table Name = TPLOGNAMES Mode = X Status = Granted Lock Escalation = NO Lock Object Name = 14053937 Node number lock is held at = 0 Object Type = Row Tablespace Name = PSAPBTABD Table Schema = SAPR3 Table Name = TPLOGNAMES Mode = X Status = Granted Lock Escalation = NO

也可以执行下列表函数（注：在DB2 V8之前只能通过命令，DB2 V8后可以通过表函数，推荐使用表函数来进行锁的监控）

db2 0select * from table(snapshot_lock(DBNAME,-1)) as locktable监控锁信息
db2 0select * from table(snapshot_lockwait(DBNAME,-1) as lock_wait_table监控应用程序锁等待的信息

4.2 事件监控方式：

当使用事件监控器进行锁的监控时候，只能监控死锁(死锁的产生是因为由于锁请求冲突而不能结束事务，并且该请求冲突不能够在本事务内解决。通常是两个应用程序互相持有对方所需要的锁，在得不到自己所需要的锁的情况下，也不会释放现有的锁)的情况，具体步骤如下：

db2 create event monitor dlock for deadlocks with details write to file $HOME/dir
db2 set event monitor dlock state 1
db2evmon -db dbname -evm dlock看具体的死锁输出（如下图）

Deadlocked Connection ... Deadlock ID: 4 Participant no.: 1 Participant no. holding the lock: 2 Appl Id: G9B58B1E.D4EA.08D387230817 Appl Seq number: 0336 Appl Id of connection holding the lock: G9B58B1E.D573.079237231003 Seq. no. of connection holding the lock: 0126 Lock wait start time: 06/08/2005 08:10:34.219490 Lock Name : 0x000201350000030E0000000052 Lock Attributes : 0x00000000 Release Flags : 0x40000000 Lock Count : 1 Hold Count : 0 Current Mode : NS - Share (and Next Key Share) Deadlock detection time: 06/08/2005 08:10:39.828792 Table of lock waited on : ORDERS Schema of lock waited on : DB2INST1 Tablespace of lock waited on : USERSPACE1 Type of lock: Row Mode of lock: NS - Share (and Next Key Share) Mode application requested on lock: X - Exclusive Node lock occured on: 0 Lock object name: 782 Application Handle: 298 Deadlocked Statement: Type : Dynamic Operation: Execute Section : 34 Creator : NULLID Package : SYSSN300 Cursor : SQL_CURSN300C34 Cursor was blocking: FALSE Text : UPDATE ORDERS SET TOTALTAX = ?, TOTALSHIPPING = ?, LOCKED = ?, TOTALTAXSHIPPING = ?, STATUS = ?, FIELD2 = ?, TIMEPLACED = ?, FIELD3 = ?, CURRENCY = ?, SEQUENCE = ?, TOTALADJUSTMENT = ?, ORMORDER = ?, SHIPASCOMPLETE = ?, PROVIDERORDERNUM = ?, TOTALPRODUCT = ?, DESCRIPTION = ?, MEMBER_ID = ?, ORGENTITY_ID = ?, FIELD1 = ?, STOREENT_ID = ?, ORDCHNLTYP_ID = ?, ADDRESS_ID = ?, LASTUPDATE = ?, COMMENTS = ?, NOTIFICATIONID = ? WHERE ORDERS_ID = ? List of Locks: Lock Name : 0x000201350000030E0000000052 Lock Attributes : 0x00000000 Release Flags : 0x40000000 Lock Count : 2 Hold Count : 0 Lock Object Name : 782 Object Type : Row Tablespace Name : USERSPACE1 Table Schema : DB2INST1 Table Name : ORDERS Mode : X - Exclusive Lock Name : 0x00020040000029B30000000052 Lock Attributes : 0x00000020 Release Flags : 0x40000000 Lock Count : 1 Hold Count : 0 Lock Object Name : 10675 Object Type : Row Tablespace Name : USERSPACE1 Table Schema : DB2INST1 Table Name : BKORDITEM Mode : X - Exclusive（略去后面信息）