db2 update dbm cfg using dft_mon_lock on(實例級別) db2 update monitor switches using lock on(會話級別，推薦使用) 當開關打開后，可以執行下列命令來進行鎖的監控 db2 get snapshot for locks on ebankdb（可以得到當前數據庫中具體鎖的詳細信息） db2 get snapshot for locks on ebankdb Fri Aug 15 15:26:00 JiNan 2004（紅色為鎖的關鍵信息）
Database Lock Snapshot Database name = DEV Database path = /db2/DEV/db2dev/NODE0000/SQL00001/ Input database alias = DEV Locks held = 49 Applications currently connected = 38 Agents currently waiting on locks = 6 Snapshot timestamp = 08-15-2003 15:26:00.951134 Application handle = 6 Application ID = *LOCAL.db2dev.030815021007 Sequence number = 0001 Application name = disp+work Authorization ID = SAPR3 Application status = UOW Waiting Status change time = Application code page = 819 Locks held = 0 Total wait time (ms) = 0 Application handle = 97 Application ID = *LOCAL.db2dev.030815060819 Sequence number = 0001 Application name = tp Authorization ID = SAPR3 Application status = Lock-wait Status change time = 08-15-2003 15:08:20.302352 Application code page = 819 Locks held = 6 Total wait time (ms) = 1060648 Subsection waiting for lock = 0 ID of agent holding lock = 100 Application ID holding lock = *LOCAL.db2dev.030815061638 Node lock wait occurred on = 0 Lock object type = Row Lock mode = Exclusive Lock (X) Lock mode requested = Exclusive Lock (X) Name of tablespace holding lock = PSAPBTABD Schema of table holding lock = SAPR3 Name of table holding lock = TPLOGNAMES Lock wait start timestamp = 08-15-2003 15:08:20.302356 Lock is a result of escalation = NO List Of Locks Lock Object Name = 29204 Node number lock is held at = 0 Object Type = Table Tablespace Name = PSAPBTABD Table Schema = SAPR3 Table Name = TPLOGNAMES Mode = IX Status = Granted Lock Escalation = NO
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 select * from table(snapshot_lock('DBNAME',-1)) as locktable監控鎖信息 db2 select * 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（略去后面信息）
/* showlock.sql */ column o_name format a10 column lock_type format a20 column object_name format a15 select rpad(oracle_username,10) o_name,session_id sid, decode(locked_mode,0,'None',1,'Null',2,'Row share', 3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type, object_name ,xidusn,xidslot,xidsqn from v$locked_object,all_objects where v$locked_object.object_id=all_objects.object_id; 5.3.2 showalllock.sql
第二個腳本showalllock.sql，該腳本主要顯示當前所有TM、TX鎖的信息；
/* showalllock.sql */ select sid,type,id1,id2, decode(lmode,0,'None',1,'Null',2,'Row share', 3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,request,ctime,block from v$lock where TYPE IN('TX','TM'); 6 DB2 多粒度封鎖機制示例 以下示例均運行在DB2 UDB中，適用所有數據庫版本。首先打開三個命令行窗口(DB2 CLP)，其中兩個（以下用SESS#1、SESS#2表示）以db2admin用戶連入數據庫，以操作SAMPLE庫中提供的示例表（employee）；另一個（以下用SESS#3表示）以db2admin用戶連入數據庫，對執行的每一種類型的SQL語句監控加鎖的情況；希望讀者通過這種方式對每一種類型的SQL語句監控加鎖的情況。(因為示例篇幅很大，筆者在此就不做了，建議讀者用類似方法驗證加鎖情況)
/home/db2inst1>db2 +c update employee set comm=9999(SESS#1) /home/db2inst1>db2 +c select * from employee(SESS#2處于lock wait) /home/db2inst1>db2 +c get snapshot for locks on sample(SESS#3監控加鎖情況)
