Recent business activities
The database administrator is able to identify that it is a lock wait problem. However why would the system suddenly encounter this problem while it has been performing well? What has changed to trigger this problem? The database administrator contacts the production team and finds out that a sales promotion has driven workload to unprecedented levels.
Answers to the questions that the database administrator had :
- Q: What is the general type of problem?
A: This is a lock wait problem.
- Q: What part of the user workload is impacted by the slowdown? What does this set of SQL statements have in common?
A: The problem is localized to statements accessing the INVENTORY
table. Once the AGENT_ID
is identified, 'LIST APPLICATIONS'
command can be used to identify the application name. Alternatively, 'SYSIBMADM.APPLICATIONS'
administrative view can be used to get the application name. Looking for the AGENT_ID
in 'LIST APPLICATIONS'
command output shows that these statements are mostly or exclusively from the Purchase item application.
- Q: What is the detailed description of the lock wait problem?
A: The slowdown occurs during the part of the transaction that accesses the INVENTORY
table because the transaction waits for locks to be released.
- Q: What sequence of events led to this problem occurring at this time?
A: A sale was advertised, causing a sharp increase in transaction volume. This increase in transaction volume led to more locks being requested for rows on the INVENTORY
table. The increase in the number of locks led to application requests spending more time waiting for locks to be released. The increased lock wait time produced experiencing slower response time for users.