Lock tree

Home > Select Project > Analysis > Lock Tree

This tool analyzes the trend of locks that occurred during the day. This function allows you to visually check the tree structure of the session that generated the lock (lock holder) and the session waiting for the lock (lock waiter). You can analyze the relationship between the holder and waiter of the lock that occurred at a specific time point. Through the WhaTap's Lock Tree feature, the DB administrator can identify and resolve the DB lock issues and optimize the DB performance.

Key features are as follows:

• Lock trend analysis: The trends of locks that occurred during the set search period are traced over time and displayed in a graph. This allows you to visually check how the locks are created and resolved.

• Check lock holder and waiter: You can see the holder and waiter for each lock in a tree structure. Through this, when a specific lock occurs, you can check the relationship between the session that generated the lock and the waiting session.

• Inter-session relationship analysis: By analyzing the relationship between the session that generated the lock and the waiting session, you can identify the cause of the lock that occurred during the query execution and transaction handling.

• Visual lock trend by time: Through the 1-Min Lock Trends and Lock Wait Sessions features, the lock occurrence data can be analyzed in time zones.

Basic usage guide

1. Select the date to view and set the time in Time at the top of the screen.

2. Select an instance to monitor.

You can check the lock that occurred during the set time on the selected instance.

If any lock occurs during the set period, you can analyze whether locks are concentrated in a specific time period or whether a specific session continuously causes locks in the Lock Wait Sessions graph and the table at the bottom.

Note

• You can see the data in 5-second increments by dragging within 3 hours on the chart. However, the data in 5-second increments can only be viewed within the most recent month. The data earlier than one month can only be viewed as 5-minute summary data. For example, you can drag (drill down) lock tree data from 40 days ago for the 5-minute summary data, but you cannot see 5-second data.

Lock Wait Sessions

The Lock Wait Sessions feature allows you to analyze the lock wait sessions. Depending on the query period, the lock wait session data is provided at 5-minute intervals when the query is for 3 hours or more, and at 5-second intervals when the query is for less than 3 hours. You can also adjust the time range by dragging the chart flexibly.

1-Min Lock Trends

If you click a specific time zone in Lock Wait Sessions, the lock occurrence status for 30 minutes, from 15 minutes before to 15 minutes after is visualized. This allows you to effectively analyze the lock occurrence trend at 30-minute intervals.

Note

Total Locks indicates the total number of locks generated in the time zone.

Viewing time

1-Min Lock Trends and Lock Tree tables are timed based on the maximum number of locks that occurred at the clicked time in Lock Wait Sessions. This allows you to find the zone with the most locks.

If you click the bar of a different time zone in the 1-Min Lock Trends chart with the Lock Wait Sessions unchanged, the time range is synced to the Lock Tree table by using the seconds with the highest number of locks for the minute. This allows you to see the information reflected in the Lock Tree table when changing the time zone.

• Viewing for 3 hours or more (using 5-minute data)

  The bar color of the 5-minute zone from the time of inquiry appears more prominent.

  When the 07:15:00 time point is clicked, the color of the 07:15:00-07:20:00 zone's bar chart appears more prominent.

  

• Viewing for less than 3 hours (using 5-minute data)

  It is useful for analyzing the locks that occur within short time intervals.

  

Column information guide

Details for each session are provided in the following columns:

Item	Description
event	Name of the waiting event.
holder type	Type of the session that owns a lock.
id1	First identifier value.
id2	Second identifier value.
lock mode	Lock mode requested or owned by the current session.
lock request	Status of the lock requested by the session.
module	Name of the running module.
pid	PID of the database process.
prev_sql_id	Unique ID of the previously executed SQL statement.
prev_sql_param	Parameter of the previously executed SQL.
prev_sql_text	Text of the previously executed SQL statement.
prog_name	Name of the program running the session.
serial#	Serial number that distinguishes repeated attempts for the same session.
sid	Session ID that uniquely identifies the session.
sql_et	SQL execution time.
sql_id	Unique ID of the SQL statement being executed.
sql_param	Parameters used to execute SQL.
sql_text	Text of the SQL statement being executed.
status	Status of the session.
username	Name of the user running the session.
waiter type	Type of the session waiting for a lock.

Note

WhaTap basically stores the client-related information.