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

4.1 快照监控方式
当使用快照方式进行锁的监控前，必须把监控锁的开关打开，可以从实例级别和会话级别打开，具体命令如下：
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监控应用程序锁等待的信息