Yesterday, I was able to take two snapshots while the tellers screens were experiencing very slow response time. The snapshots show no lock escalations taking place during the slowdown. The first snapshot (for database,locks, and applications) was for 5-seconds, and another one was taken after another 15 seconds. In both snapshots one application has an exclusive lock on a table, and nine other appls show lock- wait status on that table. I have pasted some of the snapshot info below and I have several questions for the board.
If this application (handle 67 below) is in lock-wait status why does time appl waited on locks and time UOW waited on locks is both zero? Also the status change time on all the applications in lock-wait is Locks is 12-16-2002 16:08 - almost 24 hours earlier. I don't think it is likely that these applications have been in lock-wait status for that long (since this is an OLTP environment). What could be the reason that the snapshot would show a status change time that is not accurrate? Also, would creating an event monitor for transactions help in a case like this (10-30 second workstation response times)? Snapshot time: Tue Dec 17 13:45:07 CST 2002 Snapshot of one (of 9) appl in lock-wait status: Application handle = 62 Application ID = *LOCAL.db2inst1.021122121907 Sequence number = 0001 Application name = d1s_ Authorization ID = DB2INST1 Application status = Lock-wait Status change time = 12-16-2002 16:08:46.785765 Application code page = 819 Locks held = 2 Total wait time (ms) = 0 Subsection waiting for lock = 0 ID of agent holding lock = 203 Application ID holding lock = 822001CC.11C0.021217194123 Node lock wait occurred on = 0 Lock object type = Row Lock mode = Exclusive Lock (X) Lock mode requested = Update Lock (U) Name of tablespace holding lock = USERSPACE1 Name of table holding lock = O1RCD Lock wait start timestamp = Not Collected Lock is a result of escalation = NO Snapshot of application holding the X lock: Application handle = 203 Application ID = 822001CC.11C0.021217194123 Sequence number = 0001 Application name = aspnet_wp.exe Authorization ID = XPDB Application status = UOW Waiting Status change time = Application code page = 819 Locks held = 5 Total wait time (ms) = 0 List Of Locks Lock Object Name = 1136132352 Node number lock is held at = 0 Object Type = Row Tablespace Name = USERSPACE1 Table Schema = DB2INST1 Table Name = O1RCD Mode = X Status = Granted Lock Escalation = NO Lock Object Name = 4360 Node number lock is held at = 0 Object Type = Table Tablespace Name = USERSPACE1 Table Schema = DB2INST1 Table Name = O1RCD Mode = IX Status = Granted Lock Escalation = NO Lock Object Name = 0 Node number lock is held at = 0 Object Type = Internal P Lock Tablespace Name = Table Schema = Table Name = Mode = S Status = Granted Lock Escalation = NO Lock Object Name = 5120 Node number lock is held at = 0 Object Type = Row Tablespace Name = SYSCATSPACE Table Schema = SYSIBM Table Name = SYSPROCEDURES Mode = NS Status = Granted Lock Escalation = NO Lock Object Name = 39 Node number lock is held at = 0 Object Type = Table Tablespace Name = SYSCATSPACE Table Schema = SYSIBM Table Name = SYSPROCEDURES Mode = IS Status = Granted Lock Escalation = NO --- "Henry J. Cobb" <[EMAIL PROTECTED]> wrote: > > Background info: We are runnin DB2 EE v7 on an RS/6000 with AIX. > The > > DB server is used for banking transactions and the DB size is 20 > GB. > > The problem we are experiencing is that multiple teller > workstations > > are hour glassing for about 20 seconds. This occurres > sporadically, > > sometimes 5 to 10 times a day. We are spending time trying to > > isolate which transactions are being attempted when this occurrs. > > Are you running any real time reports on this database and if so > can these > be moved to a different machine where the data they need is > replicated? > > > LOCKLIST is set at 200 and MAXLOCKS is set at 10. > > That locklist is fairly small, how much memory do you have to throw > at this > database? > > On the other hand it means that you are touching quite a few pages > in a > single transaction. Are there any read-only queries that you can > shift to > uncommitted reads so they don't set locks? > > Also, if it's just one program that's causing a lock escalation why > not set > table locks at the start of its run so it doesn't escalate? > > Do you actually get deadlocks and rollbacks or just escalations? > > -HJC > > - > ::: When replying to the list, please use 'Reply-All' and make > sure > ::: a copy goes to the list ([EMAIL PROTECTED]). > *** To unsubscribe, send 'unsubscribe' to > [EMAIL PROTECTED] > *** For more information, check http://www.db2eug.uni.cc __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
